Write-Ahead Log

The previous lesson showed that data sits on disk in pages. When a row is updated, the page in memory changes first. What if the machine shuts down before that page is written to disk? The Transactions topic said that a committed change is durable; how is that promise kept while the page is not yet on disk?

The first answer that comes to mind — writing every changed page to disk at commit time — is both expensive and insufficient. This lesson covers why it falls short and what is actually done instead. What gets measured is precise: right after a commit, which file holds the committed data?

Why Writing the Pages Is Not the Answer

Writing changed pages to disk at commit time has three separate problems.

The writes are scattered. A single transaction can touch pages far apart from each other: a loan row, the page the member sits on, the different leaves of three separate indexes. Writing these means small writes scattered across the whole file. The cost of a sequential write is per byte, not per access.

A write can be split. Writing a four-kilobyte page to disk may not be a single indivisible operation at the hardware level. If power is cut mid-write, part of the page is new and part is old. That is worse than a lost update: it is a corrupted page.

The same page is written over and over. A frequently updated page is rewritten at every commit. Yet writing that page’s final state once would be enough.

The Write-Ahead Rule

The solution is to separate durability from the data pages. The engine first writes the change to the write-ahead log: sequential records appended to the end of a file, describing what changed. The rule is a single sentence:

Before a data page is written to disk, the log record describing the change to that page must already be durable on disk.

A commit is complete once the log records are durable. The data pages can stay in memory; writing them out is deferred.

This arrangement solving all three problems is direct. The write is sequential, because the log is only ever appended to. The danger of a split write disappears, because an incompletely written log record is recognized and ignored during recovery — the data page, meanwhile, has not been touched at all yet. A frequently updated page is also written just once, because the page landing on disk is no longer tied to a commit.

Recovery follows from this. After a crash, on startup, the engine reads the log and applies committed changes that have not yet reached the data file. Uncommitted changes are not applied. The result is an exact return to the committed state at the moment of the crash.

The Relational Theory course referred to this structure by the name write-ahead log when covering ACID’s durability property; it is the same structure, and this course will use the catalog’s naming throughout.

The Log File’s Appearance

The claim can be measured. The run below sets up a database, turns on log mode, then inserts five thousand rows and checks the size of both files. The automatic checkpoint is disabled; why is the subject of the next lesson.

rm -f log.db log.db-wal log.db-shm
sqlite3 log.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;
UPDATE loan SET return_date = '2024-02-01' WHERE loan_id = 1;
.shell echo "--- start ---"
.shell echo "data file: $(wc -c < log.db)   log: $(wc -c < log.db-wal)"
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<5000) SELECT n FROM s);
.shell echo "--- after the 5000-row transaction commits ---"
.shell echo "data file: $(wc -c < log.db)   log: $(wc -c < log.db-wal)"
SELECT count(*) AS total_rows FROM loan;
SQL

wal
0
--- start ---
data file:  1380352   log:    24752
--- after the 5000-row transaction commits ---
data file:  1380352   log:   173072
55000

The first two lines are the values the two settings return: log mode became wal, and the automatic checkpoint was reset to zero. The real result is below. After the five-thousand-row insert commits, the data file’s size did not change by a single byte — it was 1,380,352, and it stayed 1,380,352. The log file, though, grew from 24,752 bytes to 173,072 bytes. The query sees the new rows: 55,000.

File sizes depend on the environment and the page size; what does not change is which file grows.

Where the Committed Data Is

The data file not changing means the committed rows are not there. The direct way to test this is to separate the files and open them apart from each other.

rm -f log.db log.db-wal log.db-shm data-only.db data-and-log.db data-and-log.db-wal
sqlite3 log.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<5000) SELECT n FROM s);
.shell cp log.db data-only.db
.shell cp log.db data-and-log.db
.shell cp log.db-wal data-and-log.db-wal
SQL
echo "--- data-only file copied ---"
sqlite3 data-only.db "SELECT count(*) FROM loan;"
echo "--- data file + log copied ---"
sqlite3 data-and-log.db "SELECT count(*) FROM loan;"

wal
0
--- data-only file copied ---
50000
--- data file + log copied ---
55000

The result leaves no room for argument. When only the data file is copied, fifty thousand rows show up: the committed five thousand rows are not there. When the log is copied along with it, fifty-five thousand rows show up. The committed data sits inside the log right after the commit.

This observation has a direct consequence on the backup side, one this course will return to in the Operations topic: a file-level backup of a running database has to cover the log too. A backup that copies only the data file does not contain the most recently committed transactions.

Recovery After a Crash

Seeing that recovery actually works requires a real crash. The script below commits two thousand rows in a single transaction, then kills the process without any shutdown procedure: the files are not closed, no checkpoint is taken, no buffer is flushed.

rm -f crash.db crash.db-wal crash.db-shm
cat > crash.mjs <<'JS'
import { DatabaseSync } from 'node:sqlite';
const db = new DatabaseSync('crash.db');
db.exec('PRAGMA journal_mode = WAL');
db.exec('PRAGMA wal_autocheckpoint = 0');
db.exec(`CREATE TABLE loan(loan_id INTEGER PRIMARY KEY, member_id INT, pickup_date TEXT)`);
db.exec('BEGIN');
for (let i = 1; i <= 2000; i++) db.exec(`INSERT INTO loan VALUES (${i}, ${1 + (i * 13) % 500}, '2024-03-01')`);
db.exec('COMMIT');
console.log('committed; row count within the transaction:',
            db.prepare('SELECT count(*) AS c FROM loan').get().c);
