04. Write-Ahead Logging & Crash Recovery¶

The Write-Ahead Log (WAL) guarantees the Durability and Atomicity properties of ACID transactions while maintaining high throughput by transforming random heap I/O into sequential log appends.

1. The Core WAL Invariant¶

The Write-Ahead Logging Rule: The log record describing a change must be flushed and fsynced to persistent storage before the dirty data page itself is written to disk.

If an OS or power failure occurs while a dirty page is partially written to disk, PostgreSQL can safely reconstruct the page from the WAL during recovery. Conversely, if a dirty page were written first and the system crashed before the log was synced, the database would suffer unrecoverable physical corruption.

2. Log Sequence Numbers (LSN) & WAL Segments¶

Every byte written to the WAL is indexed by a 64-bit integer called a Log Sequence Number (LSN):

\[\text{LSN Format}: \quad \text{XXXXXXXX/YYYYYYYY} \quad (\text{Segment File ID / Byte Offset within Segment})\]

16MB Segment Files: Stored in $PGDATA/pg_wal/ with names such as 000000010000000A0000002F.
Page LSN (pd_lsn): Every 8KB page header stores the LSN of the last WAL record that modified it.
The Flushing Rule: A buffer cannot be written to disk unless: $$\text{FlushLSN} \ge \text{Buffer.pd\_lsn}$$

3. Full Page Writes (FPW) & Torn Page Protection¶

Operating systems and filesystems (ext4, XFS) write data in 4KB blocks, while PostgreSQL pages are 8KB. A power failure midway through a write results in a torn page (half old data, half new data), which invalidates block checksums and corrupts internal line pointers.

To prevent this: * Immediately following a checkpoint, the first time any page is dirtied in shared buffers, PostgreSQL writes a full byte-by-byte copy of the entire 8KB page into the WAL stream (full_page_writes = on). * During crash recovery, PostgreSQL does not try to apply incremental deltas to the corrupted page; it simply restores the complete 8KB image from the FPW record and replays subsequent deltas on top of it.

4. Checkpoints & ARIES Crash Recovery¶

sequenceDiagram
    participant B as Backends (Queries)
    participant CP as Checkpointer
    participant SB as Shared Buffers
    participant D as Disk (Heap)
    participant W as WAL Disk

    CP->>W: 1. Record Checkpoint Start (redo_lsn)
    CP->>SB: 2. Identify all dirty buffers
    loop Spread across checkpoint_completion_target
        CP->>D: 3. Flush & sync dirty buffers
    end
    CP->>D: 4. sync() all relation files
    CP->>W: 5. Write Checkpoint End Record
    CP->>D: 6. Update pg_control file

The Checkpoint Spreading Optimization¶

If all dirty buffers were dumped to disk instantly, I/O latency would spike violently, causing query timeouts. By setting:

checkpoint_completion_target = 0.9
max_wal_size = 16GB
min_wal_size = 2GB

The checkpointer spreads buffer writeback across 90% of the checkpoint interval (checkpoint_timeout, default 5 minutes), maintaining smooth disk throughput.

REDO-Only Recovery (ARIES Variant)¶

Unlike Oracle or SQL Server, PostgreSQL uses a REDO-only recovery algorithm. Because aborted transactions leave their dead tuples in the heap (managed by MVCC visibility rules and cleaned up by VACUUM), PostgreSQL never needs an UNDO pass during recovery.

Locate the latest checkpoint in $PGDATA/global/pg_control.
Seek to the checkpoint's redo_lsn.
Scan the WAL sequentially forward from redo_lsn to the end of the log.
For each record, if record.lsn > page.pd_lsn, replay the change.
Bring the database online immediately.

5. Physical Streaming Replication & Sync Commit Modes¶

graph LR
    P["Primary Backend"] -->|"Appends WAL"| WB["WAL Buffers"]
    WB -->|"walsender"| TCP((TCP Socket))
    TCP -->|"walreceiver"| SB["Standby Node"]
    SB -->|"startup process"| DB["Standby Replay Engine"]

The synchronous_commit parameter determines how long the primary backend waits before acknowledging COMMIT to the client:

Mode	Safety Guarantee	Performance
`off`	Transaction committed in memory only. Up to `wal_writer_delay` (200ms) data loss on power crash.	Highest throughput
`local` (default)	Flushed to local disk with `fdatasync()`. Crash-proof on primary.	High throughput
`remote_write`	Primary waits until Standby OS receives the bytes (`write()`). Survives primary crash.	Low latency sync
`on`	Primary waits until Standby flushes bytes to disk (`fdatasync()`).	Zero data loss
`remote_apply`	Primary waits until Standby replays the WAL into Shared Buffers. Read-your-own-writes consistency.	Highest latency