Database field guide · 26

BM25 and Full-Text Search

Terms, relevance, indexes, and top-k retrieval

Understand BM25 scoring, inverted indexes, analyzers, and the lexical side of hybrid search across MongoDB and PostgreSQL.

Franck Pachot7 chaptersBM25 · inverted indexes · relevance · top-k

Build the mental model before choosing the mechanism.

This minibook was AI-generated from Franck Pachot's archived blog posts. Links to the original articles are included for source context and verification.

Lexical search starts with terms

Full-text search turns documents and queries into normalized terms. Tokenization, case folding, stemming, stop words, and language rules decide which evidence reaches the ranking function.

Analyzer configuration is part of the index contract.
Phrase and proximity queries need positional information.
Exact identifiers often need a different field from prose.

An inverted index reverses the question

Instead of scanning each document, an inverted index maps each term to the documents and positions where it occurs. Query execution intersects or unions those posting lists before ranking candidates.

Frequent terms produce long posting lists.
Field boundaries and term positions preserve useful structure.
Index size trades against query-time reconstruction.

TF-IDF establishes the intuition

Term frequency rewards repeated evidence within a document while inverse document frequency rewards terms that distinguish a document from the collection. Raw multiplication needs controls for document length and repeated terms.

Rare terms usually carry more lexical evidence.
Term frequency should saturate rather than grow forever.
Corpus changes can alter scores without changing one document.

BM25 controls saturation and length

BM25 applies a saturating term-frequency function and normalizes for document length. Parameters k1 and b tune how quickly repetition saturates and how strongly length affects the result.

k1 controls term-frequency saturation.
b controls document-length normalization.
Scores are meaningful for ranking within one search context, not as probabilities.

Top-k execution avoids full sorting

Search indexes maintain upper bounds and traverse promising postings so they can stop after proving that unseen candidates cannot enter the requested top k. Filters and sorts can weaken that early termination.

Measure documents examined as well as rows returned.
A non-relevance sort changes the execution problem.
Selective filters should participate in candidate generation when possible.

Hybrid search fuses independent ranks

BM25 captures exact lexical evidence while vector search captures semantic proximity. Reciprocal-rank fusion combines their ordered results without pretending their score scales are directly comparable.

Keep lexical and semantic ranks for diagnosis.
Evaluate fusion against labeled queries.
Tune candidate depth separately from final result count.

Field manual

Concrete mechanics, diagnostic evidence, and executable patterns to carry into a real system.

Inspect analyzer output first

Ranking cannot recover terms removed or transformed unexpectedly by analysis. Compare indexed and query tokens before changing score parameters.

-- PostgreSQL
SELECT to_tsvector('english', 'databases indexing database indexes'),
       plainto_tsquery('english', 'database indexing');

Read BM25 as bounded term evidence

For one term, BM25 combines inverse document frequency with saturated term frequency and document-length normalization. Tune only against relevance judgments.

score(q,d) = sum(IDF(t) * (tf*(k1+1))
  / (tf + k1*(1-b+b*dl/avgdl)))
# Typical experiments sweep k1 and b; they do not assume universal values.

Build a PostgreSQL lexical baseline

PostgreSQL ts_rank_cd provides a native lexical baseline even though it is not BM25. Keep the tsvector stored or indexed and inspect plans for candidate volume.

CREATE INDEX article_search_gin ON article USING gin(search_vector);
SELECT id, ts_rank_cd(search_vector, q) AS rank
FROM article, websearch_to_tsquery('english', $1) q
WHERE search_vector @@ q ORDER BY rank DESC LIMIT 20;

Evaluate top-k quality

Use labeled queries and report ranking metrics by query cohort. One plausible result page cannot distinguish analyzer, retrieval, and ranking defects.

precision_at_k = relevant_in_top_k / k
recall_at_k = relevant_in_top_k / all_relevant
reciprocal_rank = 1 / rank_of_first_relevant
# Retain query, judgments, index version, and analyzer version.

Source articles

Optional deep dives with the complete experiments and product-version context behind this guide.

Text Search With MongoDB (BM25 TF-IDF) and PostgreSQLdev.to · 2025-09-19 Top-K queries with MongoDB search indexes (BM25)dev.to · 2026-02-19 Hybrid Search (Full-Text and Vector Similarity) in HorizonDBdev.to · 2026-06-29 Text Search example with the OMDB sample databasedev.to · 2021-11-29