A SQLite service can keep answering health checks while its WAL grows without a successful checkpoint. One forgotten read transaction is enough to reproduce the risk.
Run a controlled drill:
- Enable WAL mode and create a small table.
- Open connection A, begin a read transaction, and keep it open.
- From connection B, commit batches of writes for 60 seconds.
- Sample WAL bytes and checkpoint results every second.
- Release A and observe recovery.
PRAGMA journal_mode=WAL;
PRAGMA wal_checkpoint(PASSIVE);
Record the three checkpoint counters plus duration, WAL file size, oldest transaction age, write latency, and free disk bytes. A green SELECT 1 says nothing about checkpoint progress.
My fault-injection gate looks like this:
Given: one reader holds its transaction
When: 10,000 writes commit
Then: writes remain bounded
And: WAL growth triggers an alert before the disk budget
When: the reader closes
Then: checkpoint progress resumes and WAL size converges
Do not start with an automatic TRUNCATE loop. A busy result is evidence of contention; aggressive checkpoints can add latency without fixing the reader lifecycle. First identify long transactions, ensure result iterators close, and define a maximum transaction age.
SQLite’s WAL documentation explains that a checkpoint must stop when it reaches pages beyond an active reader’s end mark. This is why the drill needs concurrency rather than a synthetic file-growth assertion.
Operational thresholds should be tied to a disk budget: alert when projected WAL growth reaches the remaining safe window, not at an arbitrary file size. The runbook should name how to find the oldest reader, how to shed writes, and when a restart is safer than waiting.
A health check is useful only when it measures the subsystem that is failing.
Top comments (1)
This is exactly the kind of failure drill that catches a green-but-dying service. I would add two measurements to the gate: the reader's snapshot start/end marks and the rate of WAL growth versus free-disk runway. That lets the alert say both who blocks checkpoint progress and how long remains at the current write rate. It is also worth verifying recovery under continued writes after connection A closes, not only in an idle period, and testing cancellation/exception paths so abandoned iterators release their transaction. A useful SLO pair is maximum reader transaction age plus minimum projected disk runway; WAL bytes alone will vary with workload and can create noisy thresholds.