SQLite in production with WAL
In this technical retrospective (2021), @manjana 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 Pandemic Bubble (2020–2022)
COMMUNITY SCORE 83 points
TOTAL DISCUSSIONS 29 comments
What is not so obvious to me: When should WAL be used and when not? Does it even make a difference? It says that WAL might be 1-2% slower on read heavy applications, but how much faster is it for write heavy applications? I'm trying to think of a use case because most (web) applications are ready heavy.
And another question: Are both, WAL and rollback journal multithreading and multiprocess safe while being accessed by multiple applications on a single server? I always asked myself this but somehow never found a clear yes/no answer.
One thing to note: the bottleneck demonstrated here is in write transactions per second, not in GB|rows inserted per second or anything.
The vast majority of businesses will never hit 400qps for writes anyway (and many of the ones that would could throw a sharding strategy at the problem), but just having a lot of data being written isn't sufficient to make sqlite an untenable solution; problems only arise when the insertions of that data need to be extremely fine-grained.
Can anyone share their experiences using SQLite over NFS? It seems like SQLite has major caveats here (no WAL, dot-file locking); how much of a headache is it?
For context, fish shell is considering using SQLite for history, but some users have NFS home directories.
"Benchmarks on the smallest EC2 instance, with no provisioned IOPS, put this little trooper at 400 write transactions per second, and thousands of reads" -> I think 90% of SaaS would be super fine with this write limits.
So 400 writes per second is not exactly what I would think about when thinking about a "Production" environment.