SQLite FTS5 vs Whoosh: when to reach for a pure-Python search library

#python #sqlite #programming #database

If you need full-text search inside a Python app, two options come up again and again: SQLite's built-in FTS5 extension, and Whoosh, a pure-Python search library. I maintain the current Whoosh fork, so treat this as a biased-but-honest field guide rather than a sales pitch — for a lot of apps, FTS5 is the right answer, and I'll say so.

The 30-second version

FTS5 ships with SQLite, is written in C, and is blisteringly fast on large corpora. If you already have a SQLite database and your search needs are "match these words, rank by relevance," reach for it first.
Whoosh is pure Python — no C extension, no compiler, no system database. It gives you a much richer text-analysis and query pipeline, spelling correction, faceting/grouping, and a Python-native API. It trades raw speed for control and portability.

FTS5: the baseline

import sqlite3

con = sqlite3.connect(":memory:")
con.execute("CREATE VIRTUAL TABLE docs USING fts5(title, body)")
con.executemany(
    "INSERT INTO docs (title, body) VALUES (?, ?)",
    [
        ("Intro to search", "full text search with sqlite fts5"),
        ("Python indexing", "build a search index in pure python"),
    ],
)

# bm25() ranks lower = better; column weights let you boost the title
for title, score in con.execute(
    "SELECT title, bm25(docs, 10.0, 1.0) AS s "
    "FROM docs WHERE docs MATCH 'search' ORDER BY s"
):
    print(round(score, 3), title)

That's genuinely great for a lot of cases. Where it gets awkward:

Analysis is limited. FTS5's tokenizers (unicode61, porter, trigram) cover common cases, but building a custom analysis chain (stemming + stop words + n-grams + your own filter) means writing a C tokenizer or preprocessing text yourself.
Everything is SQL. Query construction, escaping the MATCH syntax, and wiring results back into objects is on you.
No built-in "did you mean?" or faceted grouping — you assemble those from primitives.
Availability isn't guaranteed. FTS5 is usually compiled in, but not on every platform/build. Worth a CREATE VIRTUAL TABLE ... USING fts5 probe.

Whoosh: control and portability

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

schema = Schema(title=TEXT(stored=True, field_boost=2.0), body=TEXT)
ix = RamStorage().create_index(schema)

w = ix.writer()
w.add_document(title="Intro to search", body="full text search with pure python")
w.add_document(title="Python indexing", body="build a search index in pure python")
w.commit()

with ix.searcher() as s:
    q = QueryParser("body", ix.schema).parse("search")
    for hit in s.search(q):
        print(round(hit.score, 3), hit["title"])

What you get in return for writing Python instead of SQL:

A real analysis pipeline. Compose tokenizers and filters (StemmingAnalyzer, stop words, n-grams, custom filters) declaratively.
A proper query language + parser you can extend with plugins (fuzzy ~, ranges, wildcards, fields), and a Python query AST you can build programmatically.
Relevance you can tune — BM25F is the default, with per-field boosts, and you can swap in your own scoring.
Batteries: spelling correction ("did you mean?"), key-value faceting and grouping, highlighting/snippets — all first-class.
Zero native dependencies. It runs anywhere Python runs, including locked-down environments where you can't count on a particular SQLite build.

Honest tradeoffs

Speed at scale: FTS5 (C) will out-index and out-query Whoosh on millions of documents. If you're in that range and FTS5 fits, use it.
Concurrency: FTS5 rides SQLite's model; Whoosh uses a single-writer, multi-reader file index — great for read-heavy apps, plan writes accordingly.
Ecosystem: SQLite is everywhere. Whoosh is a focused library you embed.

A decision rule I actually use

Already on SQLite, simple ranked keyword search, big corpus → FTS5.
Need custom analysis, spelling correction, faceting, a query language you can extend, or you can't rely on a native extension → Whoosh.
Prototyping search logic before committing to infrastructure → Whoosh, because iterating in pure Python is fast.

They also compose: use SQLite as your system of record and Whoosh as the search index over it. That's a common and comfortable pattern.

Whoosh is actively maintained again — pip install whoosh3. Issues, PRs, and "why did this rank that way?" questions all welcome on the repo.

DEV Community