What Julia Evans learned about running SQLite behind a Django site

Julia Evans has published notes on running SQLite behind a Django site, her fourth web project to use the embedded database. She writes that the advice to use SQLite in production for a small site is sound, but that the experience underlined a point the blog posts tend to skip: SQLite is still a database, databases are complicated, and operating them is a skill of its own.

ANALYZE and a five-second query

The first surprise was a full-text search query using SQLite's FTS5 extension on a table with 4,000 rows that took five seconds to return. Running ANALYZE immediately cut it to roughly 0.05 seconds. ANALYZE builds statistics that the query planner uses to choose a better plan; Evans says she still does not know exactly what the original plan got wrong and suspects something accidentally quadratic. She has used SQLite for web projects since 2022 and only learned that ANALYZE existed now.

Cleaning up rows is trickier than it looks

Deleting batches of unwanted rows — completed tasks left behind by django-tasks-db, for example — has caused trouble more than once. When a cleanup command runs for more than five seconds, another worker tries to write to the database, hits the configured five-second timeout, crashes and takes the virtual machine down with it. The workaround so far is to perform cleanups in small batches so that no single query exceeds the timeout. Evans writes that the episode gave her more appreciation for why someone might want a database such as Postgres that allows several writers at once, and that she may schedule maintenance downtime for future cleanups.

Backups: restic and Litestream

Her current routine dumps the database with VACUUM INTO, compresses it and ships it to S3 with restic, unlocking the repository first because a killed backup can leave it locked, then pruning old snapshots. She has since started testing Litestream for incremental replication with a 400-hour retention window, in part because the restic runs were sometimes killed for running out of memory. Backups are monitored with a dead man's switch, but she admits she has not actually tested restoring from them.

On performance, Evans is deliberately relaxed: she writes queries through the Django ORM without watching for slow plans, and the database sits at around 10,000 rows, which she expects to stay small. She also splits tables across several database files when they do not need to live together — an approach she used for Mess with DNS, which has run on SQLite for four years, since 2022.