Usage & Hybrid Search

• 1: Indexing & Maintenance
• 2: Querying & Scoring
• 3: Filtered Search & Prefiltering
• 4: Hybrid Search (BM25 + VectorChord)
• 5: BM25 & Block-WeakAnd Internals

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 - 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;

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

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;

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.