Checkpointing — Truncating Old Logs

Without checkpointing, the WAL grows forever and recovery takes longer with each crash. Checkpointing writes dirty pages to disk and allows old WAL records to be discarded.

The Problem

After running for hours, the WAL might contain millions of records:

WAL: [LSN 1] [LSN 2] ... [LSN 5,000,000]
Recovery time: replay all 5 million records → slow!

What a Checkpoint Does

A checkpoint performs these steps:

1. Flush dirty pages: Write all modified pages from the buffer pool to the database file
2. Record checkpoint LSN: Note the current LSN as the checkpoint position
3. Truncate WAL: Discard all WAL records before the checkpoint LSN

Before checkpoint (LSN=5000):
WAL: [1] [2] ... [4999] [5000] [5001] ...
                          ↑ checkpoint here

After checkpoint:
WAL: [5001] [5002] ...
Database file: updated with all changes through LSN 5000

Now recovery only needs to replay records after LSN 5000.

Checkpoint Types

SQLite uses a cooperative checkpointing model with modes PASSIVE (don't block readers), FULL (wait for readers), and RESTART (block and reset WAL).

When to Checkpoint

Common strategies:

• Time-based: Every N minutes
• Size-based: When WAL reaches N megabytes
• Transaction-based: Every N committed transactions

PostgreSQL checkpoints based on both time (checkpoint_timeout, default 5 min) and WAL size (max_wal_size, default 1GB).

Fuzzy Checkpoints

A fuzzy checkpoint doesn't require stopping all activity:

1. Record "checkpoint begin" with current LSN
2. Gradually flush dirty pages while transactions continue
3. Record "checkpoint end"

On recovery, start from the "checkpoint begin" LSN — some pages before this might not have been flushed yet.

How SQLite Checkpoints

In WAL mode, SQLite's checkpoint copies pages from the WAL file back into the main database file. The wal_checkpoint pragma controls this:

PRAGMA wal_checkpoint(TRUNCATE);  -- checkpoint and reset WAL

Your Task

Implement CHECKPOINT (flush dirty pages and report count) and WAL SIZE (report current log length). After checkpoint, recovery should be faster since fewer records need replay.