vchord_bm25

vchord_bm25 : A postgresql extension for bm25 ranking algorithm

Overview

Packages

Source

pig build pkg vchord_bm25;		# build rpm/deb

Install

Make sure PGDG and PIGSTY repo available:

pig repo add pgsql -u   # add both repo and update cache

Install this extension with pig:

pig install vchord_bm25;		# install via package name, for the active PG version

pig install vchord_bm25 -v 18;   # install for PG 18
pig install vchord_bm25 -v 17;   # install for PG 17
pig install vchord_bm25 -v 16;   # install for PG 16
pig install vchord_bm25 -v 15;   # install for PG 15
pig install vchord_bm25 -v 14;   # install for PG 14

Config this extension to shared_preload_libraries:

shared_preload_libraries = 'vchord_bm25';

Create this extension with:

CREATE EXTENSION vchord_bm25;

Usage

Sources:

• 0.3.0 README
• Control file
• 0.3.0 SQL objects
• Query settings
• 0.3.0 migration
• 0.3.0 release
• Tokenizer installation
• Tokenizer models

vchord_bm25 provides BM25 ranking with a sparse token-frequency type and the bm25 index access method. Tokenization is supplied separately, commonly by pg_tokenizer. Extension objects live in the fixed bm25_catalog schema, and creation requires superuser privileges.

Core Workflow

The example uses pg_tokenizer, which requires preloading and a restart. Preserve existing entries in the preload list:

shared_preload_libraries = 'pg_tokenizer'

CREATE EXTENSION pg_tokenizer;
CREATE EXTENSION vchord_bm25;
SET search_path = public, tokenizer_catalog, bm25_catalog;

SELECT create_tokenizer('english', $$
model = "bert_base_uncased"
$$);
CREATE TABLE documents (
    id bigserial PRIMARY KEY,
    passage text,
    embedding bm25vector
);
INSERT INTO documents(passage) VALUES ('PostgreSQL full text search');
UPDATE documents SET embedding = tokenize(passage, 'english')::bm25vector;
CREATE INDEX documents_bm25 ON documents USING bm25 (embedding bm25_ops);

SELECT id, passage,
       embedding <&> to_bm25query('documents_bm25',
           tokenize('PostgreSQL', 'english')::bm25vector) AS score
FROM documents
ORDER BY score
LIMIT 10;

The index supplies corpus statistics to to_bm25query; the score from <&> is negative, so ascending order returns greater relevance first. Use the same tokenizer/model for documents and queries. Update stored token vectors when source text changes, or use the tokenizer’s maintenance-trigger helper. Changing the vocabulary requires retokenizing stored documents before rebuilding their index.

Types, Functions, and Search Limits

• bm25vector stores token IDs and frequencies; the integer-array cast aggregates duplicate IDs and discards token order.
• bm25query binds the query vector to an index. to_bm25query(regclass, bm25vector) constructs it; bm25_ops is the index operator class.
• bm25_catalog.bm25_limit defaults to 100 and limits candidates returned by the index. Increase it for larger SQL limits or restrictive filters; changing SQL LIMIT alone does not increase this candidate budget.
• bm25_catalog.enable_index controls use of the index; bm25_catalog.enable_prefilter controls prefiltering. Both default to true.
• bm25_catalog.segment_growing_max_page_size defaults to 4096 pages before sealing a growing segment.

SET bm25_catalog.bm25_limit = 1000;

The access-method name is global: this extension cannot coexist in a database with another extension that creates the same bm25 access method, including pg_textsearch and the compatibility alias in pg_search. Sparse frequencies do not preserve positions for phrase matching. Chinese text can use a custom corpus model with a Jieba pre-tokenizer; Japanese Lindera support depends on the tokenizer build and dictionary configuration.

Upgrade to 0.3.0

ALTER EXTENSION vchord_bm25 UPDATE TO '0.3.0';

The 0.2.2-to-0.3.0 migration adds bm25_page_inspect(regclass, integer), returning diagnostic page text. The release changes sealed-segment page allocation for small tokens and does not document a mandatory index rebuild. Install matching extension files before updating database objects; replacing the preloaded tokenizer library also requires a restart. Keep tokenization and ranking upgrades compatible and check representative query results.