SQLite in Production Is Solved. The Writer Problem Isn't

Every few months a "SQLite in production" post hits the front page, and the comments split into two camps: people who think it's reckless, and people who've been quietly running it for years and wonder what the fuss is about. The second camp won. Rails 8 ships SQLite as a production default, complete with WAL mode out of the box, and its Solid Queue and Solid Cache adapters assume a database-backed everything on a single node. The interesting question in 2026 isn't whether you can run SQLite behind an app server — it's which parts of the tuning folklore still matter, and which problems no pragma will solve.

Here's my read after watching this space for a while: the configuration side is a solved problem you can copy-paste in thirty seconds. The two places teams actually get hurt are the single-writer wall and the replication layer underneath — and both are architecture decisions, not tuning knobs.

The recipe is settled — stop rediscovering it

The production pragma block has converged so hard that every guide now publishes essentially the same one:

PRAGMA journal_mode = WAL;
PRAGMA synchronous = NORMAL;
PRAGMA busy_timeout = 5000;
PRAGMA foreign_keys = ON;
PRAGMA cache_size = -64000;        -- ~64MB page cache
PRAGMA journal_size_limit = 67108864;  -- cap WAL at 64MB after checkpoint

Run it on every connection, since most of these are per-connection settings. The one line people still get nervous about is synchronous = NORMAL, and the official WAL documentation is unambiguous: in WAL mode, NORMAL means the checkpoint is the only operation that issues a sync. You can lose the most recent transactions after a power failure or kernel panic — but not after an application crash, and the database won't corrupt. For a web app, trading a few hundred milliseconds of writes in a power-loss scenario for dramatically cheaper commits is the right call almost every time. If you're storing financial ledger entries, keep FULL and accept the fsync tax.

What the recipe doesn't fix: busy_timeout is widely misunderstood as a cure for SQLITE_BUSY, and it isn't. If you open a deferred transaction, read something, then try to write, SQLite must upgrade your read snapshot to a write lock. If another writer committed in between, retrying is pointless — your snapshot is stale — so SQLite returns busy immediately, timeout be damned. That's why the standard advice is BEGIN IMMEDIATE for any transaction that will write: you take the write lock up front, where the timeout actually applies. Better yet, funnel all writes through one dedicated connection in your app and let reads fan out across a pool. In-process queuing beats lock contention every time.

The single writer is an architecture constraint, wear it accordingly

WAL mode gives you concurrent readers alongside a writer, but there is exactly one WAL file and therefore exactly one writer at a time. No pragma changes that. What changes it is how you partition writes — and Rails 8 accidentally shipped the best demonstration of the pattern: cache, queue, and WebSocket state each get their own SQLite file. That's not an ORM quirk; it's write sharding by domain. Your job queue hammering its database no longer contends with user-facing writes. If you're building outside Rails, steal the idea: one file per write-heavy concern, attached or opened separately.

The other production landmine is checkpoint starvation. Checkpoints can't advance past the oldest active reader, so if your app always has at least one open read transaction — long-polling handlers, a lazy iterator someone never closes, an analytics query on a request thread — the WAL grows without bound and read performance degrades, because every reader has to scan the WAL. The fix is boring discipline: keep read transactions short, set journal_size_limit, and if you run hot enough, schedule an explicit PRAGMA wal_checkpoint(TRUNCATE) during quiet moments instead of trusting the passive autocheckpoint to win a race it structurally can't.

The layer below SQLite is where the real churn is

The trend pieces lump Litestream and LiteFS together as "custom VFS layers," and that framing is wrong in a way that matters operationally. Litestream is not a VFS at all — it's a sidecar process that holds a read lock to arrest checkpointing and streams WAL frames to object storage. Your application links stock SQLite and doesn't know Litestream exists. That's precisely why it's the safe default: zero integration risk, and the v0.5 rewrite released last October replaced the fragile "generations" model with transaction-ID-based LTX files, making point-in-time restores much more tractable.

LiteFS, Fly.io's read-replication layer, is the invasive one — a FUSE filesystem interposed between SQLite and disk (with LiteVFS as an actual VFS-based alternative). It buys you geographically distributed read replicas, at the cost of a much bigger operational surface and the classic problem of forwarding writes to a single primary. My honest assessment: adopt Litestream freely, adopt LiteFS only if multi-region reads are a hard requirement you've already failed to solve with a CDN and caching.

And the single-writer ceiling itself is finally being attacked directly. SQLite upstream has kept BEGIN CONCURRENT and wal2 on experimental branches for years without merging them — a signal of how conservative the project is. Turso took the opposite bet: a ground-up Rust rewrite, file-format compatible, with MVCC that the company claims delivers up to 4× SQLite's write throughput while eliminating SQLITE_BUSY entirely. It's in beta and MVCC is still marked experimental, so nobody should migrate a production system to it today. But it tells you where this ends up: the embedded database keeps SQLite's deployment model and sheds its 20-year-old concurrency model.

Where that leaves you

If your workload is read-heavy, fits on one box, and tolerates a heartbeat of write loss on power failure — which describes most web applications more accurately than their architects admit — single-node SQLite with the pragma block above, BEGIN IMMEDIATE writes, and Litestream replication is production-grade today, with fewer moving parts than any Postgres deployment. The moment you need sustained concurrent write throughput or multi-region write transactions, you've left SQLite's design envelope; reach for Postgres rather than fighting the single writer. The tuning was never the hard part. Knowing which side of that line your app sits on is.