WAL (Write-Ahead Log)

What is it: Before any data page modification reaches the heap, the change is written to the WAL sequentially. The WAL is an append-only log of every change.

Why: Sequential writes to WAL are orders of magnitude faster than random writes to data pages (for spinning disks: 100 MB/s sequential vs 1 MB/s random). WAL batches random writes into sequential log entries, improving write throughput dramatically. On crash, the WAL is replayed to reconstruct any changes not yet flushed to the heap.

graph LR txn["Transaction commits"] --> wal["1. Append to WAL<br/>(sequential write, fsync'd)"] wal --> ack["2. Acknowledge commit to client"] wal -.->|"applied later, can lag"| heap["3. Apply to heap pages<br/>(random writes, batched)"] crash["Crash before step 3 finishes"] -.->|"replay WAL from last checkpoint"| heap

The client never waits for step 3 — durability comes entirely from the WAL record being safely on disk, which is what makes commit latency depend on one sequential fsync rather than on however many random heap pages the transaction happened to touch.

Checkpoint: periodically, PostgreSQL flushes dirty heap pages to disk and records the checkpoint LSN. On crash recovery, only WAL after the last checkpoint needs replaying, bounding recovery time.

Replication via WAL: standby servers replay the primary's WAL stream in real time. Synchronous replication waits for standby WAL receipt before acknowledging commit — zero data loss but higher latency. Asynchronous replication does not wait — lower latency but potential data loss on primary failure (RPO > 0).

Trade-offs: fsync on every commit guarantees durability but adds ~1–5ms latency per commit. synchronous_commit = off skips fsync — commits acknowledge before WAL is on disk. Loses last ~200ms of commits on crash, but dramatically improves throughput for non-critical writes.

Common pitfall

Turning off fsync entirely (not just synchronous_commit) to chase write throughput is a much larger risk than it looks — fsync=off means the operating system's page cache can lose WAL data on a power loss or kernel crash, not just the last ~200ms of application commits synchronous_commit=off risks. That difference is the entire point of WAL existing in the first place: fsync=off can corrupt the database in a way that's unrecoverable, since the log meant to reconstruct a consistent state after a crash can itself be incomplete. synchronous_commit=off is a real, sanctioned trade-off for non-critical writes; disabling fsync outright generally isn't.