BM25 Search in PostgreSQL: The Missing Piece for Hybrid Search

In Part 1, we built full-text search with tsvector and ts_rank. In Part 2, we added semantic search with pgvector. Both are solid—but if you’ve been building search-heavy applications or retrieval pipelines, you’ve probably hit the wall where neither is quite enough.

pg_textsearch is an open-source PostgreSQL extension (from Tiger Data — the company formerly known as Timescale) that brings production-grade BM25 ranking directly into Postgres—no Elasticsearch cluster required. It went open source as a preview in early 2026 and reached v1.3.0, production-ready, by mid-2026 — which is the version benchmarked at the end of this post.

By the end of this post, you’ll have all three search techniques running in a single Postgres instance: keyword matching (tsvector), semantic understanding (pgvector), and relevance ranking (BM25). More importantly, you’ll see how to combine them for hybrid search that beats any single approach.

Why Your Current Postgres Search Isn’t Enough

TLDR: ts_rank requires all query terms to match, rewards keyword stuffing, and favors longer documents. BM25 fixes all three.

If you followed Part 1, you know how to build searches like this:

SELECT id, title,
  ts_rank(search_vector, to_tsquery('english', 'database & performance')) AS rank
FROM "Article"
WHERE search_vector @@ to_tsquery('english', 'database & performance')
ORDER BY rank DESC;

This works. But it suffers from three problems that become critical as your dataset and user expectations grow:

1. It’s Brittle (Boolean Matching) Standard @@ requires all query terms to be present. If a user searches for “high performance database” and a document says “high performance system,” it’s excluded entirely. Real search engines return partial matches ranked by relevance.

2. No Term Frequency Saturation A document that repeats “database” 50 times gets a score roughly 50x higher than one that mentions it 5 times. This is keyword stuffing—and ts_rank doesn’t handle it. A good ranking algorithm understands that the relationship between frequency and relevance is logarithmic, not linear.

3. No Length Normalization A 10,000-word article naturally contains more keyword hits than a 200-word article. ts_rank rewards longer documents unfairly, drowning out short, concise, highly relevant content.

How BM25 Actually Works

TLDR: BM25 scores relevance using three ideas: rare words matter more (IDF), repeated words have diminishing returns (TF saturation), and shorter documents get a boost (length normalization). It’s what powers Elasticsearch.

IDF — Inverse Document Frequency Rare words carry more weight. If you search for “the postgres,” the word “postgres” matters infinitely more than “the.” IDF measures how rare a term is across your entire document corpus and weights it accordingly.

TF Saturation — Diminishing Returns on Repetition The first time a word appears in a document, the score jumps significantly. The second time adds less. By the tenth occurrence, additional repetitions barely move the needle. This is controlled by the k1 parameter (default: 1.2).

Length Normalization Short documents that hit your keywords are often more relevant than long documents that happen to mention them once in passing. The b parameter (default: 0.75) controls how aggressively shorter documents are boosted.

The formula looks like this:

\text{BM25}(q, d) = \sum_{i=1}^{n} \text{IDF}(q_i) \cdot \frac{f(q_i, d) \cdot (k_1 + 1)}{f(q_i, d) + k_1 \cdot \left(1 - b + b \cdot \frac{|d|}{\text{avgdl}}\right)}

Where:

• f(q_i, d) = frequency of term q_i in document d
• |d| = document length
• avgdl = average document length across the corpus
• k_1 = term frequency saturation (default 1.2)
• b = length normalization (default 0.75)

You don’t need to memorize this. What matters is the intuition: BM25 gives you a relevance score that accounts for term rarity, frequency saturation, and document length. That’s what users expect when they type a query.

Getting Started with pg_textsearch

TLDR: Install the extension, run CREATE EXTENSION pg_textsearch, create a BM25 index on your text column, then query with the <@> operator. Requires PostgreSQL 17+.

Prerequisites

pg_textsearch supports PostgreSQL 17 and 18. Pre-built binaries are available for Linux and macOS (amd64 and arm64) on the GitHub Releases page.

Installation

Option 1: Pre-built binaries (recommended) Download the appropriate binary from the Releases page for your OS and PostgreSQL version.

Option 2: Build from source

cd /tmp
git clone https://github.com/timescale/pg_textsearch
cd pg_textsearch
make
sudo make install

If your machine has multiple Postgres installations, specify the path:

export PG_CONFIG=/Library/PostgreSQL/18/bin/pg_config
make clean && make && sudo make install

Enable the Extension

pg_textsearch ships a shared library that must be preloaded at server start — this is the step most people miss. Add it to postgresql.conf (or pass it on the command line) and restart Postgres:

shared_preload_libraries = 'pg_textsearch'

In Docker, that’s a one-liner on the service: command: ["postgres", "-c", "shared_preload_libraries=pg_textsearch"]. Then, in each database where you want BM25:

CREATE EXTENSION pg_textsearch;

Skip the preload and CREATE EXTENSION (or your first query) fails with a “library not loaded” error. This is the single most common setup gotcha.

Building a BM25-Powered Search

TLDR: Create a BM25 index with USING bm25(column), then query with content <@> 'search terms'. Lower scores = better matches (like pgvector’s distance operator).

Let’s build this step by step using the same Article table pattern from our previous posts.

1. Create the Table

CREATE TABLE "Article" (
    id SERIAL PRIMARY KEY,
    title TEXT NOT NULL,
    content TEXT NOT NULL,
    "createdAt" TIMESTAMP DEFAULT NOW()
);

2. Create a BM25 Index

Instead of the GIN index you’d create for tsvector, you create a BM25 index. This index is transactional—it updates automatically as you write to the database. No external syncing required.

CREATE INDEX article_content_idx ON "Article"
USING bm25(content)
WITH (text_config='english');

The text_config parameter uses PostgreSQL’s built-in text search configurations for stemming and stop word removal. You can use any language Postgres supports:

-- For Spanish content
CREATE INDEX article_content_es_idx ON "Article"
USING bm25(content)
WITH (text_config='spanish');

-- For simple tokenization without stemming
CREATE INDEX article_content_simple_idx ON "Article"
USING bm25(content)
WITH (text_config='simple');

You can also tune the BM25 parameters at index creation time:

CREATE INDEX article_content_idx ON "Article"
USING bm25(content)
WITH (text_config='english', k1=1.5, b=0.8);

3. Query with BM25

The extension introduces the <@> operator for scoring:

SELECT id, title, content <@> 'database performance' AS score
FROM "Article"
ORDER BY score
LIMIT 10;

Important: <@> returns the negative BM25 score because PostgreSQL only supports ascending order for index scans on operators. Lower scores = better matches. This is similar to how pgvector uses <-> for distance.

For explicit index specification (required inside PL/pgSQL functions, stored procedures, or when you have multiple BM25 indexes on the same column):

SELECT id, title,
  content <@> to_bm25query('database performance', 'article_content_idx') AS score
FROM "Article"
ORDER BY score
LIMIT 10;

4. Verify Index Usage

Always check that your index is being used:

EXPLAIN SELECT id, title
FROM "Article"
ORDER BY content <@> 'database performance'
LIMIT 10;

For small datasets, PostgreSQL may prefer a sequential scan. Force index usage during development:

SET enable_seqscan = off;

Note: Even when EXPLAIN shows a sequential scan, <@> and to_bm25query always use the BM25 index internally for corpus statistics (document counts, average length) required for accurate scoring.

Integrating with Next.js and Prisma

TLDR: Use Prisma’s $queryRaw for BM25 queries (no native support). Version-control your BM25 index with Prisma Migrate by adding the SQL to a migration file.

The API Route

import { NextResponse } from 'next/server';
import { prisma } from '@/lib/prisma';
import { Prisma } from '@prisma/client';

export async function GET(request: Request) {
  const { searchParams } = new URL(request.url);
  const query = searchParams.get('q');
  const limit = parseInt(searchParams.get('limit') || '20', 10);

  if (!query || query.length < 2) {
    return NextResponse.json([]);
  }

  const results = await prisma.$queryRaw`
    SELECT
      id,
      title,
      LEFT(content, 200) AS snippet,
      content <@> ${query} AS bm25_score
    FROM "Article"
    ORDER BY bm25_score
    LIMIT ${limit}
  `;

  return NextResponse.json(results);
}

Managing the Index with Prisma Migrate

Just like the trigger we created in Part 3, you should version-control your BM25 index using Prisma Migrate:

1. Create a new migration:

   npx prisma migrate dev --name add_bm25_index

2. Prisma generates a new migration folder with an empty migration.sql file.

3. Paste the index creation SQL:

   -- migration.sql
   CREATE EXTENSION IF NOT EXISTS pg_textsearch;
   
   CREATE INDEX article_content_bm25_idx ON "Article"
   USING bm25(content)
   WITH (text_config='english');

Now the BM25 index is version-controlled and applied automatically alongside your schema changes.

Combining BM25 + pgvector: Hybrid Search

TLDR: Combine BM25 keyword scores with pgvector semantic scores using Reciprocal Rank Fusion (RRF). Documents that rank high in both systems surface first. This is the setup you want for retrieval pipelines.

