SQLite in Production: Optimizing WAL Mode, Concurrency, and VFS Layers
Points and comments are a snapshot, not live.
Properly configured SQLite can handle hundreds of concurrent requests and millions of queries daily.
The article argues SQLite is viable for production app servers when tuned with WAL mode, busy timeout, larger cache, and memory-mapped I/O. It details WAL checkpointing strategies and transaction modes (DEFERRED, IMMEDIATE, EXCLUSIVE) to avoid deadlocks. A production-ready pragma configuration is provided. The author notes SQLite fits read-heavy workloads under a few hundred gigabytes on single-tenant servers, while PostgreSQL remains better for distributed writes or multi-terabyte datasets. Custom VFS layers (Litestream, LiteFS) enable replication for cloud environments.
What commenters are saying
Multiple commenters express skepticism based on experience, including a case of SQLite corruption with five concurrent writers. A common criticism is that the article omits practical annoyances like limited ALTER TABLE support and the hassle of schema migrations. One commenter counters that migrations can run on app startup since the database is 1:1 with the application, and that transactional DDL is supported. Others note SQLite's tradeoffs are acceptable when paired with projections from an event-log source of truth, making databases disposable. Several links reference SQLite's official "when to use" and quirks documents.