Key takeaways
- search indexing in AlloyDB and Cloud SQL for PostgreSQL 17+ using the open-source pg_textsearch extension.
- The extension implements inverse document frequency, term frequency saturation, and document length normalization, fixing long-standing relevance limitations in PostgreSQL’s native ts_rank.
- Native keyword indexing enables unified hybrid retrieval alongside vector embeddings, removing the requirement to maintain external search clusters like Elasticsearch.
- AlloyDB automates result merging through an out-of-the-box hybrid search user-defined function, whereas Cloud SQL requires manual orchestration using Common Table Expressions and Reciprocal Rank Fusion.
Google Cloud has launched a public preview of native BM25 search indexing across AlloyDB for PostgreSQL and Cloud SQL for PostgreSQL 17+. The capability integrates the open-source pg_textsearch extension developed by TigerData directly into Google Cloud’s managed database engines. By embedding Best Matching 25 (BM25) ranking directly into database tables, Google enables development teams to execute industry-standard full-text keyword retrieval alongside vector embeddings. This integrated architecture removes the operational necessity of running external search platforms, such as Elasticsearch or OpenSearch, solely to handle keyword precision in hybrid search and retrieval-augmented generation (RAG) pipelines.
Integrated BM25 indexing eliminates external search clusters for PostgreSQL
Google Cloud announced that AlloyDB and Cloud SQL instances running PostgreSQL 17 or higher can now build native BM25 indexes using the pg_textsearch extension. Traditionally, teams deploying generative AI applications discovered that vector similarity search struggled with precise alphanumeric strings, specific part numbers, and exact product SKUs. Achieving reliable retrieval required pairing semantic vectors with lexical search in a hybrid architecture.
Historically, achieving production-grade keyword relevance forced engineering teams to export relational data into independent search engines. That dual-backend model created several persistent architectural liabilities:
- Continuous data synchronization lag between the transactional database and the search index.
- Fragile extract, transform, and load (ETL) data pipelines requiring dedicated monitoring.
- Increased cloud infrastructure expenditure to provision, size, and maintain separate search clusters.
- Split security boundaries and access controls across disconnected systems.
With pg_textsearch embedded into PostgreSQL, the index resides on standard PostgreSQL data pages. Database administrators can define BM25 indexes on existing text columns with standard SQL commands, allowing transactional writes to immediately reflect in search results without synchronization delay.
Overcoming ts_rank limitations with TigerData’s pg_textsearch engine
PostgreSQL has long included full-text search capabilities through its built-in tsvector and ts_rank functions. However, platform engineers frequently encountered severe ranking quality degradation when attempting to use ts_rank at scale. The legacy mechanism relies primarily on term frequency counts without factoring in global document distribution.
TigerData designed pg_textsearch as a C-based PostgreSQL extension that brings rigorous information retrieval mathematics to standard relational tables. It corrects three fundamental structural deficiencies present in ts_rank:
- Inverse Document Frequency (IDF): Common words across the corpus receive lower relevance weights, while rare, highly descriptive terms receive higher priority.
- Term Frequency Saturation: Repeating a keyword dozens of times yields diminishing relevance returns, preventing repetitive documents from artificially dominating query results.
- Document Length Normalization: Scores adjust according to document length, ensuring short, focused descriptions compete fairly against verbose articles.
Under the hood, pg_textsearch implements inverted indexes with Block-Max WAND (Weak AND) optimizations to skip non-competitive documents during top-k evaluation. Queries use PostgreSQL’s native <@> operator, where more negative numerical scores represent closer textual matches.
| Architecture Approach | Ranking Algorithm | Corpus Awareness (IDF) | Synchronization Delay | Infrastructure Footprint |
|---|---|---|---|---|
Standard PostgreSQL (ts_rank) |
Term occurrence counting | No | None (in-engine) | Single database instance |
| External Search Cluster (e.g., Elasticsearch) | BM25 | Yes | Asynchronous ETL lag | Separate cluster, ETL connectors |
Native pg_textsearch (AlloyDB / Cloud SQL) |
BM25 with Block-Max WAND | Yes | None (in-engine) | Single database instance |
Hybrid search execution: AlloyDB UDFs versus Cloud SQL manual fusion
The operational experience of deploying hybrid search diverges between AlloyDB and Cloud SQL. In AlloyDB, Google provides a managed hybrid search user-defined function (UDF) through the google_ml_integration extension. The ai.hybrid_search function automatically dispatches vector similarity queries alongside BM25 text queries and unifies the ranked outputs using Reciprocal Rank Fusion (RRF).
AlloyDB also features Google’s proprietary ScaNN (Scalable Nearest Neighbors) index, which Google claims delivers between 6x and 10x faster vector query performance than standard PostgreSQL implementations. When combined with BM25, applications can query high-dimensional embeddings for conceptual meaning while simultaneously filtering against exact terms like geographic locations or product identifiers.
In contrast, Cloud SQL users must orchestrate hybrid retrieval manually using SQL Common Table Expressions (CTEs) and pgvector HNSW or IVFFlat indexes. Engineers query the BM25 index and the vector index in separate CTE blocks, join the result sets on primary keys with a full outer join, and compute the mathematical RRF formula directly in the outer query. While functionally equivalent, Cloud SQL requires developers to maintain their own scoring formulas and ranking logic.
This streamlined data architecture is particularly relevant as enterprises build autonomous workflow tools. Initiatives like Salesforce expanding its Google Cloud integrations for autonomous AI agents highlight the enterprise demand for direct, low-latency access to operational databases without intermediary search caches.
What happens next
The native BM25 index is currently available in public preview for AlloyDB and Cloud SQL customers running PostgreSQL 17 or higher. Organizations running older database versions, such as PostgreSQL 15 or 16, must complete a major version database upgrade before enabling the pg_textsearch extension.
Google has not yet announced a timeline for general availability or detailed performance benchmarks comparing resource consumption against external clusters under heavy concurrent write traffic. Database administrators considering this feature should evaluate index build times and memory overhead on representative production datasets before retiring standalone search infrastructure.
