Nova BM25 quick start - AnalyticDB - Alibaba Cloud Documentation Center

BM25 is a classic relevance ranking algorithm in information retrieval. It measures the match between keywords and documents based on term frequency, document length, and inverse document frequency, and serves as the default ranking foundation for search engines such as Elasticsearch. Nova BM25 integrates this capability into AnalyticDB for PostgreSQL as a native index. You only need to create a nova_bm25 index on text columns to perform Chinese tokenization, keyword recall, phrase matching, and relevance ranking in standard SQL. You can also combine BM25 search with structured conditions such as categories, numeric values, and timestamps, as well as vector retrieval, without the need for an external search system. This topic describes how to install the Nova BM25 extension in AnalyticDB for PostgreSQL and complete your first Chinese full-text search example. After completing the procedures in this topic, you will be able to write full-text search SQL queries that rank results by relevance.

The Nova BM25 SQL functions are located under the bm25 schema, and the index access method name is nova_bm25.

Prerequisites

The AnalyticDB for PostgreSQL instance runs version 7.5.1.0 or later, and vector search engine optimization is enabled.
The nova_bm25 plugin is not yet available for self-service installation from the console. To use this plugin, submit a ticket and contact technical support for assistance with instance upgrade and installation.

Procedure

Step 1: Prepare sample data

Create a sample table and insert test data for the subsequent full-text search operations.

DROP TABLE IF EXISTS docs;

CREATE TABLE docs (
    id         BIGINT PRIMARY KEY,
    title      TEXT,
    body       TEXT,
    category   TEXT,
    rating     INT,
    publish_at TIMESTAMP
) DISTRIBUTED BY (id);

INSERT INTO docs(id, title, body, category, rating, publish_at) VALUES
    (1, 'Database System Design', 'PostgreSQL supports full-text search and transaction processing', 'tech', 5, '2024-01-01 10:00:00'),
    (2, 'Search Engine Development', 'BM25 ranking is used for full-text search relevance computation', 'tech', 5, '2024-02-01 10:00:00'),
    (3, 'Vector Search Practice', 'Hybrid search combines vector recall and keyword recall', 'tech', 4, '2024-03-01 10:00:00'),
    (4, 'User Profile Analysis', 'Real-time profile processing for recommendation systems', 'biz', 3, '2024-04-01 10:00:00'),
    (5, 'Punctuation Test', 'Database, full-text search; query optimization! BM25 ranking.', 'tech', 4, '2024-05-01 10:00:00');

You do not need to use the column names shown in this example for your own tables. Simply include the text columns that require full-text search, along with any category, numeric, or timestamp columns you want to use for filtering, in the BM25 index.

Step 2: Create a Chinese BM25 index

Create a BM25 index on the sample table and configure different tokenizers and field types based on the purpose of each field.

CREATE INDEX docs_bm25_idx ON docs
USING nova_bm25 (body, title, category, rating, publish_at)
WITH (
    text_fields = '{
      "body": {"tokenizer": {"type": "jieba"}},
      "title": {"tokenizer": {"type": "jieba"}},
      "category": {"tokenizer": {"type": "keyword"}}
    }',
    numeric_fields = '{"rating": {}}',
    datetime_fields = '{"publish_at": {}}'
);

The preceding WITH clause configures each field based on its purpose. The tokenizer.type in text_fields specifies the tokenizer used for each text field:

For Chinese text columns that require full-text search, such as body and title, configure them in text_fields and use the jieba tokenizer.
For text columns that require exact matching, such as category, status, tenant, or enum values, also configure them in text_fields, but use the keyword tokenizer to match the entire value without tokenization.
For numeric columns, such as rating or price, configure them in numeric_fields.
For timestamp columns, such as publish time or update time, configure them in datetime_fields.

Step 3: Run your first query

Use the following SQL statement to perform a BM25 full-text search on the body field. The query text is database full-text search. The bm25.match function automatically tokenizes the input based on the field tokenizer. By default, a document is returned if it matches any token, and results are ranked by BM25 score.

SELECT
    id,
    title,
    bm25.score(docs) AS score
FROM docs
WHERE body @@@ bm25.match('database full-text search')
ORDER BY bm25.score(docs) DESC
LIMIT 10;

