You Probably Don't Need Elasticsearch: Writing BM25 From Scratch on SQLite

While building RagXray, my RAG visualization playground, I needed keyword search to run alongside vector search. The default answer in every architecture diagram is Elasticsearch or OpenSearch: a cluster to provision, a service to monitor, and for a $0 serverless side project, a complete non-starter.

So I looked at what keyword relevance scoring actually is. The answer is BM25, an algorithm from the 90s that is about a page of code. I implemented it from scratch on Cloudflare D1, which is SQLite running at the edge, and it turned out fast enough that I would make the same choice again for plenty of production workloads.

This post explains how BM25 works, how to build it on top of SQLite, and where the honest limits of this approach are.

What BM25 actually does

Strip away the notation and BM25 answers one question: how relevant is this document to these query terms? It combines three intuitions.

First, a document that mentions your term more often is more relevant. But not linearly. The tenth mention of "middleware" tells you less than the second, so term frequency gets dampened with diminishing returns.

Second, rare terms matter more than common ones. A match on "getServerSideProps" is a strong signal. A match on "the" is nothing. This is inverse document frequency: the fewer documents a term appears in across the corpus, the more weight a match carries.

Third, long documents get penalized. A 5,000 word page mentioning your term twice is weaker evidence than a 200 word page mentioning it twice, so scores are normalized by document length relative to the corpus average.

The full formula for a single term looks like this:

score(term, doc) = IDF(term) * (tf * (k1 + 1)) / (tf + k1 * (1 - b + b * (docLen / avgDocLen)))

Two tuning constants: k1 (usually 1.2 to 1.5) controls how quickly repeated mentions stop mattering, and b (usually 0.75) controls how strongly length normalization applies. Sum this over every query term and you have the document's score. That is the entire algorithm that a search cluster wraps in a distributed system.

The index is just two tables

An inverted index sounds intimidating and is actually just a lookup from term to the documents containing it. In SQLite:

CREATE TABLE documents (
id INTEGER PRIMARY KEY,
content TEXT NOT NULL,
token_count INTEGER NOT NULL
);

CREATE TABLE inverted_index (
term TEXT NOT NULL,
doc_id INTEGER NOT NULL,
term_frequency INTEGER NOT NULL,
PRIMARY KEY (term, doc_id)
);

CREATE INDEX idx_term ON inverted_index(term);

At ingestion time you tokenize each chunk (lowercase, strip punctuation, split on whitespace, and keep code identifiers intact, which matters a lot for technical docs), count term frequencies, and insert the rows. Corpus statistics like average document length are one query, or a small stats table you update on ingest.

Scoring a query

At query time you tokenize the query the same way, pull the postings for those terms with one SQL query, and score in application code:

const rows = await db
.prepare(
`SELECT i.term, i.doc_id, i.term_frequency, d.token_count
FROM inverted_index i
JOIN documents d ON d.id = i.doc_id
WHERE i.term IN (${placeholders})`
)
.bind(...queryTerms)
.all();

const scores = new Map<number, number>();

for (const row of rows.results) {
const idf = Math.log(
(totalDocs - docFreq[row.term] + 0.5) / (docFreq[row.term] + 0.5) + 1
);
const tf = row.term_frequency;
const norm = 1 - B + B * (row.token_count / avgDocLen);
const termScore = idf * ((tf * (K1 + 1)) / (tf + K1 * norm));
scores.set(row.doc_id, (scores.get(row.doc_id) ?? 0) + termScore);
}

Sort the map, take the top K, done. For a corpus of documentation chunks, the postings for a handful of query terms are a few hundred rows at most. SQLite eats this for breakfast, and on D1 the query runs at the edge close to the user, so the whole keyword search leg of my hybrid pipeline costs a few milliseconds and zero dollars.

Why not just use SQLite FTS5?

Fair question. SQLite ships FTS5, a full-text extension with its own BM25 implementation built in, and if it is available in your environment it is a fine default.

I wrote it manually for two reasons. First, RagXray is a learning tool, and a scoring function you wrote is a scoring function you can visualize, explain, and tweak. Second, owning the tokenizer matters for technical content. FTS5's default tokenizer will happily mangle useEffect or next.config.js in ways you cannot see and cannot easily fix, while in your own pipeline the tokenizer is ten lines you control. When retrieval quality on exact identifiers is the whole reason you added keyword search, that control is worth a page of code.

Where this approach stops working

Honesty section. Rolling your own BM25 on SQLite is the right call in a specific zone, and the wrong one outside it.

It works beautifully when your corpus is thousands to low hundreds of thousands of chunks, writes are batch-style (ingest, then mostly read), and you need exact-term matching to complement vector search. That covers most RAG applications, internal tools, and docs search.

It is the wrong tool when you need real-time indexing of high-volume writes, faceted search and aggregations, typo tolerance and fuzzy matching out of the box, or multi-language analyzers. That is the actual job Elasticsearch does, and at that point you should pay for it, or at minimum reach for something like Typesense or Meilisearch. The mistake is not using Elasticsearch. The mistake is defaulting to it for a workload that two tables and a for-loop handle.

The bigger point

Keyword search held a strange position for years: everyone depends on it, almost nobody has implemented it, so it feels like infrastructure you must buy rather than code you could write. It took one evening to break that illusion, and now every hybrid search diagram I read is legible in a way it was not before.

If there is a piece of "must buy" infrastructure in your stack you have never looked inside, that is probably the most valuable thing you could rebuild small, once, just to see.

You can play with the result inside RagXray, where you can toggle BM25 on and off and watch how it changes retrieval against pure vector search. And if you are building search or RAG into a real product, I do this for a living: salmaninayat.com.