PostgreSQL MVCC, Old Versions and Vacuum

PostgreSQL lets readers and writers work at the same time without blocking each other. A report can read a table for ten minutes while thousands of updates land on it, and the report still sees a consistent snapshot. The mechanism behind that is multiversion concurrency control (MVCC): instead of overwriting a row, an update writes a new version and leaves the old one for anyone who still needs it. The bill for that design is cleanup, and that’s what VACUUM is for.

Interviewers ask about this because it explains a family of production problems: tables that grow even though row counts are flat, queries that get slower over weeks, and the infamous transaction ID wraparound warning. Everything below was run on PostgreSQL 16.15, with autovacuum switched off on the demo tables so the effects stay visible.

An UPDATE is an insert plus a mark

Every row has hidden system columns. xmin is the id of the transaction that created that version; xmax is the id of the transaction that deleted or replaced it (0 if none); ctid is its physical location as (page, item).

CREATE TABLE accounts (id int PRIMARY KEY, balance numeric NOT NULL)
  WITH (autovacuum_enabled = off);
INSERT INTO accounts VALUES (1, 100), (2, 50);
SELECT ctid, xmin, xmax, * FROM accounts;

 ctid  | xmin | xmax | id | balance
-------+------+------+----+---------
 (0,1) |  940 |    0 |  1 |     100
 (0,2) |  940 |    0 |  2 |      50

Now update account 1:

UPDATE accounts SET balance = balance + 10 WHERE id = 1;
SELECT ctid, xmin, xmax, * FROM accounts;

 ctid  | xmin | xmax | id | balance
-------+------+------+----+---------
 (0,2) |  940 |    0 |  2 |      50
 (0,3) |  941 |    0 |  1 |     110

Account 1 moved from slot 1 to slot 3 and has a new xmin. A normal SELECT only shows versions visible to you, so the old one is hidden. The pageinspect extension shows what’s really on the page:

CREATE EXTENSION pageinspect;
SELECT lp, t_ctid, t_xmin, t_xmax FROM heap_page_items(get_raw_page('accounts', 0));

 lp | t_ctid | t_xmin | t_xmax
----+--------+--------+--------
  1 | (0,3)  |    940 |    941
  2 | (0,2)  |    940 |      0
  3 | (0,3)  |    941 |      0

Slot 1 still holds balance 100, stamped xmax = 941 and pointing forward to (0,3). A transaction whose snapshot started before 941 committed will still read 100 from slot 1. That’s how the long report keeps its consistent view: nothing it needs is overwritten. A DELETE works the same way, setting xmax without writing a new version.

Dead tuples and table growth

Once no running transaction can see the old version, it’s dead: still taking space, useful to nobody. Watch what repeated full-table updates do to a 100,000-row table:

CREATE TABLE counters (id int PRIMARY KEY, hits bigint NOT NULL, pad text)
  WITH (autovacuum_enabled = off);
INSERT INTO counters SELECT g, 0, repeat('x', 100) FROM generate_series(1, 100000) g;
-- 14 MB
UPDATE counters SET hits = hits + 1;   -- three times
-- 57 MB
SELECT n_live_tup, n_dead_tup FROM pg_stat_user_tables WHERE relname = 'counters';

 n_live_tup | n_dead_tup
------------+------------
     100000 |     299991

The table has the same 100,000 live rows and is four times larger. Every sequential scan now reads 57 MB to find 14 MB of data, and indexes have grown too, because each new version needs index entries unless the update qualifies as HOT (more below). This is what people mean by bloat.

What VACUUM does and doesn’t do

VACUUM finds versions that are dead to every possible snapshot, removes their index entries, and marks their space in each page as free for future inserts and updates. It also updates the visibility map (which makes index-only scans possible) and freezes old rows (below). It runs alongside normal reads and writes.

What it does not do, in general, is give space back to the operating system. After vacuuming counters, the file was still 71 MB (it had grown further during the experiment in the next section). Then one more full-table update ran, and the size stayed at 71 MB: the new versions went into the freed space instead of extending the file. That’s the normal steady state. A table that gets vacuumed regularly stabilises at its live size plus some working slack.

VACUUM FULL rewrites the table into a new compact file: counters went from 71 MB to 14 MB. But it takes an ACCESS EXCLUSIVE lock for the duration, blocking even reads, and needs disk space for the copy. It’s a repair tool for a table that already bloated, not maintenance. Extensions like pg_repack do the same rewrite with only brief locks, which is what most teams use on live systems.

