Gotchas with SQLite in Production
In this technical retrospective (2024), @todsacerdoti shares practical architectural lessons learned from running, scaling, or migrating production software under lean constraints.
Core Principles for Independent Builders:
Defaulting to simpler architectures (such as monolithic frameworks, SQLite, or managed VPS) prevents early engineering bankruptcy.
Evaluating true compute and egress costs reveals that high cloud bills can often be slashed by 80%+ through judicious re-platforming.
Code you write at 2 AM as a solo engineer must be easy to debug six months later without complex microservice dependencies.
Community Debate Themes:
HN engineers debated the tradeoffs between cloud convenience and raw server economics, database indexing strategies, and resisting premature architectural complexity.
CATEGORY Frugal Engineering →
HISTORICAL ERA Post-ZIRP & AI (2023–2026)
COMMUNITY SCORE 20 points
TOTAL DISCUSSIONS 4 comments
Note that WAL mode brings a LOT of limitations.
It is not safe to use network file systems (NFS, SMB, or otherwise) in WAL mode, since they cannot access the shared memory.
There are a few more limitations that I can dig up.
https://sqlite.org/lang_attach.html ↗
Transactions involving multiple attached databases are atomic, assuming that the main database is not ":memory:" and the journal_mode is not WAL. If the main database is ":memory:" or if the journal_mode is WAL, then transactions continue to be atomic within each individual database file. But if the host computer crashes in the middle of a COMMIT where two or more database files are updated, some of those files might get the changes where others might not.
To backup you should use the `vacuum into` statement. That can be safely run from another process, as it takes a read lock and doesn't block writers when using the WAL mode.
When backing up a SQLite database that is in running use, you should never use `.backup`. That command will restart the backup from scratch every time the database is written to, so if you're backing up a big database with a lot of periodic writes the backup process may never complete.
You can sometimes use the C backup API from the same process that is writing to the database, since the C backup API is aware of writes _from the same process_. It will still have to restart the backup procedure if writes from another process interfere.