WAL and Crash Recovery

A database must keep committed data after a crash, yet writing every changed data page to disk at commit would be slow (random writes, many pages). The solution is the write-ahead log (WAL): a sequential log of changes that is flushed at commit, while data pages are written later.

Example 1: the write-ahead rule

The rule has two parts:

1. A log record describing a change must reach disk before the changed data page does.
2. All log records of a transaction must reach disk before the commit is acknowledged.

sequenceDiagram
    participant C as Client
    participant B as Buffer pool
    participant W as WAL (disk)
    participant D as Data files (disk)
    C->>B: UPDATE (change page in memory)
    B->>W: append WAL record
    C->>W: COMMIT: flush WAL (fsync)
    W-->>C: commit acknowledged
    B-->>D: dirty page written later (checkpoint / background writer)

The log write is sequential and small; the data page write is random and can be deferred and batched.

Example 2: log sequence numbers

Every WAL record has a log sequence number (LSN): a byte position in the WAL stream, shown as two hexadecimal numbers such as 0/16B3A58. LSNs only increase. pg_wal_lsn_diff(a, b) returns the number of bytes between two LSNs.

SELECT pg_current_wal_lsn()::text ~ '^[0-9A-F]+/[0-9A-F]+$' AS looks_like_lsn;

 looks_like_lsn 
----------------
 t
(1 row)

A data page stores the LSN of the last record that changed it (pd_lsn). Before writing a page to disk, PostgreSQL makes sure WAL is flushed at least up to that LSN. This is how the write-ahead rule is enforced.

Example 3: measuring WAL generated

Capture the LSN, run a statement, and compare.

CREATE TABLE wal_demo (id int, payload text);
SELECT pg_current_wal_lsn() AS before \gset
INSERT INTO wal_demo SELECT g, repeat('x', 100) FROM generate_series(1, 10000) g;
SELECT pg_wal_lsn_diff(pg_current_wal_lsn(), :'before') > 1000000 AS wrote_over_1mb_of_wal;

 wrote_over_1mb_of_wal 
-----------------------
 t
(1 row)

Inserting about 1 MB of row data produces more than 1 MB of WAL, because every change is logged along with record headers. Bulk loads, index builds, and large updates are limited by WAL volume.

Example 4: WAL settings

SELECT name, setting
FROM pg_settings
WHERE name IN ('fsync', 'synchronous_commit', 'full_page_writes', 'wal_level')
ORDER BY name;

        name        | setting 
--------------------+---------
 fsync              | on
 full_page_writes   | on
 synchronous_commit | on
 wal_level          | replica
(4 rows)

Example 5: synchronous_commit trade-off

With synchronous_commit = off, COMMIT returns before the WAL is flushed. A crash can lose the last few transactions (about 3 times wal_writer_delay, 600 ms by default) but never corrupts the database. It can be set per transaction.

BEGIN;
SET LOCAL synchronous_commit = off;
INSERT INTO wal_demo VALUES (-1, 'low-value event');
COMMIT;
SHOW synchronous_commit;

 synchronous_commit 
--------------------
 on
(1 row)

SET LOCAL lasts until the end of the transaction, so the setting reverts after COMMIT. MySQL InnoDB has the equivalent innodb_flush_log_at_trx_commit (1 = flush at every commit, 2 = flush about once per second).

Group commit: when many transactions commit at the same time, one fsync flushes the WAL for all of them. Throughput of commits per second is therefore higher than 1 / fsync latency.

Example 6: checkpoints and dirty pages

A modified page in the buffer pool that has not been written to disk is dirty. A checkpoint writes all dirty pages to disk and records a point in the WAL from which recovery can start. WAL before that point is no longer needed for crash recovery and is recycled.

flowchart LR
    A["WAL start"] --- B["Checkpoint N"] --- C["Checkpoint N+1"] --- D["Crash"]
    B -. "recovery replays from here" .-> D

Checkpoints happen every checkpoint_timeout or when WAL reaches max_wal_size.

SELECT name, setting, unit
FROM pg_settings
WHERE name IN ('checkpoint_timeout', 'max_wal_size', 'checkpoint_completion_target')
ORDER BY name;

             name             | setting | unit 
------------------------------+---------+------
 checkpoint_completion_target | 0.9     | 
 checkpoint_timeout           | 300     | s
 max_wal_size                 | 1024    | MB
(3 rows)

Longer intervals mean fewer full page writes and less I/O, but longer crash recovery because more WAL is replayed.

Example 7: redo, undo, and ARIES

Most databases follow the ARIES recovery design. It uses a buffer policy of steal / no-force:

Recovery runs three passes:

PostgreSQL differs on undo: old row versions stay in the table (see MVCC and Vacuum), so an uncommitted transaction's rows are invisible because its commit was never recorded. Recovery needs only redo. InnoDB and Oracle keep undo logs and run a real undo pass.

Example 8: full page writes and torn pages

A page is 8 KB but disks and file systems may write in smaller units (often 4 KB). A crash in the middle of a page write leaves a torn page: half old, half new. WAL records describe changes relative to a page, so they cannot repair a torn page.

PostgreSQL logs the complete page image the first time a page changes after each checkpoint (full_page_writes = on). Recovery restores the image and then applies later records. The cost is extra WAL right after every checkpoint. InnoDB solves the same problem with a doublewrite buffer.

Example 9: what WAL is used for

The same log serves several features:

A replication slot shows how a consumer holds its position in the WAL by LSN.

SELECT count(*) AS active_slots FROM pg_replication_slots;

 active_slots 
--------------
            0
(1 row)

An unused slot keeps WAL on disk indefinitely, which can fill the disk. See Replication and CAP.

Example 10: backups and PITR

PITR steps:

# 1. take a base backup
pg_basebackup -D /backup/base -X none
# 2. archive WAL continuously (postgresql.conf)
#    archive_mode = on
#    archive_command = 'cp %p /backup/wal/%f'
# 3. restore: copy base backup, then in postgresql.conf
#    restore_command = 'cp /backup/wal/%f %p'
#    recovery_target_time = '2026-09-29 10:15:00'
#    and create an empty recovery.signal file, then start the server

Recovery replays archived WAL up to the target time, which undoes the effect of an accidental DROP TABLE made after it.

Summary

• WAL rule: log first, data later; COMMIT waits for the log flush, not for data pages.
• Checkpoints bound recovery time; recovery replays WAL from the last checkpoint (analysis, redo, undo in ARIES; PostgreSQL needs only redo).
• synchronous_commit = off risks losing recent commits but not consistency; group commit shares one fsync across transactions.
• Full page writes protect against torn pages.
• WAL also feeds replication, PITR, and logical decoding; physical backups plus archived WAL enable point-in-time recovery.