Autovacuum is the background process that runs VACUUM and ANALYZE for you. By default it vacuums a table when dead tuples exceed 50 plus 20% of the table (autovacuum_vacuum_threshold and autovacuum_vacuum_scale_factor). On a 500-million-row table, 20% is 100 million dead rows before cleanup starts, so large, hot tables usually get per-table settings such as ALTER TABLE events SET (autovacuum_vacuum_scale_factor = 0.01). Turning autovacuum off globally because “it uses I/O” is one of the classic ways to end up with a production incident months later.

The transaction that blocks cleanup

VACUUM can only remove a version if no snapshot could still see it. One open transaction anywhere in the database holds that line back. In this run, a separate session started a Repeatable Read transaction, read a different table, and sat there. Meanwhile counters got another full update, then a manual vacuum:

INFO:  finished vacuuming "postgres.public.counters": index scans: 1
pages: 0 removed, 9090 remain, 9090 scanned (100.00% of total)
tuples: 9 removed, 200000 remain, 100000 are dead but not yet removable
removable cutoff: 949, which was 1 XIDs old when operation ended

100000 are dead but not yet removable is the line to look for. The idle session never touched counters, but its snapshot could in principle read any table, so PostgreSQL has to keep the versions. Once that session committed, the next vacuum reported 100000 removed ... 0 are dead but not yet removable.

In production the culprit is usually an application connection left idle in transaction (a BEGIN with a forgotten COMMIT, often around an HTTP call), a multi-hour analytics query, an abandoned prepared transaction, or a replication slot or hot-standby feedback from a replica running long queries. Find it with:

SELECT pid, state, xact_start, backend_xmin, left(query, 60)
FROM pg_stat_activity
WHERE backend_xmin IS NOT NULL
ORDER BY xact_start;

and set idle_in_transaction_session_timeout so forgotten transactions get terminated instead of silently bloating everything.

Freezing and wraparound

Transaction ids are 32-bit and wrap around after about 4 billion. To keep comparisons meaningful, PostgreSQL treats ids as circular: roughly 2 billion in the past and 2 billion in the future. A row whose xmin is very old must be frozen (marked as visible to everyone) before it drifts into the “future” half and appears to vanish. Vacuum does this, and autovacuum forces an anti-wraparound vacuum on a table when its oldest unfrozen id gets too old (autovacuum_freeze_max_age, 200 million by default). If freezing falls far enough behind, PostgreSQL warns, and in the end refuses to assign new transaction ids until someone vacuums. The usual root cause is the same as for bloat: something blocking vacuum or vacuum not keeping up.

HOT updates and fillfactor

If an update changes no indexed column and the new version fits on the same page, PostgreSQL does a heap-only tuple (HOT) update: no new index entries, and the old version can be pruned during normal page access without waiting for VACUUM. Only 156 of the 500,000 updates in the counters demo were HOT, because its pages were packed full by the initial insert, leaving no room for new versions on the same page. To help HOT, avoid indexing columns that change constantly (a last_seen_at index on a hot table costs every update), and consider fillfactor = 90 or lower on update-heavy tables so pages keep room for new versions.

Interview practice: a table that keeps growing

A sessions table has about 2 million live rows, each updated every few minutes with a new last_seen_at. Disk usage for it has grown from 1 GB to 40 GB over a month, and queries on it are slowing down. Autovacuum is enabled. What do you check, and what do you change?

Answer

First, confirm it’s bloat: n_live_tup and n_dead_tup in pg_stat_user_tables, plus last_autovacuum. If dead tuples are huge and autovacuum runs but doesn’t help, run VACUUM (VERBOSE) sessions and look for “dead but not yet removable”. That points at something holding an old snapshot, so check pg_stat_activity for old xact_start or idle in transaction sessions, pg_prepared_xacts, and pg_replication_slots for inactive slots. Fix the holder (and set idle_in_transaction_session_timeout), and the next vacuum can clean up.

If nothing blocks it and autovacuum simply can’t keep up, lower autovacuum_vacuum_scale_factor for this table, raise its cost limit so each run works faster, and check whether last_seen_at is indexed. If it is, every update writes new index entries and can’t be HOT; dropping that index and setting a lower fillfactor lets most updates be HOT. Once the cause is fixed, plain vacuum stops further growth but won’t shrink the file, so I’d reclaim the 40 GB with pg_repack (or VACUUM FULL in a maintenance window). Finally, I’d question the design: writing last_seen_at on every request is often better batched, or kept in Redis and flushed periodically.

Keep going

• SQL and PostgreSQL interview questions
• SQL and PostgreSQL MCQs
• SQL transaction isolation and observable anomalies
• PostgreSQL row locks and concurrent updates
• Official docs: Routine vacuuming