Master PostgreSQL WAL: The Ultimate Guide for DBAs and Production Ops

What is WAL?

WAL (Write‑Ahead Logging) is PostgreSQL's binary log that records physical page‑level changes before they are written to data files. It guarantees durability, crash recovery, point‑in‑time recovery (PITR), streaming replication, and improved write performance. The log files reside in $PGDATA/pg_wal/.

Key Functions of WAL

Crash Recovery : After a crash, PostgreSQL replays WAL to restore committed transactions.

Durability : A transaction is considered successful only after its changes are persisted in WAL (e.g., COMMIT).

PITR : Combined with base backups, WAL enables restoration to any point in time.

Streaming Replication : Standby servers continuously receive WAL to stay synchronized.

Write Performance : Writes are first appended to WAL sequentially, which is faster than random writes to data files.

WAL Workflow

Checkpoint – The Key to WAL Recycling and Recovery Speed

During a checkpoint PostgreSQL flushes dirty pages to disk, writes a checkpoint record, and marks the point from which recovery can start, thereby allowing WAL segments before the checkpoint to be recycled.

Essential WAL Monitoring Metrics

1. Current WAL directory size

SELECT pg_size_pretty(sum(size)) AS total_wal_size
FROM pg_ls_waldir();

2. WAL generation rate

SELECT pg_current_wal_lsn();
-- Run the query again after a short interval and compute the difference.

3. Checkpoint statistics

SELECT * FROM pg_stat_bgwriter;

Important columns to watch: checkpoints_timed – automatically scheduled checkpoints. checkpoints_req – forced checkpoints (may indicate insufficient WAL space). buffers_checkpoint – I/O load caused by checkpoints.

4. Replication slot WAL retention

SELECT slot_name,
       pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn) AS retained_bytes
FROM pg_replication_slots;

If a replication slot is not being consumed, WAL will never be removed, leading to disk exhaustion.

Production‑Grade WAL Configuration

wal_level = replica

(use logical only when logical replication is required). max_wal_size = 8GB‑32GB (too small causes frequent checkpoints and performance jitter). checkpoint_timeout = 10‑15min (default 5 min is usually too short). checkpoint_completion_target = 0.8‑0.9 (smoothes I/O). wal_compression = on (recommended for high‑write workloads). archive_mode = on (essential for critical data). wal_keep_size = 1GB‑4GB (prevents standby from falling behind).

wal_level = replica
max_wal_size = 16GB
checkpoint_timeout = 10min
checkpoint_completion_target = 0.9
wal_compression = on
archive_mode = on
wal_keep_size = 2GB

DBA Daily Checklist (Practical Ops)

Emergency WAL Full Handling

-- Find inactive replication slots
SELECT slot_name, active FROM pg_replication_slots WHERE active = false;
-- Drop the unused slot
SELECT pg_drop_replication_slot('slot_name');

Detect Long‑Running Transactions

SELECT pid, usename, state, xact_start, query
FROM pg_stat_activity
WHERE xact_start IS NOT NULL
ORDER BY xact_start;

Terminate idle long transactions immediately:

SELECT pg_terminate_backend(pid);

Backup & PITR Recovery – Guarding Against Data Loss

1️⃣ Create a Base Backup

pg_basebackup -D /backup/base -Ft -z -P

2️⃣ Configure WAL Archiving

archive_mode = on
archive_command = 'cp %p /wal_archive/%f'

3️⃣ Restore to a Specific Point in Time

restore_command = 'cp /wal_archive/%f %p'
recovery_target_time = '2025-02-01 10:30:00'

Perform a real recovery drill at least once per quarter; otherwise backups provide only psychological comfort.

Final Thoughts

Understanding WAL explains why PostgreSQL achieves high performance through sequential WAL writes, relies on checkpoints to persist data long‑term, and depends on backup + WAL archiving for robust point‑in‑time recovery. Mastering WAL, tuning its parameters, and rehearsing recovery are essential for a production‑ready PostgreSQL deployment.