How SQLite FTS5 Delivers Instant Search

When someone types a query into HALO's overlay and gets results back in under 100 milliseconds, even on a machine with tens of thousands of indexed files, the honest explanation isn't a clever trick, it's a well-established piece of database technology called FTS5, doing exactly what it was built to do.

The core problem: scanning doesn't scale

Without an index, "search for text inside these files" means opening every file and checking whether it contains the query, every single time. That's fine for ten files. It falls apart at ten thousand. The fix, the same one every real search engine uses, is to build the index once, ahead of time, so a query becomes a lookup instead of a scan.

What FTS5 actually is

FTS5 is SQLite's full-text search extension, a virtual table type that maintains an inverted index under the hood: instead of storing "this file contains this text," it stores "this word appears in these files," which is the structure that makes lookups fast regardless of how much total text exists. Searching for a word means finding it once in the index and following pointers back to matching files, not reading every file's content freshly each time.

Choosing SQLite specifically, rather than a heavier database like Postgres or a dedicated search server like Elasticsearch, was a direct consequence of HALO's local-first requirement. SQLite is serverless, meaning there's no separate database process to install, run, or keep alive in the background, the entire index lives in a single file on disk. For a desktop app that has to work identically whether the user last opened it five minutes or five months ago, with zero configuration and zero background services silently consuming resources, that property matters more than raw peak throughput.

Why this is a good fit for desktop search specifically

A few properties line up well with what a desktop search tool actually needs:

No server to manage. The FTS5 index is just tables inside a normal SQLite database file (halo.db in HALO's case). No daemon, no port, no separate process that could crash independently of the app.

Incremental updates are cheap. When a file changes, HALO's file watcher (via the notify crate on macOS, using FSEvents) triggers a delete-and-reinsert for just that file's row, not a full reindex. The rest of the index stays untouched.

Prefix and phrase queries come built in. FTS5 supports prefix matching (typing "sear" and matching "search") and phrase queries natively, without extra application code to implement fuzzy-ish matching from scratch.

It's genuinely fast at the scale that matters. Desktop search isn't web search, a single user's machine, even a large one, tops out well below what web-scale search engines handle. FTS5 comfortably handles hundreds of thousands of rows with query times in the low milliseconds, which is the actual ceiling HALO needs to hit, not billions of documents across a distributed cluster.

Where the real engineering effort goes

The database doing fast lookups is really only half the problem. The harder half is everything around it: extracting clean text from PDFs, DOCX, PPTX, and XLSX files without corrupting formatting-sensitive content, running that extraction in a background thread so it never blocks the UI, deduplicating on re-index so an unchanged file doesn't get reprocessed for no reason, and capping how much text gets stored per file so the index doesn't balloon on a handful of enormous documents. FTS5 handles the query side well by default. Getting clean, complete text into it in the first place is where most of the actual work lives.

The tradeoff, stated plainly

SQLite FTS5 isn't the right choice for every search problem. It doesn't have built-in relevance tuning as sophisticated as Elasticsearch's, no distributed sharding, no real-time ranking models. For a single-user, single-machine, local-first search index, none of that matters, the problem it needs to solve is smaller and more constrained than what those tools are built for, and using something heavier would mean carrying complexity that a desktop app has no use for.