The MVCC lesson showed that UPDATE and DELETE never overwrite a row — they leave the old version behind, marked expired. Those dead versions still occupy space on disk until something cleans them up. That something is VACUUM, and this lesson is about the dead tuples it collects, the bloat they cause, and the autovacuum process that keeps it all in check.
The seed is one events table with 5,000 rows — a plain heap we can churn and measure.
SELECT count(*), pg_size_pretty(pg_table_size('events')) AS size FROM events;
Dead tuples: the debris of MVCC
Because Postgres keeps old row versions, every UPDATE writes a fresh version and leaves the previous one as a dead tuple — still on its page, invisible to new queries, taking up room. Update every row once and you've doubled the live rows with dead copies. Do it:
UPDATE events SET payload = payload + 1;
pg_stat_user_tables tracks this per table: n_live_tup is roughly the live rows, n_dead_tup the dead ones waiting to be reclaimed.
SELECT n_live_tup, n_dead_tup FROM pg_stat_user_tables WHERE relname = 'events';
You'll see something like this — 5,000 live rows shadowed by 5,000 dead ones (exact counts vary, and autovacuum may have already trimmed them):
n_live_tup | n_dead_tup
------------+------------
5000 | 5000
That is bloat: the table now occupies far more pages than its live data needs. Every sequential scan reads the dead tuples too, and indexes still point at them, so unchecked bloat quietly makes reads slower and the table bigger.
VACUUM reclaims space for reuse
VACUUM scans the table, finds dead tuples no transaction can still see, and marks their space free — available for future inserts and updates into the same table. Run it and watch the dead count drop to zero: