Adding Search to a Small App — SQLite FTS5, Not Elasticsearch

·6 min read·Updated
sqlitesearchdatabasesarchitecturevolumesperformance

Your app has a few thousand records and someone wants a search box. The reflex is to reach for a search service, and on a small container that's the wrong first move — it doubles your infrastructure to solve a problem your database already solves. SQLite has full-text search built in: FTS5 has been "included as part of the SQLite amalgamation" since version 3.9.0 (SQLite docs), which means it runs inside your existing process, against your existing file, with nothing to deploy. On this platform that matters more than usual, because a search service would have to be a second application — and two applications can't share a volume, so it couldn't read your data locally, and there's no private networking, so the connection would cross a public endpoint.

Why a separate search service is the wrong first move

Three structural reasons, before you even get to whether it would work well:

It can't reach your data. A volume belongs to exactly one application. A search container cannot open your SQLite file. You'd be shipping documents to it over the network, which means writing and maintaining a sync pipeline.

The connection is public. There's no private networking — every port binding is reachable from the internet. Your search backend would need real authentication on a public endpoint, for traffic that never leaves the same machine conceptually.

It doesn't fit the box. Search services are memory-hungry by design; they hold indexes resident to be fast. That's a poor match for an allocation measured in hundreds of megabytes, and it competes with the app you actually care about.

Against that: FTS5 costs you one virtual table. It's the same argument as not reaching for Redis when a dictionary will do — the in-process option isn't a compromise here, it's the design that fits.

What FTS5 actually gives you

Enough for most applications, and it's ordinary SQL.

Create a virtual table:

CREATE VIRTUAL TABLE email USING fts5(sender, title, body);

Populate it with normal INSERT, UPDATE and DELETE.

Query it with MATCH:

SELECT * FROM email WHERE email MATCH 'fts5';

Rank with bm25(). The docs describe it as returning "a real value indicating how well the current row matches the full-text query", with the important detail that better matches are numerically lower:

SELECT * FROM email WHERE email MATCH 'fts5' ORDER BY bm25(email);

That trips people up — sorting descending gives you the worst results first. And you can weight columns, so a hit in a title counts for more than one in a body:

-- weight sender 10x, title 5x
SELECT * FROM email WHERE email MATCH 'query' ORDER BY bm25(email, 10.0, 5.0);

Relevance ranking is the feature people assume they need a search engine for. It's one function call.

Don't store your text twice

The naive setup duplicates every document: once in your real table, once inside the FTS index. On a platform where disk is measured rather than allocated — you're billed for what the volume actually holds — that duplication is a real cost, and total disk has an account-wide consequence.

FTS5 solves this with external content tables, which store "only the full-text index entries, not the actual document content":

CREATE TABLE t1(a INTEGER PRIMARY KEY, b, c);
CREATE VIRTUAL TABLE ft USING fts5(b, c, content='t1', content_rowid='a');

The index points back at your existing table and reads column values from it when needed. Same search, roughly half the storage.

The catch is stated plainly in the documentation: "The user must keep the FTS index and content table synchronized, typically using database triggers on the content table." Write the triggers when you create the table, not later — an index that silently drifts out of date is worse than no search, because it looks like it works.

Don't rebuild the index at startup

This is the container-specific mistake, and it's the same shape as several others.

Your index lives in the SQLite file on the volume, so it persists — there's nothing to rebuild. But a "rebuild the search index on boot" line is easy to write and looks harmless locally.

Deployed, that runs on every container recreation — every deploy, every configuration change, and every lap of a crash loop. On a decent-sized table it turns a fast start into a slow one, and in a crash loop it hammers your database while you're trying to debug something else.

Build the index once, keep it in sync with triggers, and if you ever genuinely need to rebuild it, make that a deliberate action you run from the shell rather than something startup does on your behalf.

Concurrency, briefly

Search adds read load, and reads are the easy case — with WAL enabled, SQLite readers and writers don't block each other. If you haven't turned that on, it's part of the standard configuration for a multi-user app.

Do keep index updates short. Reindexing a large batch inside one transaction holds the single writer slot for its duration, which stalls every write in your app. If you're bulk-importing, chunk it and let other work through — ideally from a background task in the same process.

When FTS5 isn't enough

Being honest about the ceiling, because it does exist:

  • Typo tolerance. FTS5 does prefix matching, not fuzzy matching. "Recieve" will not find "receive". You can soften this with your own normalisation, but it isn't a fuzzy search engine.
  • Rich faceting and aggregation across many fields at once.
  • Language-aware stemming beyond what the built-in tokenizers give you.
  • Corpora far larger than one machine's disk, which is a different conversation entirely.

If you already run your own Postgres, its built-in full-text search is the natural next step — still no extra service, still one connection you already have.

A dedicated search service is a reasonable answer eventually. It's just very rarely the answer for an app with a few thousand rows, and adopting it early costs you a container, a sync pipeline and a public endpoint to protect. That's the shape of outgrowing this platform rather than starting on it.

FAQ

Do I need Elasticsearch for search in a small app?

Almost certainly not. SQLite's FTS5 is built in, runs in your process, and handles ranking and phrase queries for typical application-sized data.

Is FTS5 available by default?

It has been included in the SQLite amalgamation since version 3.9.0, so most builds have it. Confirm with your language's bundled SQLite if unsure.

How do I rank results by relevance?

Order by bm25(). Lower values are better matches, and you can pass per-column weights to prioritise titles over bodies.

How do I avoid storing my documents twice?

Use an external content table, which stores only the index and reads values from your existing table. You must keep them in sync with triggers.

Should I rebuild the index when my app starts?

No. The index persists on the volume, and startup code runs on every deploy, configuration change and crash-loop restart.

Can I run a search service alongside my app instead?

It would be a second application, and two applications can't share a volume — so it can't read your database file, and its connection would cross a public endpoint.

Wrapping up

Search feels like infrastructure, which is why people go looking for a service to run. For an application-sized corpus it's a feature of the database you already have, and the version that fits a single container is also the version with the fewest moving parts: one virtual table, a couple of triggers, and an ORDER BY bm25(). Build that first. If you outgrow it you'll know precisely what you needed and why, which is a much better position to buy infrastructure from than a guess made on day one.