SQL Reference & Comparison

SQL Function Reference (bm25_catalog)

All pg_bestmatch functions reside in the bm25_catalog schema.

bm25_create

Creates BM25 corpus statistics for a specified table and text column.

bm25_create(
    table_name     regclass,
    column_name    text,
    statistic_name text,
    b              real DEFAULT 0.75,
    k              real DEFAULT 1.2
) RETURNS void

• table_name: Target table containing the text documents.
• column_name: Name of the text column to analyze.
• statistic_name: Unique identifier for this BM25 statistic set (must be a valid SQL identifier).
• b: BM25 document-length normalization factor (default 0.75).
• k: BM25 term-frequency saturation parameter $k_1$ (default 1.2).

bm25_refresh

Refreshes the materialized corpus statistics for statistic_name after document insertions, updates, or deletions.

bm25_refresh(
    statistic_name text
) RETURNS void

bm25_drop

Drops all internal tables, materialized views, and triggers associated with statistic_name.

bm25_drop(
    statistic_name text
) RETURNS void

bm25_document_to_svector

Converts a document string into a BM25 term-frequency sparse vector representation (text) formatted for either pgvecto.rs (svector) or pgvector (sparsevec).

bm25_document_to_svector(
    statistic_name text,
    passage        text,
    style          text DEFAULT 'pgvecto.rs'
) RETURNS text

• statistic_name: Name of the registered statistic set.
• passage: Input document text.
• style: Output format — 'pgvector' (1-based indices) or 'pgvecto.rs' (0-based indices).

bm25_query_to_svector

Converts a search query string into an IDF-weighted sparse vector representation (text).

bm25_query_to_svector(
    statistic_name text,
    query          text,
    style          text DEFAULT 'pgvecto.rs'
) RETURNS text

tokenize

Tokenizes an input string into an array of WordPiece subword tokens using the built-in bert-base-uncased vocabulary.

tokenize(
    input text
) RETURNS text[]

Comparison: pg_bestmatch vs vchord_bm25

Both pg_bestmatch and vchord_bm25 were created by the TensorChord team to bring Okapi BM25 ranking into PostgreSQL, but they use fundamentally different architectures: