SQLite WAL checkpoint starvation

Everything behind the post Why your SQLite WAL file never shrinks.

Files

run.py: the entry point. Defines the 7 scenarios, starts the writer, readers and checkpoint thread, samples the file sizes every 100 ms, and writes the results. The header comment explains every measurement.
workload.py: the writer (open loop at a fixed commit rate), the readers (hold a snapshot, look up 100 rows, idle) and the maintenance checkpoint loop

Standard library only. Needs Python 3 with its built-in sqlite3 module.

Scenarios

	What runs next to the 2,000 commits/s writer
a	nothing
b	2 readers, 0.4 s snapshot every 1.0 s (about 20% of the time nobody reads)
c	3 readers, 1.0 s snapshot, 0.2 s idle, staggered (at least 2 always open)
d	c + `PRAGMA wal_checkpoint(TRUNCATE)` every 5 s, 5 s busy timeout
e	c + `PRAGMA journal_size_limit` 4 MiB
f	b + `PRAGMA journal_size_limit` 4 MiB
g	c with `wal_autocheckpoint=0` on the writer; the once-a-second PASSIVE probe is then the only checkpointer

Raw results

env.json: machine, OS, Python, SQLite version and compile options
runs.csv: one row of metrics per run (21 runs)
summary.csv: median, min and max of every metric per scenario
timeline.csv: 100 ms samples of the -wal, -shm and database sizes, open readers, WAL resets and probe results
writer_seconds.csv: per-second writer commits and latency percentiles
latency_hist.csv: writer service time and response time histograms
readers.csv: every read transaction, with open and close times and lookup latency
checkpoints.csv: every TRUNCATE call in scenario d, with its result and duration
results.json: environment, scenario definitions and the summary in one file

Run it

# print the SQLite version your Python links
python3 -c 'import sqlite3; print(sqlite3.sqlite_version)'

# full matrix: 7 scenarios x 3 runs x 30 s, about 12 minutes
python3 run.py

# a quick look at two scenarios
python3 run.py --only c,d --runs 1 --duration 10

# turn off the once-a-second PASSIVE probe
python3 run.py --probe-every 0

# where the databases live (default: the OS temp dir)
python3 run.py --workdir /path/on/another/disk

Results go to results/ next to the script and overwrite the files above. Scenario c writes a WAL of about 360 MiB per 30 s run; databases are deleted after each run, and a run stops early if the WAL passes --max-wal-gb (default 20).

Tested on an Apple M5 Pro (18 cores, 48 GiB), macOS 26.6.2, APFS on the internal SSD, Python 3.14.4 with SQLite 3.53.2. Source links in the post are pinned to version-3.53.2.