Why PostgreSQL Needs Multiple Row Versions
MVCC (Multi-Version Concurrency Control) is the mechanism that lets PostgreSQL readers never block writers and writers never block readers. Instead of overwriting a row in place, an UPDATE creates a brand-new physical row version and marks the old version as superseded, while a DELETE simply marks a row as no-longer-visible rather than physically removing it immediately. Every row carries two hidden system columns, xmin and xmax, storing the transaction ID that created it and the transaction ID that deleted or replaced it (if any); a transaction's snapshot then decides, row version by row version, which ones are visible to it based on those transaction IDs.
Cricket analogy: MVCC is like a scorecard app that never erases a previous over's figures when a correction is made — it appends a corrected entry with a new timestamp, so anyone viewing the earlier timestamp still sees the original figures untouched.
Snapshots and Visibility Rules
When a transaction takes a snapshot, PostgreSQL records the set of transaction IDs that were still in-progress at that moment; a row version is visible to that snapshot only if its creating transaction committed before the snapshot was taken and its deleting transaction (if any) either doesn't exist or hadn't committed yet at snapshot time. This is why Repeatable Read transactions can run for a long time and consistently ignore all changes made after they started — they're comparing every row's xmin/xmax against a fixed list, not querying 'live' state. The cost of this design is that dead row versions ('dead tuples') accumulate on disk and must eventually be reclaimed, which is VACUUM's job.
Cricket analogy: A snapshot is like freezing the list of players who had already reached the crease before a rain delay — any batter who comes in after play resumes doesn't count toward the pre-delay lineup you're evaluating.
Because UPDATE and DELETE don't remove data immediately, an UPDATE-heavy table can bloat: each UPDATE leaves behind a dead tuple that still occupies disk pages and still gets scanned by sequential scans until VACUUM removes it. PostgreSQL also has a special optimization called HOT (Heap-Only Tuple) updates — if an UPDATE doesn't change any indexed column and there's free space in the same page, the new row version is chained to the old one within the page without needing new index entries, which is both faster and produces less bloat than a normal update.
Cricket analogy: Table bloat is like a scorebook that never tears out crossed-out entries — the physical notebook keeps getting thicker with every correction, even though only the latest entry per over actually matters to the current score.
-- Inspect hidden MVCC system columns
SELECT xmin, xmax, ctid, id, balance
FROM accounts
WHERE id = 1;
-- A HOT update: no indexed column changes, stays in the same page
UPDATE accounts SET last_login = now() WHERE id = 1; -- last_login not indexed
-- Check bloat and dead tuple counts for a table
SELECT relname, n_live_tup, n_dead_tup,
round(n_dead_tup::numeric / NULLIF(n_live_tup + n_dead_tup, 0) * 100, 1) AS dead_pct
FROM pg_stat_user_tables
WHERE relname = 'accounts';Because MVCC never overwrites rows in place, PostgreSQL readers using any isolation level never block on writers, and writers never block on plain readers — only writer-vs-writer conflicts on the same row require waiting or locking.
A long-running Repeatable Read or Serializable transaction holds its snapshot open for its entire duration, which prevents VACUUM from reclaiming dead tuples that are newer than that snapshot — a forgotten idle transaction is one of the most common causes of runaway table bloat.
- MVCC stores multiple physical versions of a row rather than updating in place, using xmin/xmax to track visibility.
- A transaction's snapshot determines which row versions it can see, based on transaction IDs that committed before the snapshot.
- Readers never block writers and writers never block readers under MVCC — only conflicting writes require locks.
- UPDATE and DELETE leave behind dead tuples that must be reclaimed by VACUUM.
- HOT updates avoid touching indexes and reduce bloat when no indexed column changes and page space allows.
- Long-running open transactions prevent VACUUM from cleaning up dead tuples, causing bloat.
- pg_stat_user_tables exposes live/dead tuple counts useful for monitoring bloat.
Practice what you learned
1. What do the xmin and xmax system columns store?
2. Under MVCC, what happens when a reader and a writer access the same row concurrently?
3. What is required for a HOT (Heap-Only Tuple) update to occur?
4. What commonly causes runaway table bloat in a busy OLTP table?
5. Which system view shows live and dead tuple counts per table?
Was this page helpful?
You May Also Like
ACID and Isolation Levels
How PostgreSQL guarantees Atomicity, Consistency, Isolation, and Durability, and how the four SQL isolation levels trade off correctness against concurrency.
VACUUM and Autovacuum
Why PostgreSQL needs VACUUM to reclaim dead tuples and prevent transaction ID wraparound, and how the autovacuum daemon automates that maintenance.
Locking in PostgreSQL
The row-level and table-level lock modes PostgreSQL uses to coordinate concurrent writers, and how to inspect and reason about lock contention.