PostgreSQL Internals: MVCC, VACUUM and WAL

Understanding these three concepts explains a lot of PostgreSQL's behavior that otherwise looks mysterious — like table "bloat" or why an old row briefly still exists after an UPDATE.

MVCC — Multi-Version Concurrency Control

Instead of locking a row for every read, PostgreSQL keeps multiple versions of a row and shows each transaction the version that was current when that transaction started. This is exactly why readers never block writers, and writers never block readers, in PostgreSQL.

• An UPDATE doesn't overwrite a row in place — it inserts a new version and marks the old one as expired (invisible to future transactions).
• A DELETE doesn't immediately erase a row — it just marks the version as expired.
• Old, expired row versions are called dead tuples and are physically removed later, by VACUUM.

This is also the mechanism behind the isolation levels covered in an earlier lesson — "Repeatable Read" is really "see the row versions as they were when this transaction began."

Why VACUUM Is Necessary

Because UPDATE/DELETE leave dead tuples behind instead of removing them immediately, a table can accumulate a lot of unused, "bloated" space over time if nothing cleans it up.

VACUUM employees;        -- reclaims space from dead tuples for reuse
VACUUM ANALYZE employees; -- also refreshes planner statistics (see the Query Optimization lesson)
VACUUM FULL employees;    -- rewrites the table to shrink it on disk (locks the table — use sparingly, off-hours)

autovacuum

In practice, you rarely need to run VACUUM manually — PostgreSQL's autovacuum background process does this automatically, triggered once a table accumulates enough dead tuples. It's on by default and should almost always stay that way.

SHOW autovacuum;  -- 'on' by default

If a table is heavily written/deleted from and still seems to bloat, tuning autovacuum's thresholds for that specific table (rather than disabling it) is the right fix.

The Write-Ahead Log (WAL)

Before any change is applied to the actual data files, PostgreSQL first writes a record of it to the WAL — a sequential, append-only log. If the server crashes mid-write, it replays the WAL on restart to recover to a consistent state.

• This is what gives PostgreSQL its durability guarantee (the "D" in ACID) — a committed transaction survives a crash.
• WAL files are also the foundation for replication (streaming the WAL to a standby server) and point-in-time recovery backups.

Common Errors & Troubleshooting

• Table size growing much larger than the data seems to justify ("bloat") — this usually means dead tuples are accumulating faster than autovacuum is cleaning them up; check pg_stat_user_tables for the last autovacuum time, and consider tuning its thresholds for that table.
• VACUUM FULL locking the table for a long time — unlike a regular VACUUM, FULL rewrites the entire table and holds an exclusive lock the whole time; run it during low-traffic windows, and only when regular VACUUM genuinely isn't reclaiming enough space.
• Warnings about "wraparound" in server logs — a very old, long-neglected database can approach transaction ID wraparound; this is what autovacuum's more aggressive "wraparound protection" runs are for — don't disable autovacuum, and investigate immediately if you see these warnings.

Key Takeaways

• MVCC is why PostgreSQL readers and writers don't block each other — every transaction sees a consistent snapshot of row versions.
• UPDATE/DELETE leave dead tuples behind; VACUUM (usually via autovacuum, automatically) reclaims that space.
• The Write-Ahead Log is what makes committed transactions durable across a crash, and underpins replication and point-in-time recovery.