Report #5594
[bug\_fix] SQLite WAL mode checkpoint starvation causing unbounded file growth
Implement explicit \`PRAGMA wal\_checkpoint\(TRUNCATE\)\` during application idle periods or maintenance windows, ensure no long-lived read transactions hold back checkpoints, and lower \`wal\_autocheckpoint\` threshold if needed. Root cause: A background sync process kept a read transaction open for hours to cache data, preventing the automatic checkpoint from truncating the WAL file, causing it to grow to 10GB despite only 100MB of actual data.
Journey Context:
Deployed a desktop application using SQLite with WAL mode enabled for concurrent reads during writes. Over several weeks, users reported the \`.db-wal\` file growing to multiple gigabytes while the actual \`.db\` file stayed small. Initially suspected uncommitted transactions, but \`PRAGMA wal\_checkpoint\(PASSIVE\)\` returned \`busy\` status. Investigated using \`PRAGMA wal\_checkpoint\(FULL\)\` during maintenance mode—it completed but took 5 minutes and only reclaimed 10% of space. Checked for readers using \`lsof\` on the WAL file—found a background sync daemon process holding the file open with a read lock. Traced the code: the sync process opened a read transaction at startup to cache the entire table into memory, then kept that transaction \(and thus the SQLite read lock\) open indefinitely to 'optimize' subsequent queries. Because SQLite checkpoints require exclusive access to truncate the WAL, and the read transaction prevented that, the WAL grew unbounded with every write. The autogrow setting kept creating new WAL frames. Fix required modifying the sync process to use short-lived read transactions or \`PRAGMA read\_uncommitted = 1\` \(if acceptable\) to release locks immediately, plus scheduling explicit checkpoints during idle periods.
⚠ Workarounds are unverified - always check before running. Confirmations show what worked for others, not a safety guarantee.
Lifecycle
2026-06-15T21:43:02.143869+00:00— report_created — created