PostgreSQL WAL: What Every DBA Must Understand

The Log Behind Everything

If you understand one internal subsystem in PostgreSQL, make it the WAL — the Write-Ahead Log. Durability, crash recovery, replication, point-in-time restore, and streaming replicas are all built on it. For an Oracle DBA it maps almost exactly onto redo logs, and once you see that parallel, most of PostgreSQL’s recovery and HA story clicks into place.

Write-Ahead: Log First, Then Data

The rule is in the name: before any change is written to a data file, it is written to the log first. When you commit, PostgreSQL flushes the WAL record to disk and returns success — the change to the actual table pages can happen later, in the background. This is why a commit is fast even for a change scattered across many pages, and why a crash is survivable: on restart, PostgreSQL replays the WAL from the last checkpoint and reconstructs every committed change that had not yet reached the data files.

WAL is written in fixed 16 MB segment files under pg_wal/. Position in the log is a LSN (Log Sequence Number) — the equivalent of Oracle’s SCN, and the currency of replication: every replica tracks the LSN it has received, flushed, and replayed.

The Oracle Translation

Checkpoints: Where Recovery Starts

A checkpoint flushes all dirty pages to the data files and records a point in the WAL from which recovery can safely begin. Everything before the last checkpoint is already on disk; recovery only needs to replay what came after. Checkpoints that fire too often waste I/O; too rarely, and crash recovery takes longer and WAL piles up. The knobs:

max_wal_size = 4GB          # trigger a checkpoint after this much WAL
min_wal_size = 1GB
checkpoint_timeout = 15min   # ...or after this long, whichever first
checkpoint_completion_target = 0.9  # spread the flush over the interval

Watch for the log line checkpoints are occurring too frequently — it is PostgreSQL telling you max_wal_size is too small for your write volume.

Archiving and Point-in-Time Recovery

Turn on WAL archiving and you can restore to any moment in the past: take a base backup, then keep every WAL segment since. Recovery restores the base and replays WAL up to the exact target time or LSN — the same PITR you know from Oracle. In practice you let pgBackRest manage the base backups and the WAL archive rather than hand-rolling archive_command.

Practical WAL Tuning

• wal_compression = on — compresses full-page images; less WAL, cheaper replication.
• wal_level = replica (or logical if you need logical decoding / CDC).
• Put pg_wal on fast storage — it is the commit-latency critical path; separate it from data if you can.
• Watch replication slots — an abandoned slot forces the primary to retain WAL forever and can fill the disk.