Write-ahead log: the promise before the write

The write-ahead log is a sequential record of every change made to the database, written and flushed to durable storage before the pages those changes affect are. A commit is durable once its own record has been flushed, even though the modified pages are still only in memory, and crash recovery replays the log forward to put the data files back into a consistent state. Replication, point-in-time recovery and every kind of standby read the same stream.

Why writing twice is faster than writing once

Writing the change and then the page sounds like double the work. It is not, because the two writes have completely different shapes. The log is appended in order, so a commit costs one flush to a position that is already under the disk head, whatever the transaction touched. The pages are written later, in bulk, by a background process that can reorder and combine them, and a page modified fifty times before that happens is written once.

The unit of position in this stream is the log sequence number, a byte offset that only increases. Replication lag, replication slots, checkpoint progress, base backups and recovery targets are all expressed in it, which is why two numbers that look unrelated on a dashboard are frequently differences between the same pair of positions.

One property surprises people sizing storage: the volume of log a workload generates is not proportional to the rows it changes. The first time a page is touched after a checkpoint, the whole page is written into the log rather than the change to it, so log volume is bursty and tracks checkpoints more closely than it tracks traffic.

Measuring what a statement costs in log bytes

Subtracting two positions gives the bytes written between them.

CREATE TABLE ledger (id int, note text);
CREATE TABLE lsn_mark AS SELECT pg_current_wal_lsn() AS before;
INSERT INTO ledger SELECT g, 'payment ' || g FROM generate_series(1, 1000) g;
SELECT pg_size_pretty((pg_current_wal_lsn() - before)::numeric) AS wal_written,
       pg_walfile_name(pg_current_wal_lsn()) AS current_segment
FROM lsn_mark;

CREATE TABLE
SELECT 1
INSERT 0 1000
 wal_written |     current_segment      
-------------+--------------------------
 75 kB       | 000000010000000000000006
(1 row)

A thousand short rows produced seventy-five kilobytes of log, which is more than the rows themselves occupy and less than a page-heavy workload would produce for the same row count. The segment name is the file the position falls in, and it is the name that appears in archive commands, in standby error messages and in the directory listing when something stops the files being recycled.

Where the stream goes after it is written

The same measurement across a window is what the server’s own write-ahead log statistics report, and the pg_stat_wal view is where they live. If the concern is the directory rather than the throughput, the four things that stop segments being released are set out in pg_wal growth. If the volume itself is the surprise, full-page images is the mechanism to read first.