Nova BM25 index and dictionary management - AnalyticDB - Alibaba Cloud Documentation Center

This topic describes how to create and manage Nova BM25 full-text search indexes and how to configure Chinese tokenization dictionaries. Nova BM25 is a high-performance full-text search solution provided by AnalyticDB for PostgreSQL. It is implemented based on the nova_bm25 extension. The SQL functions are located in the bm25 schema, and the index access method name is nova_bm25.

For a quick start guide, see Nova BM25 quick start. For the complete Function API reference, see Function API reference.

Create an index

Create a sample table

The following example creates a business table with multiple field types, including text, numeric, timestamp, and Boolean fields, as a reference for subsequent index creation:

CREATE TABLE docs (
    id         bigint PRIMARY KEY,
    title      text,
    body       text,
    category   text,
    rating     integer,
    publish_at timestamp,
    in_stock   boolean
) DISTRIBUTED BY (id);

Create a BM25 index

Create a Nova BM25 index on the sample table. Use the WITH clause to specify the appropriate index configuration for each field type. Correct field configuration directly affects retrieval accuracy and performance:

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

Index field configuration

When creating an index, you must specify the configuration for each field that participates in retrieval through the WITH clause. The following table describes the configuration parameters for each field type:

Parameter	Applicable field type	Description	Example
`text_fields`	text, varchar	Text fields. You must specify a tokenizer type to control tokenization.	`{"title": {"tokenizer": {"type": "jieba"}}}`
`numeric_fields`	integer, bigint, float, and others	Numeric fields. Supports range queries and numeric sorting.	`{"rating": {}}`
`datetime_fields`	timestamp, date	Timestamp fields. Supports time range queries.	`{"publish_at": {}}`
`boolean_fields`	boolean	Boolean fields. Supports true/false filtering.	`{"in_stock": {}}`
`json_fields`	json, jsonb	`JSON` fields. Supports indexing key-value pairs within JSON data.	`{"metadata": {}}`

Select field configurations

Selecting the correct configuration type for each field is essential for building an efficient full-text index. The configuration type determines the retrieval behavior and available query methods for a field. The following table lists common field purposes and their recommended configurations:

Field purpose	Configuration	Example
Chinese titles and body text	`text_fields`, using jieba	title, body
Whole-value fields such as category and status	`text_fields`, using keyword	category, status
Numeric fields such as rating and price	`numeric_fields`	rating, price
Timestamp fields such as publish time	`datetime_fields`	publish_at
Boolean fields such as in-stock status	`boolean_fields`	in_stock
JSON fields	`json_fields`	metadata

Tokenizer selection guide for text fields

Selecting the correct tokenizer for text fields directly affects retrieval results. The following table lists common tokenizer types and their applicable scenarios:

Tokenizer type	Applicable scenario	Description
`jieba`	Chinese text	Based on the Jieba tokenizer, supports Chinese semantic tokenization, and allows custom dictionaries.
`default`	English text	Splits text by spaces and punctuation, suitable for English and other Western languages.
`keyword`	Exact matching fields	No tokenization. Treats the entire field value as a single retrieval unit, suitable for categories, tags, status codes, and similar fields.
`ngram`	Fine-grained matching	Splits text using n-grams, supports partial matching and fuzzy retrieval, suitable for prefix or substring search scenarios.

Verify tokenization results

After creating an index, we recommend that you use the tokenization debug function to verify that the actual tokenization results meet your expectations. The following example shows how to view the tokenization results of the jieba tokenizer for the input text:

SELECT * FROM bm25.debug_tokenizer('cloud-native database', 'jieba');

Query incremental data

Nova BM25 supports a near-real-time query mode. Newly written or updated data can be retrieved after a brief delay. You can use the following parameters and commands to control the query behavior for incremental data, balancing data freshness and query performance.

Use the query_skip_mutable parameter to control whether to query the latest incremental data:

Parameter	Description
query_skip_mutable	Default value: `true`. Uses the default near-real-time query mode. When set to `false`, the latest incremental data is included, but query latency may increase.

Set this parameter when creating an index:

CREATE INDEX docs_bm25_idx ON docs
USING nova_bm25 (title, body)
WITH (text_fields = '{"title": {"tokenizer": {"type": "jieba"}}, "body": {"tokenizer": {"type": "jieba"}}}', query_skip_mutable = false);

Modify this parameter by using ALTER INDEX:

ALTER INDEX docs_bm25_idx SET (query_skip_mutable = false);
ALTER INDEX docs_bm25_idx SET (query_skip_mutable = true);

Control this behavior at the session level by using the SET command:

SET nova_bm25.query_include_mutable = on;
RESET nova_bm25.query_include_mutable;

Modify and rebuild indexes

After a Nova BM25 index is created, its field configurations such as tokenizer type and field mapping cannot be modified directly by using ALTER INDEX. To change the index configuration, you must drop the index and recreate it.

Drop and recreate the index to modify the configuration:

DROP INDEX docs_bm25_idx;
-- Recreate the index
CREATE INDEX docs_bm25_idx ON docs USING nova_bm25 (...) WITH (...);

If the index configuration does not need to be changed, but you need to clean up index fragments or refresh index data, you can use the REINDEX command to rebuild the index:

REINDEX INDEX docs_bm25_idx;

Chinese dictionary management

Nova BM25 uses the jieba tokenizer for Chinese text processing. To meet the retrieval requirements for specialized terms and synonyms in business scenarios, you can customize tokenization behavior through dictionary management.

Add custom terms

Add specialized terms or business vocabulary to the custom dictionary of the jieba tokenizer so that the tokenizer can correctly identify these terms and prevent them from being incorrectly split. After adding terms, you must reload the dictionary for the changes to take effect:

SELECT bm25.add_dict_word('jieba', 'product_terms', 'cloud-native database', 10000, 'n');
SELECT bm25.reload_dict('jieba', 'product_terms');

Configure synonyms

Configure a synonym dictionary to map multiple terms with similar meanings to the same retrieval results, thereby expanding search coverage and improving recall:

SELECT bm25.add_dict_word('synonym', 'product_synonyms', 'postgresql,postgres,pg');
SELECT bm25.reload_dict('synonym', 'product_synonyms');

Verify dictionary effectiveness

After adding custom terms or synonyms, we recommend that you use the tokenization debug function to verify that the dictionary configuration has taken effect and that terms are correctly identified:

SELECT * FROM bm25.debug_tokenizer('cloud-native database', 'jieba');

Routine maintenance

Performing maintenance operations regularly helps maintain the query performance and data consistency of Nova BM25 indexes. The following table lists common maintenance operations and their purposes:

Operation	Description
`VACUUM`	Reclaims space occupied by deleted data and optimizes table storage.
`REINDEX`	Rebuilds the index to eliminate fragmentation and restore query performance.
`DROP INDEX`	Drops indexes that are no longer in use to release storage space.

VACUUM docs;
REINDEX INDEX docs_bm25_idx;
DROP INDEX docs_bm25_idx;