MVCC (Multi-Version Concurrency Control)

MVCC allows readers and writers to operate concurrently without blocking each other by maintaining multiple versions of each row, with visibility determined by transaction snapshots.

When to Use

• Understanding why PostgreSQL does not block reads during writes
• Debugging table bloat or unexpected disk space growth
• Tuning autovacuum for high-churn tables
• Understanding

  xmin

  /

  xmax

  system columns in query results
• Diagnosing "transaction ID wraparound" warnings in PostgreSQL logs
• Investigating slow sequential scans caused by dead tuple accumulation

Instructions

Key Concepts

1. How MVCC Works

Every row in PostgreSQL has hidden system columns:

xmin

(the transaction ID that created the row version) and

xmax

(the transaction ID that deleted or updated it). An UPDATE does not modify a row in place -- it creates a new row version and marks the old one as dead by setting its

xmax

.

-- Observe MVCC in action
CREATE TABLE demo (id int, val text);
INSERT INTO demo VALUES (1, 'original');

SELECT xmin, xmax, * FROM demo;
--  xmin | xmax | id |   val
-- ------+------+----+----------
--   100 |    0 |  1 | original

2. Snapshot Visibility

A transaction takes a snapshot of active transaction IDs when it begins (or at each statement, depending on isolation level). A row version is visible if:

xmin

is from a committed transaction that committed before the snapshot, AND

xmax

is either zero (not deleted), from an aborted transaction, or from a transaction that committed after the snapshot.

3. Dead Tuples and Bloat

Old row versions are not immediately removed. They accumulate as "dead tuples." This causes table bloat -- wasted disk space that degrades sequential scan performance and wastes I/O.

-- Check dead tuple count
SELECT relname, n_live_tup, n_dead_tup,
       ROUND(n_dead_tup::numeric / NULLIF(n_live_tup, 0) * 100, 1) AS dead_pct
FROM pg_stat_user_tables
WHERE n_dead_tup > 1000
ORDER BY n_dead_tup DESC;

4. VACUUM

Reclaims space from dead tuples.

VACUUM

marks the space as reusable for future inserts (but does not return it to the OS).

VACUUM FULL

rewrites the entire table to compact it, but requires an ACCESS EXCLUSIVE lock.

-- Standard vacuum (non-blocking)
VACUUM VERBOSE demo;
-- INFO: "demo": removed 1500 dead row versions, 850 pages remain

-- Full vacuum (blocks all access -- avoid during business hours)
VACUUM FULL demo;

5. Transaction ID Wraparound

Transaction IDs are 32-bit unsigned integers (~4.2 billion). After approximately 2 billion transactions, wraparound can cause all data to appear to be "in the future" and become invisible. Autovacuum's freeze process prevents this by marking old tuples as "frozen" (visible to all future transactions).

Worked Example

Demonstrating MVCC tuple versioning:

-- Session A                           -- Session B
BEGIN;
INSERT INTO demo VALUES (2, 'v1');
COMMIT;

SELECT xmin, xmax, * FROM demo
  WHERE id = 2;
-- xmin=200, xmax=0, val='v1'

BEGIN;                                 BEGIN;
                                       UPDATE demo SET val = 'v2'
                                         WHERE id = 2;
                                       COMMIT;

-- Session A still sees 'v1' if using
-- Repeatable Read (snapshot frozen)

SELECT xmin, xmax, * FROM demo
  WHERE id = 2;
-- Under Read Committed: xmin=201, val='v2'
-- Under Repeatable Read: xmin=200, val='v1'
COMMIT;

-- Check dead tuples accumulated
SELECT n_dead_tup FROM pg_stat_user_tables
  WHERE relname = 'demo';
-- n_dead_tup = 1 (the old 'v1' version)

VACUUM VERBOSE demo;
-- Reclaims the dead tuple

Anti-Patterns

1. Disabling autovacuum. Dead tuples accumulate without bound, table bloat grows indefinitely, and eventually transaction ID wraparound makes all data invisible. Never disable autovacuum globally. If a specific table has issues, tune its settings rather than disabling.

2. Long-running transactions holding snapshots. A transaction that stays open for hours prevents VACUUM from reclaiming any tuples created after that transaction's snapshot -- even in other tables. Monitor

   pg_stat_activity

   for

   idle in transaction

   sessions.

3. High-churn UPDATE patterns without monitoring bloat. Tables receiving millions of updates per day can bloat to 10x their actual data size if autovacuum cannot keep pace with the default settings.

