MVCC and Vacuum

MVCC (multi-version concurrency control) keeps several versions of a row so that readers and writers do not block each other. Each transaction sees a consistent snapshot of the data, while writers create new versions. The cost is dead row versions that must be cleaned up.

Example 1: the idea

Readers do not block writers, and writers do not block readers. Two writers on the same row still conflict: the second waits for the first to commit or roll back. See Locking and Deadlocks.

Example 2: xmin and xmax

Every row version has two hidden columns:

An UPDATE marks the old version with the updater's id in xmax and inserts a new version with the same id in xmin. A DELETE only sets xmax.

CREATE TABLE accounts (id int PRIMARY KEY, balance int);
INSERT INTO accounts VALUES (1, 100);
SELECT xmin::text::bigint AS created_by FROM accounts \gset

UPDATE accounts SET balance = 150 WHERE id = 1;
SELECT xmin::text::bigint > :created_by AS new_version_has_newer_xmin,
       xmax::text::bigint AS xmax,
       balance
FROM accounts;

 new_version_has_newer_xmin | xmax | balance 
----------------------------+------+---------
 t                          |    0 |     150
(1 row)

The live version has xmax = 0. Transaction ids are absolute numbers that differ between databases, so the demo compares them instead of printing them.

Example 3: two sessions, one snapshot

Session A uses REPEATABLE READ, which takes one snapshot at its first query. Session B updates the row in between. Real output from two psql sessions (transaction ids differ on each run):

-- A
BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT id, balance FROM accounts;

 id | balance 
----+---------
  1 |     100
(1 row)

-- B
BEGIN;
UPDATE accounts SET balance = 150 WHERE id = 1;
SELECT ctid, xmin, xmax, balance FROM accounts;

 ctid  | xmin | xmax | balance 
-------+------+------+---------
 (0,2) | 1480 |    0 |     150
(1 row)

B sees its own new version at ctid (0,2). Session A runs the same query while B is uncommitted, and again after B commits:

