TopolearnMVCC explains table bloat, slow counts, vacuum load and the cost of long-running transactions.
PostgreSQL implements isolation with multi-version concurrency control (MVCC). It never overwrites a row in place. An UPDATE writes a complete new row version (a tuple) and marks the old one as expired; a DELETE only marks it. Each tuple carries hidden system columns: xmin, the id of the transaction that created it, and xmax, the one that deleted or replaced it.
Every statement (or, at higher isolation levels, every transaction) runs against a snapshot: the set of transactions committed at the moment it was taken. A tuple is visible if its creator had committed by then and its deleter had not. Consequences:
- Readers never block writers, and writers never block readers. A long report reads consistent data while updates continue.
- Two writers on the same row still conflict; the second waits for the first to finish.
SELECT count(*)must check visibility, so it cannot simply read a stored counter.
Expired tuples that no snapshot can see any more are dead tuples. VACUUM removes them and makes the space reusable (it rarely shrinks the file; VACUUM FULL rewrites the table but locks it exclusively). It also updates the visibility map that index-only scans depend on, and freezes old transaction ids to prevent transaction-id wraparound, which would otherwise eventually force the database to stop accepting writes. Autovacuum does all this in the background, triggered by the number of changed rows.
The classic way to break it is a transaction left open for hours, including an abandoned "idle in transaction" session or a stale replication slot. As long as an old snapshot might need old versions, vacuum cannot remove them, and frequently updated tables bloat. Watch n_dead_tup and pg_stat_activity.xact_start.
HOT (heap-only tuple) updates soften the write cost: if no indexed column changes and the page has room, the new version goes on the same page and indexes need no new entries.
Example
SELECT xmin, xmax, ctid, id, status FROM orders WHERE id = 102;
-- xmin | xmax | ctid | id | status
-- 812 | 0 | (0,2) | 102 | pending
UPDATE orders SET status = 'shipped' WHERE id = 102;
SELECT xmin, xmax, ctid, id, status FROM orders WHERE id = 102;
-- 815 | 0 | (0,6) | 102 | shipped <- a new tuple at a new location
-- the old tuple at (0,2) is now dead, waiting for VACUUM
SELECT relname, n_live_tup, n_dead_tup, last_autovacuum
FROM pg_stat_user_tables WHERE relname = 'orders';
-- Who is holding back cleanup?
SELECT pid, state, xact_start, left(query, 40)
FROM pg_stat_activity
WHERE xact_start < now() - interval '10 minutes'
ORDER BY xact_start;
VACUUM (VERBOSE, ANALYZE) orders;Remember
- UPDATE writes a new row version; DELETE only marks the old one expired
- Snapshots decide visibility: readers and writers do not block each other
- VACUUM removes dead tuples, maintains the visibility map and prevents xid wraparound
- Long-open transactions stop cleanup and cause bloat
Watch out for
- Disabling autovacuum on a busy table to 'save resources', leading to bloat and eventually wraparound emergencies.