process.kill(process.pid, 'SIGKILL');   // terminate without cleanup, without a shutdown
JS
node crash.mjs
echo "process exit code: $?"
echo "--- files at the moment of the crash ---"
echo "data file: $(wc -c < crash.db)   log: $(wc -c < crash.db-wal)"
echo "--- reopening the database ---"
sqlite3 crash.db "SELECT count(*) FROM loan;"

committed; row count within the transaction: 2000
process exit code: 137
--- files at the moment of the crash ---
data file:     4096   log:    61832
--- reopening the database ---
2000

Exit code 137 shows the process was terminated by a kill signal. At the moment of the crash, the data file is a single page’s worth: neither the table definition nor the rows made it there. Everything is in the 61,832-byte log. When the database is reopened, all two thousand rows are in place.

This is how durability is achieved. A commit is not writing the data to where it will finally sit; it is recording, irreversibly, what changed. The row’s data settling into place is the subject of the next lesson.

Log Mode and the Sync Setting

Keeping a log does not have a single form, and the choice makes a measurable difference. There are two axes.

The first axis is log mode. In a write-ahead log, changes are appended to a separate file and the data file stays as it was. The alternative is a rollback journal: a copy of the page’s prior state is written to a separate file before the change, the page’s new state is written directly into the data file, and if the transaction is rolled back the old pages are written back. The mode’s name and its options are specific to the engine.

The second axis is sync level: whether the engine waits at commit time for the operating system to confirm the write actually reached disk, or hands the write off and continues.

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

function measure(mode, sync) {
  for (const suffix of ['', '-wal', '-shm', '-journal']) rmSync('mode.db' + suffix, { force: true });
  const db = new DatabaseSync('mode.db');
  db.exec(`PRAGMA journal_mode = ${mode}`);
  db.exec(`PRAGMA synchronous = ${sync}`);
  db.exec('CREATE TABLE loan(loan_id INTEGER PRIMARY KEY, member_id INT, pickup_date TEXT)');
  const insert = db.prepare('INSERT INTO loan VALUES (?, ?, ?)');
  const start = process.hrtime.bigint();
  for (let i = 1; i <= 2000; i++) {              // each row is its own transaction
    db.exec('BEGIN');
    insert.run(i, 1 + (i * 13) % 500, '2024-03-01');
    db.exec('COMMIT');
  }
  const duration = Number(process.hrtime.bigint() - start) / 1e6;
  db.close();
  return duration;
}

const measurements = [['delete', 'FULL'], ['wal', 'FULL'], ['wal', 'NORMAL']];
const results = measurements.map(([m, s]) => [m, s, measure(m, s)]);
const baseline = results[0][2];
console.log('log mode      sync        2000 txns (ms)   relative');
for (const [m, s, d] of results)
  console.log(m.padEnd(13), s.padEnd(10), d.toFixed(0).padStart(14), (baseline / d).toFixed(1).padStart(12) + '×');
JS
node mode.mjs

log mode      sync        2000 txns (ms)   relative
delete        FULL                  414          1.0×
wal           FULL                   58          7.1×
wal           NORMAL                 13         31.1×

Timings depend on the environment; what is worth reading is the relationship between them. In this environment, the write-ahead log committed roughly seven times faster than the rollback journal. The reason is the write pattern: with a rollback journal, every transaction changes both the journal file and the data file and then deletes the journal file at the end; with a write-ahead log, only one file is appended to.

The third line is the trade-off itself. Relaxing sync sped the measurement up further, to roughly thirty-one times the baseline, because the commit no longer waits for the write to reach disk. The cost of this is durability: in a sudden power loss, the last few committed transactions can be lost. The database does not become corrupted — the log’s structure prevents that — but a transaction that was reported “committed” may not come back. This setting means deliberately giving up ACID’s durability property, and it is defensible only for data whose loss is acceptable.

Summary

• Writing data pages to disk at commit time produces scattered writes, split writes, and the same page being written over and over.
• The write-ahead rule requires the log record describing a change to be durable before the data page reaches disk; a commit is complete once the log is durable.
• In the measurement, a five-thousand-row insert left the data file completely unchanged when it committed; the log file grew from 24,752 to 173,072 bytes.
• Copying only the data file showed 50,000 rows; copying it with the log showed 55,000: committed data sits inside the log.
• After a process was killed, the data file stayed at a single page, yet all two thousand rows were recovered from the log.
• Log mode and sync level make a measurable difference; relaxing sync sped this environment up roughly thirty-one times and traded away durability in exchange.

Next Step

In this lesson’s runs, the automatic checkpoint was disabled and the log file grew without ever shrinking. This is not sustainable: a log that grows without bound fills the disk, and it lengthens recovery, because the number of records to read at startup grows. The changes in the log need to be applied to the data file so the log can be shortened. The next lesson takes up this operation: what a checkpoint does, how often it should run, and how the trade-off between frequency and recovery time is set.