Diagnosing SQLite WAL Checkpoint Starvation Under Concurrent Readers
SQLite
Storage
Enabling Write-Ahead Logging (PRAGMA journal_mode=WAL) is the single highest-impact tweak for concurrent SQLite services: readers no longer block writers, and writers no longer block readers. However, under sustained read concurrency, operators sometimes wake up to find a 12 MiB database accompanied by a 4 GiB -wal file.
How Unbounded WAL Growth Happens
Every open read transaction pins the WAL read mark (mxFrame) at the point the transaction began. Background auto-checkpoints can copy pages up to that read mark back into the main database file, but they cannot wrap around or truncate the WAL file until every older reader releases its snapshot.
Common Culprit
An idle cursor left open in an HTTP handler, or a slow analytics export running inside
BEGIN DEFERRED, blocks WAL reset for every concurrent writer in the process.
PASSIVE vs RESTART vs TRUNCATE
sql — checkpoint_check.sql
-- Inspect current WAL pages vs checkpointed pages: returns (busy, log_frames, checkpointed)
PRAGMA wal_checkpoint(PASSIVE);
-- Force truncation once active readers drain, using a 5s busy timeout
PRAGMA busy_timeout = 5000;
PRAGMA wal_checkpoint(TRUNCATE);
Production Guardrails
- Set
PRAGMA journal_size_limit = 67108864;(64 MiB) so SQLite trims excess tail frames immediately after a successful reset. - Separate OLTP connection pools from reporting queries, and enforce strict context deadlines on read transactions (<2 seconds).
- Monitor the ratio of
wal_framesto database page count; alert whenever the-walfile exceeds 128 MiB without resetting.