WAL — The Write-Ahead Log

What is it? #

The write-ahead log is the mechanism behind almost everything PostgreSQL promises: durability, crash recovery, replication and point-in-time recovery.

The rule it is named after is simple. Before a change is written to the data files, it is written to the log. Write-ahead means log-first.

Why bother? Because writing to the data files means updating scattered pages all over the disk, which is slow. Writing to the log means appending to a single file sequentially, which is fast. So PostgreSQL records what it is about to do, tells you the transaction is committed, and updates the data files later.

If the server loses power in between, the log still holds every committed change, and PostgreSQL replays it on startup.

Once you are keeping that log anyway, two more things become possible: streaming it to another server (replication) and saving it to replay later (point-in-time recovery).

Think of it like this #

A shop where every sale is written in a notebook by the till the moment it happens, and the shelves are restocked in batches later.

Writing one line in a notebook is fast. Walking the aisles to adjust stock is slow. So the notebook is the record of truth, and the shelves catch up afterwards.

If the power fails, the shelves may be out of date — but the notebook has every sale, so you can work through it and bring the shelves back in line. That is crash recovery.

Photocopy each page as you fill it and send it elsewhere, and you can reconstruct the day's trading anywhere, up to any moment you choose. That is WAL archiving, and it is what makes point-in-time recovery possible.

Simple example #

A server loses power at 14:32. PostgreSQL had told the application that a payment at 14:31:58 was committed.

On restart, PostgreSQL reads the WAL from the last checkpoint, finds that payment recorded there, and reapplies it to the data files. The payment is not lost, even though it never reached the data files before the power went.

Code #

---------- The write-ahead rule ----------

    COMMIT
      │
      ├─ 1. Change written to the WAL buffer, then FLUSHED TO DISK (pg_wal/)
      │                                              ^^^^^^^^^^^^^^
      │                   COMMIT returns to the application at THIS point
      │
      └─ 2. Data pages in shared_buffers are marked dirty and written to the
            data files LATER, at a checkpoint

If the server crashes between 1 and 2, the data files are out of date
but the WAL holds the change. On startup PostgreSQL REPLAYS the WAL
from the last checkpoint. Nothing committed is ever lost.

# ---------- Where the WAL lives ----------

ls -lh /var/lib/postgresql/16/main/pg_wal/

# -rw------- 1 postgres postgres 16M 000000010000000000000023
# -rw------- 1 postgres postgres 16M 000000010000000000000024
#                                    ^^^^^^^^^^^^^^^^^^^^^^^^
#                                    timeline | logical id | segment

# Files are a FIXED 16 MB each, regardless of how much is in them.
# !! NEVER delete files from pg_wal by hand. PostgreSQL manages this
# !! directory, and removing a needed segment can make the database
# !! unrecoverable.

-- ---------- LSN: the position in the log ----------

SELECT pg_current_wal_lsn();          -- 0/23A4F120
--                                        ^ Log Sequence Number:
--                                          a byte offset into the WAL stream.
--                                          It only ever moves forward.

-- How much WAL has been generated between two points:
SELECT pg_size_pretty(
    pg_wal_lsn_diff('0/23A4F120', '0/22000000')
);

-- Which file holds a given LSN?
SELECT pg_walfile_name(pg_current_wal_lsn());

-- How much WAL is on disk right now?
SELECT count(*) AS segments,
       pg_size_pretty(count(*) * 16 * 1024 * 1024) AS total
FROM pg_ls_waldir();

---------- wal_level: how much detail is recorded ----------

minimal   Enough for crash recovery on this server only.
          NO replication, NO point-in-time recovery.

replica   DEFAULT. Enough to rebuild the database elsewhere:
          supports streaming replication and PITR.

logical   Everything in "replica", plus enough to reconstruct
          individual row changes for logical replication.

Leave it at "replica" unless you specifically need logical replication.
Changing it requires a RESTART.

# ---------- Enabling WAL archiving (required for PITR) ----------