The following list describes the key components of the preceding SQL statement:

body @@@ bm25.match(...): performs a BM25 full-text search on the body field.
bm25.score(docs): returns the BM25 relevance score. A higher score indicates greater relevance.
ORDER BY bm25.score(docs) DESC: sorts results by relevance score in descending order, with the most relevant results first.

The following table shows a sample query result. The exact score values may vary slightly depending on the version and tokenizer configuration:

id	title	score
2	Search Engine Development	...
5	Punctuation Test	...
1	Database System Design	...
3	Vector Search Practice	...

In practice, you only need to pass your search text to bm25.match to perform a full-text search.

Common query patterns

AND matching (all tokens must match)

By default, bm25.match returns a document if it matches any token. To require all tokens to match, set operator => 'and':

SELECT id, title, bm25.score(docs) AS score
FROM docs
WHERE body @@@ bm25.match('database full-text search', operator => 'and')
ORDER BY bm25.score(docs) DESC
LIMIT 10;

You can also use the shorthand operator &&&, which produces the same result:

SELECT id, title, bm25.score(docs) AS score
FROM docs
WHERE body &&& 'database full-text search'
ORDER BY bm25.score(docs) DESC
LIMIT 10;

Phrase matching

To search for a complete phrase such as full-text search, where the tokens must appear adjacent to each other in the original order, use bm25.phrase.

SELECT id, title, bm25.score(docs) AS score
FROM docs
WHERE body @@@ bm25.phrase('full-text search')
ORDER BY bm25.score(docs) DESC
LIMIT 10;

Structured filtering

BM25 full-text search can be combined with standard SQL conditions to filter by category, rating, time, and other structured fields.

SELECT id, title, bm25.score(docs) AS score
FROM docs
WHERE body @@@ bm25.match('database search')
  AND category = 'tech'
  AND rating >= 4
  AND publish_at >= '2024-01-01'
ORDER BY bm25.score(docs) DESC
LIMIT 10;

Multi-field weighted query

When a match in the title field is more important than a match in the body, you can assign a higher weight to the title field.

SELECT id, title, bm25.score(docs) AS score
FROM docs
WHERE body @@@ bm25.multi_match(
    ARRAY['title^3', 'body'],
    query => 'database full-text search'
)
ORDER BY bm25.score(docs) DESC
LIMIT 10;

In the preceding example, title^3 sets the title field weight to three times that of the body field. When no weight is specified for body, it defaults to 1.

Note

All fields included in a multi-field weighted query must belong to the same BM25 index, and weight values must be positive numbers.

Query visibility after writes

This example follows the order of creating a table, inserting data, creating an index, and then running queries. Data committed before the index is created is indexed during the CREATE INDEX operation. Therefore, all data in this example is immediately queryable after the index is created.

After the index is created, subsequent INSERT or UPDATE operations are incremental writes. Nova BM25 uses a near-real-time query mode by default. The latest data from incremental writes may require a brief background processing period before it appears in default query results.

If you need to query the latest incremental data immediately in the current session, run the following statement:

SET nova_bm25.query_include_mutable = on;

To restore the default setting when it is no longer needed:

RESET nova_bm25.query_include_mutable;

Note

This setting applies only to the current database connection. When you use a connection pool, connections may be reused. Configure this setting based on your business requirements and restore it promptly.

If an index always needs to include the latest incremental data, you can configure it by running the following statement:

ALTER INDEX docs_bm25_idx
SET (query_skip_mutable = false);

Important

Querying the latest incremental data requires additional processing of index data that has not yet been fully organized in the background, which may increase query latency and resource consumption.

Application-side parameter binding

To prevent SQL injection, do not concatenate user input directly into SQL strings. In JDBC, we recommend that you use PreparedStatement for parameter binding:

PreparedStatement ps = conn.prepareStatement("""
    SELECT id, title, bm25.score(docs) AS score
    FROM docs
    WHERE body @@@ bm25.match(?, operator => 'or')
    ORDER BY bm25.score(docs) DESC
    LIMIT ?
""");
ps.setString(1, userQuery);
ps.setInt(2, 10);

AnalyticDB:Nova BM25 quick start