MVCC, dead tuples and VACUUM

Transactions, isolation and locking Senior Python 3.13+ PostgreSQL 17+ Reviewed By Best Latest Apps

Predict the output

What does the SELECT print after one UPDATE in PostgreSQL?

SQL · PostgreSQL 17
CREATE TABLE t (id int PRIMARY KEY, v int);
INSERT INTO t VALUES (1, 10);
SELECT xmin = xmin FROM t;   -- warm-up
UPDATE t SET v = 11 WHERE id = 1;
SELECT count(*) AS live, v FROM t GROUP BY v;

Options
Hint 1 of 1

Which versions does your snapshot see?

Choose an answer, then open Show answer below to check it.

Show answer

Answer: A t then 1|11

Correct. The warm-up prints t; the old version still exists on disk as a dead tuple, but no snapshot sees it, so one live row with v = 11.

An UPDATE in PostgreSQL writes a new row version and marks the old one dead. Queries only see versions visible to their snapshot, so there is one live row with v = 11 until VACUUM reclaims the dead one.

Why not the others?
  • B t then 2|11. Dead versions are invisible to queries; count(*) counts visible rows only.
  • C t then 1|10. The update committed (autocommit), so the new version is visible.
  • D An error: xmin can't be compared. System columns can be selected and compared like ordinary columns.
Result on PostgreSQL 17 (psql)
livev
111

(1 row)

30-second answer

PostgreSQL never updates a row in place. An UPDATE writes a new row version and marks the old one with the transaction id that replaced it; a DELETE just marks it. Each transaction sees the versions its snapshot allows, so readers never block writers. Old versions nobody can see any more are dead tuples; VACUUM, usually autovacuum, makes their space reusable and freezes old transaction ids before the 32-bit counter wraps around.

Practise saying it · 40 s. No recording, just a timer

Explain MVCC and VACUUM.

Say your answer out loud, then check which of these points you covered:

  • UPDATE writes a new version
  • xmin/xmax visibility
  • Readers don't block writers
  • VACUUM reclaims dead tuples, freezes xids
  • Long transactions cause bloat

Deep dive

Every row version carries xmin (created by) and xmax (deleted or replaced by).

A long-running or idle-in-transaction session holds back the oldest visible snapshot, so VACUUM can't remove anything newer: tables and indexes bloat.

Plain VACUUM doesn't shrink the file; VACUUM FULL rewrites the table under an ACCESS EXCLUSIVE lock.

HOT updates (heap-only tuples) avoid touching indexes when no indexed column changes and the page has room.

Common mistakes

  • Disabling autovacuum on a busy table to 'save I/O'.
  • Running VACUUM FULL on a production table during traffic.
  • Ignoring sessions stuck 'idle in transaction'.

Senior take & follow-ups

Explain versions, visibility and cleanup, and name the operational trap: long transactions block cleanup.

Follow-up questions

Think of your answer first, then open the hint.

  1. What happens if freezing falls behind for too long?

    Show hint

    PostgreSQL eventually refuses new transactions to prevent transaction id wraparound.

  2. How do you see bloat candidates?

    Show hint

    pg_stat_user_tables.n_dead_tup and last autovacuum times.

Related questions

Suggest a fix if something here is wrong or out of date.