AlloyDB and Cloud SQL Add Native BM25 Ranking for Full-Text Search

Search applications often need to find documents that match a user's exact words, not just content with a similar meaning. A product search should recognize a specific model number, a support system should find an exact error code, and an AI assistant should retrieve documents containing the terminology relevant to a question.

Vector search helps applications understand semantic similarity, but it does not always rank exact keyword matches effectively. Traditional PostgreSQL full-text search provides keyword matching, but its ranking behavior differs from modern relevance algorithms such as BM25.

Google Cloud introduced native BM25 indexing for AlloyDB and Cloud SQL for PostgreSQL 17 and later, using the open-source pg_textsearch extension developed by Tiger Data. Announced in September 2026, the capability is available in preview.

The main benefit is that developers can perform BM25-based full-text search directly inside their PostgreSQL-compatible database instead of maintaining a separate search engine solely for keyword ranking.

For applications that combine semantic search with exact keyword matching, this can simplify the architecture and reduce the operational work associated with synchronizing multiple search systems.

What Is BM25 Ranking?

BM25 stands for Best Matching 25. It is a ranking algorithm used in information retrieval to estimate how relevant a document is to a search query.

Unlike a basic keyword-matching approach, BM25 considers several factors:

• Term frequency: How often a query term appears in a document.

• Inverse document frequency: How rare the term is across the document collection.

• Document length normalization: How the length of a document affects its relevance score.

These factors help BM25 distinguish between documents that mention a keyword repeatedly and documents that contain a more meaningful match.

For example, suppose an online store contains thousands of product descriptions. A customer searches for cherry tree.

One product description might mention trees and gardening many times, while another describes a cherry tree specifically. BM25 can use term frequency, rarity, and document length to rank the more relevant description appropriately.

BM25 does not understand meaning in the same way that an embedding model does. It is a statistical ranking algorithm based on textual evidence. Its strength is relevance ranking for keyword-based queries.

Why Native BM25 Support Matters

Before native BM25 support, teams using AlloyDB or Cloud SQL often needed an additional search backend to obtain BM25 ranking. That architecture introduced another system to provision, monitor, secure, and synchronize with the primary database.

Native BM25 indexing changes that design by allowing keyword ranking to operate on data stored in the PostgreSQL database itself.

The native approach does not eliminate every operational concern. Index maintenance, query performance, database capacity, and access control still matter.

It also does not mean that dedicated search platforms are obsolete. Large-scale search workloads with specialized indexing, advanced filtering, or distributed retrieval requirements may still benefit from a separate search engine.

How to Enable BM25 in AlloyDB or Cloud SQL

The following examples illustrate the basic PostgreSQL workflow. The database must run a supported version, and the pg_textsearch extension must be available for the selected service and configuration.

Step 1: Enable the Extension

Connect to the target database and execute:

CREATE EXTENSION IF NOT EXISTS pg_textsearch;

This enables the BM25 functionality exposed by the extension, subject to the database service's supported extension configuration.

If the command fails, verify the database version, extension availability, and the privileges required to install it.

Step 2: Create a Table for Searchable Documents

Consider an application that stores product information.

CREATE TABLE products (
    product_id BIGINT PRIMARY KEY,
    product_name TEXT NOT NULL,
    product_description TEXT NOT NULL
);

The table contains a unique product identifier, a name, and a description.

In an existing application, the BM25 index can be created on an appropriate text column without redesigning the entire data model.

Step 3: Add Sample Data

Insert several products to demonstrate how the ranking query works.

INSERT INTO products (
    product_id,
    product_name,
    product_description
)
VALUES
(
    1,
    'Cherry Tree',
    'A cherry tree suitable for home gardens.'
),
(
    2,
    'Apple Tree',
    'An apple tree for outdoor gardens and orchards.'
),
(
    3,
    'Garden Soil',
    'Soil for growing fruit trees and flowering plants.'
);

These records are illustrative. Real search applications would typically index a much larger collection of product descriptions, articles, support tickets, or other documents.

Step 4: Create a BM25 Index

Create an index on the column that contains the searchable text.

CREATE INDEX products_description_bm25_idx
ON products
USING bm25 (product_description)
WITH (text_config = 'english');

The USING bm25 clause selects the BM25 index access method. The text_config option specifies the text-search configuration used to process the content.

The English configuration is suitable for the example, but multilingual applications should evaluate an appropriate configuration for their language and tokenization requirements.

Step 5: Search and Rank Results

Use the BM25 operator to calculate relevance scores and sort the results.

SELECT
    product_id,
    product_name,
    product_description,
    product_description <@> 'cherry tree' AS bm25_score
FROM products
ORDER BY bm25_score ASC
LIMIT 10;

The <@> operator performs BM25 ranking for the search text.

An important detail is that a lower, more negative BM25 score represents a stronger match in this implementation. Therefore, the query orders results in ascending score order.

This differs from many search APIs where higher relevance scores indicate better matches. Developers should preserve the correct sort direction when integrating the database query into an application.

How BM25 Improves on Basic PostgreSQL Ranking

PostgreSQL already provides full-text search capabilities, including tsvector, tsquery, and the ts_rank ranking function.

These tools remain useful, but their ranking behavior differs from BM25.

