Dailyy Notes

Checkpointing Tail Latency in SQLite WAL Mode

October 19, 2026 · Storage

When a long-lived read transaction holds an older read mark in the -shm index, SQLite cannot reset the WAL file header back to frame zero. Running dedicated short-lived read connections and scheduling periodic PRAGMA wal_checkpoint(PASSIVE) keeps the WAL bounded under 4 MB.

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

In benchmark runs with 8 concurrent readers and 1 writer, p99 write latency dropped from 41 ms to 1.8 ms after bounding reader transaction lifetimes to 250 ms.