Hybrid Search combines BM25 keyword precision with vector semantic understanding. If you’re building any retrieval pipeline—whether for search, recommendations, or feeding context to an LLM—this is the pattern you want: precise keyword hits and contextual understanding, ranked by a single score.

The Hybrid Search Query

The most common fusion strategy is Reciprocal Rank Fusion (RRF). It combines rankings from multiple search systems without needing to normalize raw scores. The idea is simple: a document that ranks highly in both keyword and semantic search should rank highest overall.

-- Hybrid Search: BM25 + pgvector with Reciprocal Rank Fusion
WITH bm25_results AS (
  SELECT
    id,
    ROW_NUMBER() OVER (ORDER BY content <@> $1) AS bm25_rank
  FROM "Article"
  ORDER BY content <@> $1
  LIMIT 20
),
vector_results AS (
  SELECT
    id,
    ROW_NUMBER() OVER (ORDER BY embedding <-> $2) AS vec_rank
  FROM "Article"
  ORDER BY embedding <-> $2
  LIMIT 20
),
fused AS (
  SELECT
    COALESCE(b.id, v.id) AS id,
    -- RRF formula: 1/(k + rank) — lower k = more weight on top results
    COALESCE(1.0 / (60 + b.bm25_rank), 0) +
    COALESCE(1.0 / (60 + v.vec_rank), 0) AS rrf_score
  FROM bm25_results b
  FULL OUTER JOIN vector_results v ON b.id = v.id
)
SELECT a.id, a.title, LEFT(a.content, 200) AS snippet, f.rrf_score
FROM fused f
JOIN "Article" a ON a.id = f.id
ORDER BY f.rrf_score DESC
LIMIT 10;

Where $1 is the raw text query and $2 is the query embedding vector (generated by your embedding model).

Why k = 60? The constant k in RRF controls how much we favor top-ranked results. k = 60 is the standard value from the original RRF paper. Lower values amplify the difference between ranks 1 and 2; higher values flatten it.

Hybrid Search in Next.js

import { NextResponse } from 'next/server';
import { prisma } from '@/lib/prisma';
import { generateEmbedding } from '@/lib/embeddings';

export async function GET(request: Request) {
  const { searchParams } = new URL(request.url);
  const query = searchParams.get('q');

  if (!query || query.length < 2) {
    return NextResponse.json([]);
  }

  // Generate embedding for semantic search (from Part 2)
  const embedding = await generateEmbedding(query);
  const embeddingStr = `[${embedding.join(',')}]`;

  const results = await prisma.$queryRawUnsafe(`
    WITH bm25_results AS (
      SELECT id, ROW_NUMBER() OVER (ORDER BY content <@> $1) AS bm25_rank
      FROM "Article"
      ORDER BY content <@> $1
      LIMIT 20
    ),
    vector_results AS (
      SELECT id, ROW_NUMBER() OVER (ORDER BY embedding <-> $2::vector) AS vec_rank
      FROM "Article"
      ORDER BY embedding <-> $2::vector
      LIMIT 20
    ),
    fused AS (
      SELECT
        COALESCE(b.id, v.id) AS id,
        COALESCE(1.0 / (60 + b.bm25_rank), 0) +
        COALESCE(1.0 / (60 + v.vec_rank), 0) AS rrf_score
      FROM bm25_results b
      FULL OUTER JOIN vector_results v ON b.id = v.id
    )
    SELECT a.id, a.title, LEFT(a.content, 200) AS snippet, f.rrf_score
    FROM fused f
    JOIN "Article" a ON a.id = f.id
    ORDER BY f.rrf_score DESC
    LIMIT 10
  `, query, embeddingStr);

  return NextResponse.json(results);
}

Filtering: Pre-filter vs. Post-filter

TLDR: For selective filters (multi-tenant, category), filter before BM25 scoring — it’s faster. For score thresholds, filter after. RLS policies from Part 4 work as automatic pre-filters.

Pre-filtering (Use a Separate Index)

Filter rows before BM25 scoring. Best when the filter is selective (matches less than ~10% of rows):

-- Create a B-tree index on the filter column
CREATE INDEX ON "Article" ("tenantId");

-- PostgreSQL filters first, then scores the reduced set
SELECT id, title, content <@> 'search terms' AS score
FROM "Article"
WHERE "tenantId" = 42
ORDER BY score
LIMIT 10;

This is the optimal strategy for multi-tenant search. Row-Level Security policies from Part 4 work seamlessly here—they act as automatic pre-filters.

Post-filtering (Score First, Then Filter)

Apply BM25 scoring first, then filter. Use when filtering by score threshold:

