pg_bestmatch Documentation

• 1: Getting Started
• 2: Usage Guide
• 3: SQL Reference & Comparison

1 - Getting Started

How Does pg_bestmatch Work?

Given a document $D$ and a query $Q$ tokenized into vocabulary terms $q_1, q_2, \dots, q_n$, the Okapi BM25 relevance score is:

Notice that this sum is the inner product (dot product) of two sparse vectors over the vocabulary space $V$ (dimension $|V| = 30{,}522$ for bert-base-uncased):

1. Document Sparse Vector $\vec{v}_D$: For each unique token $t \in D$, store the normalized term-frequency weight: $$v_D[t] = \frac{f(t, D) \cdot (k_1 + 1)}{f(t, D) + k_1 \cdot \left(1 - b + b \cdot \frac{|D|}{\text{avgdl}}\right)}$$
2. Query Sparse Vector $\vec{v}_Q$: For each token $q_i \in Q$, store its inverse document frequency weight $\text{IDF}(q_i)$.

When you compute the inner product $\vec{v}_D \cdot \vec{v}_Q$ using pgvector or pgvecto.rs’s <#> operator, PostgreSQL returns $-\text{BM25}(D, Q)$ so that ORDER BY embedding <#> query_vec ASC returns the highest-scoring documents first!

Installation

Prerequisites

Install either pgvector (v0.7.0+ for sparsevec support) or pgvecto.rs (v0.3.0+ for svector support) first.

Install from Release Packages (Debian / Ubuntu)

Download the prebuilt .deb package matching your PostgreSQL major version from GitHub Releases:

# Example for PostgreSQL 16 on x86_64
wget https://github.com/tensorchord/pg_bestmatch.rs/releases/download/v0.0.1/pg-bestmatch-pg16_0.0.1_amd64.deb
sudo dpkg -i pg-bestmatch-pg16_0.0.1_amd64.deb

Build from Source (cargo pgrx)

Ensure Rust and cargo-pgrx (0.12.0-alpha.1 or compatible) are installed:

git clone https://github.com/tensorchord/pg_bestmatch.rs.git
cd pg_bestmatch.rs
cargo pgrx install --release

Enable the Extension

Connect to PostgreSQL, create the extension, and add bm25_catalog to your search_path:

CREATE EXTENSION IF NOT EXISTS pg_bestmatch;
CREATE EXTENSION IF NOT EXISTS vector; -- or vectors for pgvecto.rs

SET search_path TO "$user", public, bm25_catalog;

Test tokenization with a single SQL query:

SELECT tokenize('PostgreSQL BM25 full-text search with pg_bestmatch');
--                 tokenize
-- -----------------------------------------
--  {postgresql,bm,##25,full,-,text,search,with,pg,_,best,##mat,##ch}

Continue to the Usage Guide for a complete end-to-end indexing and query workflow.

2 - Usage Guide

This guide walks through a complete BM25 text retrieval pipeline using pg_bestmatch and pgvector (sparsevec), followed by the equivalent configuration for pgvecto.rs (svector).

1. Enable Extensions & Set Search Path

CREATE EXTENSION IF NOT EXISTS pg_bestmatch;
CREATE EXTENSION IF NOT EXISTS vector;

SET search_path TO "$user", public, bm25_catalog;

2. Create the Documents Table & Load Data

CREATE TABLE documents (
    id bigint PRIMARY KEY,
    passage text
);

INSERT INTO documents (id, passage) VALUES
  (1, 'PostgreSQL is an advanced open-source relational database system.'),
  (2, 'VectorChord brings disk-friendly RaBitQ vector search to PostgreSQL.'),
  (3, 'BM25 is a bag-of-words ranking function used in information retrieval.');

3. Create BM25 Corpus Statistics

Call bm25_create(table_name, column_name, statistic_name, b, k) to register corpus statistics (b = 0.75 and k = 1.2 are standard defaults):

SELECT bm25_create('documents', 'passage', 'documents_passage_bm25', 0.75, 1.2);

What bm25_create does under the hood:

1. Creates materialized views and helper tables inside bm25_catalog to track term frequencies, document frequencies, and total document count.
2. Installs a trigger on documents so newly inserted or deleted rows are tracked automatically.

SELECT bm25_refresh('documents_passage_bm25');

4. Materialize Document Sparse Vectors (pgvector)

Add a sparsevec column to documents and populate it using bm25_document_to_svector:

ALTER TABLE documents ADD COLUMN embedding sparsevec(30522);

UPDATE documents
SET embedding = bm25_document_to_svector(
    'documents_passage_bm25',
    passage,
    'pgvector'
)::sparsevec;

5. Create an HNSW Sparse Vector Index

Use pgvector’s HNSW index with inner-product operator class sparsevec_ip_ops:

CREATE INDEX documents_embedding_bm25_idx
ON documents
USING hnsw (embedding sparsevec_ip_ops);

6. Run BM25 Queries

Convert the search query into a sparse vector with bm25_query_to_svector and order by <#> (negative inner product):

SELECT
    id,
    passage,
    (embedding <#> bm25_query_to_svector('documents_passage_bm25', 'PostgreSQL vector search', 'pgvector')::sparsevec) * -1 AS bm25_score
FROM documents
ORDER BY embedding <#> bm25_query_to_svector('documents_passage_bm25', 'PostgreSQL vector search', 'pgvector')::sparsevec
LIMIT 5;

Using pg_bestmatch with pgvecto.rs (svector)

If you are using pgvecto.rs (v0.3.0+) instead of pgvector, omit the 'pgvector' style argument (or pass 'pgvecto.rs') and cast to svector:

CREATE EXTENSION IF NOT EXISTS vectors;

ALTER TABLE documents ADD COLUMN embedding svector(30522);

UPDATE documents
SET embedding = bm25_document_to_svector('documents_passage_bm25', passage)::svector;

CREATE INDEX documents_embedding_svector_idx
ON documents
USING vectors (embedding svector_dot_ops);

SELECT id, passage
FROM documents
ORDER BY embedding <#> bm25_query_to_svector('documents_passage_bm25', 'PostgreSQL vector search')::svector
LIMIT 5;

3 - 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: