FTS5 and BM25

"Before you reach for neural anything, remember: a 40-year-old keyword ranking function still wins on names, jargon, and exact phrases. It never needs a GPU."

The Inverted Index

Full-text search is fast because of one data-structure flip. The naive layout maps each document to the words it contains; to find a word you'd scan every document. An inverted index flips that: it maps each word to the list of documents (and positions) that contain it. Now finding every place "afogato" appears is a single lookup, not a scan of the whole corpus. SQLite's FTS5 extension builds and queries exactly this structure, right inside the database, with no external service.

BM25: Ranking Without a Model

Finding the matches is half the job; ranking them is the other half, and that's what BM25 does. It scores each matching document on three intuitions:

Term frequency, with saturation. A document that mentions your term more is more relevant — but with diminishing returns. The tenth occurrence barely adds over the ninth, so a page can't win just by repeating a word.
Inverse document frequency. Rare terms carry more signal than common ones. Matching "afogato" tells you far more than matching "the", so rare matches are weighted up.
Length normalization. A long document naturally contains more words; BM25 discounts for length so a sprawling page doesn't outrank a tight, on-topic one.

All three are pure arithmetic over the index. No embeddings, no training, no GPU — and it has been quietly excellent since the 1990s.

Why Keyword Search Still Matters

Vector search is the fashionable half of retrieval, but it blurs. It's brilliant at "find things that mean something similar" and clumsy at "find this exact term." Names, error codes, rare jargon, a specific function name, an exact turn of phrase — these are precisely where embeddings smear a crisp query into a fuzzy neighborhood. BM25 nails them. In a real engine, keyword search isn't the legacy path you tolerate; it's the precision instrument you keep sharp.

Keyword search is your deterministic floor. Because BM25 over an inverted index needs no model and no network, it's the retrieval path that always works — the one still standing when the embedding server is down. Build it first, make it good, and treat vectors as an enhancement layered on top, not a replacement for it. A hybrid engine with a weak keyword half is quietly fragile.

What It Looks Like in Practice

FTS5 exposes ranking as a first-class function you can order by. You create a virtual table over your text, query it with a MATCH expression, and sort by the built-in bm25() score. The whole thing is a SQL query against a local file — fast, deterministic, and completely offline. This is the boring, reliable bedrock the fancier retrieval sits on.

Code

FTS5 keyword search ranked by BM25, no model involved·sql

-- A full-text index over chunk text, queried and ranked, entirely in SQLite.
CREATE VIRTUAL TABLE chunks_fts USING fts5(
  text,
  chunk_id UNINDEXED,      -- carried along, not searched
  corpus_id UNINDEXED
);

-- Find chunks matching the query, ranked by BM25 (lower score = better match).
-- No model, no network — this runs against a local database file.
SELECT chunk_id, bm25(chunks_fts) AS score, text
FROM chunks_fts
WHERE chunks_fts MATCH 'long-term AND investor'
ORDER BY score          -- FTS5's bm25() is negative; ascending = most relevant first
LIMIT 8;

Exercise

Pick a query where you'd expect keyword search to beat semantic search, and one where you'd expect the opposite. For the first, think of an exact name, code, or rare term; for the second, a vague conceptual ask. Explain in one sentence each why BM25 wins the first and blurs the second. This is the intuition that tells you when to lean on which half of a hybrid engine.

Hint

Keyword search wins when the query IS the answer's exact words (a name, an error code, a quoted phrase). Semantic search wins when the answer uses totally different words for the same idea. A good hybrid engine needs both because real queries are a mix.

The Inverted Index

BM25: Ranking Without a Model

Why Keyword Search Still Matters

What It Looks Like in Practice

Code

External links

Exercise

Progress

Comments 0