SELECT id, title, content <@> to_bm25query('search terms', 'article_content_idx') AS score
FROM "Article"
WHERE content <@> to_bm25query('search terms', 'article_content_idx') < -5.0
ORDER BY content <@> 'search terms'
LIMIT 10;

Caveat: Post-filtering happens after the index returns top-k results. If your WHERE clause eliminates most results, you may get fewer rows than your LIMIT. Compensate by increasing the LIMIT and re-limiting in application code.

Performance Tuning

TLDR: Load data before creating the index, use parallel workers (SET max_parallel_maintenance_workers = 4) for large tables, and monitor index usage with pg_stat_user_indexes.

Load Data Before Indexing

Like other index types, it’s faster to create the index after loading your data:

-- Load data first
INSERT INTO "Article" (title, content) VALUES ...;

-- Then create the index
CREATE INDEX article_content_idx ON "Article"
USING bm25(content)
WITH (text_config='english');

Parallel Index Builds

For large tables, pg_textsearch supports parallel index builds:

SET max_parallel_maintenance_workers = 4;
SET maintenance_work_mem = '256MB'; -- ≥64MB required for parallel builds

CREATE INDEX article_content_idx ON "Article"
USING bm25(content)
WITH (text_config='english');

You’ll see a notice when parallel workers are used:

NOTICE: parallel index build: launched 4 of 4 requested workers

Note: The planner requires maintenance_work_mem >= 64MB to enable parallel builds. With insufficient memory, it silently falls back to serial mode.

Monitoring Index Usage

SELECT schemaname, tablename, indexname, idx_scan, idx_tup_read, idx_tup_fetch
FROM pg_stat_user_indexes
WHERE indexdef LIKE '%USING bm25%';

Key Configuration Options

These optional settings in postgresql.conf let you tune behavior:

Limitations to Know

TLDR: Use to_bm25query() inside PL/pgSQL functions. Partitioned tables have separate statistics per partition. PostgreSQL 17+ only.

PL/pgSQL Compatibility The implicit text <@> 'query' syntax relies on planner hooks that don’t run inside PL/pgSQL DO blocks, functions, or stored procedures. Always use to_bm25query() with an explicit index name inside PL/pgSQL:

-- ❌ Won't work in PL/pgSQL
SELECT * FROM "Article" ORDER BY content <@> 'search terms' LIMIT 10;

-- ✅ Use explicit index name
SELECT * FROM "Article"
ORDER BY content <@> to_bm25query('search terms', 'article_content_idx')
LIMIT 10;

Partitioned Tables Each partition maintains its own BM25 statistics (document count, average length, IDF). Scores from different partitions aren’t directly comparable. For time-partitioned data, query individual partitions when score accuracy matters.

PostgreSQL Version Currently supports PostgreSQL 17 and 18 only.

Word Length Inherits PostgreSQL’s tsvector word length limit of 2,047 characters. This only matters for documents containing very long tokens like base64-encoded data or URLs.

BM25 vs Embeddings: Different Tools, Same Database

TLDR: BM25 ranks by exact word importance. Embeddings rank by meaning similarity. They’re complementary, not competing. For the best embedding quality, embed summaries or key passages rather than raw content.

A common question: “If I already have pgvector embeddings, why do I need BM25?” They solve fundamentally different problems.

BM25 is a lexical scoring algorithm. It counts words, weighs them by rarity, and adjusts for document length. It’s precise: searching for “LISTEN/NOTIFY” will find documents containing exactly those words. It has zero understanding of meaning—it doesn’t know that “real-time notifications” is related.

Vector embeddings are semantic representations. They encode meaning into a high-dimensional space where similar concepts cluster together. Searching for “real-time notifications” finds documents about webhooks, SSE, and pub/sub—even if those exact words never appear. But embeddings can miss precise keyword matches, especially for technical terms, function names, or error codes.

They fail in opposite ways, which is exactly why combining them works.

Embedding Quality: What You Embed Matters

A practical lesson from production search systems: embedding a summary of your content often produces better retrieval than embedding the raw content.

Why? Embeddings compress a text into a fixed-size vector (e.g., 1536 dimensions for OpenAI’s text-embedding-3-small). A long, detailed document full of code snippets and tangential explanations produces a “diluted” embedding—the vector tries to represent everything and ends up being mediocre at matching any specific query.

A focused summary or abstract creates a denser, more representative embedding:

-- Instead of embedding raw content
UPDATE "Article"
SET embedding = generate_embedding(content);  -- ❌ diluted

-- Embed a summary or key passage
UPDATE "Article"
SET embedding = generate_embedding(summary);  -- ✅ focused

Practical strategies:

