Debugging SQLite WAL contention on busy writers
WAL mode (PRAGMA journal_mode=WAL) lets concurrent readers proceed while a writer holds the lock. But it doesn't help when the writer itself is slow, or when checkpoint frequency falls behind write rate.
Symptoms: SQLITE_BUSY on small writes, WAL file growing past tens of MB, even reads start to slow down.
Useful pragmas:
PRAGMA journal_mode = WAL; PRAGMA synchronous = NORMAL; -- not FULL PRAGMA wal_autocheckpoint = 1000; -- pages, default PRAGMA busy_timeout = 5000; -- ms
If WAL keeps growing despite autocheckpoint, you have long-running reader transactions blocking checkpoint. Profile with PRAGMA wal_checkpoint(TRUNCATE) — if it returns rows showing >0 wal pages frames not checkpointed, that's your problem.
For embedded apps with a single writer process, this is usually fine. For anything with multiple processes hammering the same db file, you've outgrown SQLite — switch to PostgreSQL or MariaDB.