Why does ANALYZE make such a big difference in SQLite?

SQLite's query planner has no statistics about your data until ANALYZE generates them. Without those statistics it guesses which index to use, and a bad guess can turn a fast lookup into an accidentally quadratic scan. In the case reported by Julia Evans, running ANALYZE took a full-text search query from about 5 seconds to about 0.05 seconds.

How often should I re-run ANALYZE?

After bulk data loads and after any significant change in table size or data distribution. Statistics are stored in a table and go stale as the data shifts, so a periodic run in a maintenance job is a reasonable default. Re-running is cheap compared to a query that regresses.

When should I stop using SQLite and switch to Postgres?

The usual trigger is write concurrency, not data size. SQLite allows one writer at a time. If multiple workers are contending for writes and you are hitting your write timeout, you can batch and shorten transactions for a while, but eventually the single-writer model is the wall you're standing at.

The SQLite Setting That Took a Query From 5s to 0.05s

July 18, 2026

dev-toolssqliteperformance

Julia Evans posted notes on running SQLite for a real project, and buried in them is one of the highest-value-per-character facts in database operations: running ANALYZE took a full-text search query from roughly 5 seconds to 0.05 seconds.

That is a 100x change from a single command with no schema change and no new index.

Why ANALYZE matters so much

SQLite’s query planner decides which index to use for a query. To make that decision well it needs statistics — how many rows a table has, how selective each index is, how values are distributed.

By default, it has none of that. It works from heuristics and a guess.

ANALYZE populates a table called sqlite_stat1 with real statistics, and from that point the planner makes informed choices. When the guess was wrong, the difference is not “a bit faster” — it’s the difference between using an index and scanning, or between a linear join and an accidentally quadratic one. That’s how you get 100x on a query you never touched.

-- generate statistics for the whole database
ANALYZE;

-- or for a single table
ANALYZE my_table;

-- see what it produced
SELECT * FROM sqlite_stat1;

The catch is that statistics go stale. They reflect the data as of the last run. Bulk-load a million rows into a table that had ten thousand, and the planner is now optimizing against a picture of a database that no longer exists. Re-run ANALYZE after bulk loads and on a schedule.

The short pre-ship checklist

From the same notes, plus what they imply:

1. Enable WAL mode. Write-ahead logging lets readers continue while a write is in progress. It’s the near-universal recommendation for any SQLite database that isn’t single-threaded and read-only.

PRAGMA journal_mode = WAL;

This one persists — it’s a property of the database file, not the connection, so you set it once.

2. Run ANALYZE, then re-run it. See above. Cheap to do, expensive to skip.

3. Set a busy timeout you’ve actually thought about. SQLite permits exactly one writer at a time. When a second writer arrives it waits, and if it waits longer than your timeout it fails. Evans describes workers crashing on a 5-second write timeout under contention. The fix applied was to batch cleanup work so no single query held the write lock past the limit.

The number you pick is a real decision. Too short and you get spurious failures under normal load; too long and a stuck writer stalls the whole system instead of failing fast.

4. Pick a backup method before you need one. Two approaches, with different failure modes:

VACUUM INTO a copy, compress it, ship it somewhere. Simple, and it produces a clean consistent file — but it materializes the whole database, and Evans hit OOM kills doing this.
Litestream for continuous incremental replication. More moving parts, but the memory profile doesn’t scale with database size.

5. Consider splitting into multiple database files. If two sets of tables never join, they don’t need to share a file — or a write lock. This is a genuinely underused escape hatch for write contention, since the single-writer limit is per database file, not per process.

The honest limit

The write path is where SQLite stops being the easy answer. Reads scale beautifully. Writes serialize, full stop.

Everything above — batching, timeouts, splitting files — buys headroom against that one constraint. It’s worth doing, and it goes a long way. But if you find yourself designing increasingly clever workarounds for write contention, that’s the signal to move to a database built around concurrent writers, not a sign that you haven’t tuned hard enough.

Evans notes the project in question was managing around 10,000 rows without performance problems. Most projects that reach for Postgres on day one would have been fine on SQLite for a long time — and most projects that stay on SQLite forever never ran ANALYZE.

Do this today

If you have a SQLite database in production right now, connect to it and run ANALYZE. It takes seconds. Then time your slowest query again.

The most likely outcome is that nothing changes, because your planner was already guessing correctly. The other outcome is that you get an afternoon’s worth of performance work for free.

Source: Learning a few things about running SQLite by Julia Evans, discussed on Hacker News.