Skip to content
Tutorials Center
My learning

Topics

Lesson 70 of 90 ยท Partitioning and Scaling

Write-Ahead Logging Explained

Understand PostgreSQL write-ahead logging: how commits become durable, what LSNs and WAL segments are, checkpoints, full-page writes, wal_level, and how WAL powers recovery.

Every durable database must answer one question: if the power fails in the middle of a write, what is on disk when the machine comes back? PostgreSQL's answer is the write-ahead log, or WAL. The rule is in the name: the log describing a change must reach disk before the change itself does. That one rule gives you crash recovery, fast commits, point-in-time recovery and replication.

This lesson explains how the WAL works from the inside: why commits are cheap, what an LSN and a segment file are, what a checkpoint does, and which settings control the trade-offs. It builds the background for Streaming and Logical Replication and Backup and Recovery. Server internals cannot be observed in the browser, so the PostgreSQL commands are read-only and run in your own psql. The two live examples are Python programs that simulate the log and compute segment file names exactly as the server does.

Why a log at all?

Imagine updating a row. PostgreSQL changes an 8 KB page in its shared memory buffers (shared_buffers). That dirty page must eventually be written to the table's data file, but writing it right now at every commit would be slow: the pages are scattered all over the disk (random writes), and one transaction may touch many of them.

The log solves this. A WAL record is a small description of a change ("in page 17 of table 16384, insert this tuple"), and records are appended one after another in a single file (sequential writes, the fastest thing a disk does). At commit time PostgreSQL only has to flush the log up to the commit record. The data pages can be written later, in the background, in whatever order suits the disk.

A commit, step by step

Change the page The page is modified in memory only.
Write the WAL record A small record describing it is appended.
Flush the log fsync puts the commit record on disk.
Tell the client COMMIT returns. Data files can wait.

Durability comes from the log, not from the table files.

1 / 2

LSNs and segment files

Every byte of WAL has a position called a log sequence number (LSN). It is a 64-bit number, printed as two hexadecimal halves: 0/3000148. LSNs only grow, so the difference between two LSNs is the number of WAL bytes between them. Ask the server for the current position and measure distances:

SQL
-- PostgreSQL
SELECT pg_current_wal_lsn();                      -- e.g. 0/3000148
SELECT pg_current_wal_insert_lsn();               -- where new records are being placed

SELECT pg_wal_lsn_diff('0/5000000', '0/3000148'); -- bytes between two positions
SELECT pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), '0/3000148'));

On disk the log lives in the pg_wal directory as files called segments, 16 MB each by default (you can choose another power of two from 1 MB to 1 GB at initdb time). A segment's name encodes three things: the timeline, and the position of the segment in the log, each as eight hexadecimal digits. The segment for LSN 0/3000148 is:

TEXT
000000010000000000000003
|------||------||------|
 timeline  "log"   segment

Old segments are not kept forever. After a checkpoint, segments that are no longer needed are recycled (renamed for future use) or removed, unless something still needs them: an archiving command that has not copied them yet, a replication slot, or the wal_keep_size setting.

You can ask the server which file holds a position, and force a switch to a new segment:

SQL
-- PostgreSQL
SELECT pg_walfile_name('0/3000148');              -- 000000010000000000000003
SELECT pg_switch_wal();                           -- close the current segment and start the next one
SELECT * FROM pg_ls_waldir() ORDER BY modification DESC LIMIT 5;

The first example turns an LSN into the file name and counts how many bytes of WAL a workload produced:

Python From LSN to WAL file name

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

What a WAL record contains

A record has a header (which transaction, which kind of operation, a checksum) and a payload (the page, the change). Each resource manager describes its own records: the heap (table rows), B-tree indexes, commits, sequences, and so on. You can read them with the pg_walinspect extension or the pg_waldump command:

SQL
-- PostgreSQL (extension shipped with PostgreSQL 15 and later)
CREATE EXTENSION pg_walinspect;

SELECT start_lsn, resource_manager, record_type, record_length
FROM pg_get_wal_records_info('0/3000148', '0/3001000')
LIMIT 5;
Terminal
pg_waldump -p /var/lib/postgresql/18/main/pg_wal 000000010000000000000003 | head

You will see entries such as Heap INSERT, Btree INSERT_LEAF and Transaction COMMIT. This is a fascinating way to learn what one statement really does: a single INSERT into a table with three indexes writes four records, one for the row and one for each index entry.

Full-page writes

A page is 8 KB but disks and file systems may write in smaller units. If the power fails during the write of one page, the page can end up torn: half old, half new. A normal WAL record ("change these bytes") cannot repair a page that is already corrupt.

So, the first time a page is modified after a checkpoint, PostgreSQL writes the entire page image into the WAL (full_page_writes = on, the default). Recovery can then throw away the torn page and rebuild it from the image, followed by the later small records. The price is volume: right after each checkpoint, WAL traffic is heavier, which is one more reason to make checkpoints infrequent. wal_compression compresses these images and is often worth enabling. Turn full_page_writes off only if you are certain your storage guarantees atomic 8 KB writes.

Checkpoints

