Why a log
PostgreSQL does not change rows on disk directly. It copies each 8 kB page it needs from the data file into shared buffers, in memory, and changes it there. Memory is fast, but a power cut wipes it.
The write-ahead log (WAL) is an append-only file of records, each describing one change. Every record gets a number, its LSN. Record 1 is a checkpoint: every changed page had reached disk, so recovery can start reading here.
Commit and the WAL rule
T1 sells a copy of Dune. PostgreSQL changes page 0 in shared buffers to Dune 2, logs record 2 in the WAL buffers, and stamps the page with lsn 2. All in memory; on disk, the WAL must land before the page.
COMMIT adds record 3, writes the WAL buffers to the WAL file on disk and waits until the disk has them. Only then does it answer COMMIT. Page 0 is still changed only in memory: the log alone keeps T1 safe.
T2 restocks Holes, on page 1. Page 1 in memory gets a new row version, Holes 9, and record 4 goes to the WAL buffers; the page now says lsn 4. T2 has not committed, so other users still see Holes 5.
PostgreSQL needs page 1's buffer for other work, so it writes the page out. The rule: the log must be on disk up to the page's LSN. The WAL file ends at 3 but page 1 says 4, so record 4 is flushed first.
Crash and recovery
The power fails. Memory is wiped: shared buffers and WAL buffers are gone. On disk, page 0 still says Dune 3 though T1 committed, and page 1 holds T2's uncommitted Holes 9.
On restart PostgreSQL reads the WAL from the checkpoint and redoes each change. Record 2 brings page 0 back to Dune 2, lsn 2. Record 3 marks T1 as committed. Every committed change is back.
Record 4 restores page 1 the same way. Records 2 and 4 carry a full copy of their page, as each was the page's first change after the checkpoint. A crash mid-write can leave a page half old, half new; the copy fixes that.
The log ends with no commit record for T2, so T2 counts as aborted. Its Holes 9 version stays in page 1, but no one will ever see it; VACUUM clears it later. Readers get Dune 2 and Holes 5.
What happens
;; step 1/10 · memory data file (disk) -> shared buffers (memory) page 0 Dune 3 lsn 0 page 1 Holes 5 lsn 0 a page is 8192 bytes; reads and writes move whole pages changed pages are written back later, not at once power cut? everything in memory is lost
Real LSNs are byte positions in the WAL, printed like 0/16B3748, and a page stores the position just past its last record. The figure numbers the records 1, 2, 3, 4 to keep them readable. In a table this small, both rows would share page 0. The figure puts Holes on page 1 so each transaction changes its own page.