Write-Ahead Log (WAL)

Also Known As

TL;DR

Explanation

The Write-Ahead Log (WAL) is the primary mechanism for crash recovery and replication in databases. Before modifying any data pages, the database writes a log record describing the change to a sequential append-only file. On crash recovery, the database reads the WAL from the last checkpoint forward and re-applies all committed transactions. Uncommitted transactions are rolled back. This 'write log first, then data' rule (the WAL protocol) ensures durability — once a transaction is acknowledged, its log record is on disk even if the data pages are still in memory. PostgreSQL exposes WAL directly: pg_wal/ contains the log files; WAL streaming is the mechanism for replication; logical decoding reads WAL for change data capture.

Common Misconception

Why It Matters

Common Mistakes

• Setting fsync=off in production for performance — this is the default for many Docker-based PostgreSQL setups; it causes database corruption on power loss or OOM kills.
• Not archiving WAL for point-in-time recovery — without WAL archiving, you can only restore to your last base backup, not to any moment in between.
• Underestimating WAL volume during high write periods — replication lag grows when WAL is generated faster than replicas can apply it; monitor pg_replication_slots.
• Using logical replication slots without monitoring — unconsumed logical replication slots cause WAL to accumulate indefinitely, filling disk.

Avoid When

• Do not disable WAL/redo logging in production databases — it is the primary durability guarantee.
• Avoid extremely large WAL files by tuning checkpoint frequency — unbounded WAL growth increases crash recovery time.

When To Use

• WAL is the mechanism behind database crash recovery and replication — understanding it helps debug replication lag and disk I/O spikes.
• Enable WAL mode in SQLite for better concurrent read performance.

Code Examples

# ❌ PostgreSQL settings that sacrifice durability for speed
# Dangerous in production — data loss on crash
fsync = off                  # Disables OS fsync — data NOT guaranteed on disk
synchronous_commit = off     # Commits return before WAL written to disk
full_page_writes = off       # Risks torn page corruption after crash

# ✅ PostgreSQL WAL settings — durability vs performance
# Production defaults (safe):
fsync = on                   # Ensure WAL is on disk before ack
synchronous_commit = on      # Default — safest
full_page_writes = on        # Protects against torn page writes
wal_level = replica          # Required for streaming replication
archive_mode = on            # Enable WAL archiving for PITR
archive_command = 'cp %p /mnt/wal-archive/%f'

# For read replicas:
# primary_conninfo in recovery.conf
# Replica streams WAL from primary and applies it continuously

References

• ↗ https://www.postgresql.org/docs/current/wal-intro.html
• ↗ https://www.postgresql.org/docs/current/runtime-config-wal.html

Tags

Related Terms