Running SQLite in Production: ANALYZE, Single-Writer Pain, and Backups

Julia Evans documents lessons from running a Django site on SQLite, her fourth SQLite-backed project but the first where the Django ORM pushes the database hard enough to expose operational rough edges. The standout fix: a full-text search query over just 4,000 rows was taking five seconds until she ran ANALYZE, which generates table statistics for the query planner and dropped the query to roughly 0.05 seconds. Beyond enabling WAL mode, she hadn’t otherwise tuned ORM query performance, betting that a small (~10,000-row) database that stays small won’t demand it.

SQLite’s single-writer model is where the pain shows. Bulk cleanup of unwanted rows produces slow DELETE statements that exceed her 5-second busy timeout; when a background worker tries to write during the operation, it times out, crashes, and takes down the VM. Her workaround is deleting in small batches, though the episode gave her fresh appreciation for why a multi-writer database like Postgres exists. For durability she runs two backup strategies — restic (VACUUM INTO a copy, gzip, push to S3, with an unlock step because backups occasionally get OOM-killed and leave a lock) and Litestream for more efficient incremental replication — while admitting she hasn’t actually tested a restore, only monitored the jobs with a dead man’s switch.

The piece is a useful reminder that “SQLite is fine for small production sites” is true but incomplete: it’s still a database, and operating one carries real complexity. Her Mess with DNS project, which split its tables across three separate database files, has run on SQLite for four years after migrating off Postgres, which she considers a clear win — but the caveats around locking, cleanup, and untested backups are exactly the operational knowledge that blog posts recommending SQLite tend to skip.