Checkpoints and Log Truncation

Why this stage exists

Every commit so far does the work twice. It appends its pages to the log and syncs, then writes the same pages to the database file and syncs again. The second half buys nothing any more: if the program stops before it, recovery does it at the next start. And it is the slow half. The log is one file written from start to end; the database file takes pages scattered all over it, and a busy page is written again at every commit that touches it.

So this stage drops it. A commit is done when its log record is synced. Its pages stay in the pool, dirty, and the database file catches up later, all at once, in a checkpoint. Real engines commit this way: PostgreSQL writes its data files in the background and at checkpoints, never at commit.

That raises the question the stage's title answers. If the database file lags behind, the log must keep every commit since it last caught up, and it grows with every commit. A checkpoint is the moment the database file has caught up completely, so the log before it is no longer needed and can be cut back to its header.

What changes: where a commit lives

The rule from the start of this part still holds: a page the open transaction changed never reaches the file. What is new is that a committed page may, at any time. That is safe because of the rule from the log's stage: by the time the commit is done, its records are synced. The database file can never get ahead of the log.

What changes: CHECKPOINT

A checkpoint writes every dirty page in page order, syncs the database file, and only then empties the log. The order matters for the same reason it did in recovery: if the program stops in the middle, the log is still whole, and the next start replays it. A checkpoint cut short is just a recovery waiting to happen.

The program checkpoints at three moments: when asked with CHECKPOINT;, at the clean end of a session, and before VACUUM moves its first page, since VACUUM leaves the log empty and must not leave any commit behind in it.

A real engine also checkpoints on its own. SQLite does when its log reaches 1000 pages; PostgreSQL every few minutes or when the log reaches max_wal_size. Both write the pages gradually, alongside other work, so a checkpoint does not stall the commits behind it. This engine checkpoints only when asked, so a test always knows what the log holds.

Output contract for this stage

The full definition is on the output contract page, section 37.

k is the pages written to the database file, r the good records the log had. mid_page_write now stops a checkpoint after its first page, the silent ones included.

What you should see when done

$droid --db notes.db
Welcome to droid!
>create table notes (id int, text varchar(100));
Create table statement executed successfully
>checkpoint;
Checkpoint statement executed successfully
[WAL] Checkpoint: pages=3 records=4
>insert into notes (id, text) values (1, 'buy milk');
Insert statement executed successfully (1 rows)
>insert into notes (id, text) values (2, 'call mom');
Insert statement executed successfully (1 rows)
>wal status
[WAL] Status: size=8288 records=4
[WAL]   Record 1: lsn=16 tx=1 type=PAGE page=2
[WAL]   Record 2: lsn=4132 tx=1 type=COMMIT
[WAL]   Record 3: lsn=4152 tx=2 type=PAGE page=2
[WAL]   Record 4: lsn=8268 tx=2 type=COMMIT
>checkpoint;
Checkpoint statement executed successfully
[WAL] Checkpoint: pages=1 records=4
>checkpoint;
Checkpoint statement executed successfully
[WAL] Checkpoint: pages=0 records=0
>.exit
exiting.. good bye!

Two commits changed the same page, and the checkpoint wrote it once. That is the saving: a hot page committed a hundred times between checkpoints is written to the database file once.

Try a commit followed by .crash, then delete notes.db.wal before restarting. The row is gone: the database file never had it. With the log kept, recovery brings it back. With a checkpoint; before the crash, deleting the log loses nothing.

Common bugs in this stage

• Emptying the log before the file is synced. A crash between the two loses every commit since the last checkpoint. Write, sync, then cut the log.
• Still holding committed pages in the pool. Only the open transaction's pages are held. A committed page that cannot be evicted brings back the transaction-too-large error for work that is long committed.
• ROLLBACK reading pages back from the file. The file may not have the last commit of a page yet. Before-images hold it; the file does not. A page restored by ROLLBACK that held an unwritten commit stays dirty.
• Forgetting the clean end. A session that ends with .exit must leave the database file holding everything: the next start should find an empty log and print no recovery line.
• Writing pages in the order you find them. Write in ascending page number. The tests' fail-point stops after the first page, and the order is what makes that page known.
• VACUUM throwing commits away. VACUUM empties the log; without a checkpoint first, every commit since the last one goes with it.
• Counting records the log no longer trusts. records= counts the good records, as wal status does, not the bytes after a torn tail.

Language notes

The page above is the same for every language. These are the only language-specific hints.

• C: collect the dirty frames' page numbers into an array and qsort it before writing; pwrite each page, fsync the database file, then ftruncate the log.
• C++: std::sort the dirty page numbers, or keep frames in a std::map keyed by page so they come out in order.
• Rust: collect the dirty page numbers into a Vec<u32> and sort_unstable(); a BTreeMap of frames gives the order for free.
• Zig: std.mem.sort(u32, pages, {}, std.sort.asc(u32)) on the dirty page numbers, then write, sync, setEndPos(16).