# SQLite in Production Two July 2026 pieces on running SQLite as an application server's real database, worth reading against each other because they disagree about where the risk is. Hynek Schlawack's TIL (26 July) is a first-hand report that WAL mode produced `database is locked` errors for *readers*, on a database nobody had written to in days. The Micrologics configuration guide from nine days earlier is the standard pragma blueprint, and it assumes the opposite: enable WAL, set a busy timeout, and readers stop being a concern. ## Readers get locked out, and read-only systems still write The advice Hynek quotes as conventional is three lines — WAL mode, a non-zero busy timeout, `BEGIN IMMEDIATE` for any transaction that will write. He grants it's correct for the workload it was written for: a web application with long-lived, pooled connections. His workload inverts every assumption in that sentence. Writes happen in some cases less than once a month. Reads happen tens to hundreds of times per second, from independent processes with no connection pooling, each of which opens the database with `SQLITE_OPEN_READONLY`, runs one `SELECT`, and closes it. WAL mode was chosen defensively, so that the rare writer wouldn't block readers. The mechanism that bites is WAL's connection lifecycle rather than its write path. Connections coordinate through the `-shm` shared-memory index, and opening or closing an *empty* WAL database can briefly require exclusive locks. A new reader that arrives inside one of those windows gets `SQLITE_BUSY`, with no application data being written and none written in days. Because the SQLite C library defaults `busy_timeout` to 0, that comes straight back to the caller as an error instead of being retried. The file listing is the evidence: ``` -rw-r----- 1 root root 294912 Jul 21 09:49 /vmws/config/config.db -rw-r----- 1 root root 32768 Jul 24 18:26 /vmws/config/config.db-shm -rw-r----- 1 root root 0 Jul 24 18:26 /vmws/config/config.db-wal ``` The `ls` ran on 24 July. The database itself was last touched on 21 July, while `-shm` and `-wal` carry timestamps from minutes earlier — written by the readers. The `-wal` is zero bytes and still generating churn. The stdlib-only Python reproducer runs 64 processes doing 100 open/select/close rounds each, synchronised on a barrier so everyone opens at the same instant, across three configurations: ``` A) WAL, no busy timeout 7 / 6400 locked B) WAL, 1s busy timeout 0 / 6400 locked C) DELETE, no busy timeout 0 / 6400 locked ``` Scenario A produces reliably between 1 and 10 failures on a 2023 MacBook Pro with Python 3.14; B and C produce none. Hynek's fix was to move the databases to `DELETE` journal mode, which reinstates writers blocking readers — a trade he can afford at monthly write frequency and which has held since. His closing note is that the C library's zero default is what made the bug findable at all: "yay things breaking loudly." ## The blueprint the other source recommends Micrologics gives the per-connection bootstrap sequence that most SQLite-in-production writeups converge on: ```sql PRAGMA journal_mode = WAL; PRAGMA synchronous = NORMAL; PRAGMA busy_timeout = 5000; PRAGMA cache_size = -64000; -- ~64 MB; negative means KiB, positive means pages PRAGMA mmap_size = 1073741824; -- 1 GB mapped PRAGMA foreign_keys = ON; PRAGMA journal_size_limit = 67108864; ``` `synchronous = NORMAL` skips the fsync on every commit and syncs at critical moments such as checkpoints; in WAL mode this costs recently committed transactions on a crash but not database integrity. `cache_size` defaults to roughly 2 MB, which is nothing for a server working set. `mmap_size` maps the database file into the process address space so reads become pointer arithmetic instead of `read()` calls, with the kernel page cache doing the caching; if the database is smaller than the limit, all of it is mapped. On transactions the guide is right and worth repeating. A `DEFERRED` transaction, the default, takes no lock at all and starts as a read that escalates to a write only when the first write statement runs — two connections that both read and then both try to write are the classic failure. `IMMEDIATE` takes a reserved lock up front, still allowing readers, and `EXCLUSIVE` blocks everything. The rule is that a transaction containing any write at all begins with `BEGIN IMMEDIATE`. Checkpointing gets four modes, distinguished by how much they're willing to block: - `PASSIVE` merges whatever it can without blocking anyone, and stops early if a reader is still holding an older page in the WAL. - `FULL` blocks new writers and waits for existing readers so the whole WAL merges. - `RESTART` is `FULL` plus resetting the WAL so subsequent writes begin at the start of the file. - `TRUNCATE` is `RESTART` plus truncating the WAL to zero bytes on disk. The operational point behind the list: with a continuously active reader, automatic checkpointing can never finish, so the WAL grows without bound. A scheduled `PRAGMA wal_checkpoint(PASSIVE)` from a background thread, plus `journal_size_limit`, is the containment. ## Two claims in the blueprint that don't hold The guide presents Litestream as a custom VFS layer. It isn't one. Litestream is a separate process that reads the WAL file and streams frames to object storage; SQLite has no idea it exists. LiteFS is the FUSE-based one, and it's the only half of that pair the VFS framing applies to. The section header ("Custom VFS Layers for the Cloud Era") ends up describing two tools that work at different layers as if they were the same design. `PRAGMA auto_vacuum = INCREMENTAL` also doesn't belong in a per-connection bootstrap. Auto-vacuum mode has to be set before any table is created, or changed afterwards with a full `VACUUM`; issuing it on each new connection to a populated database does nothing. The trailing comment in the blueprint, "Optimize index page allocation and query plans," describes neither what auto-vacuum does nor anything a per-connection pragma could do. ## Where the two land Hynek's case is the counterexample to the blueprint's framing rather than to any individual pragma. The pragma sequence is written for a connection pool — Micrologics says so explicitly, telling you to architect "your connection pool and transaction logic" around the single-writer constraint. Applied to open-query-close processes, an eight-pragma bootstrap runs once per `SELECT`, and the WAL mode it turns on is the thing generating the errors. `busy_timeout = 5000` would have masked Hynek's problem, at the cost of never learning that his effectively read-only system was doing constant write churn on two sidecar files. The wider claim in the Micrologics piece is the local-storage argument: on NVMe, an in-process database removes network round-trips entirely, and [[postgresql]] stays the right answer for distributed writes across regions or datasets past a few terabytes. That boundary is roughly where [[duckdb-quack-protocol]] picks up — an in-process-first engine adding a wire protocol because the workaround zoo grew larger than the protocol would be. Both are the same question of when in-process stops paying. For the schema-level half of running SQLite deliberately rather than by default, see [[sqlite-strict-tables]]: the same pattern of a permissive default that's convenient until it silently admits a class of bug. [[database-index-gotchas]] catalogs the query-planner version. [[charles-leifer-blog]] is the standing source in this vault for SQLite internals written by someone who reads the patches.