Insights on Operating SQLite

By Julia Evans

I have recently been developing a website using Django, and for the data layer, I opted for SQLite.

I thought it would be simple. While I'd read numerous articles claiming that SQLite is perfectly viable for small-scale production sites—and I still believe that—I didn't fully grasp that SQLite is still a database. Databases are inherently complex, and I realized I have a lot to learn about the operational side of managing them.

The Realization: Even "simple" databases require operational knowledge.

This is my fourth project utilizing SQLite, but it feels more challenging. Because the Django ORM is so powerful, I'm pushing the database to do significantly more heavy lifting than I did in previous non-Django projects. My first step was following the common advice: enabling WAL (Write-Ahead Logging) mode and hoping for the best.

---

🛠️ Key Lessons Learned

1. The Importance of ANALYZE

I encountered a situation where a full-text search query (using FTS5) on a table of $4,000$ rows was taking a staggering 5 seconds. After running the ANALYZE command, the performance improved drastically:

$\text{Query Time: } 5s \rightarrow 0.05s$

While I'm not entirely sure what the query planner was doing wrong (perhaps something accidentally quadratic), ANALYZE fixed it. It generates statistics—likely regarding row counts and distribution—which allows the query planner to make more efficient decisions.

2. The Difficulty of Database Cleanup

Cleaning up unwanted data (such as old entries from django-tasks-db) has proven tricky. I've hit a specific failure loop:

1. I execute a DELETE command to purge rows.
2. The operation takes $> 5$ seconds (I'm still unsure why these are so slow; perhaps Python overhead within a transaction).
3. A separate worker attempts to write to the DB and hits my configured 5-second timeout.
4. The worker crashes, triggering a VM shutdown.

My current workaround: Perform cleanup in small, incremental batches to ensure no single query exceeds the 5-second threshold.

3. SQLite vs. Postgres

This experience highlighted why one might prefer a "heavy" database like PostgreSQL.

Future Plan: I might implement scheduled maintenance windows to take the site offline during heavy cleanup, though I haven't formalized that workflow yet.

---

📈 Performance and Backups

ORM Usage

So far, I've used the Django ORM without worrying about query optimization. Since the database is small—roughly $10,000$ rows—and is expected to stay that way, this "blind" approach is working (aside from the ANALYZE issue).

Backup Strategies

I use a "dead man's switch" to monitor my backups, though I haven't actually practiced a full restore yet. I've tried two different methods:

Method A: restic + VACUUM

This involves creating a consistent snapshot and uploading it to S3.

# 1. Create a consistent copy
sqlite3 /data/calendar.db "VACUUM INTO '/tmp/calendar.sqlite'"
gzip /tmp/calendar.sqlite

# 2. Handle potential OOM locks
restic -r s3://s3.amazonaws.com/some_bucket/ unlock

# 3. Backup and prune
restic -r s3://s3.amazonaws.com/some_bucket/ backup /tmp/calendar.sqlite.gz
restic -r s3://s3.amazonaws.com/some_bucket/ forget -l 1 -H 6 -d 2 -w 2 -m 2 -y 2
restic -r s3://s3.amazonaws.com/some_bucket/ prune

Method B: litestream

To avoid the OOM (Out of Memory) kills associated with restic, I've started using Litestream for incremental backups.

• Command: litestream replicate -config litestream.yml
• Configuration: I set retention: 400h to keep a history of changes (though I'm not 100% sure it's working as intended).
• Pain Point: Navigating the AWS console for credentials is tedious; I may switch to a different S3-compatible provider.

---

📂 Architectural Tips

One useful trick I discovered is that you can utilize multiple database files. In my Mess with DNS project, I split tables into three separate files since they didn't need to be joined.

• Project: Mess with DNS
• Status: Running on SQLite since 2022.
• Verdict: Moving from Postgres to SQLite was a great decision for that specific use case.

Final Thoughts

It is amusing how long it takes to learn the "basics." I first used SQLite for web projects in 2022, yet I only discovered the ANALYZE command today. I expect that in another year or two, I'll stumble upon another fundamental feature I didn't know existed.

Checklist of Learning

• Enable WAL Mode
• Use ANALYZE for query planning
• Batch large DELETE operations
• Implement incremental backups with Litestream
• Test actual backup restoration

References

• The definitive guide to using Django with SQLite in production
• A gist on sqlite performance tuning