0
Challenge · 30Correct & Fast

MVCC, dead tuples, VACUUM & bloat

Perfused in ~60% of apps

A junior assumes UPDATE edits a row in place, like changing a cell in a spreadsheet. It does not. Every UPDATE writes a whole new copy of the row and leaves the old one lying on disk, marked dead. A post that gets liked and unliked a thousand times a day accumulates a thousand corpses nobody asked for -- and if cleanup falls behind, the table gets slower and bigger with no schema change to blame it on.

Core idea. Postgres never overwrites a row. It uses MVCC (Multi-Version Concurrency Control): an UPDATE inserts a brand-new tuple version and stamps the OLD version's xmax with the transaction that replaced it, leaving that old version physically in place but invisible going forward. Those old, invisible versions are dead tuples -- garbage that only VACUUM (run by you, or automatically by autovacuum) can reclaim. Skip VACUUM long enough and dead tuples pile up: this is bloat.
Playground, this isn't a screenshot
posts, id = 1 — physical tuple versions on disk0 dead tuples
LIVExmin=100xmax=—like_count = 5
UPDATE never overwrites a tuple. It writes a brand-new version (a fresh xmin), stamps the old one's xmax with the transaction that replaced it, and leaves it exactly where it was. Click Like a few times before ever running VACUUM and every old version just piles up — that pile is bloat, and it is why a table that gets liked constantly needs autovacuum keeping up with it, not occasionally.
↑ play a little first, then dig in