4. Using

   VACUUM FULL

   in production during business hours. Requires ACCESS EXCLUSIVE lock, blocking all queries on the table for the duration. Use

   pg_repack

   for online compaction instead.

PostgreSQL Specifics

Autovacuum thresholds: Vacuum triggers when dead tuples exceed

autovacuum_vacuum_threshold

(default 50) +

autovacuum_vacuum_scale_factor

(default 0.2) * table row count. For a 1 million row table, vacuum triggers at 200,050 dead tuples.

Per-table tuning for high-churn tables:

ALTER TABLE hot_table SET (
  autovacuum_vacuum_scale_factor = 0.01,  -- 1% instead of 20%
  autovacuum_vacuum_cost_delay = 2        -- 2ms instead of 20ms (faster vacuum)
);

Monitoring commands:

• SELECT n_dead_tup, last_vacuum, last_autovacuum FROM pg_stat_user_tables;

• SELECT age(relfrozenxid) FROM pg_class WHERE relname = 'tablename';

  -- distance to wraparound

• pg_bloat_check

  extension for comprehensive bloat detection

Details

Advanced Topics

HOT (Heap-Only Tuples): If an UPDATE does not change any indexed columns, PostgreSQL can chain the new version to the old one without updating indexes. This significantly reduces bloat and index maintenance overhead. The

fillfactor

storage parameter (default 100) can be lowered to leave room for HOT updates:

ALTER TABLE frequently_updated SET (fillfactor = 80);
-- 20% of each page reserved for HOT updates

Transaction ID wraparound prevention: Monitor

age(relfrozenxid)

for all tables. When it approaches 2 billion, PostgreSQL forces an aggressive autovacuum. If this emergency vacuum cannot complete (e.g., due to long-running transactions), the database shuts down to prevent data loss.

-- Find tables closest to wraparound
SELECT relname, age(relfrozenxid) AS xid_age
FROM pg_class
WHERE relkind = 'r'
ORDER BY age(relfrozenxid) DESC
LIMIT 10;

pg_repack

: Extension that performs online table compaction without ACCESS EXCLUSIVE lock (unlike

VACUUM FULL

). Safe for production use during business hours.

Engine Differences

MySQL InnoDB also uses MVCC but implements it differently. InnoDB stores undo information in a separate undo log (rollback segment) rather than keeping old row versions in the table heap. The current version is always in the table; older versions are reconstructed from undo records.

InnoDB's purge thread automatically cleans old undo records -- there is no equivalent of PostgreSQL's VACUUM. This means InnoDB does not suffer from table-level bloat in the same way. However, undo log bloat is possible with long-running transactions (the undo tablespace grows and is not reclaimed until the transaction completes).

InnoDB does not have transaction ID wraparound concerns because it uses 48-bit transaction IDs with a different visibility mechanism.

Real-World Case Studies

A high-traffic analytics platform had a 200GB event tracking table that grew to 1.8TB over three months. Investigation revealed massive bloat: the table received 5 million small updates per hour (status changes on event records), but autovacuum with default settings could not keep pace. Dead tuples accumulated faster than vacuum could remove them. Tuning

autovacuum_vacuum_scale_factor

to 0.01 and

autovacuum_vacuum_cost_delay

to 2ms for that specific table allowed vacuum to run more aggressively. After a one-time

pg_repack

to reclaim the existing bloat, the table stabilized at 230GB with ongoing bloat below 15%.

Source

• PostgreSQL MVCC Introduction
• PostgreSQL Routine Vacuuming

Process

1. Understand that MVCC creates dead tuples on every UPDATE and DELETE -- this is normal, not a problem unless vacuum falls behind.
2. Monitor dead tuple counts and bloat ratios for high-churn tables using

   pg_stat_user_tables

   .
3. Tune autovacuum thresholds per-table for tables with update rates that exceed the defaults, and use

   pg_repack

   for online compaction when needed.

Harness Integration

• Type: knowledge -- this skill is a reference document, not a procedural workflow.
• No tools or state -- consumed as context by other skills and agents.
• related_skills: db-isolation-levels, db-read-phenomena, db-acid-in-practice, db-pessimistic-locking, db-optimistic-locking, db-deadlock-prevention

Success Criteria

• Autovacuum enabled and monitored for all tables (never globally disabled)
• High-churn tables have per-table autovacuum settings tuned for their update rate
• Dead tuple counts and bloat ratios tracked in monitoring dashboards
• No tables approaching transaction ID wraparound (age(relfrozenxid) < 500 million)