Create and manage a BM25 index

This document shows you how to create BM25 (Best Matching 25) indexes to optimize full-text search in AlloyDB for PostgreSQL. It provides examples for common use cases, including ranking searches, configuring saturation parameters, and tuning normalization weights.

BM25 is a probabilistic ranking algorithm widely used to estimate how relevant a document is to a given query. It evaluates Term Frequency (TF), Inverse Document Frequency (IDF), and Document Length Normalization to offer more accurate search rankings than standard text search.

Before you begin

To use a BM25 index, you must enable the pg_textsearch extension and meet the following requirements:

Enable the pg_textsearch extension

You must enable the pg_textsearch extension for each database:

  1. Connect to your AlloyDB database using psql or another client. For more information, see Connect to a cluster instance.

  2. Run the following SQL command to create the extension:

    CREATE EXTENSION IF NOT EXISTS pg_textsearch;

Create a BM25 index

The following example creates a table named documents with a content column to index text data for BM25 similarity queries.

  1. Create a table named documents:

    CREATE TABLE documents (
  id SERIAL PRIMARY KEY,
  title TEXT NOT NULL,
  content TEXT NOT NULL
);

  2. Populate the table with sample data:

    INSERT INTO documents (title, content) VALUES
  ('Database systems', 'AlloyDB is a fully managed PostgreSQL-compatible database service'),
  ('Google Cloud FTS', 'Full-text search lets you identify natural-language documents'),
  ('Probabilistic Ranking', 'BM25 uses term frequency and document length normalization');

  3. Create a BM25 index on the content column:

    CREATE INDEX idx_docs_bm25
ON documents
USING bm25 (content)
WITH (text_config = 'english');

The index supports three parameters in its WITH clause:

  • text_config (Required): The PostgreSQL text search configuration to use (for example, english).
  • k1 (Optional): The term frequency saturation parameter. The default value is 1.2.
  • b (Optional): The document length normalization parameter. The default value is 0.75.

Query using a BM25 index

To perform relevance ranking against a BM25 index, use the <@> operator.

The <@> operator returns a negative BM25 score. This is because PostgreSQL only supports ascending (ASC) index scans on operators. A lower (more negative) score indicates a stronger relevance match.

Run a search query sorted by BM25 score in ascending order:

SELECT title, content, content <@> 'database system' AS score
FROM documents
ORDER BY content <@> 'database system' ASC
LIMIT 5;

The output shows the highly relevant document at the top with the lowest negative score:

      title       |                            content                                   |  score
------------------+----------------------------------------------------------------------+----------
 Database systems | AlloyDB is a fully managed PostgreSQL-compatible database service    | -0.9971461892127991
 Google Cloud FTS | Full-text search lets you identify natural-language documents        | 0
 Probabilistic ranking | BM25 uses term frequency and document length normalization      | 0
(3 rows)

Tune BM25 index parameters

You can adjust parameters to optimize ranking for different types of document collections.

  • Increase k1: If you want query terms repeated multiple times to consistently increase a document's score.
  • Increase b: If you want longer documents to be penalized more heavily for including miscellaneous terms.

To create an index customized for short documents prioritizing term frequency, set k1 to 1.5 and b to 0.8:

CREATE INDEX idx_docs_bm25_tuned
ON documents
USING bm25 (content)
WITH (text_config = 'english', k1 = 1.5, b = 0.8);

What's next