Understanding SQLite WAL Checkpoints Under Steady Writes

Write-Ahead Logging (WAL) makes SQLite a remarkably capable single-node store for telemetry and lightweight web services. Readers do not block writers, and small transactions flush sequentially rather than seeking across B-tree pages.

Why the -wal File Grows Unexpectedly

By default SQLite triggers an automatic PASSIVE checkpoint every 1,000 pages. However, if a long-lived read transaction stays open—even a forgotten query cursor in a worker goroutine—the checkpoint cannot move past that reader's snapshot mark.

PRAGMA journal_mode = WAL;
PRAGMA synchronous = NORMAL;
PRAGMA busy_timeout = 5000;
PRAGMA wal_autocheckpoint = 1000;

Inspecting Checkpoint Progress

You can inspect pending WAL frames programmatically without stopping the application:

sqlite3 app.db "PRAGMA wal_checkpoint(PASSIVE);"
# returns: busy | log | checkpointed

• busy = 0 and log == checkpointed means every committed page is back in the main database file.
• If log keeps climbing while checkpointed stalls, check for open read snapshots holding an earlier mark.

Note also that external scripts writing directly to a WAL-mode database must respect column storage classes and file permissions so daemon writers do not hit read-only or type-mismatch errors on reload.