vchord_bm25 Documentation

• 1: Getting Started
  • 1.1: Overview
  • 1.2: Installation
  • 1.3: Quick Start
• 2: Usage & Hybrid Search
  • 2.1: Indexing & Maintenance
  • 2.2: Querying & Scoring
  • 2.3: Filtered Search & Prefiltering
  • 2.4: Hybrid Search (BM25 + VectorChord)
  • 2.5: BM25 & Block-WeakAnd Internals
• 3: Reference & Benchmarks
  • 3.1: SQL Reference
  • 3.2: GUC & Index Parameters
  • 3.3: Benchmarks
  • 3.4: Migrating to 0.4.0
  • 3.5: Release History & API Evolution

1 - Getting Started

Get started with vchord_bm25 in PostgreSQL:

1.1 - Overview

PostgreSQL includes built-in full-text search via tsvector, tsquery, ts_rank, and GIN indexes. While effective for boolean keyword matching (@@), PostgreSQL’s built-in ranking has two structural limitations for modern search and Retrieval-Augmented Generation (RAG) workloads:

1. Suboptimal Ranking Formula (ts_rank): PostgreSQL’s ts_rank and ts_rank_cd do not track corpus-wide Inverse Document Frequency (IDF) or Average Document Length ($\text{avgdl}$) across the index. Common words are weighted the same as rare, high-signal terms, and term frequency is not properly saturated.
2. Exhaustive Index Scans on ORDER BY ... LIMIT K: A PostgreSQL GIN index only accelerates boolean matching (WHERE tsv @@ tsquery). To return the Top-10 highest-ranked documents, PostgreSQL must fetch every matching row from the heap, compute ts_rank for all of them, and sort the entire result set.

How VectorChord-BM25 Solves Both Problems

VectorChord-BM25 (vchord_bm25) introduces a dedicated PostgreSQL index access method (USING bm25) and sparse BM25 scoring operator (<&>) specifically engineered for Top-K lexical retrieval:

Architecture Overview

flowchart LR
    subgraph Tokenization["1. Tokenization Layer"]
        T1["Raw Text"] --> T2["pg_tokenizer (v0.1–v0.3)\\nor to_tsvector (main)"]
        T2 --> T3["bm25vector / tsvector\\n(Token IDs + Term Frequencies)"]
    end
    subgraph Index["2. BM25 Index (USING bm25)"]
        I1["Growing Segment\\n(Append-only pages)"] --> I2["Sealed Segment\\n(Delta-Bitpacked 128-doc blocks)"]
        I2 --> I3["Term Statistics (DF)\\n+ 1-Byte Fieldnorm Array"]
    end
    subgraph Query["3. Top-K Query (ORDER BY vec <&> bm25query LIMIT k)"]
        Q1["Block-Max Score Upper Bounds"] --> Q2["Block-WeakAnd Pivot Pruning"]
        Q2 --> Q3["Top-K Heap (-BM25 Score)"]
    end
    T3 --> Index
    Index --> Query

Two API Generations

When working with vchord_bm25, be aware of the two API generations across releases:

1. Stable Releases (v0.1.0 – v0.3.0, schema bm25_catalog):

   • Uses custom data types bm25vector (sparse token_id:frequency map, e.g., {1:1, 2:2}) and bm25query.
   • Pairs with the pg_tokenizer extension (tokenizer_catalog), which converts text into INT[] token ID arrays that cast implicitly to bm25vector.
   • Index creation: CREATE INDEX idx ON documents USING bm25 (embedding bm25_ops);
   • Query construction: to_bm25query('idx', tokenize('query text', 'tokenizer_name'))

2. Next-Gen Native tsvector Architecture (main branch):

   • Operates directly on PostgreSQL’s built-in tsvector data type (CREATE OPERATOR CLASS bm25_ops FOR TYPE tsvector USING bm25), removing the requirement for a separate bm25vector column.
   • Index creation: CREATE INDEX idx ON documents USING bm25 (vector bm25_ops) WITH (options = $$ k1 = 1.2, b = 0.75 $$);
   • Query construction: to_bm25query(to_tsvector('english', 'query text'), 'idx')

1.2 - Installation

vchord_bm25 supports PostgreSQL 14, 15, 16, 17, and 18 on Linux (x86_64 and aarch64) and macOS.

Method 1: Pigsty / Native OS Packages (Recommended)

Pigsty provides pre-built native RPM and DEB packages for vchord, vchord_bm25, and pg_tokenizer across EL 8/9/10, Debian 12/13, and Ubuntu 22.04/24.04 for PostgreSQL 14–18.

# Install via Pigsty CLI (automatically resolves PG version and OS distro)
pig ext install vchord_bm25
pig ext install pg_tokenizer

Or install directly via apt / dnf once the Pigsty repository is configured:

# Debian / Ubuntu (replace 17 with your PostgreSQL major version)
sudo apt-get install postgresql-17-vchord-bm25 postgresql-17-pg-tokenizer

# RHEL / Rocky / AlmaLinux (replace 17 with your PostgreSQL major version)
sudo dnf install vchord_bm25_17 pg_tokenizer_17

Method 2: Docker (tensorchord/vchord-suite)

The official tensorchord/vchord-suite Docker image bundles PostgreSQL, VectorChord (vchord), VectorChord-BM25 (vchord_bm25), and pg_tokenizer out of the box:

docker run \\
  --name vchord-suite \\
  -e POSTGRES_PASSWORD=postgres \\
  -p 5432:5432 \\
  -d tensorchord/vchord-suite:pg18-latest

Connect with psql:

psql -h localhost -p 5432 -U postgres