# In postgresql.conf:
wal_level = replica
archive_mode = on
archive_command = 'test ! -f /archive/%f && cp %p /archive/%f'
#                  ^ %p = full path of the WAL file to archive
#                  ^ %f = just the filename
#                  ^ "test ! -f" refuses to overwrite an existing archived
#                    file — an important safety check.

archive_timeout = 300      # force a segment switch every 5 minutes even if
                           # it is not full, so a quiet database still
                           # archives regularly. This caps your data loss
                           # window at 5 minutes.

# archive_mode requires a RESTART. archive_command only needs a reload.
sudo systemctl restart postgresql

# ---------- A production-grade archive_command ----------

# Copying to a local directory is fine for learning. In production, archive
# somewhere the database server's failure cannot take with it:

archive_command = 'rsync -a %p backup@archive-host:/archive/%f'
archive_command = 'aws s3 cp %p s3://my-wal-archive/%f --only-show-errors'

# Or use a purpose-built tool, which handles retries, compression,
# encryption and verification for you:
archive_command = 'pgbackrest --stanza=main archive-push %p'

# !! THE CRITICAL RULE: archive_command MUST return 0 ONLY on success.
# If it returns 0 without actually archiving, PostgreSQL believes the
# segment is safe, recycles it, and YOUR RECOVERY CHAIN IS BROKEN
# with no error at the time.

-- ---------- Is archiving actually working? MONITOR THIS ----------

SELECT archived_count,
       last_archived_wal,
       last_archived_time,
       failed_count,
       last_failed_wal,
       last_failed_time
FROM pg_stat_archiver;

-- failed_count > 0, or last_failed_time recent  -> ARCHIVING IS BROKEN.
--
-- This matters more than it first appears: if archiving fails, PostgreSQL
-- KEEPS the WAL segments rather than discarding them. pg_wal grows without
-- limit, and when the disk fills, THE DATABASE STOPS ACCEPTING WRITES.
--
-- Alert on failed_count and on pg_wal size.

-- ---------- Checkpoints: where replay starts from ----------

-- A checkpoint writes all dirty pages to the data files and records a
-- point in the WAL. Crash recovery replays from the LAST checkpoint,
-- not from the beginning of time.

CHECKPOINT;        -- force one (normally only before maintenance)

SELECT * FROM pg_stat_bgwriter;
-- checkpoints_timed  triggered by checkpoint_timeout       <- healthy
-- checkpoints_req    forced because max_wal_size filled up <- too many means
--                    max_wal_size is too small

-- Trade-off:
--   frequent checkpoints -> fast recovery, more repeated disk writing
--   rare checkpoints     -> less writing, longer recovery after a crash

-- ---------- Replication slots: useful, and a common cause of a full disk ----------

SELECT slot_name, active, restart_lsn,
       pg_size_pretty(
         pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)
       ) AS wal_retained
FROM pg_replication_slots;

-- A slot guarantees PostgreSQL keeps WAL until the consumer has read it.
-- If a replica goes away and its slot is left behind, active = false and
-- wal_retained grows FOREVER until the disk fills.

-- Remove a slot that is genuinely no longer needed:
-- !! Only after confirming the replica is gone for good — dropping a slot
-- !! that is still in use will break that replica.
SELECT pg_drop_replication_slot('old_replica_slot');

-- Bound the damage instead:
--   max_slot_wal_keep_size = '50GB'   (PostgreSQL 13+)

# ---------- Manually switching and inspecting ----------

psql -c "SELECT pg_switch_wal();"     # finish the current segment now
                                      # (useful before taking a backup)

# What is in a WAL file? (mostly for curiosity and debugging)
pg_waldump /var/lib/postgresql/16/main/pg_wal/000000010000000000000023 | head

How it works #

The write-ahead rule exists because of a simple asymmetry: appending to one file sequentially is far cheaper than updating many scattered pages. PostgreSQL exploits this by making the log the authoritative record of a commit, and letting the data files catch up afterwards.

This is what COMMIT actually waits for. It does not wait for your table's pages to be written to disk; it waits for the WAL record to be flushed. That is why commits are fast while still being durable.

