Checkpoints

In the previous lesson, the log file grew and never shrank, because the automatic checkpoint had been disabled. That state cannot last long. A log that grows without bound fills the disk; on top of that, recovery gets longer as the number of records to read at startup grows. The data file, meanwhile, can sit unchanged for days while the information inside it grows increasingly stale.

These two files sooner or later have to be brought in sync. A checkpoint is the name of that syncing. This lesson’s questions are: how does each file change during the sync, why does the log sometimes shrink and sometimes not, and how often should this be done?

What a Checkpoint Does

A checkpoint applies the changes accumulated in the log to the pages in the data file. The sequence of steps is short: the log records are read, the final state of the corresponding pages is written to the data file, the write is confirmed durable, and only then is that log space considered free.

The ordering is mandatory. If log space is freed before the data file write is durable, a change ends up with neither copy existing anywhere. This is the other face of the write-ahead rule: the log record becomes durable before the data page, and the log space becomes free after the data page.

There is one more constraint. A checkpoint cannot overwrite pages that are currently visible to an in-progress read; the next lesson will establish why. This means a long-running read can delay a checkpoint, and the log keeps growing in the meantime. In administration, one of the common answers to “why did the log bloat” is a forgotten long-running read.

A Measured Checkpoint

The run below compares the state before and after a checkpoint using three measurements: the size of the data file, the size of the log file, and the database’s logical page count.

rm -f checkpoint.db checkpoint.db-wal checkpoint.db-shm
sqlite3 checkpoint.db <<'SQL'
CREATE TABLE loan(loan_id INTEGER PRIMARY KEY, book_id INT, member_id INT,
                   pickup_date TEXT, return_date TEXT);
INSERT INTO loan SELECT n, 1+(n*7)%200000, 1+(n*13)%120000, '2024-01-01', NULL
  FROM (WITH RECURSIVE s(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM s WHERE n<50000) SELECT n FROM s);
PRAGMA journal_mode = WAL;
PRAGMA wal_autocheckpoint = 0;
INSERT INTO loan SELECT 100000+n, 1+(n*7)%200000, 1+(n*13)%120000, '2024-03-01', NULL
  FROM (WITH RECURSIVE s(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM s WHERE n<20000) SELECT n FROM s);
.shell echo "--- before checkpoint ---"
.shell echo "data: $(wc -c < checkpoint.db)  log: $(wc -c < checkpoint.db-wal)"
SELECT 'page count in the data file', page_count FROM pragma_page_count;
PRAGMA wal_checkpoint(PASSIVE);
.shell echo "--- after PASSIVE checkpoint ---"
.shell echo "data: $(wc -c < checkpoint.db)  log: $(wc -c < checkpoint.db-wal)"
SELECT 'page count in the data file', page_count FROM pragma_page_count;
PRAGMA wal_checkpoint(TRUNCATE);
.shell echo "--- after TRUNCATE checkpoint ---"
.shell echo "data: $(wc -c < checkpoint.db)  log: $(wc -c < checkpoint.db-wal)"
SQL

wal
0
--- before checkpoint ---
data:  1380352  log:   580952
page count in the data file|475
0|141|141
--- after PASSIVE checkpoint ---
data:  1945600  log:   580952
page count in the data file|475
0|0|0
--- after TRUNCATE checkpoint ---
data:  1945600  log:        0

Before the checkpoint, the data file is 1,380,352 bytes, or 337 pages. But the query reports the page count as 475. The 138 pages that do not exist anywhere in the data file are in the log. This is the concrete form of the first lesson’s architecture: the database’s logical state is read from the union of the data file and the log; the data file alone is an old snapshot.

The PRAGMA wal_checkpoint output gives three numbers: a busy flag, the number of frames in the log, and the number of frames processed. On the first call, 0|141|141 — not busy, 141 frames in the log, all processed. The frame count can be verified against the file size: each frame is a page plus a 24-byte header, and there is a 32-byte section at the start of the file; 32 + 141 × (4096 + 24) = 580,952, identical to the measured size. The frame count exceeding 138 is because some pages appear in the log more than once: during inserts the tree’s upper levels also change, and each change writes a separate frame.

After the checkpoint, the data file grows to 1,945,600 bytes: exactly 475 pages. The logical page count did not change, because the data did not change — only its location did. The second call returns 0|0|0: nothing left to process.

Reuse Versus Truncation

After the first checkpoint, the log file’s size stayed at 580,952 bytes; even though its contents had been processed, the file did not shrink. This is not a defect; it is deliberate behavior: a processed log file can be overwritten from the start. Truncating a file and then growing it back out is unnecessary work for the file system; overwriting the same space is cheap.

The second call’s TRUNCATE form actually resets the file. Its place is situations where the log has bloated as a one-off event and the space is genuinely wanted back. In a system that runs continuously, the log file sitting at a stable size is expected behavior; being reset continuously is not.

The form names used here (PASSIVE, TRUNCATE) are specific to this tool. The distinction itself — reusing processed log space versus truncating the file — is independent of the engine.

The Frequency Trade-Off

