Practical guide to running SQLite in production: performance, limits, tuning (WAL/PRAGMA), replication tools and migration signs.
Fair point
One thing that bit me the first time I tried “SQLite in prod” was backups: you can’t just cp the . db file whenever, especially with WAL on, or you end up with a snapshot that won’t restore cleanly.
Yep, backups are where the “it’s just a file” mental model quietly dies. In practice I’ve treated sqlite3 db. sqlite ". backup /path/db-$(date). sqlite" (or the online backup API) as the only sane baseline, because it gives you a consistent snapshot even with WAL and active writes.
Yep — copying the .sqlite file while WAL is in play is how you end up with “it restored fine” and then weird corruption/partial state later. SQLite’s own backup command/API is the boring-but-correct answer because it’s snapshot-consistent even under writes.
kirupa has a quick refresher on WAL + journaling modes that helps explain why the mental model breaks: https://www.kirupa.com/data/sqlite.htm
I’ve seen teams treat a .sqlite file like a little “document” and just rsync it around, and it behaves… until WAL makes that assumption quietly false.
The backup API feels annoyingly ceremonial, but it’s the one thing that reliably gives you “one coherent moment in time” even while writes are still happening.
Yeah, treating the DB as a “document” works right up until you’ve got -wal/-shm files in play and your nice single-file mental model turns into a little family of files that have to move together. I’ve had decent luck framing it in docs as “SQLite is a directory, not a file” once WAL is on, which at least stops the rsync-by-habit backups before they bite someone.
