BM25 Full-Text Search in Postgres for RAG Pipelines

Postgres full-text search has a ranking problem, and most teams building retrieval-augmented generation pipelines on top of it don't find out until relevance quality plateaus. The default stack, tsvector and tsquery for indexing, ts_rank for scoring, was never built with the statistical rigor that modern search demands. BM25 fixes that, and it's now possible to run true BM25 scoring directly inside Postgres, without bolting on Elasticsearch or standing up a separate search cluster.

Most Postgres-backed RAG systems reach for the built-in text search toolkit because it's already there. Create a tsvector column, index it with a general-purpose inverted index, run a tsquery match, order by ts_rank, done. It works, in the sense that it returns rows. Whether it returns the right rows in the right order is a separate question, and that's where three structural gaps start to show.

First, ts_rank has no concept of inverse document frequency. A match on a rare, high-signal term like "RAFT" gets weighted the same as a match on "the" or "system," so documents don't get rewarded for containing the words that actually distinguish them. Second, there's no term-frequency saturation. A document that repeats "database" fifty times scores far above one that mentions it five times, even though five mentions in a focused, well-written passage is often the better match. ts_rank doesn't punish keyword stuffing; it rewards it. Third, there's no document-length normalization. A 10,000-word article racks up more keyword hits than a 200-word one purely by virtue of being longer, so short, precise documents get buried under long, diffuse ones.

On top of that, Postgres's @@ match operator is a strict boolean gate: every query term has to be present, or the row is excluded. A document missing one out of four query terms is dropped from the results, not ranked lower. And at scale, none of this comes with a top-k shortcut. Postgres scores every matching row rather than stopping early once it has enough good candidates, so ranking quality problems compound with a performance problem as the corpus grows.

What BM25 does differently

BM25, short for Best Matching 25, is a probabilistic ranking function that addresses each of those three gaps directly rather than patching around them. It comes out of decades of information retrieval research, and it is the scoring method that powers Elasticsearch, Solr, and Apache Lucene, making it about as close to an industry standard as text ranking gets. Adopting it in Postgres isn't an experiment; it's catching up to what production search systems have used for years.

The mechanism has three parts. IDF gives rare terms more weight across the corpus, so a match on an unusual, specific term outweighs a match on a common one, the opposite of how ts_rank treats them. Term-frequency saturation means relevance still grows as a term repeats, but the curve flattens out: going from one occurrence to two changes the score substantially, while going from forty-nine to fifty barely moves the needle. The relationship is asymptotic, so repetition stops being a cheap way to inflate a score. Length normalization then adjusts for document size directly, so a term match inside a 200-word document counts for more than the identical match buried inside a 10,000-word one.

Two parameters control the shape of that curve, and both come with defaults that most implementations, including the ones covered below, ship with out of the box: k1 = 1.2 governs how fast term-frequency saturation kicks in, and b = 0.75 governs how much length normalization weighs into the final score. Neither parameter needs tuning to get value from BM25, but both are there when a corpus's characteristics call for adjustment.

How to implement BM25 in Postgres: the available extensions

Three extensions currently bring BM25 scoring into Postgres, and they take meaningfully different architectural approaches.

pg_textsearch, from another vendor (formerly an earlier company under a different name), is open source under the PostgreSQL License and available on GitHub. It's built from scratch in C directly on top of Postgres's own storage layer, rather than wrapping an external search library. The design borrows from a write-optimized indexing architecture used by another data store: an in-memory memtable that spills to immutable on-disk segments, which then compact across levels. Because the index lives in standard Postgres pages managed by the normal buffer cache, it participates in WAL logging and works with pg_dump and streaming replication without any special backup procedure or external storage layer to manage.

Performance-wise, pg_textsearch uses Block-Max WAND for fast top-k retrieval, which Tiger Data reports as up to 4x faster than native BM25 implementations elsewhere, along with posting list compression with SIMD-accelerated decoding that shrinks index size by 41% and speeds up shorter queries by 10 to 20%. It also supports parallel index builds. In a self-reported warm-cache benchmark at a large document count, a parallel build finished in under 18 minutes, with query performance reported at 2.4x to 6.5x faster than ParadeDB's Tantivy backend on two-to-four term queries, and 8.7x higher concurrent throughput. The project picked up 1,000 GitHub stars within days of open-sourcing and is at around 1,800 at last count. Version 1.0.0 reached production readiness in March 2026, supporting self-hosted Postgres 17 and 18 (Postgres 19 beta support isn't documented yet), and it's available by default on Tiger Cloud with no manual install step. Self-hosted users need to add it to shared_preload_libraries and restart Postgres before running CREATE EXTENSION.

ParadeDB's pg_search takes the opposite architectural bet: rather than building a storage engine from scratch, it wraps Tantivy, the Rust-based search library that plays a role similar to Apache Lucene, using the pgrx framework for Postgres extensions. It's licensed AGPL 3, with 0.25.9 the latest version confirmed on PGXN. Its index layout is also an LSM tree, but each segment carries both an inverted index and a columnar index. A single index can support full-text search and fast columnar lookups or aggregations across multiple columns at once. Prebuilt binaries cover Debian 12 and 13, Ubuntu 22.04 and 24.04, another major enterprise Linux distribution's 9 and 10 releases, and macOS 14 and 15, for Postgres 15 and up. It's positioned as an alternative to standing up Elasticsearch, and built with real-time, update-heavy workloads in mind.

