SQLite in Production: Key Lessons from a Django Developer
Original: Learning a few things about running SQLite
Why This Matters
Highlights real operational gaps when scaling SQLite beyond simple read-heavy workloads in web apps.
Developer Julia Evans shares practical lessons from running SQLite as a production database for a Django site, covering query optimization via ANALYZE, write-lock timeouts during large DELETEs, and backup strategies for small-scale deployments.
Julia Evans, a developer known for technical writing, documented several operational lessons learned while using SQLite as the backend for a Django-powered website — her fourth project using SQLite but the first where Django's ORM drove heavier database usage.
A key discovery was the importance of running ANALYZE. A full-text search query on a table with 4,000 rows was taking 5 seconds; after running ANALYZE to generate query planner statistics, the same query dropped to approximately 0.05 seconds. Evans suspects the original plan involved an accidentally quadratic execution path.
Cleanup operations also proved problematic. Large DELETE operations lasting more than 5 seconds triggered write-lock timeouts in concurrent worker processes, sometimes crashing the VM. Her current workaround is batching deletions to stay under the 5-second timeout threshold. She acknowledged this limitation highlights why multi-writer databases like PostgreSQL can be advantageous.
On ORM performance, Evans noted that with only around 10,000 rows, Django's ORM queries have generally been acceptable without manual tuning. For backups, she uses SQLite's VACUUM INTO command to create a clean snapshot, then compresses and uploads it to S3, monitored via a dead man's switch — though she has not yet tested a full restore.