Method 3: GitHub Release Packages (.deb & RHEL .zip)

Download prebuilt .deb or RHEL 9+ .zip packages for your PostgreSQL major version (14–18) and CPU architecture (amd64 / x86_64 or arm64 / aarch64) from the GitHub Releases pages of pgsty/vchord_bm25 (upstream supervc-stack/VectorChord-bm25) and pgsty/pg_tokenizer (upstream supervc-stack/pg_tokenizer.rs):

Debian / Ubuntu (.deb)

# Install vchord_bm25 (example for PostgreSQL 16 on x86_64)
curl -LO https://github.com/supervc-stack/VectorChord-bm25/releases/download/0.3.0/postgresql-16-vchord-bm25_0.3.0-1_amd64.deb
sudo apt-get install ./postgresql-16-vchord-bm25_0.3.0-1_amd64.deb

# Install pg_tokenizer (example for PostgreSQL 16 on x86_64)
curl -LO https://github.com/supervc-stack/pg_tokenizer.rs/releases/download/0.1.1/postgresql-16-pg-tokenizer_0.1.1-1_amd64.deb
sudo apt-get install ./postgresql-16-pg-tokenizer_0.1.1-1_amd64.deb

RHEL 9+ / Enterprise Linux (.zip Archive)

For RHEL 9+ systems when installing manually from the release .zip archive (requires glibc >= 2.34):

# 1. Locate PostgreSQL library and extension directories
pg_config --pkglibdir
# Example: /usr/pgsql-16/lib
pg_config --sharedir
# Example: /usr/pgsql-16/share

# 2. Download and extract the release archive
wget https://github.com/supervc-stack/pg_tokenizer.rs/releases/download/0.1.1/pg16-pg_tokenizer-0.1.1-x86_64-unknown-linux-gnu.zip
unzip pg16-pg_tokenizer-0.1.1-x86_64-unknown-linux-gnu.zip