VectorChord-BM25, from TensorChord, implements BM25 through Block-WeakAnd algorithms and introduces a custom bm25vector type. It's designed to pair with a companion tokenizer extension, pg_tokenizer.rs, for workloads that need customized tokenization behavior. It's dual-licensed under AGPLv3 and ELv2.

pg_textsearch defaults to k1 = 1.2 and b = 0.75, the same values that are standard BM25 defaults, and uses English stemming and stopword handling, but no systematic ranking-equivalence comparison between pg_textsearch and pg_search has been published. Differences in IDF computation and tokenization edge cases could produce different result orderings on the same query, even with identical parameters. And the licensing terms differ meaningfully for production planning: PostgreSQL License for pg_textsearch, AGPL 3 for pg_search, dual AGPLv3/ELv2 for VectorChord-BM25. Which license fits depends on how the software gets distributed and used, and that's a call for whoever owns compliance, not a technical one.

Building a BM25 index with pg_textsearch: the practical setup

Getting pg_textsearch running self-hosted starts in postgresql.conf. Add pg_textsearch to the shared_preload_libraries line (append it if other libraries are already listed), restart Postgres, then run:

CREATE EXTENSION pg_textsearch;

in each database that needs it. On Tiger Cloud, the preload step is handled automatically; running CREATE EXTENSION pg_textsearch; in the SQL editor or a client is the only step required.

Creating the index itself follows a familiar CREATE INDEX pattern:

CREATE INDEX docs_bm25_idx ON documents USING bm25(content)
WITH (text_config = 'english');

Loading data before building the index, rather than after, makes the build faster: a bulk build runs faster than accumulating the index through incremental inserts. Confirming the extension is active is a one-line check:

SELECT * FROM pg_extension WHERE extname = 'pg_textsearch';

Querying uses a dedicated operator, <@>, which returns a BM25 relevance score as a negative number:

SELECT * FROM documents
ORDER BY content <@> 'database system'
LIMIT 5;

Because scores are negative, ascending order is what surfaces the most relevant rows first. This is one of those details that trips people up the first time: a more negative number means a better match, not a worse one. When multiple BM25 indexes exist on a table, the query can name the index explicitly:

content <@> to_bm25query('database system', 'docs_idx')

A handful of index options control scoring behavior. text_config is required and picks the Postgres text search configuration, 'english', 'french', 'german,' and so on, that governs stemming and stopwords. k1 controls term-frequency saturation (default 1.2, tunable from 0.1 to 10.0): raising it rewards repeated terms more generously, lowering it makes the score saturate faster. b controls length normalization (default 0.75, range 0.0 to 1.0); setting it to 0.0 turns off length normalization entirely, which matters for corpora where document length isn't a meaningful signal. Finally, compaction can run inline (the default), in the background, or manually; background mode handles compaction without requiring a user-managed schedule.

Why BM25 alone is not enough for RAG

BM25 fixes ts_rank's ranking math, but it doesn't fix the underlying limitation of lexical search: it only finds what it can match on the word level. A query like "why is my database slow" shares no keywords with a document titled "query optimization" or "index tuning," so BM25 returns nothing. In a RAG pipeline, that's a silent failure: the retriever comes back empty or with garbage, and the model downstream either hallucinates an answer or admits it can't help, with no error thrown anywhere in between.

Vector search covers exactly that blind spot, since embeddings capture conceptual similarity rather than word overlap. But vectors have their own failure mode, and it's the mirror image of BM25's. A query for a specific error code, "PG-1234," is exactly the kind of rare, precise token that dense embeddings tend to smooth over. Semantic search is liable to surface generic troubleshooting documents about errors in general, rather than the one document that actually contains that code.

Placed side by side, the two approaches cover each other's weak points almost exactly. On the exact-code query, BM25 finds the document containing "PG-1234" precisely because it's doing literal term matching; vectors return generic error documents instead. On the conceptual query about slow databases, BM25 comes up empty while vectors correctly surface documents about performance optimization and query tuning. Hybrid retrieval, running both and merging or re-ranking the results, catches both cases instead of choosing one strength and eating the other's blind spot.

This isn't a hypothetical concern for domains with dense, exact terminology. On the T2-RAGBench benchmark, which tests financial question answering, BM25 showed strong results against dense retrieval on multiple metrics. Financial documents are full of precise, standardized vocabulary, company names, ticker symbols, and metric labels that show up verbatim across filings; lexical matching has a structural advantage over semantic similarity on this kind of content. It's a useful check against the assumption, common in RAG system design, that dense retrieval is always the stronger default. For some domains, exact words still beat approximate meaning, and a ranking function like BM25, built to reward the right words instead of just the most words, is what makes that lexical leg worth keeping.