A WAL segment is a fixed 16 MB file. Its name encodes the timeline, a logical file id and a segment number, which is why the names look opaque but sort correctly. An LSN is a byte position within the overall WAL stream, and it only ever moves forward — which makes it the natural way to express "how far behind is this replica" or "recover up to here".

wal_level controls how much is recorded. minimal supports only crash recovery on the same server. replica, the default, records enough to rebuild the database elsewhere, which is what both replication and PITR require. logical adds row-level detail for logical replication.

Archiving is what turns the WAL from a crash-recovery mechanism into a recovery-to-any-point mechanism. When a segment fills, PostgreSQL runs archive_command to copy it somewhere permanent. The safety rule here is absolute and worth stating plainly: archive_command must return zero only when the file is genuinely safely stored. A command that returns success without archiving causes PostgreSQL to recycle the segment, silently breaking the recovery chain — and nothing will reveal the problem until a recovery is attempted and fails.

The test ! -f ... && cp ... idiom exists to refuse overwriting an existing archived file, which protects against a subtle corruption where two servers archive to the same place.

archive_timeout matters on quiet databases. Without it, a database generating little traffic might not fill a 16 MB segment for hours, so the most recent hours would not be archived at all. Forcing a switch every few minutes caps how much data a recovery could lose.

Checkpoints determine where replay starts. Because a checkpoint guarantees all prior changes are in the data files, recovery only needs the WAL written since the last one. More frequent checkpoints mean faster recovery but more repeated writing of the same hot pages.

Replication slots guarantee WAL retention until a consumer has consumed it — genuinely useful, and the most common cause of a full pg_wal in production. An abandoned slot retains WAL indefinitely, and when the disk fills the database stops accepting writes. max_slot_wal_keep_size bounds it.

Real-world use #

Two things about the WAL will take a database down if unmonitored, and both are easy to alert on.

The first is failed archiving. When archive_command fails, PostgreSQL correctly refuses to discard the unarchived segments, so pg_wal grows without limit. The disk fills, writes stop, and the application is down. Alerting on pg_stat_archiver.failed_count and on pg_wal size catches this long before it becomes an outage.

The second is an inactive replication slot, which retains WAL for a consumer that is never coming back, with the same ending. Check pg_replication_slots for slots where active is false and wal_retained is growing.

Beyond that, keep the archive somewhere the database server's failure cannot destroy — a different host, or object storage. WAL archived to the same disk as the database protects against nothing that matters.

Test that the archive is usable, not just that it is being written. The archiving equivalent of an untested backup is an archive full of files nobody has ever replayed. The PITR lesson covers doing exactly that, and it is the only real proof.

For anything beyond a small single server, use a purpose-built tool. pgBackRest, Barman or WAL-G handle archiving with retries, parallelism, compression, encryption and integrity checks, and they fail loudly in the ways a hand-rolled cp command does not. A hand-written archive_command is excellent for learning how this works and a liability to maintain at scale.

Finally, watch checkpoints_req versus checkpoints_timed. A high proportion of requested checkpoints is PostgreSQL telling you max_wal_size is too small, and the fix is one configuration change.

Common mistakes #

• Writing an archive_command that returns 0 without actually archiving, silently breaking recovery.
• Never monitoring pg_stat_archiver.failed_count, so archiving failures fill pg_wal and stop writes.
• Leaving an inactive replication slot in place, retaining WAL indefinitely until the disk fills.
• Deleting files from pg_wal by hand to free space, which can make the database unrecoverable.
• Archiving WAL to the same disk as the database, so a disk failure destroys both.

Practice #

On a test server, enable archive_mode with an archive_command copying to a local directory, and set archive_timeout to 60 seconds. Generate some writes, then confirm files appear in the archive directory and that pg_stat_archiver shows a rising archived_count and zero failures. Now deliberately break it — point the archive at a non-existent directory — and watch failed_count rise and pg_wal start growing. Fix it and confirm the backlog drains.