# 3. Copy the shared library (.so) and extension control/SQL files
sudo cp ./usr/lib/postgresql/16/lib/pg_tokenizer.so $(pg_config --pkglibdir)
sudo cp ./usr/share/postgresql/16/extension/* $(pg_config --sharedir)/extension

Method 4: Build from Source

To compile pgsty/vchord_bm25 from source:

# 1. Install Rust toolchain and system dependencies (clang, libclang, pkg-config, PostgreSQL dev headers)
curl --proto '=https' --tlsv1.2 -sSf https://sh.rustup.rs | sh

# 2. Build and install vchord_bm25
git clone https://github.com/pgsty/vchord_bm25.git
cd vchord_bm25
PG_CONFIG=/path/to/pg_config make build
PG_CONFIG=/path/to/pg_config make install

cargo pgrx install --release --pg-config $(which pg_config) \
  --features "lindera-ipadic lindera-ipadic-neologd lindera-unidic lindera-ko-dic lindera-cc-cedict"

Development & Debugging

Use the dev profile for an optimized BM25 build with debug symbols:

PG_CONFIG=/path/to/pg_config PROFILE=dev make build
PG_CONFIG=/path/to/pg_config PROFILE=dev make install

Restart a disposable PostgreSQL instance configured to preload vchord_bm25, then connect with psql. To attach a debugger, obtain that session’s backend PID:

SELECT pg_backend_pid();

Configure PostgreSQL & Enable Extensions

1. Shared Preload Libraries (Required for 0.4.0)

Current source requires vchord_bm25 in shared_preload_libraries. Preserve other required libraries. pg_tokenizer is optional for the native to_tsvector workflow:

shared_preload_libraries = 'vchord_bm25'

Restart PostgreSQL after modifying shared_preload_libraries.

2. Create Extensions and Set search_path

Connect to your target database and enable the extensions:

CREATE EXTENSION IF NOT EXISTS vchord_bm25;
-- pg_tokenizer is a separate extension for the historical bm25vector workflow.

In v0.1.0–v0.3.0, vchord_bm25 installs its objects into the bm25_catalog schema and pg_tokenizer installs into tokenizer_catalog. Add both schemas to your search_path:

-- Per-session
SET search_path TO "$user", public, bm25_catalog, tokenizer_catalog;

-- Or persist for your database / role
ALTER DATABASE postgres SET search_path TO "$user", public, bm25_catalog, tokenizer_catalog;

Verify the installed version:

SELECT extname, extversion FROM pg_extension WHERE extname IN ('vchord_bm25', 'pg_tokenizer');

1.3 - Quick Start

This guide walks through creating a table, tokenizing documents, building a bm25 index, and executing Top-K BM25 queries.

Workflow A: bm25vector + pg_tokenizer (v0.1 – v0.3)

In released versions v0.1.0 through v0.3.0, vchord_bm25 pairs with pg_tokenizer to tokenize text into a bm25vector column.

1. Enable Extensions and Configure search_path

CREATE EXTENSION IF NOT EXISTS pg_tokenizer CASCADE;
CREATE EXTENSION IF NOT EXISTS vchord_bm25 CASCADE;

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

2. Create a Tokenizer

Use create_tokenizer with the built-in llmlingua2 (or bert_base_uncased) model:

SELECT create_tokenizer('my_tokenizer', $$
model = "llmlingua2"
$$);

3. Create a Table and Populate Documents

CREATE TABLE documents (
    id SERIAL PRIMARY KEY,
    passage TEXT NOT NULL,
    embedding bm25vector
);

INSERT INTO documents (passage) VALUES
  ('PostgreSQL is a powerful, open-source object-relational database system. It has over 35 years of active development.'),
  ('Full-text search is a technique for searching a single computer-stored document or a collection in a full-text database.'),
  ('BM25 is a bag-of-words retrieval function that ranks a set of documents based on the query terms appearing in each document.'),
  ('VectorChord-bm25 is a PostgreSQL extension that provides native BM25 ranking index using the Block-WeakAnd algorithm.');

4. Tokenize Documents into bm25vector

tokenize(text, tokenizer_name) returns an INT[] array of token IDs, which implicitly casts to bm25vector (aggregating duplicate token IDs into term frequencies):

UPDATE documents
SET embedding = tokenize(passage, 'my_tokenizer');

-- Inspect the sparse bm25vector representation ({token_id:term_frequency, ...})
SELECT id, embedding FROM documents LIMIT 2;

5. Build the bm25 Index

CREATE INDEX documents_embedding_bm25
ON documents
USING bm25 (embedding bm25_ops);

6. Run Top-K BM25 Queries

Use the <&> operator with to_bm25query(index_name, query_vector) in an ascending ORDER BY clause:

SELECT
    id,
    passage,
    -(embedding <&> to_bm25query('documents_embedding_bm25', tokenize('PostgreSQL BM25 index', 'my_tokenizer'))) AS bm25_score
FROM documents
ORDER BY embedding <&> to_bm25query('documents_embedding_bm25', tokenize('PostgreSQL BM25 index', 'my_tokenizer'))
LIMIT 10;

Workflow B: Native tsvector API (main Branch)

In the next-generation main branch rewrite, vchord_bm25 indexes PostgreSQL’s native tsvector type directly—no external tokenizer extension is required for standard languages.

1. Create Extension and Table

CREATE EXTENSION IF NOT EXISTS vchord_bm25 CASCADE;

CREATE TABLE documents (
    id SERIAL PRIMARY KEY,
    passage TEXT NOT NULL,
    vector tsvector GENERATED ALWAYS AS (to_tsvector('english', passage)) STORED
);

INSERT INTO documents (passage) VALUES
  ('PostgreSQL is a powerful, open-source object-relational database system.'),
  ('Full-text search is a technique for searching documents in a full-text database.'),
  ('BM25 is a bag-of-words retrieval function that ranks documents based on query terms.'),
  ('VectorChord-bm25 provides a native BM25 ranking index in PostgreSQL.');

2. Build the bm25 Index on tsvector

CREATE INDEX documents_bm25_idx
ON documents
USING bm25 (vector bm25_ops)
WITH (options = $$
k1 = 1.2
b = 0.75
$$, limit = 100);

3. Query with to_bm25query(tsvector, regclass)

SELECT
    id,
    passage,
    -(vector <&> to_bm25query(to_tsvector('english', 'PostgreSQL BM25 index'), 'documents_bm25_idx')) AS bm25_score
FROM documents
ORDER BY vector <&> to_bm25query(to_tsvector('english', 'PostgreSQL BM25 index'), 'documents_bm25_idx')
LIMIT 10;

2 - Usage & Hybrid Search

Learn how to build, tune, and query vchord_bm25 indexes in production:

2.1 - Indexing & Maintenance

vchord_bm25 provides the bm25 index access method and the explicit bm25_ops operator class. The SQL handler is bm25_amhandler in 0.4.0 and _bm25_amhandler in 0.3.x.

Creating a bm25 Index

Stable Releases (v0.1 – v0.3, bm25vector)

CREATE INDEX documents_embedding_bm25
ON documents
USING bm25 (embedding bm25_ops);

Next-Gen main Branch (tsvector)

In the main branch, bm25_ops operates on tsvector and supports index-level storage parameters (options, limit, and prefilter):

CREATE INDEX documents_vector_bm25
ON documents
USING bm25 (vector bm25_ops)
WITH (
    options = $$
    k1 = 1.2
    b = 0.75
    $$,
    limit = 100,
    prefilter = false
);

You can dynamically update limit or prefilter on an existing index with ALTER INDEX:

ALTER INDEX documents_vector_bm25 SET (limit = 200, prefilter = true);
ALTER INDEX documents_vector_bm25 RESET (limit, prefilter);

Automatic Tokenization Triggers (v0.1 – v0.3)

When using pg_tokenizer, you can keep your bm25vector column automatically synchronized on INSERT and UPDATE:

Using a Custom Model Trigger (One-Step Helper)

create_custom_model_tokenizer_and_trigger creates a custom model, a tokenizer, backfills existing rows, and attaches an automatic BEFORE INSERT OR UPDATE trigger:

SELECT create_custom_model_tokenizer_and_trigger(
    tokenizer_name => 'my_tokenizer',
    model_name     => 'my_model',
    text_analyzer_name => 'my_analyzer',
    table_name     => 'documents',
    source_column  => 'passage',
    target_column  => 'embedding'
);

Using a Standard PL/pgSQL Trigger with Pretrained Models

If you use a pretrained model (such as llmlingua2 or bert_base_uncased), create a standard trigger function:

CREATE OR REPLACE FUNCTION documents_tokenize_trigger()
RETURNS trigger AS $$
BEGIN
    NEW.embedding := tokenize(NEW.passage, 'my_tokenizer');
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_documents_tokenize
BEFORE INSERT OR UPDATE OF passage ON documents
FOR EACH ROW
EXECUTE FUNCTION documents_tokenize_trigger();

Segment Architecture: Growing vs. Sealed Segments (v0.1 – v0.3)

Internally, vchord_bm25 organizes index tuples into two segment states:

1. Growing Segment: Newly inserted tuples (INSERT / UPDATE) are appended to the growing segment for fast write throughput without re-compressing entire posting lists.
2. Sealed Segment: When the growing segment exceeds bm25_catalog.segment_growing_max_page_size pages (default 4096 pages, ~32 MB), it is sealed into an immutable, read-only segment with delta-bitpacked 128-document blocks, precomputed block-max upper bounds, and term document frequency statistics.

-- Adjust the growing segment seal threshold (in 8 KB pages, range 1..1000000)
SET bm25_catalog.segment_growing_max_page_size = 4096;

Parallel Index Build & Progress Monitoring

Index creation supports PostgreSQL parallel maintenance workers and reports live build progress via pg_stat_progress_create_index across three phases:

1. initializing
2. scanning table
3. writing index structures

-- Speed up large index builds with parallel workers
SET max_parallel_maintenance_workers = 8;
SET maintenance_work_mem = '2GB';

-- Monitor build progress from another session
SELECT
    pid,
    phase,
    tuples_total,
    tuples_done,
    round(100.0 * tuples_done / nullif(tuples_total, 0), 2) AS pct_done
FROM pg_stat_progress_create_index;

Inspecting Index Pages (v0.3.0)

Version 0.3.0 provides bm25_page_inspect(index regclass, blkno int) to inspect the internal metadata and page headers of a bm25 index:

-- Inspect the metapage (block 0) of a bm25 index
SELECT bm25_page_inspect('documents_embedding_bm25', 0);

Index Constraints & Limitations

• Single-Column Only: A bm25 index can only be created on a single bm25vector (or tsvector) column. Multicolumn bm25 indexes are not supported.
• No Unlogged Tables: UNLOGGED tables are not supported by the bm25 access method (ambuildempty raises Unlogged indexes are not supported).
• Forward Scan Direction Only: bm25 index scans only support forward (ASC) scan direction on <&>.

2.2 - Querying & Scoring

Basic Top-K Query Syntax

To run an index-accelerated Top-K BM25 search, use the <&> operator in the ORDER BY clause together with to_bm25query:

-- v0.1 – v0.3 (bm25vector + pg_tokenizer)
SELECT
    id,
    passage,
    embedding <&> to_bm25query('documents_embedding_bm25', tokenize('PostgreSQL vector search', 'my_tokenizer')) AS raw_score,
    -(embedding <&> to_bm25query('documents_embedding_bm25', tokenize('PostgreSQL vector search', 'my_tokenizer'))) AS bm25_score
FROM documents
ORDER BY raw_score ASC
LIMIT 10;

-- main branch (native tsvector)
SELECT
    id,
    passage,
    -(vector <&> to_bm25query(to_tsvector('english', 'PostgreSQL vector search'), 'documents_bm25_idx')) AS bm25_score
FROM documents
ORDER BY vector <&> to_bm25query(to_tsvector('english', 'PostgreSQL vector search'), 'documents_bm25_idx') ASC
LIMIT 10;

Why to_bm25query Requires the Index Name

Unlike vector distance operators (where the distance between two vectors depends only on the two vectors themselves), the BM25 score of a document depends on corpus-wide statistics stored inside the BM25 index:

• Total number of documents in the corpus ($N$)
• Average document length across the corpus ($\text{avgdl}$)
• Document frequency of each query token ($n(q_i)$)

Passing the index ('documents_embedding_bm25'::regclass) into to_bm25query(...) allows the standalone scoring function (search_bm25query / _bm25_evaluate) to read corpus statistics from the index even when PostgreSQL evaluates <&> in the SELECT projection list or during a brute-force sequential scan.

Understanding Negative BM25 Scores

By design, <&> returns negative BM25 scores:

• PostgreSQL’s index access method API (amcanorderbyop) only supports ORDER BY <operator> ASC for index-ordered scans.
• Because higher BM25 scores mean better relevance, returning $-\text{BM25}(D, Q)$ places the most relevant documents first when sorted in default ascending (ASC) order.
• Never use ORDER BY embedding <&> to_bm25query(...) DESC—PostgreSQL cannot use the forward bm25 index scan for DESC ordering and will either fall back to a full scan or raise a scan direction error.

Tuning bm25_limit vs SQL LIMIT

Because Block-WeakAnd maintains a bounded Top-K min-heap during the index scan to aggressively prune non-competitive posting blocks, the index scanner needs to know $K$ (the maximum number of candidates to collect) when the scan begins.

In v0.1.0 – v0.3.0: bm25_catalog.bm25_limit

The GUC bm25_catalog.bm25_limit (default 100, range -1 to 65535) controls the maximum number of Top-K documents returned by the bm25 index scan:

-- Default is 100. Increase it if your SQL query requests LIMIT 500:
SET bm25_catalog.bm25_limit = 500;

SELECT id, passage
FROM documents
ORDER BY embedding <&> to_bm25query('documents_embedding_bm25', tokenize('database', 'my_tokenizer'))
LIMIT 500;

Special values of bm25_catalog.bm25_limit:

In main: bm25.limit and Index limit Reloption

In the main branch, the effective limit is determined in priority order:

1. An explicitly set session GUC bm25.limit, including zero.
2. Otherwise, the index storage parameter WITH (limit = ...).

Both defaults are zero; set a positive limit before searching. Explicit zero errors and does not fall back. RESET "bm25.limit" restores the index default.

SET "bm25.limit" = 100;

Settings from postgresql.conf, ALTER DATABASE, ALTER ROLE, or client startup options also override index reloptions. RESET restores that configured default; it falls back to the index only when the GUC source returns to the built-in default.

Verifying the Query Plan with EXPLAIN

Use EXPLAIN (ANALYZE, BUFFERS) to confirm that PostgreSQL is using Index Scan using ... on documents:

EXPLAIN (ANALYZE, BUFFERS)
SELECT id
FROM documents
ORDER BY embedding <&> to_bm25query('documents_embedding_bm25', tokenize('PostgreSQL', 'my_tokenizer'))
LIMIT 10;

To force a sequential scan + exact brute-force evaluation (useful for verifying recall or debugging), disable the index scan temporarily:

-- v0.1 – v0.3
SET bm25_catalog.enable_index = false;

-- main branch
SET bm25.enable_scan = false;

2.3 - Filtered Search & Prefiltering

Real-world applications frequently combine full-text BM25 ranking with structured relational filters (such as category_id = 5, tenant_id = 'acme', or date ranges). vchord_bm25 supports three strategies for filtered BM25 search.

1. Post-Filtering vs. Pre-Filtering

When you execute a query with a WHERE clause and ORDER BY embedding <&> to_bm25query(...) LIMIT K:

SELECT id, passage
FROM documents
WHERE category = 'database'
ORDER BY embedding <&> to_bm25query('documents_embedding_bm25', tokenize('PostgreSQL BM25', 'my_tokenizer'))
LIMIT 10;

There are two ways vchord_bm25 can evaluate the WHERE predicate:

Post-Filtering (enable_prefilter = false)

1. The bm25 index runs Block-WeakAnd over the entire corpus and returns the global Top-bm25_limit candidates (e.g., Top 100).
2. PostgreSQL fetches those 100 heap tuples and applies WHERE category = 'database'.
3. Caveat: If only 3 of the global Top-100 documents have category = 'database', the query returns only 3 rows even if hundreds of other 'database' documents exist with slightly lower BM25 scores!

Pre-Filtering (enable_prefilter = true)

When pre-filtering is enabled, vchord_bm25 checks heap visibility and evaluates filter acceptability before pushing candidate documents into the Block-WeakAnd Top-K heap:

1. Non-matching documents never enter the Top-K heap and never raise the Block-WeakAnd pruning threshold $\theta$.
2. The index scan continues until it finds $K$ documents that both match the WHERE filter and have the highest BM25 scores among all matching rows.

Enabling Pre-Filtering

In v0.2.2 – v0.3.0:

-- Enabled by default in v0.3.0; can be toggled per session:
SET bm25_catalog.enable_prefilter = true;

In main branch:

-- Enable per session:
SET bm25.prefilter = true;

-- Or enable by default on the index:
ALTER INDEX documents_bm25_idx SET (prefilter = true);

2. Partial BM25 Indexes for High-Selectivity Partition Keys

If you frequently filter by a small set of known partition keys (for example, status = 'published' or specific tenant_id values), you can create partial bm25 indexes:

CREATE INDEX documents_published_bm25
ON documents
USING bm25 (embedding bm25_ops)
WHERE status = 'published';

When querying a partial index, reference the partial index’s name in to_bm25query and include the matching WHERE condition:

SELECT id, passage
FROM documents
WHERE status = 'published'
ORDER BY embedding <&> to_bm25query('documents_published_bm25', tokenize('PostgreSQL BM25', 'my_tokenizer'))
LIMIT 10;

Choosing the Right Filtering Strategy

2.4 - Hybrid Search (BM25 + VectorChord)

Modern retrieval pipelines combine dense vector search (vchord, which captures semantic similarity) with sparse lexical BM25 search (vchord_bm25, which captures exact keyword, identifier, and rare-term matches). Because both extensions run natively inside PostgreSQL, you can execute end-to-end hybrid search in a single SQL query with zero external orchestration.

Schema Setup for Hybrid Search

CREATE EXTENSION IF NOT EXISTS vchord CASCADE;
CREATE EXTENSION IF NOT EXISTS pg_tokenizer CASCADE;
CREATE EXTENSION IF NOT EXISTS vchord_bm25 CASCADE;

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

-- 1. Create a BM25 tokenizer
SELECT create_tokenizer('bert', $$
model = "bert_base_uncased"
$$);

-- 2. Create a table storing both dense vectors and sparse bm25vectors
CREATE TABLE chunks (
    id SERIAL PRIMARY KEY,
    content TEXT NOT NULL,
    dense_emb vector(768),
    bm25_emb bm25vector
);

-- 3. Create VectorChord dense index (vchordrq) and BM25 lexical index (bm25)
CREATE INDEX chunks_dense_idx ON chunks USING vchordrq (dense_emb vector_l2_ops);
CREATE INDEX chunks_bm25_idx  ON chunks USING bm25 (bm25_emb bm25_ops);

Method 1: Reciprocal Rank Fusion (RRF) in Pure SQL

Reciprocal Rank Fusion (RRF) combines rankings from multiple retrieval systems without requiring score calibration between L2/cosine distance and BM25 scores:

where $k$ is a smoothing constant (typically $k = 60$).

WITH dense_results AS (
    SELECT
        id,
        RANK() OVER (ORDER BY dense_emb <-> '[0.012, -0.034, ...]'::vector) AS dense_rank
    FROM chunks
    ORDER BY dense_emb <-> '[0.012, -0.034, ...]'::vector
    LIMIT 100
),
bm25_results AS (
    SELECT
        id,
        RANK() OVER (
            ORDER BY bm25_emb <&> to_bm25query('chunks_bm25_idx', tokenize('PostgreSQL BM25 hybrid search', 'bert'))
        ) AS bm25_rank
    FROM chunks
    ORDER BY bm25_emb <&> to_bm25query('chunks_bm25_idx', tokenize('PostgreSQL BM25 hybrid search', 'bert'))
    LIMIT 100
),
fused AS (
    SELECT
        COALESCE(d.id, b.id) AS id,
        d.dense_rank,
        b.bm25_rank,
        COALESCE(1.0 / (60 + d.dense_rank), 0.0) +
        COALESCE(1.0 / (60 + b.bm25_rank), 0.0) AS rrf_score
    FROM dense_results d
    FULL OUTER JOIN bm25_results b ON d.id = b.id
    ORDER BY rrf_score DESC
    LIMIT 10
)
SELECT
    c.id,
    c.content,
    f.dense_rank,
    f.bm25_rank,
    round(f.rrf_score::numeric, 6) AS rrf_score
FROM fused f
JOIN chunks c ON c.id = f.id
ORDER BY f.rrf_score DESC;

Method 2: Hybrid Retrieval + ColBERT Late-Interaction Reranking

For even higher accuracy, you can retrieve candidate documents using vchordrq (dense) + vchord_bm25 (lexical) and rerank the union of candidates in PostgreSQL using VectorChord’s @# (max_sim) multivector ColBERT operator:

WITH dense_candidates AS (
    SELECT id FROM chunks
    ORDER BY dense_emb <-> $1::vector
    LIMIT 50
),
bm25_candidates AS (
    SELECT id FROM chunks
    ORDER BY bm25_emb <&> to_bm25query('chunks_bm25_idx', tokenize($2, 'bert'))
    LIMIT 50
),
candidate_ids AS (
    SELECT id FROM dense_candidates
    UNION
    SELECT id FROM bm25_candidates
)
SELECT
    c.id,
    c.content,
    -(c.colbert_emb @# $3::vector[]) AS colbert_maxsim_score
FROM candidate_ids u
JOIN chunks c ON c.id = u.id
ORDER BY c.colbert_emb @# $3::vector[] ASC
LIMIT 10;

2.5 - BM25 & Block-WeakAnd Internals

This page details the exact scoring equations and data structures implemented in vchord_bm25 (crates/bm25/src/bm25.rs and src/algorithm/block_wand.rs).

1. Okapi BM25 Scoring Formula

Given a document $D$ and a query $Q = \\{q_1, q_2, \\dots, q_m\\}$ with query term frequencies $c(q_i, Q)$ and document term frequencies $f(q_i, D)$, vchord_bm25 computes:

Inverse Document Frequency ($\text{IDF}$)

where:

• $N$ is the total number of indexed documents in the table.
• $n(q_i)$ is the number of documents containing token $q_i$ (document frequency).

Saturated Term Frequency ($\text{TF}$)

where:

• $f(q_i, D)$ is the term frequency of token $q_i$ in document $D$.
• $\\hat{|D|}$ is the quantized document length (total token count in $D$) decoded from its 1-byte fieldnorm ID.
• $\\text{avgdl}$ is the average document length across all indexed documents.
• $k_1 \\in [1.2, 2.0]$ (default 1.2) controls term frequency saturation.
• $b \\in [0.0, 1.0]$ (default 0.75) controls document length normalization.

2. 1-Byte Fieldnorm Quantization (FIELDNORM_TO_LENGTH)

Storing a 32-bit float or integer length for every document and computing floating-point division for every scored posting would consume excessive memory bandwidth and CPU cycles. Instead, vchord_bm25 quantizes document length $|D|$ into a single byte (u8, 0..=255) using the 256-entry FIELDNORM_TO_LENGTH lookup table:

• Exact for short documents (0 – 40 tokens): Values 0..=40 map 1:1 to exact token counts.
• Logarithmically stepped for longer documents (41 – 255): Steps widen progressively (e.g., 42, 44, 46, ... up to 2,013,265,944 tokens).
• 256-Entry Precomputed Score Cache: Because fieldnorm has only 256 possible values, vchord_bm25 precomputes the denominator term for all 256 possible document lengths once per query term:

Scoring a candidate posting during scan execution reduces to a single table lookup and one division:

3. Posting List Compression & Block-WeakAnd (Block-Max WAND)

Fixed 128-Document Blocks with Delta-Bitpacking

For each term in a sealed segment, the inverted list of (doc_id, term_frequency) pairs is partitioned into fixed blocks of 128 documents and compressed with Delta-Bitpacking:

1. Document IDs within a block are sorted in ascending order and stored as deltas ($\Delta_i = \text{doc}_i - \text{doc}_{i-1}$), bitpacked using the minimum bit width required by the largest delta in the block.
2. Each block header stores the last document ID in the block (for fast shallow seeking) and the Block-Max TF / Fieldnorm pair $(\text{tf}{\max}, \text{fn}{\min})$ that achieves the highest possible BM25 contribution inside that 128-document block.

Block-WeakAnd Top-K Pruning Loop

During an ORDER BY vec <&> to_bm25query(...) LIMIT K scan:

flowchart TD
    A["Sort query term cursors by current doc_id"] --> B["Accumulate upper-bound max_score across cursors\\nuntil sum > current Top-K threshold θ (Pivot Document)"]
    B --> C{"Sum of Block-Max scores\\nat Pivot > θ ?"}
    C -- "No (Prune Block!)" --> D["Skip entire 128-doc block\\nwithout decoding postings"]
    C -- "Yes" --> E{"All leading cursors\\naligned at Pivot doc_id?"}
    E -- "No" --> F["Advance leading cursor to Pivot doc_id"]
    E -- "Yes" --> G["Decode block & evaluate exact BM25 score\\nUpdate Top-K min-heap and raise θ"]
    D --> A
    F --> A
    G --> A

1. Pivot Selection: Query term cursors are ordered by their current doc_id. Starting from the first cursor, vchord_bm25 sums each term’s global upper-bound max_score until the cumulative sum exceeds the current $K$-th best score threshold $\theta$ in the Top-K min-heap. The cursor where the sum crosses $\theta$ defines the pivot document.
2. Block-Max Refinement: Without decompressing any posting block, vchord_bm25 shallow-seeks each cursor up to the pivot’s 128-document block and sums the tighter per-block upper bounds (block_max_score).
3. Fast Block Skipping: If the sum of block_max_score values is $\le \theta$, no document in the current block intersection can possibly enter the Top-K results—the scanner immediately jumps past the block without decoding document IDs or frequencies.
4. Exact Evaluation: Only when the block upper bound exceeds $\theta$ and cursors align on pivot_doc does vchord_bm25 decode the block, verify visibility/deletion bitmap (and optional prefilter), compute the exact BM25 score, and update the Top-K heap.

3 - Reference & Benchmarks

Complete technical reference for vchord_bm25 and pg_tokenizer:

3.1 - SQL Reference

vchord_bm25 Data Types

bm25vector Literal Syntax (v0.1 – v0.3)

-- Empty bm25vector
SELECT '{}'::bm25vector;

-- Explicit token_id:frequency pairs
SELECT '{1:1, 2:3, 10:2}'::bm25vector;

-- Implicit cast from int[] (automatically aggregates duplicate token IDs into counts)
SELECT ARRAY[1, 2, 2, 2, 10, 10]::bm25vector;
-- Output: {1:1, 2:3, 10:2}

vchord_bm25 Operators

vchord_bm25 Functions

vchord_bm25 Access Method & Operator Class

pg_tokenizer Functions (tokenizer_catalog)

All pg_tokenizer functions reside in the tokenizer_catalog schema:

Tokenizer Management & Execution

Text Analyzer Management

Model Management

Dictionary & Preload Management

Internal Trigger & Helper Functions (tokenizer_catalog)

pg_tokenizer Catalog Tables (tokenizer_catalog)

pg_tokenizer TOML Configuration Reference

Tokenizer Options (create_tokenizer)

Text Analyzer Options (create_text_analyzer)

Custom Model Options (create_custom_model)

3.2 - GUC & Index Parameters

Stable Releases (v0.1.0 – v0.3.0): bm25_catalog.* GUCs

In versions v0.1.0 through v0.3.0, all session GUC parameters use the bm25_catalog. prefix and can be changed per session or transaction by any user (GucContext::Userset):

Examples (v0.1 – v0.3)

-- Increase Top-K window for deep pagination (e.g., LIMIT 500)
SET bm25_catalog.bm25_limit = 500;

-- Force brute-force scan over all index postings
SET bm25_catalog.bm25_limit = -1;

-- Enable prefiltering for queries with selective WHERE clauses
SET bm25_catalog.enable_prefilter = true;

-- Disable index scan to benchmark sequential scan baseline
SET bm25_catalog.enable_index = false;

Next-Gen main Branch: bm25.* GUCs & Index Reloptions

In the main branch rewrite, session GUCs use the bm25. prefix and work together with per-index storage parameters (WITH (...)):

Session GUC Parameters (main)

Settings from postgresql.conf, ALTER DATABASE, ALTER ROLE, or client startup options also override index reloptions. RESET restores that configured default; it falls back to the index only when the GUC source returns to the built-in default.

Index Storage Parameters (WITH (...) in main)

3.3 - Benchmarks

Throughput Benchmark (QPS vs. Elasticsearch, Lucene, ParadeDB, PG GIN)

VectorChord-BM25’s Block-WeakAnd implementation with 128-document delta-bitpacked blocks and 1-byte fieldnorm quantization delivers up to 3x higher QPS than Tantivy-based PostgreSQL extensions (ParadeDB / pg_search) and an order of magnitude higher QPS than native PostgreSQL GIN indexes on Top-10 ranked queries:

Why vchord_bm25 Achieves High QPS

1. Zero Cross-Engine Serialization: Unlike foreign-data-wrapper or external Tantivy/Lucene bridges, vchord_bm25 stores its segments directly inside PostgreSQL buffer pages (PostgresRelation), avoiding tuple marshaling overhead.
2. Aggressive Block-Max Pruning: On multi-term queries, Block-WeakAnd skips over 80%–95% of 128-document posting blocks without decompressing them once the Top-10 threshold stabilizes.
3. 256-Entry Precomputed BM25 Lookup Table: Quantizing document length into a 1-byte fieldnorm replaces expensive per-document floating-point math with a single array lookup.

Retrieval Accuracy Benchmark (NDCG@10 on BEIR & LoTTE)

The table below (from our BEIR / LoTTE Hybrid Search & ColBERT Reranking evaluation) compares retrieval accuracy (NDCG@10) across:

• PostgreSQL vchord Dense Vector Search (Vector)
• PostgreSQL vchord_bm25 Lexical Search (BM25)
• Hybrid Search (vchord + vchord_bm25 with ColBERT Rerank)

3.4 - Migrating to 0.4.0

The current pgsty/vchord_bm25 source prepares 0.4.0 (unreleased). It uses PostgreSQL’s native tsvector and a new index storage engine. Historical 0.3.x packages and suite images use bm25vector; the two APIs and index formats are incompatible.

The current build ships vchord_bm25--0.4.0.sql, generated from the Rust library. Packaging compares the checked-in schema with the compiled API and rejects mismatches. The old install SQL remains available in Git for old binaries; changing the control version to 0.3.0 does not make the current engine compatible with it.

Install and query

Build current source using PG_CONFIG=/path/to/pg_config make build and PG_CONFIG=/path/to/pg_config make install. Add vchord_bm25 to shared_preload_libraries and restart PostgreSQL. Standard text search needs no external tokenizer:

CREATE EXTENSION vchord_bm25;
CREATE TABLE documents (
    id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    passage text NOT NULL,
    vector tsvector GENERATED ALWAYS AS (to_tsvector('english', passage)) STORED
);
INSERT INTO documents (passage) VALUES
    ('PostgreSQL supports full text search.'),
    ('BM25 ranks documents by relevance.');
CREATE INDEX documents_bm25 ON documents USING bm25 (vector bm25_ops)
WITH (limit = 100);

SELECT id, vector <&> to_bm25query(to_tsvector('english', 'PostgreSQL'), 'documents_bm25') AS rank
FROM documents ORDER BY rank LIMIT 10;

The index needs document positions to recover term frequencies. strip(tsvector) and array_to_tsvector cannot supply them. Use the same analyzer for documents and queries. An integer token ID is not a lexeme; no automatic bm25vector-to-tsvector cast is provided.

An explicit SET "bm25.limit" = 100 overrides the index limit. RESET "bm25.limit" restores the index setting. Explicit zero makes the scan error; it does not select the index default or unlimited results. Both defaults are zero, so a positive setting is required. The same source-based precedence applies to bm25.prefilter, including explicitly setting false. SQL LIMIT does not set either GUC.

Settings from postgresql.conf, ALTER DATABASE, ALTER ROLE, or client startup options also override index reloptions. RESET restores that configured default; it falls back to the index only when the GUC source returns to the built-in default.

Schema access

A DBA can install into a schema chosen for the application:

CREATE SCHEMA search;
CREATE EXTENSION vchord_bm25 WITH SCHEMA search;
GRANT USAGE ON SCHEMA search TO app_user;
ALTER ROLE app_user SET search_path = public, search;

This is an alternative to installing into public; do not run both installations in one database. Grant table access separately. Function execution is granted to PUBLIC by PostgreSQL’s default privileges; if your deployment revokes it, grant the required functions explicitly. Schema-qualified calls to search.to_bm25query(...) work without adding search to search_path, including after relocating the extension.

Move existing data

ALTER EXTENSION vchord_bm25 UPDATE TO '0.4.0' from 0.3.0 deliberately raises an error before changing objects. It neither rewrites old indexes nor drops dependent application data.

1. Keep the old database and old binary available. Inventory bm25vector columns, indexes, views, functions, and tokenizer triggers; export source text and application data while the old datatype can still be read.
2. Install 0.4.0 into a separate PostgreSQL installation and new database. Databases in the same installation share the extension library, so replacing the binary while any database still uses 0.3.x is unsafe.
3. Restore the source text, generate tsvector values, and rebuild all BM25 indexes. Set a positive search limit. A binary copy of an old index or an ordinary REINDEX on a bm25vector column cannot migrate the API.
4. Update the constructor argument order, types, GUC names, and tokenizer triggers. Compare ranking quality and query results before switching the application.

The pg_tokenizer integer-array workflow remains documented for 0.3.x. For 0.4.0, use to_tsvector until the tokenizer version you deploy explicitly supports compatible tsvector output.

3.5 - Release History & API Evolution

This page tracks the release history and SQL schema evolution of vchord_bm25 (pgsty/vchord_bm25 / supervc-stack/VectorChord-bm25).

Version Summary Matrix

Detailed Upgrade Changes

v0.3.0 (sql/vchord_bm25--0.2.2--0.3.0.sql)

• Added page diagnostic function:

  CREATE FUNCTION bm25_page_inspect(index regclass, blkno INT) RETURNS TEXT;

• Added to_bm25query(regclass, int[]) convenience overload in main/late 0.3.x.

v0.2.2 (sql/vchord_bm25--0.2.1--0.2.2.sql)

• Added prefiltering support in Block-WeakAnd (bm25_catalog.enable_prefilter GUC, enabled by default in 0.3.0).
• Improved MVCC visibility checking during index scans to prevent dead/filtered tuples from polluting the Top-K threshold.

v0.2.0 (sql/vchord_bm25--0.1.1--0.2.0.sql)

• Added = and <> operators for bm25vector.
• Added implicit cast from int[] to bm25vector (_vchord_bm25_cast_array_to_bm25vector), allowing tokenize(text, name) output (INT[]) to be assigned or passed directly wherever bm25vector is expected.

v0.1.1 (sql/vchord_bm25--0.1.0--0.1.1.sql)

• Extracted built-in create_tokenizer / tokenize functions into the dedicated pg_tokenizer extension (tokenizer_catalog), enabling rich text analyzers, Snowball stemmers, stopwords, synonyms, HuggingFace models, jieba, and lindera.
• Simplified to_bm25query(index_oid regclass, query_vector bm25vector) to accept a bm25vector directly.

Next-Generation Rewrite (main Branch)

• Replaced custom bm25vector with PostgreSQL’s native tsvector (CREATE OPERATOR CLASS bm25_ops FOR TYPE tsvector USING bm25), allowing users to index to_tsvector(...) columns directly with USING bm25.
• Added parallel index build (bm25_parallel_build_main) with external merge sort and constant maintenance memory consumption.
• Added configurable BM25 hyperparameters ($k_1 \in [1.2, 2.0]$, $b \in [0.0, 1.0]$) via WITH (options = $$ k1 = 1.2, b = 0.75 $$).