SQLite's WAL grows while any reader is open, even short ones

In WAL mode, SQLite appends every commit to a -wal file and copies it back into the database at a checkpoint, by default once the WAL reaches 1,000 pages. With no readers, my test writer's WAL never got past 3.9 MB. With one Python cursor left half-read, it grew 88 MB in 15,000 commits and kept growing. Four readers that each lived for only 20 ms grew it exactly as fast, as long as one of them was always open. When the readers go away, the file stays 89 MB until something truncates it. Explicit RESTART checkpoints with a short busy timeout held it to about 12 MB; a longer timeout made the writer wait up to a second and didn't help.

Why a reader holds the WAL

A read transaction in WAL mode sees the database as of the moment it started: the database file plus WAL frames up to a mark. A checkpoint can copy frames into the database file only up to the oldest open reader's mark, because copying later frames would change pages that reader still reads from the file. And the writer can only go back to the start of the WAL, instead of appending, once every frame has been copied and no reader is still using the WAL. SQLite's WAL documentation calls the second case checkpoint starvation: readers that overlap so there's never a moment without one.

The test

One writer connection commits one 1,000-byte row per transaction, about 1.5 WAL frames per commit, with the default auto-checkpoint. A reader opens before the first commit and is released after commit 15,000 of 20,000. I recorded the WAL file's size every 100 commits. Everything is single-threaded and deterministic, so each number below is exact for this workload; SQLite 3.46.1 and 3.53.1 gave identical sizes. Python 3.14.

The Python rows are the ones that surprise people. A query whose rows you haven't all read is still running inside SQLite, and in these runs a running statement kept its snapshot even outside any transaction I started. So cur.fetchone() on a multi-row result, or a generator that yields rows and then waits for its caller, pins the WAL for as long as the cursor or generator lives. With autocommit=False, Python's PEP 249 mode added in 3.12, the module keeps a transaction open at all times: a connection that only reads and never calls commit() holds its first snapshot forever. The default mode opens transactions only before writes, so a read-only connection there is harmless.

Short readers do it too

Next, no reader stays long. Each reader runs for L commits and a new one starts every L/R commits, so R readers overlap and one is always open.

How long each reader lasts doesn't matter, only whether there's ever a gap. One commit with no reader open, every 201 commits, was enough to keep the WAL under 5 MB. The gap has to land on a commit, though. In a threaded version, with one reader thread looping on BEGIN, a 20 ms read and COMMIT, and a writer at 1,000 commits a second, the reader is idle for microseconds between transactions. The writer never hit those gaps, and the WAL grew to 59.6 MB in 10 seconds, the same as with four overlapping readers.

The file doesn't shrink afterwards

Once the readers in the first table were gone, a checkpoint copied everything back and the next write started over at the beginning of the WAL. The file stayed 89 MB anyway: SQLite reuses the WAL file from the start rather than shrinking it. It only got smaller with PRAGMA wal_checkpoint(TRUNCATE), which cut it to 0, or with PRAGMA journal_size_limit set, which trimmed it to the limit (4 MB in my runs) when the WAL was reset. Without either, a single long reader leaves its high-water mark on disk for good.

Can a checkpoint break the starvation?

The threaded setup again: four reader threads, each holding a read transaction for 20 ms, staggered so one is always open; the writer at 1,000 commits a second for 10 seconds, calling an explicit checkpoint every 1,000 commits with the connection's busy timeout set to various values. Five runs each (PASSIVE: one run each at timeouts of 0, 100 and 1,000 ms), three at a time on the same machine. Without any explicit checkpoint the WAL reached 59.6 MB.

PASSIVE is what auto-checkpoint already does, so it changes nothing. FULL waits until readers are on the latest snapshot and copies everything back, but it doesn't wait for them to stop using the log, so the writer still can't start over and the file keeps growing. RESTART and TRUNCATE work, but only with a busy timeout: at 0 they give up every time. With one they succeeded most of the time, and the WAL stayed at two or three checkpoint intervals' worth. The checkpoints that failed waited out the whole timeout and failed anyway, so raising the timeout from 50 ms to a second barely changed the WAL size and made the worst writer stall about 16 times longer. In this workload, about 50 ms, a bit more than the readers' 20 ms, was the useful setting.

What to do

Fix the readers first. Read results with fetchall() or close the cursor, don't keep a generator over a live query paused across slow work, and if you use autocommit=False, commit or roll back read-only connections when they're done. If many short readers overlap all the time, as on a busy web server, schedule a RESTART or TRUNCATE checkpoint with a busy timeout a little longer than a typical read. And set journal_size_limit so that one bad afternoon doesn't leave a WAL on disk the size of its worst moment.

Versions: Python 3.14.8 with SQLite 3.46.1 on Linux, and Python 3.14 with SQLite 3.53.1 on macOS for the single-threaded runs, page size 4,096, default wal_autocheckpoint of 1,000 pages. Sizes are in MiB, written MB. The threaded numbers depend on timing, so treat their exact values as one machine's.