Predictable SQLite WAL Checkpoints in Read-Heavy Edge Services

SQLite in Write-Ahead Logging (WAL) mode comfortably serves 90,000 concurrent point queries per second per node—right up until a single streaming export coroutine holds an open read snapshot for twenty minutes. While that reader holds its end-mark, RESTART and TRUNCATE checkpoints cannot recycle frames past that mark, ballooning the -wal file from 4 MB to 3.8 GB.

The Dedicated Writer + Bounded Reader Pool Pattern

Never share a single connection pool across reads and writes in SQLite. Open one connection with txlock=immediate exclusively for serialized writes, and a separate read-only pool (?mode=ro) capped to CPU core count with a hard 2,500 ms statement deadline.

PRAGMA journal_mode = WAL;
PRAGMA synchronous = NORMAL;
PRAGMA wal_autocheckpoint = 0; -- Disable surprise inline checkpoints on write commits
PRAGMA mmap_size = 1073741824; -- 1 GiB read-only memory map
PRAGMA busy_timeout = 5000;

Disabling wal_autocheckpoint on the hot write connection and running PRAGMA wal_checkpoint(PASSIVE) on a dedicated background ticker every 1,500 ms moves page-copy work off user-facing HTTP mutations.

When Embedded Beats Networked Caches

Shipping read-mostly catalog snapshots alongside WebAssembly isolates—as detailed in Anatomy of a Sub-Millisecond WebAssembly Component Cold Start—eliminates Redis serialization hops entirely. Just remember that NVMe flush behavior still obeys the physical NAND rules analyzed in Pacing LSM-Tree Compaction Debt on High-Queue NVMe Drives.