Day 91: MVCC internals: tuple versions, bloat
MVCC: how Postgres avoids readers blocking writers
Multi-Version Concurrency Control means every row can have multiple physical versions (tuples) simultaneously — an UPDATE doesn't overwrite in place, it inserts a new tuple version and marks the old one as expired (but not yet deleted). Each transaction sees only the tuple versions valid as of its own snapshot, so readers never block writers and writers never block readers.
Row 1, version A: created by txn 100, expired by txn 105
Row 1, version B: created by txn 105 (current)
A transaction that started before txn 105 committed still sees version A — its own consistent snapshot.
A transaction starting after sees version B.Why long transactions hurt
As long as ANY transaction is still running with an old snapshot, Postgres cannot vacuum away the old tuple versions it might still need to see — a single long-running transaction (an idle connection left open in a transaction, a slow analytics query) can single-handedly prevent vacuum from reclaiming bloat across the whole database, even on unrelated tables.
Key terms
- MVCC
- Multiple physical row versions coexisting so readers and writers never block each other.
- Tuple
- A single physical version of a row's data.
A single analytics query has been running for 6 hours against a snapshot from that long ago. What's the operational risk?
