SQLite WAL checkpoint starvation: success is not completion

The snapshot, not the connection count

A WAL reader retains a view of committed data for its transaction. Its end mark limits which page versions it can see. A later commit need not become visible until that reader ends its transaction and starts another. [4]

Checkpointing must preserve the versions still needed by readers. Repeatedly retained snapshots can therefore prevent WAL recycling: checkpoint starvation. An open connection without an active read transaction is not the same condition. [1]

Observed: three different completion signals

Both cases started with the same 32 rows. One reader kept an explicit transaction open after reading generation 1 from the WAL. The control read and finalized its statement without retaining a transaction. A separate connection committed four updates. We then requested a PASSIVE checkpoint after each update.

The pinned reader continued returning generation 1 while the writer saw generations 2 through 5. All 32 IDs, generation fields and complete 1,024-byte payloads matched their expected values on every check. After reader release, both connections saw generation 5. Structural integrity checks also returned ok.

The control checkpointed every current frame after each batch. Its WAL file remained 90,672 bytes even when only 11 frames were current. In the pinned case, RESTART finished after release without shrinking the file; the following TRUNCATE reduced it to zero. A retained file is not, by itself, a backlog measurement.

Read all three columns, and keep the mode

PRAGMA main.wal_checkpoint(PASSIVE) returns a busy indicator, log-frame count and checkpointed-frame count. Zero in the first column does not assert equality of the other two. A count of -1 can mean there is no WAL; do not turn that sentinel into a negative backlog. [3]

The checkpointed count includes earlier progress in the current WAL cycle. It is not the number of frames copied by just this call. PASSIVE does not wait for readers via the busy handler. RESTART additionally waits for WAL readers to finish; TRUNCATE also shortens the file on success. These stronger modes can still return busy. Our test explicitly used a zero busy timeout. [2]

Monitoring synthesis: keep mode, result, current frame counts, file length and read-transaction lifetime together. Compare nonnegative counts from the same call; resets make them unsuitable as lifetime counters. A growing difference deserves investigation, not automatic deletion of the WAL. Calling PASSIVE changes database state; it is not a read-only status query.

The auto-checkpoint threshold is not a file-size cap

SQLite's auto-checkpoint setting is per connection and triggers a PASSIVE attempt after a commit reaches the configured frame threshold. It does not promise that every attempt completes or that the file is truncated. [5]

Gaffney and colleagues' VLDB 2022 paper connects BLOB size and checkpoint cost: its large-BLOB workload crossed the checkpoint threshold within one update. The evaluation used SQLite 3.38.0 and DuckDB 0.3.2 on cloud and Raspberry Pi hardware, reporting means of three trials. Those historical throughput results do not estimate long-reader prevalence or establish a current speed ranking. We reviewed the method and BLOB results, but did not reproduce that benchmark. [6, sections 4 and 4.3]

Our experiment disables automatic checkpoints to isolate the requested operations. It does not measure the default policy. Frequent checkpoint attempts cannot by themselves end an application's old read transaction.

Method, reproduction and limits

Recorded on Linux, Python 3.12.3, SQLite 3.45.1; source ID, compile options, kernel, script hash and effective settings are in the JSON. Each fresh database used 4,096-byte pages, synchronous=FULL, wal_autocheckpoint=0 and busy_timeout=0. A seed update created a WAL-resident snapshot before the reader opened its transaction.

All SQLite calls ran sequentially on one thread. Transaction lifetimes overlapped, but writer and checkpoint calls never ran simultaneously. This is a deterministic software observation, not a concurrency stress test, power-loss experiment, durability proof, latency benchmark or probability sample. Exact counts belong to this build and workload. No confidence interval is meaningful for this deliberately constructed two-case comparison.

The installed library is not a patch recommendation. SQLite documents a separate WAL-reset race fixed in 3.51.3 and specified backports. Our schedule does not exercise that race or establish its absence in this build. [1, section 11]

Full observations and provenance (JSON) / Reproduction script (Python). Use disposable storage. The script creates and removes its own temporary databases and refuses an existing output filename.

python3 sqlite-wal-readers.py --work-root . --output result.json

Run recorded 2026-10-14T08:20:23.161354+00:00; no random seed or external Python packages.

A practical investigation sequence

• Record actual transaction lifetime, not only connection-pool size.
• Check statement finalization and application paths that retain read transactions.
• Record the checkpoint mode and all three result columns before interpreting success.
• Keep database, WAL and recovery state intact; do not delete a WAL to reclaim space.
• Test any scheduling change against the application's read consistency and latency requirements.

These are engineering checks, not a universal tuning recipe. For persistence and recovery scope, continue with WAL, checkpoints and torn pages. A checkpoint still is not an independent backup.

Primary sources

1. SQLite: Write-Ahead Logging, sections 2, 6 and 11
2. SQLite: sqlite3_wal_checkpoint_v2, checkpoint modes and return counters
3. SQLite: PRAGMA wal_checkpoint, three-column result
4. SQLite: Isolation, WAL snapshots and transaction lifetime
5. SQLite: sqlite3_wal_autocheckpoint, connection-local PASSIVE threshold
6. Gaffney, Prammer, Brasfield, Hipp, Kennedy and Patel. SQLite: Past, Present, and Future. PVLDB 15(12), 3535-3547 (2022). DOI 10.14778/3554821.3554842. Sections 2.2.2, 4 and 4.3.
7. SQLite: WAL-mode File Format, WAL-index header and read marks

Sources checked 14 October 2026. Paper figures are not reproduced.