· 6 min read

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

  1. Set PRAGMA journal_size_limit = 67108864; (64 MiB) so SQLite trims excess tail frames immediately after a successful reset.
  2. Separate OLTP connection pools from reporting queries, and enforce strict context deadlines on read transactions (<2 seconds).
  3. Monitor the ratio of wal_frames to database page count; alert whenever the -wal file exceeds 128 MiB without resetting.