SQLite in Production: Lessons from Four Years of Running It

Julia Evans just published a fantastic piece on running SQLite in production for her Mess With DNS project, and it hit the front page of Hacker News with over 270 points and a rich discussion. After four years of production use, she's distilled the real operational challenges you won't find in getting-started tutorials.

The 100x Query Speedup You Might Be Missing#

The most striking discovery: running ANALYZE dropped a full-text search query from 5 seconds to 0.05 seconds. That's a 100x improvement from a single command.

SQLite's query planner makes decisions based on table statistics stored in sqlite_stat1 and sqlite_stat4. Without running ANALYZE, those statistics don't exist - and the planner can choose disastrously bad query plans.

In Julia's case, a query on a 4,000-row table was likely hitting an accidentally quadratic plan. The fix was simple:

ANALYZE;

The lesson: if you're seeing unexpected slow queries on tables with indexes, run ANALYZE before reaching for more complex solutions.

What HN Is Saying#

The Hacker News discussion surfaced several additional insights from battle-tested SQLite users:

On query plans and the .expert command: Multiple commenters pointed to SQLite's .expert mode as a way to avoid learning query plan syntax entirely. It analyzes your queries and suggests indexes:

sqlite> .expert
sqlite> SELECT * FROM x1 WHERE a=? AND b>?;
CREATE INDEX x1_idx_000123a7 ON x1(a, b);

On batching large operations: One commenter noted that even "real" databases like MySQL require batching large DELETE or UPDATE operations. The advice to batch cleanup operations in SQLite isn't a limitation - it's universal database wisdom. Row-based replication in MySQL can choke on a million-row UPDATE just as badly.

On backup approaches: Simon Willison shared his s3-credentials tool for generating scoped AWS credentials - solving the same "annoying to navigate AWS console" pain Julia mentioned. Others suggested Cloudflare R2 as an S3-compatible alternative with simpler pricing.

One user shared a particularly elegant backup approach:

sqlite3 -readonly "${db}" .dump | zstd --fast --rsyncable -o "${db}.sql.zst"

This produces a compressed dump that's also sync-friendly - because --rsyncable structures the compression so unchanged portions of the file stay byte-identical, tools like borg or restic only transfer what actually changed.

The Write Contention Problem#

SQLite allows only one writer at a time. Julia hit this when running cleanup jobs - deleting large batches of rows caused other workers to timeout after 5 seconds and crash.

The solution was straightforward: batch deletions into smaller operations. Instead of DELETE FROM table WHERE condition affecting thousands of rows, she runs smaller batches that complete within the write timeout.

This isn't unique to SQLite. As one commenter noted: "Anyone that's done significant database work has come to the understanding that large updates need to be done in batches, otherwise you nuke performance. Once you get to about 1M rows of data, batching is essential."

Backup Strategies That Actually Work#

Julia outlined two main approaches:

Restic + VACUUM INTO: Create a complete copy of the database, compress it, send to S3. The downside: occasional out-of-memory failures on larger databases.

Litestream: Incremental backup that streams WAL changes continuously. Julia runs it with retention: 400h for historical preservation.

The key insight: neither approach alone is sufficient. You need monitoring - a "dead man's switch" that alerts if a backup hasn't succeeded within a configured window. Backup jobs can fail silently, hang forever, or crash in ways that don't trigger alerts.

When SQLite Isn't Enough#

Julia's honest about the limits: "This whole experience has given me more of an appreciation for why someone might want to use a 'real' database like Postgres which can have more than one writer at the same time."

For her use case - a read-heavy project with ~10,000 rows and a single primary writer - SQLite works well. But the single-writer limitation becomes painful when you need concurrent write operations or have workers that might step on each other.

Multiple Databases Are Fine#

One underappreciated pattern: you can split data across multiple SQLite files. Julia uses this for the Mess With DNS project, keeping separate databases for tables that don't need referential integrity between them.

SQLite can even query across files in a single statement using ATTACH DATABASE. This lets you shard by natural boundaries (per-user data, logs vs. core data) while keeping the operational simplicity of SQLite.

The Developer Experience Win#

What makes SQLite compelling isn't just performance - it's the operational simplicity. No server process to manage. No network configuration. Backups are just file copies (with the right precautions). Your database is debuggable with standard Unix tools.

As Julia puts it, Mess With DNS "has been running on SQLite for 4 years" successfully. For stable, read-heavy workloads without complex concurrent write patterns, that's a strong endorsement.

Key Takeaways#

1. Run ANALYZE after your database has representative data - the query planner needs statistics
2. Batch large write operations to avoid hitting the write timeout
3. Use .expert mode for index recommendations instead of reading query plans
4. Monitor your backups with dead man's switches, not just failure alerts
5. Consider multiple databases when tables don't need cross-references
6. Know when to graduate - concurrent writes are SQLite's limit

The full post has more detail on the specific issues Julia encountered with each approach. If you're running SQLite in production or considering it, it's worth the read.

Sources#

• Learning a few things about running SQLite - Julia Evans' original post
• Hacker News discussion - 73+ comments with additional production tips
• SQLite .expert mode documentation - Index recommendation feature
• SQLite ANALYZE documentation - Statistics collection command