-- A (before and after B's COMMIT: same result)
SELECT ctid, xmin, xmax, balance FROM accounts;

 ctid  | xmin | xmax | balance 
-------+------+------+---------
 (0,1) | 1479 | 1480 |     100
(1 row)

A still sees the old version (0,1), whose xmax is set to B's transaction id but B was not in A's snapshot. A was never blocked. After A commits and takes a new snapshot, the query returns (0,2) | 1480 | 0 | 150. Under READ COMMITTED (the default), each statement takes a new snapshot, so A would see 150 after B commits. See Isolation Levels.

Example 4: snapshots and visibility rules

A snapshot records which transaction ids were committed at the moment it was taken. A row version is visible to a snapshot when both conditions hold:

1. xmin committed before the snapshot (or is the current transaction).
2. xmax is 0, or belongs to a transaction that is not committed or not in the snapshot (aborted, still running, or started later).

flowchart TD
    V[Row version] --> C1{"xmin committed<br/>and in snapshot?"}
    C1 -- no --> N[Invisible]
    C1 -- yes --> C2{"xmax set and<br/>committed in snapshot?"}
    C2 -- yes --> N
    C2 -- no --> Y[Visible]

Commit status is looked up in the pg_xact commit log; a rolled-back transaction leaves its versions in place, and they are simply invisible.

Example 5: dead tuples and bloat

A version that no snapshot can see any more is a dead tuple. Dead tuples occupy space until vacuum removes them. Space taken by dead tuples is called bloat. Updating every row doubles the table size:

CREATE TABLE bloat (id int PRIMARY KEY, v int) WITH (autovacuum_enabled = off);
INSERT INTO bloat SELECT g, 0 FROM generate_series(1, 10000) g;
SELECT pg_relation_size('bloat') / 8192 AS pages;

UPDATE bloat SET v = 1;
SELECT pg_relation_size('bloat') / 8192 AS pages;

 pages 
-------
    45
(1 row)

 pages 
-------
    89
(1 row)

Every UPDATE adds a row version. A table updated constantly without vacuum grows without limit, and scans must read the dead versions too.

Example 6: VACUUM and n_dead_tup

VACUUM marks dead tuples as reusable space and updates the free space map and visibility map. It does not block reads or writes. pg_stat_user_tables counts dead tuples.

SELECT pg_sleep(1.5) \gset
SELECT n_live_tup, n_dead_tup FROM pg_stat_user_tables WHERE relname = 'bloat';

VACUUM bloat;
SELECT pg_sleep(1.5) \gset
SELECT n_live_tup, n_dead_tup FROM pg_stat_user_tables WHERE relname = 'bloat';
SELECT pg_relation_size('bloat') / 8192 AS pages_after_vacuum;

 n_live_tup | n_dead_tup 
------------+------------
      10000 |      10000
(1 row)

 n_live_tup | n_dead_tup 
------------+------------
      10000 |          0
(1 row)

 pages_after_vacuum 
--------------------
                 89
(1 row)

After plain VACUUM the dead tuples are gone but the file keeps its size: the space is reused by later inserts and updates. Only trailing empty pages are returned to the operating system.

Example 7: VACUUM FULL and autovacuum

VACUUM FULL rewrites the table into a new file without dead space. It shrinks the file, but takes an ACCESS EXCLUSIVE lock, which blocks all reads and writes for its duration.

VACUUM FULL bloat;
SELECT pg_relation_size('bloat') / 8192 AS pages_after_vacuum_full;

 pages_after_vacuum_full 
-------------------------
                      45
(1 row)

The autovacuum daemon runs vacuum and analyze automatically. A table is vacuumed when dead tuples exceed autovacuum_vacuum_threshold + autovacuum_vacuum_scale_factor * rows.

SELECT name, setting
FROM pg_settings
WHERE name IN ('autovacuum', 'autovacuum_vacuum_threshold', 'autovacuum_vacuum_scale_factor')
ORDER BY name;

              name              | setting 
--------------------------------+---------
 autovacuum                     | on
 autovacuum_vacuum_scale_factor | 0.2
 autovacuum_vacuum_threshold    | 50
(3 rows)

That is 50 + 20% of the rows by default. On a table with 100 million rows, 20 million dead tuples accumulate before vacuum starts, so large tables usually get a lower per-table scale factor:

ALTER TABLE big_table SET (autovacuum_vacuum_scale_factor = 0.01);

Never disable autovacuum globally.

Example 8: HOT updates

A heap-only tuple (HOT) update happens when the new version fits in the same page and no indexed column changes. No new index entries are needed, and the old version can be pruned inside the page without a full vacuum. This is why a lower fillfactor helps update-heavy tables (see Storage and Pages).

CREATE TABLE hot (id int PRIMARY KEY, indexed_col int, plain_col int) WITH (autovacuum_enabled = off);
CREATE INDEX ON hot (indexed_col);
INSERT INTO hot SELECT g, g, 0 FROM generate_series(1, 100) g;

UPDATE hot SET plain_col = 1 WHERE id <= 10;
UPDATE hot SET indexed_col = indexed_col + 1000 WHERE id BETWEEN 11 AND 15;
SELECT pg_sleep(1.5) \gset

SELECT n_tup_upd AS updates, n_tup_hot_upd AS hot_updates
FROM pg_stat_user_tables WHERE relname = 'hot';

 updates | hot_updates 
---------+-------------
      15 |          10
(1 row)

The 10 updates of plain_col are HOT. The 5 updates of the indexed column are not, because each needs new index entries.

Example 9: long transactions hurt

Vacuum can remove a dead version only if no snapshot can still see it. An open transaction (or an idle-in-transaction session, or a lagging replica with hot_standby_feedback) pins the oldest snapshot, and vacuum cannot clean anything newer. Verified with two sessions on a 1000-row table where B updates every row:

-- A
BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT count(*) FROM t;
-- B
UPDATE t SET v = v + 1;
VACUUM (VERBOSE) t;

tuples: 0 removed, 2000 remain, 1000 are dead but not yet removable

-- A
COMMIT;
-- B
VACUUM (VERBOSE) t;

tuples: 1000 removed, 1000 remain, 0 are dead but not yet removable

Consequences of long transactions: bloat, slower queries, and wraparound risk. Find them with:

SELECT count(*) AS long_transactions
FROM pg_stat_activity
WHERE xact_start < now() - interval '1 hour';

 long_transactions 
-------------------
                 0
(1 row)

Example 10: transaction id wraparound

Transaction ids are 32-bit numbers, compared modulo 2^32: about 2 billion ids are "in the past" and 2 billion "in the future". Without action, an old row would eventually appear to be in the future and become invisible. Vacuum prevents this by freezing old row versions (marking them visible to all transactions).

SELECT name, setting
FROM pg_settings
WHERE name IN ('autovacuum_freeze_max_age', 'vacuum_freeze_min_age')
ORDER BY name;

           name            |  setting  
---------------------------+-----------
 autovacuum_freeze_max_age | 200000000
 vacuum_freeze_min_age     | 50000000
(2 rows)

When a table's oldest unfrozen id reaches autovacuum_freeze_max_age (200 million), autovacuum starts an aggressive vacuum even if autovacuum is off. If ids approach the 2 billion limit, PostgreSQL stops accepting write transactions until a vacuum completes. Monitor it with age(datfrozenxid) from pg_database.

Example 11: comparison with other engines

SQL Server uses locking by default; with READ_COMMITTED_SNAPSHOT or snapshot isolation it keeps versions in tempdb.

Summary

• MVCC gives each transaction a snapshot; readers and writers do not block each other, but two writers on one row do.
• UPDATE creates a new tuple version (xmin = updater, old xmax = updater); old versions become dead tuples and cause bloat.
• VACUUM reclaims dead space for reuse without locking; VACUUM FULL shrinks the file but locks the table.
• HOT updates avoid index writes when no indexed column changes and the page has room.
• Long transactions block cleanup; vacuum also freezes rows to prevent transaction id wraparound.