Why your SQLite WAL file never shrinks

Originally published at getnobody.org.

If you run SQLite in WAL mode with a busy writer and a few readers, you may one day find a -wal file of several gigabytes next to a database that is much smaller. Autocheckpoint is on, nothing reports an error, and PRAGMA wal_checkpoint says it worked. I read the checkpoint code in SQLite 3.53.2, then built a small lab to watch it happen.

The short version: when read transactions overlap so that at least one is always open, the checkpoint can never finish and the writer can never go back to frame 1. In my runs the WAL grew by 11.9 MiB per second for as long as the overlap lasted, and when the readers went away it stayed at 357 MiB. A wal_checkpoint(TRUNCATE) every 5 seconds kept it under 60 MiB in 2 of 3 runs (119 MiB in the run where one call came back busy), at the price of stalling the writer for up to 1.8 s each time.

How WAL mode commits and checkpoints

In WAL mode a commit doesn't touch the database file. The writer appends the changed pages to the -wal file as frames (a 24-byte header plus one page), and a shared-memory index in the -shm file maps page numbers to the newest frame that holds them. Readers look there first and fall back to the database file.

A checkpoint copies frames back into the database file. Two counters in the shared index drive everything (wal.c L321-L401):

• mxFrame is the last committed frame.
• nBackfill is how far the checkpoint has copied.

When nBackfill reaches mxFrame, everything in the WAL is in the database, and the next writer can start again at frame 1 instead of appending. It overwrites the old file from the start. It doesn't truncate it.

By default SQLite runs a checkpoint after any commit that leaves the WAL at 1,000 frames or more (sqlite3WalDefaultHook, main.c L2470-L2481). That checkpoint is the PASSIVE kind: it never waits for anybody.

(Chart: see the original post.)

What a reader pins

A reader doesn't copy anything. When it starts a read transaction it pins one of four read marks (aReadMark[1..4], since WAL_NREADER is 5 and slot 0 is special) whose value is no greater than the mxFrame it saw. If a slot already holds that value it shares the slot with the readers already on it. Otherwise it writes its mxFrame into a slot it can lock exclusively, and if it can't get one, it settles for the largest older mark (walTryBeginRead, L3163-L3199). It holds a shared lock on that slot until the transaction ends.

Frames after its mark are newer than its snapshot. For pages changed in those frames, the reader still needs the old copy in the database file, so the checkpoint may not copy those frames over it until the reader finishes (L378-L381). Frames up to its mark are safe to copy.

If the WAL is fully copied when the reader starts, it takes slot 0 instead, which means "read the database file only, ignore the WAL" (walTryBeginRead, L3128-L3160). Remember that slot 0, it matters later.

Why the checkpoint stops at the oldest reader

walCheckpoint starts by assuming it can copy everything, then walks the four read slots (L2227-L2245):

mxSafeFrame = pWal->hdr.mxFrame;
mxPage = pWal->hdr.nPage;
for(i=1; i<WAL_NREADER; i++){
  u32 y = AtomicLoad(pInfo->aReadMark+i); SEH_INJECT_FAULT;
  if( mxSafeFrame>y ){
    assert( y<=pWal->hdr.mxFrame );
    rc = walBusyLock(pWal, xBusy, pBusyArg, WAL_READ_LOCK(i), 1);
    if( rc==SQLITE_OK ){
      u32 iMark = (i==1 ? mxSafeFrame : READMARK_NOT_USED);
      AtomicStore(pInfo->aReadMark+i, iMark); SEH_INJECT_FAULT;
      walUnlockExclusive(pWal, WAL_READ_LOCK(i), 1);
    }else if( rc==SQLITE_BUSY ){
      mxSafeFrame = y;
      xBusy = 0;
    }else{
      goto walcheckpoint_out;
    }
  }
}

If it can lock a slot exclusively, nobody is using that mark, so it resets it: slot 1 to mxSafeFrame, the others to READMARK_NOT_USED (0xffffffff, unused). If it can't, a live reader is there, and mxSafeFrame drops to that reader's mark. The copy loop then skips every frame after mxSafeFrame (L2306), and nBackfill only advances to it (L2331).

Then comes the line that makes this hard to notice (L2340-L2345):

if( rc==SQLITE_BUSY ){
  /* Reset the return code so as not to report a checkpoint failure
  ** just because there are active readers.  */
  rc = SQLITE_OK;
}

So PRAGMA wal_checkpoint(PASSIVE) returns busy = 0 even when it copied only part of the WAL. The signal is in the other two columns: log (frames in the WAL) keeps getting bigger than checkpointed.

Why the file grows, and why it stays big

Copying is only half of it. The WAL goes back to frame 1 in walRestartLog (L3869-L3909), which runs at the start of every write. It rewinds only if the writer's own snapshot is on slot 0 (pWal->readLock==0, so the WAL was fully copied when the writer started), and it can lock read slots 1 to 4 exclusively, meaning no reader is using the WAL.

