Whoosh vs SQLite FTS5: which should you use for full-text search in Python?

Adding search to a Python app almost always comes down to one of these two, once you've decided you don't want to stand up Elasticsearch or a hosted service. Both are embeddable, both run in-process, and both are free. They make very different trade-offs, though, and picking the wrong one leads to either a painful rewrite or a search box that never quite does what your users expect. This is a practical guide to choosing, with real numbers.

The core difference in one sentence

SQLite FTS5 is a full-text index bolted onto a C database engine. Whoosh is a full-text search engine written entirely in Python. That single fact explains almost every trade-off below.

Feature-by-feature comparison

Benchmarks: how much speed are you trading away?

Here's the honest part. SQLite FTS5 is a heavily optimised C extension, and it is much faster than pure Python. The repo ships a reproducible micro-benchmark (examples/benchmark_vs_sqlite.py) that indexes a synthetic corpus and times build, size, and query latency. On a modest CI-class machine, 20,000 documents / 300 queries:

Read that plainly: FTS5 builds and queries orders of magnitude faster, and index sizes are about the same. If you are searching millions of documents on a hot path, that gap matters and you should probably use FTS5 (or something even bigger). Whoosh's sweet spot is thousands to low-millions of documents where a ~40 ms query is completely fine, and where the things it gives you — pluggable analysis, real ranking control, faceting, highlighting, spelling — save you more engineering time than the raw speed costs you. Numbers vary by machine and corpus; reproduce with your own data before deciding.

The same task in both

Whoosh

from whoosh.fields import Schema, TEXT, ID
from whoosh.analysis import StemmingAnalyzer
from whoosh.filedb.filestore import RamStorage
from whoosh.qparser import QueryParser

schema = Schema(path=ID(stored=True),
                body=TEXT(analyzer=StemmingAnalyzer()))
ix = RamStorage().create_index(schema)
w = ix.writer()
w.add_document(path="/a", body="Indexing large document collections")
w.commit()

with ix.searcher() as s:
    q = QueryParser("body", ix.schema).parse("index")   # stemmed: matches "Indexing"
    print([hit["path"] for hit in s.search(q)])   # ['/a']

SQLite FTS5

import sqlite3
db = sqlite3.connect(":memory:")
db.execute("CREATE VIRTUAL TABLE docs USING fts5(path, body, tokenize='porter')")
db.execute("INSERT INTO docs VALUES (?, ?)", ("/a", "Indexing large document collections"))
rows = db.execute("SELECT path FROM docs WHERE docs MATCH 'index'").fetchall()
print([r[0] for r in rows])   # [('/a',)] -> ['/a']

Both work. The difference shows up the moment you need something FTS5 doesn't offer out of the box: a custom analyzer for your domain (product SKUs, code identifiers, CJK text), a "did you mean?" suggestion, faceted navigation, or a scoring tweak. In Whoosh those are objects you configure or subclass; in FTS5 you're either writing SQL gymnastics or reaching for another tool.

A decision checklist

Pick SQLite FTS5 if…

• Your data already lives in SQLite and you want search co-located with it.
• Raw query throughput on a large corpus is your top priority.
• Plain keyword/phrase matching with BM25 is enough.
• You don't need programmable analysis, faceting, or spelling built in.

Pick Whoosh if…

• You want a real search engine you fully control from Python.
• You need custom analysis/stemming, faceting, highlighting, or "did you mean?".
• You want a no-compile, dependency-free install that runs on PyPy, in serverless functions, or in the browser via Pyodide/WASM.
• Your corpus is in the thousands-to-low-millions range and ~tens-of-ms queries are fine.

And it isn't strictly either/or: some apps keep records in SQLite and build a Whoosh index for the search experience, using each where it's strongest.

See also: Whoosh vs Tantivy (tantivy-py).