Neither approach is automatically the best choice for every workload.

For applications that already use ts_rank, evaluate BM25 against representative queries before migrating. Ranking quality depends on the dataset, language configuration, query patterns, and how relevance is measured by the application.

Combining BM25 With Vector Search

One of the most useful applications of native BM25 is hybrid search.

Vector search retrieves documents based on semantic similarity. BM25 retrieves documents using keyword relevance. Combining them helps applications handle both natural-language intent and exact terminology.

Consider a user asking:

Find trees that grow taller than houses in California.

A vector search may identify documents about tall trees, even if they do not use exactly the same wording as the query. A keyword search can emphasize the term California, which may be important for identifying the correct location-specific result.

Hybrid search combines both result sets to improve retrieval.

Vector Index for Semantic Search

For a table that already stores vector embeddings, a Cloud SQL PostgreSQL configuration can use the vector extension and an HNSW index.

CREATE EXTENSION IF NOT EXISTS vector;

CREATE INDEX products_embedding_hnsw_idx
ON products
USING hnsw (product_embedding vector_cosine_ops);

This example assumes that the table has a compatible product_embedding column of the appropriate vector type. The column and data model must be added separately if they do not already exist.

AlloyDB also supports its own vector indexing options, including ScaNN in supported configurations.

Merge Keyword and Vector Results

A common hybrid-search approach is Reciprocal Rank Fusion (RRF). Rather than directly combining scores that use different scales, RRF combines the positions of documents in their respective ranked lists.

A simplified RRF calculation is:

[
\operatorname{RRF}(d)=
\sum_{r \in R}
\frac{1}{k+\operatorname{rank}_r(d)}
]

Here, (d) is a document, (R) is the set of ranked result lists, and (k) is a smoothing constant.

Documents that rank well in multiple result lists receive a stronger combined score.

AlloyDB provides a supported hybrid-search function that can combine keyword and vector retrieval through RRF. Cloud SQL can implement the same general approach using common table expressions, ranked result sets, and SQL joins.

This makes it possible to build hybrid search without requiring a separate application-side ranking service.

AlloyDB and Cloud SQL: Important Differences

Although both services support native BM25 through pg_textsearch, their broader search capabilities differ.

The choice should depend on existing infrastructure, performance requirements, supported extensions, operational constraints, and cost.

Native BM25 is a ranking capability, not a complete replacement for every feature offered by a dedicated search engine.

Production Considerations and Common Mistakes

Treating Preview as General Availability

The native BM25 feature was announced in preview. Before using it for a critical production workload, verify its current release status, regional availability, supported database versions, and applicable service limitations.

Assuming BM25 Understands Semantic Meaning

BM25 is based on lexical relevance. It does not automatically recognize that two different expressions have the same meaning.

For example, a search for automobile repair may not rank a document about car maintenance highly unless the indexed text or query processing provides sufficient lexical overlap. Vector search can help address this limitation.

Ignoring Index Maintenance

Indexes consume storage and affect write operations. Measure index creation time, query latency, and the impact of data updates before expanding the index to large tables.

Using the Wrong Score Direction

For the documented <@> BM25 operator, more negative scores represent stronger matches. Ordering the results in descending order can return less relevant records first.

Combining Scores Without a Ranking Strategy

BM25 scores and vector distances use different scales. Adding them directly can produce misleading rankings.

Use a deliberate fusion strategy, such as RRF, or evaluate a properly calibrated scoring model.

Ignoring Language and Text Processing

Search quality depends on tokenization, language configuration, punctuation, identifiers, and the structure of the indexed text. Test with realistic queries rather than relying exclusively on simple English examples.

Best Practices for Adopting Native BM25

1. Start with representative search queries. Establish a baseline using real user searches and expected results.

2. Evaluate ranking quality. Compare BM25 with the existing ts_rank implementation or search backend.

3. Use the appropriate text configuration. Validate tokenization and language behavior for your dataset.

4. Measure index overhead. Track storage, write performance, index creation, and query latency.

5. Use hybrid search when necessary. Combine BM25 with vector retrieval when both exact terms and semantic intent matter.

6. Choose a deliberate ranking strategy. RRF is useful when combining result lists with different scoring systems.

7. Validate service compatibility. Confirm extension availability and database-version requirements.

8. Retain a fallback plan. For preview features, document how the application can continue operating if the feature's availability or behavior changes.

Conclusion

Google Cloud's native BM25 support in AlloyDB and Cloud SQL for PostgreSQL gives developers a way to perform relevance-ranked full-text search directly inside their database.

The pg_textsearch extension provides BM25 indexing and scoring, while the broader PostgreSQL ecosystem supports combining keyword retrieval with vector search. This can simplify hybrid-search architectures and reduce the need to maintain a separate backend solely for keyword ranking.

The feature is particularly relevant for product catalogs, documentation search, support systems, and retrieval-augmented generation applications that need both exact keyword matches and semantic understanding.

For developers evaluating the capability, the right starting point is a representative dataset, a realistic search benchmark, and a comparison with the existing ranking implementation. Native BM25 can simplify the architecture, but the quality of the final search experience still depends on indexing, query design, ranking strategy, and careful testing.