PostgreSQL Internals — MVCC, WAL, VACUUM

DatabasesSeniordbpostgresqlsql

The Question

Explain PostgreSQL's MVCC, WAL, and VACUUM.

What a Strong Answer Covers

• xmin/xmax as transaction IDs (NOT "min/max versions"). WAL for durability. VACUUM = dead tuples.

Senior-Level Answer

PostgreSQL's MVCC, WAL, and VACUUM form an interlocking system. Understanding how they interact is essential for diagnosing performance problems in production.

**MVCC (Multi-Version Concurrency Control)**

PostgreSQL never overwrites tuples in place. Instead, each `UPDATE` or `DELETE` creates a new version of the tuple and marks the old version as dead. Each tuple has hidden system columns: `xmin` (transaction ID that created it) and `xmax` (transaction ID that deleted/updated it, or 0 if live).

When a query runs, PostgreSQL determines its **snapshot** — the set of transaction IDs that are committed and visible at that point in time. A tuple is visible if `xmin` is committed and in the snapshot, and `xmax` is either 0 or not yet committed. This gives each transaction a consistent view of the data without taking read locks.

Consequences: readers never block writers, writers never block readers. This is PostgreSQL's concurrency strength. Isolation levels (Read Committed, Repeatable Read, Serializable) differ in when snapshots are taken.

**WAL (Write-Ahead Log)**

Before any data page change is written to disk, PostgreSQL writes a record to the WAL. This ensures that on crash, the WAL can be replayed to recover committed transactions that hadn't been flushed to the main data files. `fsync` on WAL commits guarantees durability (the D in ACID).

WAL also enables **streaming replication** — standby servers replay the WAL stream to stay in sync. `wal_level` controls how much information is logged (minimal, replica, logical). Logical replication uses logical decoding of the WAL to replicate data changes at a higher abstraction level.

**VACUUM**

Because MVCC leaves dead tuple versions on disk, PostgreSQL needs a process to reclaim that space. `VACUUM` scans tables for dead tuples (where `xmax` is a committed, no-longer-visible transaction) and marks their space as reusable. `VACUUM FULL` rewrites the table to disk, returning space to the OS — but it takes an exclusive lock.

`autovacuum` runs automatically based on the fraction of dead tuples (`autovacuum_vacuum_scale_factor`). **Transaction ID wraparound** is the critical reason VACUUM must not be disabled: PostgreSQL uses 32-bit transaction IDs. After ~2 billion transactions, IDs wrap around. VACUUM freezes old tuples (setting `xmin` to a special frozen transaction ID) to prevent this. Tables that aren't vacuumed will eventually trigger `autovacuum` in an emergency freeze mode, and if ignored, PostgreSQL will refuse writes to protect data integrity.

`pg_stat_user_tables` tracks `n_dead_tup` and `last_vacuum` — monitor these to catch tables where autovacuum isn't keeping up (common on high-write tables).

What Separates a 2/3 from a 3/3

Common Mistakes

• Saying PostgreSQL 'locks rows for reads' — MVCC specifically avoids this
• Describing VACUUM as equivalent to defragmentation — it marks space reusable, not necessarily returning it to OS (VACUUM FULL does)
• Not knowing about transaction ID wraparound — a critical operational concern
• Conflating WAL archiving with streaming replication without explaining the difference

Follow-Up Questions

• What is transaction ID wraparound and what happens if you ignore it? — After ~2B transactions, IDs wrap. Frozen tuples prevent this. Ignored: PostgreSQL enters shutdown mode and refuses writes to prevent data loss.
• Why does a high-write table sometimes have autovacuum not keeping up, and how do you fix it? — autovacuum_vacuum_scale_factor as a percentage doesn't work for huge tables. Set autovacuum_vacuum_cost_delay lower or use per-table storage parameters.
• How does MVCC enable Repeatable Read isolation? — A snapshot is taken at transaction start (not statement start as in Read Committed). All queries in the transaction see the same consistent view.
• What is the difference between WAL archiving and streaming replication? — Archiving copies completed WAL segment files (minutes of lag). Streaming replication sends WAL records in real-time as they're generated (seconds of lag).

Related Questions

• ACID
• Indexes
• Clustered vs Non-clustered
• Isolation Levels
• Normalization