How often should a checkpoint be taken? The question looks like a matter of picking a setting value; it is really a choice between two costs. A frequent checkpoint writes the same page to the data file over and over and weighs down the write path. An infrequent checkpoint lets the log grow and lengthens recovery.

The run below changes the threshold and measures five thousand small transactions, placing two results side by side: elapsed time, and the number of frames left in the log at the end of the run. The second number is how much work recovery would have to do if a crash happened right then.

cat > frequency.mjs <<'JS'
import { DatabaseSync } from 'node:sqlite';
import { rmSync, statSync } from 'node:fs';

const FRAME = 4096 + 24;                          // page + frame header
function measure(threshold) {
  for (const suffix of ['', '-wal', '-shm']) rmSync('frequency.db' + suffix, { force: true });
  const db = new DatabaseSync('frequency.db');
  db.exec('PRAGMA journal_mode = WAL');
  db.exec('PRAGMA synchronous = NORMAL');
  db.exec('CREATE TABLE loan(loan_id INTEGER PRIMARY KEY, member_id INT, return_date TEXT)');
  db.exec(`INSERT INTO loan SELECT n, 1+(n*13)%500, NULL
           FROM (WITH RECURSIVE s(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM s WHERE n<20000) SELECT n FROM s)`);
  db.exec('PRAGMA wal_checkpoint(TRUNCATE)');
  db.exec(`PRAGMA wal_autocheckpoint = ${threshold}`);
  const update = db.prepare('UPDATE loan SET return_date = ? WHERE loan_id = ?');
  const start = process.hrtime.bigint();
  for (let i = 1; i <= 5000; i++) { db.exec('BEGIN'); update.run('2024-04-01', 1 + (i * 37) % 20000); db.exec('COMMIT'); }
  const duration = Number(process.hrtime.bigint() - start) / 1e6;
  const frame = Math.round((statSync('frequency.db-wal').size - 32) / FRAME);
  db.close();
  return [duration, frame];
}
console.log('threshold (pages)   5000 txns (ms)   frames left in log');
for (const e of [1, 10, 100, 1000, 0]) {
  const [d, f] = measure(e);
  console.log(String(e === 0 ? 'off' : e).padStart(17), d.toFixed(0).padStart(16), String(f).padStart(19));
}
JS
node frequency.mjs

threshold (pages)   5000 txns (ms)   frames left in log
                1              330                   6
               10               72                  15
              100               36                 103
             1000               32                1000
              off               39                5719

Timings depend on the environment; the relationship is what to read from ratios. When a checkpoint is taken at every page, five thousand transactions took roughly ten times as long as against a thousand-page threshold. In exchange, only six frames were left in the log: a crash would leave recovery almost nothing to do. As the threshold grows, the time drops quickly and flattens out past a hundred pages; the number of frames accumulating in the log, meanwhile, grows linearly.

The shape of the curve makes the choice easier. A very frequent checkpoint is expensive, and the recovery time it buys back is already small. A very infrequent checkpoint does not shorten the time any further — in the last row, the “off” case’s time is no better than the thousand-page threshold — but it lets the log and the recovery work grow without bound. The efficient range sits in the middle, and this is less a performance tuning question than a recovery-target decision: the answer to “how long should we take at most to come back up after a crash” determines how much log is allowed to accumulate.

Recovery’s Work

A checkpoint’s effect on the recovery side is direct. At startup, the engine has to process the log records after the last checkpoint; the records before the checkpoint are already in the data file. The work recovery has to do is proportional not to the table’s size, but to the number of records accumulated since the checkpoint.

One measurement caution is needed here. In this environment, recovery consists of scanning the log and rebuilding its internal index, and because the file is in the operating system’s cache, its duration does not produce a measurable value at these sizes. This is why the table above used the work itself — the frame count — as the measure instead of time. In engines that also have to write the pages out to the data file separately, the same number is the direct determinant of recovery time.

The second face of the frequency decision also shows up here: because a checkpoint writes accumulated pages in bulk, it produces a spike in write load at that moment. In a system running continuously, this spike shows up as regular peaks in latency measurements. Engines soften this by spreading the checkpoint’s work over an interval; the setting’s name varies by engine, its purpose does not.

Summary

• A checkpoint applies changes accumulated in the log to the pages in the data file; log space becomes free only once that write is durable.
• The database’s logical state is the union of the data file and the log; in the measurement, the data file was 337 pages while the logical page count came out at 475, with the difference sitting in the log.
• A processed log file is generally not truncated; it is overwritten from the start. Truncation is a separate, optional step.
• Checkpoint frequency is a choice between two costs: checkpointing at every page slowed this environment down roughly tenfold, while never checkpointing let 5,719 frames accumulate in the log.
• Recovery’s work is proportional not to table size, but to the number of records accumulated since the last checkpoint.

Next Step

This lesson passed over one constraint: a checkpoint cannot overwrite pages currently visible to an in-progress read. Behind that sentence stands the way the engine provides concurrency. The Advanced SQL course observed that a read inside a transaction was unaffected by commits happening outside it; how that is possible was never asked. The next lesson builds that mechanism: multiple versions of a row stored at the same time, the rule that determines which version a given transaction sees, and readers and writers not blocking each other.