sdlc.dev

SQL and PostgreSQL path · Many writers at once · Topic 9 of 13

MVCC: row versions, dead tuples and VACUUM

Not started

Multiversion concurrency control (MVCC) is why readers in PostgreSQL never wait for writers and writers never wait for readers. Instead of locking a row while it changes, PostgreSQL keeps several versions of it. Each transaction's snapshot decides which version it sees. The price is that old versions pile up in the table until something removes them, and that something is VACUUM.

Why it matters: Bloat, slow scans after mass updates and the dreaded wraparound shutdown all come from how PostgreSQL keeps old row versions.

  1. 1An update is an insert plus a mark

    PostgreSQL never changes a row in place; it writes a new version and marks the old one as deleted by your transaction.

  2. 2Snapshots pick a version

    Each row version records which transaction created it (xmin) and deleted it (xmax); your snapshot decides which versions you see.

  3. 3Dead tuples wait for VACUUM

    A version no snapshot can see any more is dead; VACUUM makes its space reusable, and autovacuum does this in the background.

  4. 4Long transactions hold back cleanup

    An open transaction keeps every version it might still need, so one idle session can make tables bloat.

1

How it works

The explanation, with examples you can run.

Watching a row get a new version

Every row version carries hidden system columns: xmin, the transaction that created it; xmax, the transaction that deleted or replaced it (0 if none); and ctid, its physical position (page, item).

SELECT ctid, xmin, xmax, * FROM accounts WHERE id = 1;--  ctid  | xmin | xmax | id | owner | balance_cents-- (0,1)  |  875 |    0 |  1 | ana   |         10000 UPDATE accounts SET balance_cents = balance_cents - 100 WHERE id = 1; SELECT ctid, xmin, xmax, * FROM accounts WHERE id = 1;-- (0,3)  |  876 |    0 |  1 | ana   |          9900

The row moved from item 1 to item 3 of page 0 and was created by transaction 876. The old version at (0,1) is still in the page, with xmax = 876. Any snapshot taken before 876 committed still sees 10000. Once no snapshot can see the old version, it is a dead tuple:

SELECT n_live_tup, n_dead_tup FROM pg_stat_user_tables WHERE relname = 'accounts';--  n_live_tup | n_dead_tup--           2 |          1

VACUUM

VACUUM scans the table, removes dead versions from the table and its indexes, and records the freed space in the free space map so new rows can reuse it. It runs alongside normal reads and writes. It usually does not give space back to the operating system (only empty pages at the very end of the file). VACUUM FULL rewrites the whole table compactly, but holds an ACCESS EXCLUSIVE lock while it does, which blocks even reads.

VACUUM (VERBOSE) accounts;INFO:  finished vacuuming "shop.public.accounts": index scans: 0tuples: 1 removed, 2 remain, 0 are dead but not yet removable

VACUUM also sets bits in the visibility map (which index-only scans rely on) and freezes old rows (below).

Autovacuum

The autovacuum launcher runs VACUUM and ANALYZE on tables that have changed enough. With the defaults, a table is vacuumed when its dead tuples exceed 50 plus 20% of its rows (autovacuum_vacuum_threshold, autovacuum_vacuum_scale_factor). On a 100-million-row table, that is 20 million dead rows before it starts, so large, busy tables often get a lower per-table scale factor:

ALTER TABLE orders SET (autovacuum_vacuum_scale_factor = 0.02);

What stops VACUUM

VACUUM can only remove versions that are older than every snapshot still open. A transaction left open for hours (often a session idle in transaction, or a long report) pins them all. VACUUM VERBOSE reports them as "dead but not yet removable". Find the culprit in pg_stat_activity by xact_start, and set idle_in_transaction_session_timeout to end abandoned sessions.

Wraparound

Transaction ids are 32-bit counters, so they wrap around after about four billion. To keep old rows visible, VACUUM freezes them, marking them as visible to everyone regardless of id. If freezing falls too far behind, PostgreSQL warns and, as a last resort, stops assigning new transaction ids until a VACUUM catches up. Healthy autovacuum prevents this; monitor age(datfrozenxid) in pg_database to be sure.

2

Check yourself

3 questions. Get them all right to finish this topic.

  1. 1.You UPDATE every row of a 10 GB table once. Roughly how big is the table afterwards, before VACUUM?
  2. 2.Autovacuum runs, but n_dead_tup keeps growing and VACUUM VERBOSE reports many tuples that are dead but not yet removable. What is the likely cause?
  3. 3.What is the difference between VACUUM and VACUUM FULL?

0 of 3 right so far. Get all three to finish this topic.

Go deeper