📺 Prefer to watch? 90-second YouTube Short · 💬 Telegram
Originally published on software-engineer-blog.com.
You have a folder of 10,000 markdown notes and you search for postgres backup. grep takes 400 ms and hands back 40 files in the order they happen to sit on disk. SQLite FTS5 takes 3 ms and puts the right note first.
Same files. Same query. The entire difference is two ideas: an inverted index and BM25 ranking. Both fit in your head, and both fit in one SQLite file — no Elasticsearch cluster, no search service to babysit.
Start honest: grep is not wrong
grep -r "postgres backup" notes/ opens every note and reads every byte. At 300 notes that is genuinely the correct answer — the simplest thing that works. At 10,000 notes it re-reads all of them on every keystroke, and your 400 ms search-as-you-type feels like typing through mud.
But speed is only half the problem, and it's the half everyone notices. The half that quietly hurts more: grep cannot rank. A tight 200-word note that is about postgres backups and a standup note that mentions "postgres backup" once in paragraph nine come back identical — two matching file paths, in folder order. grep knows containment. It has no concept of relevance.
The flip: store "word → files", not "file → words"
Every file on disk is already a mapping of file → the words in it. Full-text search flips that around and stores word → the files it appears in. That's the whole trick — the inverted index — and in SQLite it's one line:
CREATE VIRTUAL TABLE notes USING fts5(path UNINDEXED, title, body);
Under the hood, FTS5 keeps a postings list per term: for postgres, a list of (docid, column, position) entries — which note, which column, and where in the text the word sits.
Now run the query:
SELECT path FROM notes WHERE notes MATCH 'postgres backup';
FTS5 reads the postings list for postgres, the postings list for backup, intersects them — and touches nothing else. Files scanned: 0. That's the 400 ms → 3 ms jump: the work is proportional to the query's terms, not to the corpus.
And because the index stores positions, not just membership, you get two things grep can only fake with regex:
-- exact phrase: the words must be adjacent, in order
SELECT path FROM notes WHERE notes MATCH '"postgres backup"';
-- proximity: within 5 tokens of each other
SELECT path FROM notes WHERE notes MATCH 'NEAR(postgres backup, 5)';
The part that trips everyone: the ranking is NOT stored
Here's the correction beat. It's tempting to imagine the index stores a rank next to each posting — "this note is a 9/10 for postgres". It doesn't, and it can't.
BM25 — the ranking function FTS5 uses — is computed at query time, from three inputs:
- Term frequency (TF): how often the term appears in this note. More mentions → more likely the note is actually about it.
-
Inverse document frequency (IDF): how rare the term is across the corpus. If
postgresappears in 12 notes andbackupin 3,000, apostgreshit carries far more signal — the rare term dominates the score. - Length normalization: a 5,000-word brain-dump can't out-score a tight 200-word note by sheer bulk. Frequency is judged relative to document length.
The reason it can't be precomputed: BM25 scores a pair — this query against this document. The same note scores differently for postgres than for postgres backup. A stored per-note rank isn't even well defined.
The real query — and the negative-number gotcha
Production search almost always wants a title hit to outrank a body hit. BM25's column weights do exactly that:
SELECT path, bm25(notes, 0.0, 10.0, 1.0) AS rank
FROM notes
WHERE notes MATCH 'postgres backup'
ORDER BY rank
LIMIT 10;
Two details bite here:
- The weights are positional — one per declared column, including the
UNINDEXEDone.0.0forpath,10.0fortitle,1.0forbody: a title hit counts 10× a body hit. -
bm25()returns a negative number. More relevant = more negative. So plainORDER BY rankascending is already best-first. Everyone writesORDER BY rank DESCexactly once, stares at the worst results on top, and never does it again.
grep vs FTS5, honestly
| grep | SQLite FTS5 | |
|---|---|---|
| Question it answers | Which files contain this word? | Which file is this word most about? |
| Work per query | Reads every byte of every file (~400 ms at 10k notes) | Reads only the query terms' postings lists (~3 ms) |
| Ranking | None — results in folder order | BM25 (TF × IDF × length norm), computed per query |
| Phrase / proximity | Regex gymnastics | Native — "exact phrase", NEAR() from stored positions |
| Freshness | Always current — reads the real files | Derived copy — goes stale if you write outside the ingestion path |
| Setup | Zero | One CREATE VIRTUAL TABLE + an ingestion step you now own |
The two costs nobody puts in the demo
1. The index is a derived copy — you now own a sync problem. Edit a note in your editor, outside whatever code inserts into the FTS table, and the index goes quietly stale. It will keep answering, confidently, from old text. Stale-but-confident is strictly worse than grep's honest "no match" — grep at least never lies about the current state of disk.
2. BM25 is purely lexical. It counts tokens. It has no idea car and automobile are related, that pg_dump is about postgres backups, or that "restore my database" and "postgres backup" are the same intent. That's not a bug in FTS5 — it's the ceiling of lexical search, and exactly where semantic/vector search begins.
The AI angle: why RAG pipelines still run BM25
If you're building retrieval for an LLM — a RAG system over docs, tickets, or a codebase — this isn't retro plumbing. It's half of the current best practice.
Embedding search has the opposite failure mode to BM25: it knows car ≈ automobile, but it's mushy on exact tokens. Ask a pure vector index for ERR_CONN_RESET_1042 or bm25(notes, 0.0, 10.0, 1.0) and it happily returns text that's semantically nearby while missing the one chunk containing the literal string. BM25 nails exact identifiers, function names, error codes, version strings — precisely the queries developers actually make.
That's why production RAG stacks run hybrid search: BM25 and vector similarity in parallel, merged with reciprocal rank fusion or a reranker. The inverted index + BM25 you just learned isn't the "old way" — it's the lexical leg of that hybrid, and with FTS5 it costs you one file and zero services. A SQLite database with an FTS5 table and an embedding table is a legitimate, boringly reliable retrieval layer for a small-to-mid RAG system.
And the mental model transfers one-to-one: IDF's "rare terms carry the signal" is the same instinct behind why a good chunking strategy keeps identifiers intact, and BM25's query-document pair scoring is the same shape as a cross-encoder reranker — score the pair at query time, because relevance isn't a property of the document alone.
Verdict
Under ~300 documents, grep (or LIKE '%…%') is genuinely fine — don't build an ingestion pipeline you don't need. The moment you need search-as-you-type or ranked results, FTS5 gives you real search-engine machinery — inverted index, postings lists, BM25, phrase and proximity queries — for one CREATE VIRTUAL TABLE, inside a database you're probably already shipping. Reach for a dedicated search service (or the vector leg) only when you hit FTS5's honest ceilings: cross-machine scale, typo tolerance, or synonym/semantic matching.
grep asks which files contain this word. FTS5 asks which file is this word most about. That move — from containment to relevance — is the whole idea.
🎥 Watch the 3-minute version: SQLite FTS5 — inverted index + BM25, animated
Top comments (0)