• Generate a 2-3 sentence summary of each document (using an LLM) and embed that
• Embed the title + first paragraph as a proxy for the document’s core topic
• Store multiple embeddings per document (title embedding, content embedding, summary embedding) and search across them

For a deep dive into pgvector setup, embedding generation, and similarity search patterns, see Part 2: Advanced Search with pgvector.

Why All Three Coexist

In practice, each technique handles a different layer of search quality:

You don’t have to choose. Run BM25 and pgvector searches in parallel CTEs, fuse with RRF (shown below), and let the math surface the best results. All three live in the same Postgres instance—same transactions, same backups, same connection pool.

Proof: a measured benchmark on real data

TLDR: I scored every ranker on a standard, human-labeled test set using nDCG@10 — a 0-to-1 measure of how well the top 10 results are ordered (higher is better). Postgres’s built-in ranker (ts_rank) managed just 0.07; BM25 hit 0.69, pgvector 0.66, and combining the two 0.70 — all in one Postgres, every query under ~12 ms.

Claims are cheap, so I ran all four rankers against a real, labeled dataset: BEIR SciFact — 5,183 scientific abstracts and 300 test queries, each paired with a human-written list of which abstracts actually answer it. One Postgres 17 instance, pg_textsearch 1.3.0 + pgvector 0.8.2, embeddings from nomic-embed-text (768-dim).

Two numbers tell the story, both scored on the top 10 results each query returns:

• nDCG@10 — ranking quality. Did the best answers land at the top, not buried at #9? Runs 0 (useless order) to 1 (perfect order).
• Recall@10 — coverage. Of all the documents a human marked relevant, how many showed up in the top 10 at all? Also 0 to 1.

So a ranker can have decent recall (it found the right docs) but poor nDCG (it ordered them badly). Here’s how the four compared:

Three things jump out, and they’re exactly the argument of this post:

1. Native ts_rank collapses to 0.07 — not because the algorithm is subtly worse, but because plainto_tsquery is boolean AND: every term must match, so the moment a query has a word the document phrases differently, the document scores zero. This is the “brittle matching” problem from the top of the post, measured. It’s a fine filter; it is not a ranker.
2. BM25 turns that 0.07 into 0.69 — a ~10× jump in ranking quality and a 0.83 recall, at sub-millisecond latency. Same database, one extension, one index.
3. Hybrid wins, but the gap is honest. RRF fusion edges out BM25 alone (0.70 vs 0.69 nDCG). On SciFact — dense scientific terminology where exact words matter — BM25 actually beats pure vector, which is the opposite of what you’d see on conversational, synonym-heavy text. That’s the point: their strengths are corpus-dependent, so fusing them is the robust default rather than betting on one. The hybrid’s higher latency (~12 ms) is just the two ranked sub-queries plus the fusion — still trivial.

For completeness, index builds on the 5,183-doc corpus: GIN (tsvector) 173 ms, BM25 840 ms, HNSW 518 ms; on-disk sizes 7 MB / 14 MB / 20 MB respectively. Nothing here needs a cluster.

(SciFact is one corpus; absolute numbers shift with your data and your embedding model. The durable takeaway isn’t a leaderboard — it’s that ts_rank alone is not production ranking, BM25 closes most of the gap by itself, and hybrid is the safe default, all without leaving Postgres.)

When to Use What

The Bigger Picture: Postgres as a Complete Search Stack

For a long time, “search in Postgres” meant “good enough for simple apps, but use Elasticsearch for anything serious.” That’s changing.

With the additions we’ve covered in this series, PostgreSQL now offers:

1. Full-Text Search — Built-in tsvector and ts_rank
2. Semantic Search — pgvector for embeddings
3. BM25 Ranking — pg_textsearch for production-grade relevance
4. Real-Time Updates — LISTEN/NOTIFY for reactive features
5. Multi-Tenant Security — Row-Level Security for data isolation

PostgreSQL is becoming a single-store solution for search-heavy applications. One database. One connection. No Elasticsearch cluster to manage, no Redis to sync, no external search service to pay for.

That’s not just simpler—it’s a better architecture for most teams.

Conclusion

You’ve now added production-grade BM25 ranking to your PostgreSQL toolkit. Combined with full-text search and pgvector, you have everything you need to build search experiences that rival dedicated search engines—all inside your existing database.

The hybrid search pattern (BM25 + pgvector with Reciprocal Rank Fusion) is particularly useful for retrieval pipelines. Whether you’re feeding context to an LLM or building a search page, your users get documents that match the exact keywords and the semantic meaning of the query.

No Elasticsearch. No sync jobs. No infrastructure headaches. Just Postgres.