PostgreSQL WAL and MVCC Under the Hood: How Postgres Guarantees Durability, Crash Recovery, and Concurrent Reads Without Locks
Every time you run a COMMIT in PostgreSQL, the database makes an iron-clad promise: if the server crashes one millisecond later, your data will survive. At the same time, a reader on another connection sees a perfectly consistent snapshot without waiting for your transaction to finish. These two guarantees — durability and non-blocking reads — seem like they should conflict. They do not. PostgreSQL delivers both through two interlocking mechanisms: Write-Ahead Logging (WAL) and Multi-Version Concurrency Control (MVCC). This post traces a single UPDATE statement from execution through WAL record construction, LSN assignment, checkpoint flush, crash recovery replay, and MVCC visibility checks, exposing every data structure along the way.
1. Why Databases Need Write-Ahead Logging
1.1 The Fundamental Problem: RAM Is Volatile, Disk Is Slow
Every relational database maintains a buffer pool (shared_buffers in Postgres) — a region of shared memory that caches frequently accessed data pages from disk. When a row is modified, Postgres changes it in the buffer pool first, marking that page as dirty. The dirty page is eventually flushed to disk, but not immediately — flushing after every single write would serialize all writes to disk, capping throughput to the speed of one fsync per transaction.
This creates a dangerous window: if the server crashes while dirty pages sit in RAM, those changes are lost. The data on disk reflects a state from some earlier point. Without any recovery mechanism, the database is in an inconsistent state after every crash.
1.2 The WAL Contract: Write the Log Before the Data
Write-Ahead Logging solves this with a strict ordering rule: before any data page modification is considered committed, a description of that modification must be written and flushed to a separate append-only log file. This log is the WAL. Because the WAL is append-only and written sequentially, it achieves far higher write throughput than random page writes. At commit time, Postgres calls fsync() on the WAL file. Only after that fsync succeeds is the COMMIT acknowledged to the client. The actual data pages may still be dirty in RAM — that is fine, because the WAL contains everything needed to reconstruct those changes after a crash.
Setting synchronous_commit = off tells Postgres to acknowledge COMMIT to the client before the WAL is flushed to disk. This trades durability for roughly 1–3ms of latency improvement per transaction. If the server crashes in that window, the last few committed transactions are silently lost. This is acceptable for high-throughput metrics ingestion where some data loss is tolerable, but catastrophic for financial or user-data transactions. Never use it on your primary OLTP database without explicit documentation of the trade-off.
2. WAL Anatomy: The Structure of a WAL Record
2.1 WAL Files and Segments
WAL is stored in the pg_wal/ directory. Each WAL file is exactly 16MB by default and named with a 24-character hexadecimal identifier encoding the timeline and segment number, for example: 000000010000000000000001. Each WAL file is divided into 8KB pages matching the default heap page size. WAL records are packed into pages sequentially and can span across page boundaries.
2.2 WAL Record Header Fields
| Field | Size | Contents |
|---|---|---|
xl_tot_len | 4 bytes | Total length of this WAL record including header |
xl_xid | 4 bytes | Transaction ID that generated this record |
xl_prev | 8 bytes (LSN) | LSN of the previous WAL record (enables backward traversal) |
xl_rmid | 1 byte | Resource Manager ID (Heap, Btree, Transaction, etc.) |
| Data blocks | Variable | One or more blocks: each references a relation/page and contains change delta or full-page image (FPI) |
2.3 Full-Page Images (FPIs): Protection Against Torn Pages
When a page is modified for the first time after a checkpoint, Postgres writes a Full-Page Image (FPI) into the WAL record: the entire 8KB page is embedded in the WAL. Why? A power failure mid-write could leave a disk page in a torn state — half old, half new. A WAL delta alone cannot fix a torn page, but a full-page image can overwrite it completely during recovery.
This behavior is controlled by full_page_writes = on (the default). FPIs significantly increase WAL volume but provide essential protection. FPIs are the safest choice unless you are using a filesystem that guarantees atomic 8KB writes, which most cloud storage does not.
Setting full_page_writes = off reduces WAL volume by 30–50% on write-heavy workloads. But on a filesystem without atomic large-block writes, a crash mid-checkpoint can produce a torn page that cannot be repaired by WAL replay, resulting in database corruption. Never disable FPIs on cloud VM storage (EBS, Google Persistent Disk).
3. The LSN: Postgres's Monotonic Clock of Truth
3.1 What Is an LSN?
The Log Sequence Number (LSN) is a 64-bit unsigned integer representing a byte offset into the WAL stream. Every WAL record is assigned the LSN of its starting byte position. LSNs are monotonically increasing and never reset. Postgres displays LSNs as two hex numbers separated by a slash, for example 0/15D4B40. You can query the current WAL position at any time:
-- Current WAL write position on primary SELECT pg_current_wal_lsn(); -- 0/15D4B40 -- WAL position a standby has replayed up to SELECT replay_lsn FROM pg_stat_replication; -- Replication lag in bytes SELECT pg_wal_lsn_diff(sent_lsn, replay_lsn) AS lag_bytes FROM pg_stat_replication;
3.2 How LSNs Anchor the Entire System
Every heap page stores the LSN of the last WAL record that modified it. During crash recovery, if a page's on-disk LSN is ≥ the WAL record's LSN, that record is skipped (already applied). The checkpoint record stores the redo pointer LSN from which WAL replay begins after a crash. Streaming replication standbys report their replay LSN; the primary computes lag as a byte difference. Point-in-time recovery replays WAL up to a target LSN or timestamp.
48MB of WAL lag means 5 seconds behind on a system generating 10MB/s, but only 96ms behind on a system generating 500MB/s. Always divide WAL byte lag by the current WAL generation rate to get meaningful time estimates. Use pg_stat_replication together with WAL bytes/sec from your monitoring tool.
4. Worked Trace: How a Single UPDATE Travels the WAL Pipeline
4.1 The Statement
BEGIN; UPDATE accounts SET balance = balance - 100 WHERE id = 42; COMMIT;
Here is the exact sequence of operations Postgres executes:
xmax field of the old row version (id=42, balance=500) to 7412. This marks it as deleted by transaction 7412. The old tuple stays on the page physically.xmin=7412 and xmax=0 (not yet deleted). Both versions now coexist on the page.XLOG_HEAP_UPDATE WAL record containing: XID (7412), relation OID, block number, old tuple offset, and new tuple data (the full updated row).0/15D4B40. The heap page's pd_lsn field is updated to this LSN.When an UPDATE modifies a non-indexed column, Postgres uses Heap-Only Tuple (HOT) updates: the new version is placed on the same page with a pointer chain and no new index entry is created. But if the update modifies an indexed column, or if the page is full, Postgres inserts a new index entry for every index on the table. On write-heavy tables with many indexes, this creates significant index bloat. Check EXPLAIN (ANALYZE, BUFFERS) and monitor Heap Fetches in the output.
5. Checkpoints: When Dirty Pages Hit Disk
5.1 Why Checkpoints Exist
Without checkpoints, crash recovery would need to replay the entire WAL history from the beginning of time. Checkpoints solve this by periodically flushing all dirty buffer pool pages to disk and recording the checkpoint LSN. After a crash, recovery only needs to replay WAL from the last checkpoint's redo pointer.
A checkpoint is triggered when: elapsed time exceeds checkpoint_timeout (default 5 minutes), WAL generated since the last checkpoint exceeds max_wal_size (default 1GB), or the DBA runs CHECKPOINT manually. Writing every dirty page instantly creates a massive I/O spike. Postgres spreads dirty page writes over checkpoint_completion_target (default 0.9) of the inter-checkpoint interval, meaning 90% of the time between checkpoints is used to gradually flush dirty pages.
# postgresql.conf tuning for write-heavy workloads checkpoint_timeout = 15min # Allow longer inter-checkpoint windows max_wal_size = 4GB # Allow more WAL before forcing a checkpoint checkpoint_completion_target = 0.9 # Spread dirty writes over 90% of the window wal_buffers = 64MB # Larger WAL buffer reduces write overhead shared_buffers = 8GB # 25% of RAM typical rule
If max_wal_size is too small for your write volume (e.g., 1GB on a database generating 5GB/hour of WAL), Postgres is forced to trigger checkpoints more frequently than the configured interval. These appear in logs as "WARNING: checkpoints are occurring too frequently." Increase max_wal_size to at least 2–4x your expected peak WAL generation per checkpoint_timeout window.
6. Crash Recovery: Replaying WAL from the Last Checkpoint
6.1 The Recovery Sequence
When Postgres starts after a crash, it detects an unclean shutdown and enters recovery mode:
- Read the control file (
pg_control) which records the LSN of the most recent checkpoint and database state. - Locate the checkpoint record in WAL at the LSN stored in
pg_control. The checkpoint record contains the redo pointer — the LSN from which replay must start. - Sequential WAL replay from the redo pointer: for each WAL record, read the target page and compare the page's
pd_lsnagainst the record's LSN. Ifpage.pd_lsn < record.lsn, apply the change; otherwise skip it (already applied, idempotent). - Replay ends when the last WAL record is reached. Uncommitted transactions whose COMMIT record was never written are treated as aborted because they are absent from pg_xact.
- Postgres writes a new checkpoint and transitions to normal operation.
6.2 The pg_xact Commit Log
Postgres maintains a commit log (pg_xact/) that records, for every transaction ID, whether it committed, aborted, or is in-progress. This is a compact bit array: 2 bits per XID. MVCC visibility checks consult pg_xact to determine whether a tuple's xmin transaction committed or aborted.
WAL replay is idempotent by design. A crash during recovery is safe: Postgres restarts recovery from the same checkpoint LSN. The replay may re-apply some records, but since the page LSN check prevents double-application, the result is always correct. This idempotency is a core correctness property of the WAL design.
7. MVCC: How Postgres Serves Reads Without Locks
7.1 The Core Insight: Keep Every Version
Most databases use read/write locking to prevent readers from seeing partially written data. PostgreSQL never locks data for reads. Instead, it keeps multiple versions of each row simultaneously and gives each transaction a consistent snapshot showing only the versions that were committed before the transaction began. This is Multi-Version Concurrency Control. The trade-off is storage: old row versions accumulate on heap pages until VACUUM reclaims them.
7.2 Snapshots: The Visibility Window
When a transaction begins (or at each statement in READ COMMITTED isolation), Postgres takes a snapshot capturing:
- xmin: The smallest XID of a still-active transaction at snapshot time. Any tuple created by an XID less than xmin is visible (that transaction definitely committed).
- xmax: One past the highest XID assigned at snapshot time. Any tuple created by an XID ≥ xmax is invisible (started after our snapshot).
- xip_list: The list of XIDs that were active (in-progress) at snapshot time. Tuples from these XIDs are invisible even if their XID falls in [xmin, xmax].
A row version is visible to a snapshot if: its xmin committed AND is less than snapshot.xmin (or in [xmin,xmax] but not in xip_list), AND its xmax is either 0 or a transaction that had not yet committed at snapshot time.
In READ COMMITTED (Postgres default), a new snapshot is taken at the start of each statement. Two SELECTs in the same transaction can see different data if another transaction commits between them. In REPEATABLE READ, the snapshot is taken once at transaction start. Use REPEATABLE READ whenever your transaction reads data, makes a decision based on it, and then writes based on that decision — otherwise you risk write-skew anomalies.
8. xmin and xmax: The Visibility Engine in Practice
8.1 Viewing Tuple Headers Directly
SELECT xmin, xmax, ctid, id, balance FROM accounts WHERE id = 42; -- xmin | xmax | ctid | id | balance -- --------+------+---------+----+--------- -- 7411 | 0 | (0,1) | 42 | 500 -- old version (before update) -- 7412 | 0 | (0,2) | 42 | 400 -- new version (after update) -- ctid = (page_number, item_offset_on_page) -- xmax = 0 means not yet deleted = currently the live version
Both row versions appear on the same page (0,_). A transaction with snapshot xmin less than 7412 sees the first row. A transaction started after 7412 committed sees the second. The old row with xmax=7412 remains on the page until VACUUM determines no active snapshot can see it and reclaims the slot.
8.2 Hint Bits Optimization
Each tuple header has two hint bits: HEAP_XMIN_COMMITTED and HEAP_XMAX_COMMITTED. Initially unset, they are set when a transaction first verifies that xmin or xmax has committed by consulting pg_xact. Future readers can check the hint bit instead of consulting pg_xact, saving a lookup per tuple. However, setting a hint bit modifies the heap page, which generates a WAL record. This is one reason read-heavy patterns still produce some WAL writes.
VACUUM cannot reclaim any dead tuple visible to any active snapshot. An idle transaction that opened a snapshot hours ago holds a very old xmin. As other transactions update rows, dead versions accumulate but VACUUM cannot remove them because the idle transaction's snapshot might still need them. Monitor with: SELECT pid, now() - xact_start AS age, state, query FROM pg_stat_activity WHERE state != 'idle' ORDER BY age DESC LIMIT 20;
9. VACUUM: The Dead Row Reaper
9.1 What VACUUM Does and Does Not Do
VACUUM scans heap pages and identifies row versions whose xmax transaction committed and whose xmax is older than any active snapshot's xmin. These dead tuples are safe to reclaim. VACUUM marks their space as reusable in the page's free-space map so future INSERTs can reuse the space. VACUUM does not shrink the physical file — it only marks space as reusable within existing pages. VACUUM FULL rewrites the entire table to a new file but takes an ACCESS EXCLUSIVE lock blocking all reads and writes.
9.2 autovacuum Tuning for High-Churn Tables
-- Per-table autovacuum tuning for high-churn tables ALTER TABLE events SET ( autovacuum_vacuum_scale_factor = 0.01, -- Trigger at 1% dead tuples (not 20%) autovacuum_vacuum_threshold = 100, -- Plus at least 100 dead tuples autovacuum_vacuum_cost_delay = 2, -- Faster vacuuming autovacuum_analyze_scale_factor = 0.005 -- More frequent stats updates ); -- Monitor dead tuple accumulation SELECT relname, n_dead_tup, n_live_tup, last_autovacuum FROM pg_stat_user_tables WHERE n_dead_tup > 10000 ORDER BY n_dead_tup DESC;
autovacuum deliberately throttles itself with a pause between pages based on autovacuum_vacuum_cost_delay (default 2ms). On very write-heavy tables, this throttling means autovacuum cannot keep up with dead-tuple accumulation. If n_dead_tup is growing faster than last_autovacuum intervals, reduce autovacuum_vacuum_cost_delay per-table to 1ms and increase autovacuum_vacuum_cost_limit globally from 200 to 800.
10. Advanced: Transaction ID Wraparound — The 32-Bit Ticking Clock
10.1 The Wraparound Problem
Postgres transaction IDs are 32-bit unsigned integers. The maximum XID is approximately 4 billion (2³²). Postgres uses a circular XID space: XIDs wrap around after reaching the maximum. To prevent an old transaction from appearing as newer than current transactions after wraparound, Postgres treats XID space as circular distance where any XID more than 2 billion older than the current XID is considered "in the past." If any live table contains a tuple whose xmin is more than 2 billion transactions old, that tuple risks becoming invisible after wraparound.
VACUUM prevents this by freezing old tuple XIDs: replacing them with the special FrozenTransactionId (XID=2) which is always considered older than everything. Freezing is triggered automatically by autovacuum when a table's oldest unfrozen XID exceeds autovacuum_freeze_max_age.
10.2 Monitoring XID Age
-- Transactions remaining until wraparound per database
SELECT datname,
age(datfrozenxid) AS xid_age,
2147483648 - age(datfrozenxid) AS xids_remaining
FROM pg_database
ORDER BY xid_age DESC;
-- Tables with highest XID age (most urgent vacuum targets)
SELECT schemaname, relname,
age(relfrozenxid) AS xid_age,
pg_size_pretty(pg_total_relation_size(oid)) AS size
FROM pg_class
WHERE relfrozenxid != 0
ORDER BY xid_age DESC LIMIT 10;
-- Alert threshold: xid_age > 1.5 billion (leaves 500M safety buffer)
-- Emergency: xid_age > 2 billion (Postgres enters anti-wraparound forced vacuum mode)
-- Fatal: xid_age > 2.147 billion (Postgres refuses writes: "not accepting commands")When a database's oldest unfrozen XID reaches 2³¹ transactions old, Postgres refuses all write transactions with: "database is not accepting commands to avoid wraparound data loss." Recovery requires running VACUUM FREEZE on every table, which can take many hours on a large database. Prevention: alert at 1 billion XID age, take corrective action at 1.5 billion. Set autovacuum_freeze_max_age = 150000000 on busy systems.
11. WAL as Replication: How Standbys Stay in Sync
11.1 Physical vs Logical Replication
Physical streaming replication ships the raw WAL byte stream from primary to standby. The standby is an exact binary copy of the primary and continuously replays incoming WAL records, enabling read-only queries while staying in sync. This is the foundation of read replicas in managed services like Amazon RDS, Google Cloud SQL, and Supabase.
Logical replication decodes the WAL stream into a row-level change feed (INSERT/UPDATE/DELETE with column values) using the logical decoding infrastructure. This enables table-level selective replication, replication to different Postgres versions, and CDC pipelines via tools like Debezium that emit WAL changes to Kafka or other message buses.
11.2 WAL Level Comparison
| wal_level | WAL Volume | Supports | Use Case |
|---|---|---|---|
| minimal | Lowest | Crash recovery only | Standalone DB, no replicas |
| replica | Medium | Crash recovery + physical replication | Most production setups |
| logical | Highest | All above + logical replication + CDC | Debezium, cross-version replication, audit logs |
A replication slot preserves all WAL segments until the subscriber has consumed them. If a subscriber goes offline and the slot is not dropped, the pg_wal/ directory grows unboundedly until disk space runs out and the database crashes. Always monitor SELECT slot_name, active, pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn) AS lag_bytes FROM pg_replication_slots; and drop inactive slots immediately when a subscriber is decommissioned.
12. Tuning WAL for Production: Essential Configuration Reference
| Parameter | Default | Production Recommendation | Controls |
|---|---|---|---|
| synchronous_commit | on | on (never off for OLTP) | Whether COMMIT waits for WAL fsync |
| wal_buffers | -1 (auto) | 64MB on write-heavy systems | WAL write buffer size in shared memory |
| max_wal_size | 1GB | 4–16GB on write-heavy | WAL size threshold that triggers checkpoint |
| checkpoint_timeout | 5min | 15–30min | Time-based checkpoint trigger interval |
| full_page_writes | on | on (always) | Embed full-page images in WAL after checkpoint |
| autovacuum_freeze_max_age | 200M | 150M on busy systems | XID age at which autovacuum forces FREEZE |
pg_waldump reads WAL segment files and prints human-readable records showing: LSN, resource manager, record type, relation OID, block number, and XID. Example: pg_waldump -p /var/lib/pgsql/data/pg_wal -s 0/15D4B40 -e 0/16000000 | grep COMMIT lists all commits in that LSN range. Many Postgres DBAs never discover this tool until they face a crisis — know it before you need it.
13. Frequently Asked Questions
Q1: What is the difference between WAL and undo logs as in MySQL InnoDB?
PostgreSQL uses a redo-only WAL: it writes forward-only change records. Old row versions are stored directly in the heap as dead tuples. MySQL InnoDB uses both a redo log (for durability) and a separate undo log (for MVCC old-version storage in a dedicated undo tablespace). In Postgres there is no separate undo log, so old tuple versions sit in the heap until VACUUM reclaims them. This makes Postgres MVCC simpler but means table bloat from dead tuples is a real operational concern, whereas InnoDB's undo-based approach handles reclamation more aggressively via purge threads.
Q2: How does Postgres recover quickly from a crash on a large database?
Crash recovery time is bounded by the amount of WAL generated since the last checkpoint, not by database size. With checkpoint_timeout = 5min and max_wal_size = 1GB, at most 1GB of WAL needs to be replayed after a crash. Reading 1GB of sequential WAL is fast, typically under 10 seconds on NVMe, plus the time to write each modified page to disk. To minimize recovery time, keep max_wal_size reasonable and ensure checkpoints are completing on schedule. The WARNING: checkpoints are occurring too frequently message in logs is your signal that checkpoint intervals are too short.
Q3: Why does VACUUM not shrink the table file?
VACUUM reclaims space within existing pages by marking dead tuple slots as available for future inserts. Shrinking the physical file would require moving all live tuples to the front of the file and truncating the end, which is an O(table_size) rewrite operation requiring an exclusive lock. Regular VACUUM is designed to run concurrently with normal operations without blocking reads or writes. VACUUM FULL does the rewrite and does shrink the file, but takes an ACCESS EXCLUSIVE lock. The better long-term solution is preventing bloat through aggressive autovacuum tuning rather than running VACUUM FULL reactively.
Q4: What is a replication slot and why can it fill up disk?
A replication slot is a server-side bookmark tracking how much WAL a subscriber has consumed. As long as the slot exists, Postgres retains all WAL segments after that bookmark, even if the subscriber disconnects. This guarantees the subscriber will not miss any WAL when it reconnects. The danger: if a subscriber goes permanently offline and the slot is not dropped, WAL accumulates indefinitely. On a busy database generating 10GB/hour of WAL, a slot 24 hours behind retains 240GB+ of WAL files. Always monitor pg_replication_slots and alert on inactive slots immediately.
Q5: How can I read WAL in human-readable form?
The pg_waldump utility reads WAL segment files from pg_wal/ and prints each record in human-readable format: LSN, resource manager, record type, relation OID, block number, and XID. For logical-level row changes (actual column values), use logical decoding via pg_logical_slot_get_changes() with the test_decoding or wal2json plugin. These tools are invaluable for diagnosing replication issues, auditing what a long-running transaction was doing, or understanding exactly what a failed migration wrote to disk.
Q6: How does Postgres MVCC handle SELECT FOR UPDATE?
SELECT FOR UPDATE acquires a row-level lock on the selected rows, blocking other transactions from acquiring conflicting row locks until the transaction commits. Unlike ordinary SELECTs, which are purely version-based and non-blocking, FOR UPDATE follows the current version of rows and waits on lock queues. This is necessary for optimistic locking patterns and preventing lost updates. If a row is locked by another transaction, FOR UPDATE waits unless SKIP LOCKED (skip locked rows entirely) or NOWAIT (raise an error immediately) is specified.
Q7: What happens to WAL during pg_dump?
pg_dump runs inside a single REPEATABLE READ transaction. It takes a consistent snapshot at the start and reads all tables in that snapshot, producing a logically consistent backup. Normal WAL continues to be generated by other transactions during the dump. For a physical backup, use pg_basebackup, which copies data files while continuously archiving WAL, producing a consistent backup that can be restored and replayed to any point in time. pg_dump provides logical portability (restore to different versions, partial restore), while pg_basebackup provides fast PITR recovery.
Q8: How does Postgres MVCC compare to CockroachDB?
CockroachDB also uses MVCC but stores row versions in RocksDB (an LSM-tree key-value store) keyed by (key, timestamp). Old versions are compacted away by RocksDB background compaction rather than a separate VACUUM process. This eliminates table bloat but introduces compaction write amplification. CockroachDB uses hybrid logical clocks for transaction timestamps rather than integer XIDs, enabling distributed MVCC across nodes without a central XID counter. The core visibility principle is identical to Postgres, but the implementation and operational characteristics differ significantly.
Written by Professor Pixel · CodingPancake · Database Internals Series