WAL and durability

Write-ahead: log first, then the page

Before you decide whether to flip synchronous_commit, you need to understand what it controls — because the two durability flags do very different things. The core rule of crash safety is write-ahead logging: before Postgres modifies a data page, it first appends a compact record of the change to the WAL — a sequential, append-only log. Only the WAL record must be flushed to durable storage at commit time; the actual table and index pages can be written later, lazily, by the background writer and checkpointer.

Why is this faster and safer than writing the data page itself at commit? Because WAL writes are sequential (append to the end of the current log file) while data-page writes are random (scattered across the heap). Forcing one small sequential fsync per commit is far cheaper than forcing many random page writes — yet it’s enough, because if the box crashes before the dirty pages are flushed, recovery replays the WAL and reconstructs them. The data pages are an optimization; the WAL is the source of truth.

-- Where WAL currently is, and how fast it's being generated:
SELECT pg_current_wal_lsn();                       -- current write position (LSN)
SHOW synchronous_commit;                            -- on | off | remote_apply ...
SHOW wal_level;                                     -- replica (default) | logical | minimal
SHOW max_wal_size;                                  -- soft ceiling that triggers checkpoints

The durability/throughput knob

synchronous_commit controls when a commit is acknowledged relative to the WAL flush:

• on (default) — COMMIT waits until the WAL record is fsync’d to durable storage. A committed transaction survives a crash. Full durability, one fsync of latency per commit.
• off — COMMIT returns immediately after writing WAL to the OS buffer, before fsync. The flush happens asynchronously within wal_writer_delay (≈200 ms). On a crash you can lose the last fraction of a second of committed transactions — but the database stays consistent (no corruption, no torn state), you just lose the tail. In exchange, throughput on small write transactions can jump 2–3× because commits no longer block on disk.

This is the single most consequential durability tuning decision. The senior framing: synchronous_commit = off is not “unsafe” the way disabling fsync is — it never corrupts the database, it only widens the window of lost-but-acknowledged commits. For a payments ledger: never. For an analytics ingest pipeline that can replay its source: often a free 3× win.

Two related flags people confuse: fsync = off is the genuinely dangerous one — it lets the WAL itself skip the disk flush, so a crash can corrupt the cluster; never use it outside a throwaway test box. And synchronous_commit = remote_apply extends durability to a replica having applied the change (covered with replication below).

Checkpoints bound recovery

If WAL accumulated forever, crash recovery would replay from the dawn of time. A checkpoint is the periodic act of flushing all dirty data pages to the heap and recording, “everything up to this WAL position is now safely on the data files.” Recovery only needs to replay WAL after the last checkpoint — so checkpoint frequency bounds recovery time.

The tradeoff: frequent checkpoints mean short recovery but more constant write I/O (flushing dirty pages often); rare checkpoints mean less steady I/O but a long, spiky recovery and large WAL. Postgres triggers a checkpoint when either checkpoint_timeout (default 5 min) elapses or WAL hits max_wal_size (default 1 GB). Production tuning usually raises both so checkpoints are spread out and write spikes smooth, accepting a longer recovery — checkpoint_completion_target (default 0.9) spreads the flush across the interval to avoid an I/O wall.