PostgreSQL includes full-text search, but production retrieval systems often need search-engine behavior that goes beyond built-in tsvector: BM25 ranking, top-k retrieval, block-level score skipping, fielded queries, hybrid ranking with vectors, and index maintenance that follows database transactions. A BM25 index extension brings search-engine data structures into the database instead of forcing every application to mirror rows into a separate search service.
The core pattern is to build a PostgreSQL access method or extension that stores an inverted index beside table data, updates it from row changes, and exposes SQL functions/operators for query construction and scoring. The extension owns tokenization, posting-list storage, document-length statistics, and ranking, while PostgreSQL still owns transactions, snapshots, joins, permissions, backups, and operational tooling.
This is not merely “BM25 in SQL.” The engineering challenge is integration: mutable in-memory posting buffers must flush into durable segments, ranking must fit PostgreSQL’s executor model, hybrid search must combine sparse and dense candidates, and query-time pruning must avoid scanning every matching posting when a user only needs the top results.
An inverted index maps each analyzed term to a posting list of matching documents. Each posting may store document id, term frequency, field information, and optional positions. Inside PostgreSQL, the index must also map postings back to heap tuples or stable row identifiers that remain valid across updates, vacuum, and transaction visibility checks.
BM25 scores a document from term frequency, inverse document frequency, document length, and average document length. A database extension therefore needs more than token postings: it must maintain document counts, per-term frequencies, and field/document length statistics as rows are inserted, updated, and deleted.
Search indexes typically buffer writes in memory and flush immutable segments, then merge segments later. That LSM-like approach works well in PostgreSQL extensions because it minimizes random updates to posting lists and allows background compaction to trade write amplification for query efficiency.
Running BM25 inside PostgreSQL reduces dual-write complexity. A query can combine keyword relevance, vector similarity, joins, filters, row-level security, and fresh transactional data in one SQL plan. The tradeoff is that the extension must respect PostgreSQL’s visibility, memory, and planner constraints.
BM25 can be expensive when common query terms have long posting lists. WAND and Block-Max WAND store upper bounds for blocks of postings so the executor can skip candidates that cannot enter the current top-k heap.
A search extension typically exposes an index method and SQL functions for tokenization, query parsing, ranking, and optional hybrid fusion. The table remains ordinary PostgreSQL data; the search index is an access path over selected text columns.
CREATE EXTENSION IF NOT EXISTS pg_textsearch;
CREATE TABLE documents (
id bigserial PRIMARY KEY,
title text NOT NULL,
content text NOT NULL,
embedding vector(768)
);
CREATE INDEX documents_text_idx
ON documents
USING bm25 (content)
WITH (text_config='english');
SELECT id, title, content <@> 'transactional hybrid retrieval' AS bm25_score
FROM documents
ORDER BY content <@> 'transactional hybrid retrieval'
LIMIT 20;Hybrid retrieval usually runs two candidate generators: BM25 for lexical precision and vector search for semantic recall. The results can be combined with reciprocal rank fusion or a weighted score if the score scales are calibrated. Keeping both candidate sets in PostgreSQL makes post-filtering and joins simpler than stitching service responses together in application code.
WITH sparse AS (
SELECT id, row_number() OVER (ORDER BY content <@> 'postgres bm25 extension') AS rank
FROM documents
ORDER BY content <@> 'postgres bm25 extension'
LIMIT 100
),
dense AS (
SELECT id, row_number() OVER (ORDER BY embedding <-> $1) AS rank
FROM documents
ORDER BY embedding <-> $1
LIMIT 100
),
fused AS (
SELECT id, sum(1.0 / (60 + rank)) AS rrf_score
FROM (
SELECT * FROM sparse
UNION ALL
SELECT * FROM dense
) candidates
GROUP BY id
)
SELECT d.id, d.title, fused.rrf_score
FROM fused
JOIN documents d USING (id)
ORDER BY fused.rrf_score DESC
LIMIT 20;During ingestion, the extension can accumulate postings in shared memory or a transaction-local buffer, then flush them into durable index segments. Segment metadata records term ranges, block-max score bounds, document counts, and deletion information. Queries read a merge view across active segments and live buffers.
Smaller teams can avoid operating Elasticsearch/OpenSearch for workloads where PostgreSQL is already the source of truth. Keeping BM25 in the database reduces synchronization lag, retry queues, schema duplication, and failure modes caused by partial indexing.
RAG systems often need to filter by tenant, permission, lifecycle state, language, or product metadata. In-database BM25 lets retrieval use the same constraints as the application while vector search contributes semantic recall. This is especially useful for support, CRM, legal, and analytics data that changes frequently.
Analysts can combine relevance with relational operations: find matching documents, join to customers, aggregate by account, filter by time, or inspect low-ranked misses. The search result becomes a relation rather than an opaque response object from an external service.
Treat token analysis, posting storage, and ranking as separate components. Analysis converts text into normalized terms. Indexing stores term-to-document evidence. Ranking uses corpus statistics and query weights to produce top-k results. This separation keeps language changes from corrupting storage format assumptions.
struct Posting {
doc_id: u64,
term_freq: u32,
field_id: u16,
positions_offset: u32,
}
struct SegmentTermStats {
doc_freq: u32,
max_block_score: f32,
postings_start: u64,
postings_len: u64,
}
fn bm25(tf: f32, df: f32, docs: f32, len: f32, avg_len: f32) -> f32 {
let k1 = 1.2;
let b = 0.75;
let idf = ((docs - df + 0.5) / (df + 0.5) + 1.0).ln();
idf * (tf * (k1 + 1.0)) / (tf + k1 * (1.0 - b + b * len / avg_len))
}Store upper bounds per posting block. During top-k execution, maintain the current kth score. If a block’s maximum possible contribution cannot beat that threshold, skip decoding the block. This is how a database-resident search index avoids turning every common-term query into a full posting-list scan.
Do not hide sparse/dense fusion inside a black-box function too early. Expose intermediate candidate sets, ranks, and scores so teams can debug why a result appeared. Once the ranking policy is stable, wrap the SQL into views or functions.