DATABASES Signal 195
Learning a few things about running SQLite
Engineers who reach for SQLite on small sites still need to think about database operations. The single-writer constraint creates real operational friction when long-running writes block other workers, and query performance can degrade dramatically without up-to-date planner statistics.
Written by elseif from the cluster below · every claim links back to a sourceThe three things worth knowing
Running ANALYZE can fix severely degraded queries—here a 5-second FTS5 query on 4000 rows dropped to roughly 0.05 seconds once the planner had table statistics.
Long-running DELETE operations hold the SQLite write lock, causing other workers to time out; batching deletes into sub-5-second chunks is a practical workaround.
Backup approaches include restic with VACUUM INTO (which can OOM) and Litestream for incremental replication, and tables that don't need joins can be split across separate database files.
THE CLUSTER