Now put a steady writer next to readers that overlap. Some reader always holds a mark from a second or so ago. The checkpoint copies up to that mark and stops. nBackfill never catches up with mxFrame, so the writer is never on slot 0, so it never rewinds. Every commit appends. The -shm index grows with it, one 32 KiB block per 4,096 frames (HASHTABLE_NPAGE, L615).

The SQLite docs describe exactly this under checkpoint starvation: "if a database has many concurrent overlapping readers and there is always at least one active reader, then no checkpoints will be able to complete and hence the WAL file will grow without bound."

And when the overlap ends, the file keeps its size. A rewind moves the write position to frame 1, but, as the same page says, a checkpoint "does not normally truncate the WAL file (unless the journal_size_limit pragma is set)". The file only gets smaller when:

• journal_size_limit is set, and the first commit after a rewind trims the file to that limit (L4210-L4221). The default in this build is -1, no limit.
• someone runs wal_checkpoint(TRUNCATE), which truncates it to 0 bytes (L2363-L2381).
• the last connection to the database closes, checkpoints and deletes the WAL.

The experiment

One writer process commits one row with a 1,000-byte blob per transaction, open loop at a fixed 2,000 commits per second, with synchronous=NORMAL. Reader processes each run BEGIN, a SELECT that takes the snapshot, hold it, look up 100 recent rows, COMMIT, then idle. A sampler records the file sizes every 100 ms. Seven scenarios, 30 s each, 3 runs of each, interleaved:

Here is what the readers actually did in 3 seconds of one run of b and one of c, straight from the logged open and close times:

(Chart: see the original post.)

And here is the WAL file:

(Chart: see the original post.)

With gaps (b), the WAL rewinds twice a second, once per gap, 60 times in 30 s, and peaks at 6.05 to 6.08 MiB. With overlap (c), it never rewinds once, and grows in a straight line at 11.91 MiB/s in all three runs. That is about 1.5 frames of 4,120 bytes per commit: the final checkpoint counted 90,873 frames for 60,000 commits. At the end the -shm file was 736 KiB, 23 index blocks.

Through all of c, every PASSIVE probe returned busy = 0, while log ran up to 2,751 frames ahead of checkpointed. That is a little under one second of writes, about one reader hold time.

After the readers leave

When the run ended I stopped the readers and ran one more PASSIVE checkpoint with nothing else open. It copied everything, returned 0 | 90873 | 90873, and the file stayed where it was:

(Chart: see the original post.)

journal_size_limit did nothing in e. Its trim runs on the first commit after a rewind, and under overlap there are no rewinds. In f, where there are, it held the file at 4.00 MiB, though the high-water mark still reached 6.01 MiB between trims. In one of the three e runs the file was already 4.00 MiB before that final checkpoint. Most likely the readers closed a moment before the writer's last commits during shutdown, so one commit found a fully copied WAL, rewound and was trimmed: the same mechanism, with the blocker gone.

What TRUNCATE costs

The non-passive modes behave differently (sqlite3WalCheckpoint, L4295-L4426). FULL, RESTART and TRUNCATE take the writer lock first, waiting with the busy handler. Because xBusy is set, walBusyLock then waits on each reader slot instead of giving up. RESTART and TRUNCATE also wait until no reader is using the WAL at all (L2358-L2362), and TRUNCATE then cuts the file to 0 bytes. Readers that start after the WAL is fully copied take slot 0, which doesn't hold the checkpoint up, and in my runs the readers' 100-row lookup was no slower in d than in c (226 against 224 µs median). The SQLite docs do warn that readers might block while it runs. The writer is blocked for sure, because the checkpoint holds its lock.

In d, 14 of 15 TRUNCATE calls reset the WAL and returned 0 | 0 | 0. They took 1.40 to 1.81 s (median 1.70 s) with readers that hold for 1.0 s. That is close to two hold times, and the code explains why: first the call waits for the readers pinned below mxFrame, but while it waits, readers that start see a WAL that isn't fully copied yet, so they take a real mark at the new mxFrame. The RESTART step then has to wait for those too. On all 14 calls, the checkpoint returned within 105 ms of the last close of a reader that had started before the copy finished.

"Response time" is when a commit finished minus when the open-loop schedule said it should start, so a stall counts in full. Time inside execute() hides it: only one commit per TRUNCATE actually waits, and the thousands queued behind it run fast once it lets go. If you measure the effect of a checkpoint job on your writer, measure response time.

