Four years in, SQLite still surprises me

I've been building websites on SQLite since 2022, and I keep learning that it's still a database — meaning it comes with all the operational complexity of one, even if the deployment footprint is smaller. On my current Django project, the ORM has been pushing more work into the database than I'm used to, which is exposing gaps in my knowledge.

Turning on WAL mode was my first step, as every blog post recommends. That handles concurrent reads, but it doesn't solve everything.

Run ANALYZE before you blame the query

Recently, a query using SQLite's FTS5 full-text search on a table of only 4,000 rows was taking 5 seconds. That's obviously wrong — computers are fast. The fix came down to running ANALYZE, which populates the query planner with statistics about table sizes and other distribution data. The same query dropped to about 0.05 seconds afterward.

I don't know exactly where the original query plan went wrong, but an accidentally quadratic execution path seems likely. I haven't learned to read query plans well enough to say for sure. The lesson sticks anyway: when a query on a small table is inexplicably slow, ANALYZE is a cheap thing to try first.

Cleanup operations need a batching strategy

I've occasionally loaded unwanted rows into the database — completed tasks from django-tasks-db, for example. Cleaning those up has caused a repeating failure pattern:

  1. I issue a delete command touching a large number of rows.
  2. The statement takes longer than 5 seconds (possibly because Python code is running inside the transaction, though I haven't confirmed that).
  3. Another worker tries to write while the transaction is still running and hits my 5-second timeout.
  4. The worker crashes, and the VM shuts down.

My solution has been to run cleanup in small batches so no single query exceeds the timeout. It works, but this experience did make me appreciate why a database like Postgres, with multiple concurrent writers, might be the better tool for some projects. At some point, I may instead take the site down for scheduled maintenance during big cleanup operations — I just haven't sorted out that workflow yet.

Under normal load, though, the ORM queries have been fine. The database sits at maybe 10,000 rows and should stay small, so I haven't had to worry about query performance day-to-day beyond the ANALYZE issue.

Two backup approaches

I've tried two ways of backing up SQLite databases, both monitored with a dead man's switch to confirm they're running. I haven't yet tested a restore end-to-end.

First, restic for whole-database snapshots:

sqlite3 /data/calendar.db "VACUUM INTO '/tmp/calendar.sqlite'"
gzip /tmp/calendar.sqlite

# Upload backup to S3
# Sometimes the backup gets OOM killed and so it stays locked, do an unlock
restic -r s3://s3.amazonaws.com/some_bucket/ unlock
# Do the backup & prune old backups
restic -r s3://s3.amazonaws.com/some_bucket/ backup /tmp/calendar.sqlite.gz
restic -r s3://s3.amazonaws.com/some_bucket/ snapshots
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

Restic backups were getting OOM-killed periodically, though, so I recently started experimenting with Litestream for incremental backups. The setup is just a config file and a command:

litestream replicate -config litestream.yml

I set retention: 400h in the config in an attempt to keep some history, though I can't confirm it's behaving as intended. Both systems back up to AWS, which is functional but painful — the console's credential generation flow is a constant nuisance. I'd switch to another S3-compatible provider if the friction got worse.

Separate database files can be better than one

My current project uses a single database, but Not much — Mess with DNS, which has been on SQLite for four years, runs with its tables split across three separate database files. The tables didn't need to share a database, so separating them cut down on contention and made the whole setup easier to reason about. That project moved from Postgres to SQLite and has been running well ever since.

References

It's amusing how long basic operational knowledge takes to accumulate. I first used SQLite for a web project in 2022, and only just learned that ANALYZE exists. In another year or two, I'll probably be discovering another equally fundamental feature.