In WAL mode, SQLite writes the pages your DELETE just freed

I deleted 10,000 rows from a 200,000-row SQLite table, which emptied 526 pages. With a rollback journal, SQLite wrote 8 pages to the database file. In WAL mode it wrote 533 pages to the WAL and copied all 533 back into the database at the next checkpoint: the 526 freed pages, which hold nothing anyone will read, were written twice. The code marks freed pages "don't write", and only the rollback-journal path checks that mark.

That led to a second question about a common pattern, a rolling log table that inserts new rows and deletes the oldest. For it, auto_vacuum=FULL cut WAL writes from 116 to 67 pages per commit. No mode kept the table's pages in order, though, and once the order was broken a cold full scan of the 226 MB table took about 1 second instead of 0.11 seconds after a VACUUM.

Freed pages in WAL mode

When a b-tree page becomes empty, freePage2() in btree.c puts it on the freelist and calls sqlite3PagerDontWrite(), which flags the cached page PGHDR_DONT_WRITE: its content is garbage, so there's no need to save it. The page is already dirty, because the delete removed its cells one at a time before it emptied. In rollback-journal mode, pager_write_pagelist() skips flagged pages when writing the database file. In WAL mode the commit goes through pagerWalFrames() instead, which drops only pages past the end of a shrinking file and never looks at the flag. So every freed page becomes a WAL frame, and the checkpoint later copies it into the database file.

I counted page writes per file with a counting VFS (apsw with SQLite 3.54.0), for one DELETE of the oldest 10,000 of 200,000 rows of about 200 bytes each:

The rollback journal still has to save the old contents of all 533 pages, so it isn't free either, but the database file only gets the 8 pages that really changed. WAL writes the garbage to both files. SQLite 3.53.1 through Python gave the same 533 WAL frames, and secure_delete, which zeroes freed pages on purpose, didn't change the count.

A rolling log table

The workload: a rowid table of 1 million rows of 150 to 250 bytes (226 MB). Each transaction inserts the 1,000 newest rows and deletes the 1,000 oldest. WAL mode, with a checkpoint after every commit so I could count frames. I ran 2,000 commits (the table turned over twice) and 10,000 commits (ten times), under each auto_vacuum setting (the table below has no secondary index):

• NONE, the default: freed pages go on the freelist and later inserts reuse them.
• FULL: at every commit, autoVacuumCommit() moves pages from the end of the file into the free slots and truncates the file.
• INCREMENTAL: the same moves, but only when you run PRAGMA incremental_vacuum. I ran it after every commit, or after every 100th.
• NONE followed by one VACUUM at the end, as the reference for a freshly rebuilt file.

The file size stayed at 226 to 227 MB in every mode, since inserts reuse whatever deletes free. The differences were in what got written and where pages ended up.

Page order comes from the dbstat virtual table, which lists every b-tree page with its path from the root; sorted by path, the table's leaf pages come in the order a scan visits them. A "sequential run" is a stretch of leaves on consecutive page numbers. The numbers are means over 2 or 3 seeds, which agreed to within 0.3 percentage points.

FULL writes less for the reason in the first section. Each commit adds about 55 new leaf pages at the end of the file and frees about 55 old ones. Under NONE, both sets get written: the new pages because they're new, the freed ones because WAL ignores the "don't write" flag. Under FULL, the new pages are moved into the freed slots before anything is written, the file is truncated, and the commit writes each page once, at its final place. Running incremental_vacuum as a separate step doesn't get this saving, because by then the freed pages have already been committed.

But no mode keeps the table in order. Under NONE, new leaves land wherever freelist pages happen to be. Under FULL they're moved into the holes, and that scattered them fastest: after ten turnovers, the average sequential run was under 3 pages.

What scattered pages cost

With the file in the OS cache, nothing: a full scan (SELECT sum(length(payload)) FROM log) took 0.10 seconds in every mode. Cold is different. I made each cold copy by writing the file with caching turned off (F_NOCACHE on macOS), checked with mincore that such a copy has none of its pages resident, and scanned each layout 5 times this way:

The cost isn't proportional to the damage. A layout where 93% of steps go to the next page scanned about as slowly as one where 64% do. The likely reason is readahead: the OS reads ahead only while reads stay sequential, and a jump every 7 to 14 pages keeps it from ever getting going. Reading the same 55,198 pages with plain 4 KiB reads took 0.05 s cold in sorted order and 0.95 s in the table's scan order.

An index on a random key is worse: its page splits scatter it in every mode (mean run 1.0 page). A cold index-only scan over the index's 4,113 pages took 0.53 s under both NONE and FULL, and 0.006 s after VACUUM laid them out in order.

These cold numbers are noisy, and I nearly got one wrong. Random 4 KiB reads on this machine's SSD came in two speeds, about 17 µs or 50 to 65 µs per page, switching between runs for reasons I didn't pin down. Sometimes a churned scan finished in 0.2 s. With three seeds, FULL looked 8 times slower than NONE on the index scan. Alternating the two files 9 times gave 0.527 s and 0.533 s. The medians above are the slow speed, which was the usual one.

What to do

If a WAL database mostly inserts new rows and deletes old ones, auto_vacuum=FULL saved about 40% of WAL writes here, and the file never holds free pages. It has to be set before the first table is created, or followed by a VACUUM. It won't keep the data in order; nothing short of VACUUM does. That only matters for data read cold: databases larger than memory, or the first queries after a restart. Then a periodic VACUUM, or VACUUM INTO a new file that you then swap in, makes cold scans close to 10 times faster here, and index scans much more than that.

Versions: SQLite 3.53.1 (Python 3.14 on macOS, Apple SSD) for the workload and timings, SQLite 3.54.0 through apsw on Linux for the write counts. Page size 4,096. Sizes in MB are millions of bytes.