The write-ahead log

The rule

A change to a table touches pages scattered across a file: the row’s page, a page in each index. Forcing all of those to disk before saying “committed” would be slow. Instead PostgreSQL follows one rule:

Describe the change in the log, and make sure the log is on disk, before the changed pages are allowed to be.

The log, the write-ahead log (WAL), is written sequentially, so flushing it is cheap. At commit, PostgreSQL flushes only the log. The changed table and index pages stay in memory and are written out later, in the background.

If the server crashes before those pages reach disk, nothing is lost: on restart, PostgreSQL reads the log and replays every change that the data files might be missing.

What is in the log

Every record has a position, its LSN (log sequence number), which only increases. You can see how much log a statement writes:

SELECT pg_current_wal_insert_lsn();     -- e.g. 2/CD9BF400
INSERT INTO accounts VALUES (2, 50);
UPDATE accounts SET balance = balance - 10 WHERE id = 1;
SELECT pg_current_wal_insert_lsn();     -- e.g. 2/CD9BF5C8: 456 bytes later

The pg_waldump tool decodes the records between two LSNs. Ours, slightly trimmed:

rmgr: Heap        len (rec/tot): 54/166  tx: 3316  desc: INSERT off: 2 ... blk 0 FPW
rmgr: Btree       len (rec/tot): 53/133  tx: 3316  desc: INSERT_LEAF off: 2 ... blk 1 FPW
rmgr: Transaction len (rec/tot): 34/34   tx: 3316  desc: COMMIT
rmgr: Heap        len (rec/tot): 70/70   tx: 3317  desc: HOT_UPDATE old_off: 1 ... new_off: 3
rmgr: Transaction len (rec/tot): 34/34   tx: 3317  desc: COMMIT

The insert logged a record for the table (Heap) and one for its primary key index (Btree). The update logged one record. Each transaction ended with a COMMIT record, and it is the flush of that record that makes the commit durable.

Checkpoints, and why some records are bigger

The log cannot grow forever. Periodically, at most every five minutes by default, PostgreSQL runs a checkpoint: it writes every changed page to the data files and records in the log that everything before this point is safely on disk. Crash recovery only has to replay from the last checkpoint, and older log can be recycled.

Notice the FPW on the first two records above. The first time a page changes after a checkpoint, PostgreSQL writes the entire page into the log, not just the change. A crash in the middle of writing an 8 kB page can leave it half old, half new, and a small change record cannot repair that; a full copy can. The insert’s records are bigger (166 and 133 bytes instead of about 54) because they carry page images. The update came after, touched a page that was already in the log since the checkpoint, and needed no image.

One log, three jobs

Because the log describes every change in order, it does more than crash recovery. Ship it to another server and replay it there, and you have a replica. Keep it alongside an old copy of the files, and you can restore to any moment. Both are later topics; both are why “how much WAL does this write?” is a question worth asking of any big change.

Check yourself

1. Why is flushing the WAL at commit faster than flushing the changed table pages?

   Show answer

   The WAL is written sequentially to one place, while the changed pages are scattered across table and index files. One sequential flush is much cheaper than many scattered page writes.

   I could explain this without looking

2. What does crash recovery do?

   Show answer

   It starts at the redo point of the last checkpoint and replays every logged change from there, so the data files contain everything that was committed before the crash.

   I could explain this without looking

3. Why did the inserted row’s records carry a full page image, but the update’s did not?

   Show answer

   They were the first changes to those pages since the checkpoint, so PostgreSQL logged whole pages to guard against torn writes. The update’s page had already been logged in full since that checkpoint.

   I could explain this without looking