The one TRUNCATE that failed came back in 1.68 ms with 1 | -1 | -1. That is the WAL_CKPT_LOCK check at the very start (L4336): if another checkpoint is already running, every mode returns busy without calling the busy handler. My probe wasn't running at that moment, so the other checkpoint was almost certainly the writer's own autocheckpoint, which under starvation runs after every commit. That call missed, the WAL grew for another 5 s, and that run's high-water mark was 118.86 MiB instead of about 60.

Two smaller results

• Checkpointing on the writer's connection costs it something under starvation. Once the WAL is past 1,000 frames, the autocheckpoint runs after every commit and copies whatever it can. In g the writer's autocheckpoint was off, and the only checkpoint was the sampler's PASSIVE probe once a second from another connection. That dropped the writer's p50 from 38 to 32 µs and its p99.9 from 2,653 to 694 µs. The WAL grew exactly as fast, and the probe kept nBackfill as close as the autocheckpoint did (at most 2,743 frames behind, against 2,751 in c), since the checkpoint wasn't the thing holding the WAL down anyway.
• Reads did not get slower, and the code says they shouldn't. A reader doesn't search the whole WAL index. walFindFrame walks hash blocks only from its mxFrame down to the block holding minFrame (L3568-L3569), and minFrame is nBackfill + 1 when the read transaction starts (L3253). So lookup cost depends on how far the checkpoint lags, not on the size of the WAL. Under starvation the lag stayed at most 2,751 frames, less than one 4,096-frame block, so a lookup searched 1 or 2 blocks. In c the median was 216 µs for transactions that started 5 to 12 s into the run and 218 µs for 22 to 30 s, at a WAL of up to 357 MiB. Reads would slow down only if nBackfill itself fell far behind, for example with autocheckpoint off and no other checkpointer. I didn't measure that.

What to do in your app

1. Keep read transactions short. The problem is overlap, and overlap comes from long snapshots: a cursor left open while you call an API, a BEGIN that a connection pool never closed. In Python, a SELECT whose rows you haven't fully fetched keeps its statement active, and an active statement keeps its read snapshot.
2. Watch for it. Poll PRAGMA wal_checkpoint(PASSIVE) and alert when log - checkpointed keeps rising, and watch the size of the -wal file. The busy column won't tell you.
3. Set journal_size_limit, for example PRAGMA journal_size_limit = 67108864 for 64 MiB. It doesn't stop starvation, but it gives back the disk the next time the WAL rewinds.
4. Run wal_checkpoint(TRUNCATE) or (RESTART) from a maintenance job with a busy timeout, when writes are quiet. Expect it to block the writer for up to about twice your longest read transaction. If it returns busy = 1, retry soon rather than waiting for the next period.
5. If you turn off autocheckpoint, checkpoint from another connection. In g, one PASSIVE checkpoint a second from a separate connection kept up as well as the autocheckpoint and took that work off the writer. With nothing checkpointing at all, nBackfill never reaches mxFrame, so the WAL never rewinds, gaps or not.

How I measured

• Machine: Apple M5 Pro (18 cores, 48 GiB), macOS 26.6.2, APFS on the internal SSD.
• Software: Python 3.14.4 and the SQLite it links, 3.53.2 (DEFAULT_WAL_AUTOCHECKPOINT=1000, DEFAULT_JOURNAL_SIZE_LIMIT=-1, from PRAGMA compile_options). The source links above are pinned to the version-3.53.2 tag, because that is what Python links here. 3.53.3 and 3.53.4 are out.
• Runs: 7 scenarios × 3 runs × 30 s, interleaved. The first 5 s of each run are left out of the latency statistics. Tables show the median of 3 runs. The writer is open loop, so every run issued all 60,000 scheduled commits, and all of them completed with no write errors. That says nothing about stalls; the response times above do.
• Caveats:
  • The sampler's PASSIVE probe once a second is itself a checkpoint, and it runs in every scenario. Where autocheckpoint is on, it copies no more than the autocheckpoint would. In g it is the only checkpointer, so g means "one PASSIVE checkpoint a second from another connection", not "no checkpointing". --probe-every 0 turns it off.
  • The first full pass had a probe colliding with the TRUNCATE calls and a maintenance call firing after the run had ended. I fixed both and reran everything. All numbers here are from the second pass.
  • This is one machine and one filesystem. I did not run the Linux cross-check or a sweep of write rates.
  • "MiB" is 2^20 bytes.

The scripts, the raw CSVs and how to rerun them are in the lab folder. python3 run.py runs the full matrix in about 12 minutes with nothing but the standard library.

Credit goes to D. Richard Hipp and the SQLite developers, who wrote and maintain the WAL code and whose WAL documentation already describes this failure and the "reader gaps" fix in plain words. The code is commented well enough that a stranger can follow a checkpoint line by line, which is how this post got written. The same page also documents this year's WAL-reset bug, a rare corruption case when two connections write or checkpoint at the same instant, which one of the SQLite developers, Dan, found and fixed in 3.51.3.