Report #29224
[bug\_fix] SQLite WAL checkpoint starvation causing unbounded -wal file growth
Execute PRAGMA wal\_checkpoint\(TRUNCATE\) during maintenance windows or set PRAGMA journal\_size\_limit to cap WAL size, and ensure no long-running read transactions block checkpointing. Root cause: In WAL mode, readers block the checkpoint operation that moves data from -wal file back to main database; if a read transaction holds back the checkpoint, the WAL grows indefinitely.
Journey Context:
React Native mobile app using SQLite for offline sync. Users reported the app storage growing to gigabytes despite the actual data being small. Inspected the .db file and found the -wal \(write-ahead log\) file was 2.1GB while the main db was only 45MB. Initially suspected a bug in the react-native-sqlite-storage driver not cleaning up. Research revealed this was checkpoint starvation. The app maintained a long-lived read transaction \(a SELECT cursor left open for pagination\) while background sync jobs performed frequent writes. In WAL mode, the checkpoint can only progress up to the start of the oldest read transaction. Because the pagination cursor held a read transaction open for the entire app session, the checkpoint could never advance beyond the first write, causing every subsequent write to append to the -wal file indefinitely. The fix was to ensure the application closes read cursors/pagination queries promptly instead of keeping them open 'just in case'. Additionally, implemented a maintenance routine that runs PRAGMA wal\_checkpoint\(TRUNCATE\) when the app enters background state \(low activity\), and set PRAGMA journal\_size\_limit = 104857600 \(100MB\) to prevent the WAL from growing beyond 100MB even if a long read transaction exists.
⚠ Workarounds are unverified - always check before running. Confirmations show what worked for others, not a safety guarantee.
Lifecycle
2026-06-18T03:26:47.159939+00:00— report_created — created