If recovery had to replay the log from the beginning of time it would take forever, and the log could never be deleted. A checkpoint puts a boundary in the log:

  1. Note the current log position as the redo point.
  2. Flush every dirty page in shared buffers to the data files (spread over time to avoid a burst of disk writes).
  3. Write a checkpoint record and update the pg_control file with the redo point.

After a crash, recovery starts at the last completed checkpoint's redo point and replays from there. Everything older than that is safe in the data files, so those segments can be recycled.

Checkpoints are triggered by time (checkpoint_timeout, 5 minutes by default), by volume (max_wal_size, 1 GB by default) or on demand (CHECKPOINT; and some operations such as CREATE DATABASE and shutdown). The trade-off:

Setting More frequent checkpoints Less frequent checkpoints
Crash recovery time Short Longer
Full-page-write volume Higher Lower
Write bursts More Fewer
Disk space for WAL Less More

Tuning these settings is covered in Configuration Tuning. To see how it is going, pg_stat_checkpointer (PostgreSQL 17 and later; earlier versions use pg_stat_bgwriter) and pg_stat_wal report counts and volumes.

The second example is a tiny database engine with a log. It writes records, crashes before the data file is updated, then recovers by replaying from the checkpoint:

Python Crash and recover from a log

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

The data files alone were stale (a=1), but the log held the newer commits, so nothing committed was lost. This is the guarantee behind the D in ACID (see Transactions and ACID).

The settings that matter

Setting Default What it does
fsync on Force WAL to disk. Turning it off can corrupt the whole cluster after a crash. Never off in production.
synchronous_commit on Whether COMMIT waits for the flush. off returns early: a crash may lose the last few commits (about three times wal_writer_delay, 200 ms by default) but never corrupts data.
wal_level replica How much detail is logged: minimal (crash recovery only), replica (adds what archiving and standbys need), logical (adds what logical decoding needs).
wal_buffers -1 (automatic) Memory for WAL not yet written; automatic is 1/32 of shared_buffers, capped at one segment.
full_page_writes on Protect against torn pages (see above).
wal_compression off Compress full-page images; trades CPU for less WAL.
max_wal_size / min_wal_size 1 GB / 80 MB Soft ceiling that triggers checkpoints, and the amount of recycled WAL to keep.
archive_mode / archive_command off Copy completed segments elsewhere, for point-in-time recovery.
wal_keep_size 0 Extra WAL to retain for standbys that fall behind.

Changing wal_level or archive_mode requires a restart, so choose replica or logical ahead of time if you will use replication. The minimal level can speed up bulk loads (see Bulk Loading) but forbids streaming replication and base backups.

synchronous_commit = off is a legitimate tuning knob for data you can afford to lose a few moments of, such as session activity. fsync = off is not: it removes the safety the log provides and can leave the database unrecoverable after a crash.

From crash recovery to replication and PITR

The log was designed for crash recovery, but any process that replays it ends up with the same state as the original. That is the whole idea behind the rest of PostgreSQL's availability features:

  • A streaming standby receives WAL records from the primary over the network and replays them continuously (Replication).
  • Point-in-time recovery (PITR) restores a base backup and replays archived WAL up to a chosen moment, such as just before an accidental DROP TABLE (Backup and Recovery).
  • Logical decoding turns the log (at wal_level = logical) into a stream of row changes for logical replication and tools such as change-data-capture connectors.
  • Replication slots make the primary keep the segments a consumer still needs, which is also the way a forgotten slot can fill the disk with WAL.

Monitoring pg_wal size, archive lag and slot retention is therefore part of operating any serious installation (see Monitoring and Catalogs).

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. What does "write-ahead" mean in write-ahead logging?

    That ordering is what makes recovery possible: after a crash the log can redo any change whose data page never reached disk.
  2. 2. Why is a COMMIT fast even though the changed table pages are not on disk yet?

    The WAL is a sequential file. Flushing it is cheap compared with writing scattered data pages, which happens later in the background.
  3. 3. What is stored in a name like 000000010000000000000003?

    The three groups of eight hexadecimal digits are the timeline, the high part of the segment number and the low part.
  4. 4. What is the purpose of full-page writes?

    A partly written page cannot be fixed by a small change record, so the first change after a checkpoint logs a complete image of the page.
  5. 5. Which setting must never be turned off on a production server you care about?

    synchronous_commit = off risks losing recent commits; fsync = off risks corrupting the entire cluster after a crash.

Summary

  • The WAL records every change before the data files are updated; a commit only needs the log flushed, which is a fast sequential write.
  • Positions in the log are LSNs; the log is stored in 16 MB segment files whose names encode timeline and segment number.
  • A checkpoint flushes dirty pages and sets the redo point; crash recovery replays WAL from that point.
  • Full-page writes protect against torn pages at the cost of extra WAL right after each checkpoint; wal_compression can reduce it.
  • wal_level, synchronous_commit, max_wal_size and checkpoint_timeout are the settings you tune; fsync stays on.
  • The same log powers streaming replication, point-in-time recovery and logical decoding.

Next: Streaming and Logical Replication.