Building Instant Search with SQLite FTS5: Why You Don't Need Elasticsearch

Every time a web startup adds full-text search to their product, someone inevitably recommends spinning up an Elasticsearch cluster, a Meilisearch container, or subscribing to Algolia for $100 a month.

Unless you are indexing hundreds of millions of enterprise documents, dedicated search clusters are massive operational overkill.

SQLite includes an embedded full-text search engine called FTS5 directly compiled into the core library.

Setting Up BM25 Ranked Search in 5 Lines of SQL

FTS5 builds an inverted index using an optimized virtual table that supports the industry-standard BM25 ranking algorithm:

-- Create an FTS5 full-text virtual table with Porter stemming
CREATE VIRTUAL TABLE articles_fts USING fts5(
    title,
    dek,
    content,
    tokenize = 'porter unicode61'
);

-- Query with automatic BM25 relevance ranking
SELECT title, dek, bm25(articles_fts) AS rank
FROM articles_fts
WHERE articles_fts MATCH 'hardware OR macbook*'
ORDER BY rank
LIMIT 10;

On a database with 50,000 articles, this query executes in 1.2 milliseconds while consuming zero network calls. Simple tools that run in-process will always beat distributed network clusters for everyday software engineering.