MVCC and the WAL: Why Postgres Behaves the Way It Does

Every confusing Postgres behavior — bloat, vacuum, "why did my UPDATE take so long," transaction ID wraparound — is a consequence of two design decisions: rows are never updated in place (MVCC), and every change hits the log before it hits the table (WAL). Understand those two and the rest of Postgres makes sense.

Updates are inserts plus a mark

UPDATE users SET email = '[email protected]' WHERE id = 42;

Postgres doesn't overwrite the row. It writes a new version of the row and marks the old version's xmax — "no longer visible after transaction X." Readers whose transaction started before that still see the old version; new readers see the new one. That's MVCC: readers never block writers, writers never block readers, and each transaction sees a consistent snapshot.

The cost: the old row version is a dead tuple — dead weight on disk until vacuum removes it. Update-heavy workloads generate dead tuples continuously, which is why autovacuum exists and why "my table is 40% dead rows" is a normal ops conversation.

The WAL is the source of truth

Before a change touches the data files, it's appended to the write-ahead log — a sequential, fsynced stream of "what changed." On crash, Postgres replays the WAL from the last checkpoint and reconstructs a consistent state. The WAL also powers everything else you care about: streaming replication is literally streaming WAL, and point-in-time recovery is replaying WAL to a target timestamp.

Practical consequences

• DELETE doesn't shrink the table — vacuum reclaims space; VACUUM FULL rewrites the table (locks it; pg_repack avoids that).
• A long-running transaction blocks vacuum — dead tuples pile up behind it. Kill idle-in-transaction connections.
• TRUNCATE is instant because it drops the whole storage file — it's not a delete at all.

Field notes

• pg_stat_user_tables shows n_dead_tup — when dead tuples outnumber live ones, autovacuum settings need attention.
• SELECT xmin, xmax FROM ... shows row version stamps — useful for convincing yourself MVCC is real.