PostgreSQL MVCC Deep-Dive — Multi-Version Concurrency Control Internals and Best Practices

By Marcus Vance · Lead Systems & Backend Architect · November 2026

MVCC Fundamentals — How PostgreSQL Achieves Concurrency Without Locks

The Core Idea

PostgreSQL uses Multi-Version Concurrency Control (MVCC) — readers never block writers, writers never block readers. Instead of locking rows, each transaction sees a snapshot of the database as of its start time. Concurrent transactions operate on different versions of the same row.

Tuple Structure — xmin, xmax, and the Hidden Columns

Every heap tuple carries two hidden system columns:

-- Visible via system columns
SELECT *, xmin, xmax, ctid FROM users WHERE id = 1;
--  id | name  | xmin  | xmax  | ctid
-- ----+-------+-------+-------+-------
--   1 | alice | 1001  | 0     | (0,1)

• xmin — Transaction ID that created this tuple version (INSERT or UPDATE).
• xmax — Transaction ID that deleted/updated this tuple (0 = live).
• ctid — Physical location (block, offset). Changes on UPDATE (new version = new ctid).

An UPDATE is internally DELETE + INSERT: marks old tuple xmax = updating XID, inserts new tuple with xmin = updating XID.

Visibility Rules — When Is a Tuple Visible?

A tuple is visible to transaction T (with snapshot xmin, xmax, xip_list) iff:

tuple.xmin is committed AND (tuple.xmin  T.xmax OR tuple.xmax NOT IN T.xip_list)

In practice — tuple visible if:

1. Creator (xmin) committed before T started (or is T itself).
2. Deleter (xmax) either hasn’t committed (0), aborted, or committed after T started.

Snapshot Isolation — REPEATABLE READ vs READ COMMITTED

READ COMMITTED — Each SELECT sees latest committed data. UPDATE/DELETE re-evaluate WHERE clause on latest version.

REPEATABLE READ — Single snapshot for entire transaction. Write conflicts abort with ERROR: could not serialize access due to concurrent update.

Transaction IDs — 32-bit, Wraparound, and Freezing

XID Space — 32-bit Circular

Transaction IDs are 32-bit unsigned integers (~4 billion). PostgreSQL treats XID space as circular — half “past” (committed/aborted), half “future”.

• FirstNormalTransactionId = 3 (0=invalid, 1=bootstrap, 2=frozen).
• XID comparison uses TransactionIdPrecedes/Following (modulo 2^31).

Freezing — Preventing Wraparound

Old tuples with xmin in “past” half must be marked frozen (xmin = 2, FrozenTransactionId) so they appear visible to all future transactions. Done by VACUUM.

Critical Thresholds

Monitoring Wraparound Risk

-- Age of oldest xmin per database
SELECT datname, age(datfrozenxid) AS xid_age
FROM pg_database
ORDER BY xid_age DESC;

-- Per-table freeze status
SELECT relname, age(relfrozenxid) AS freeze_age
FROM pg_class
WHERE relkind = 'r'
ORDER BY freeze_age DESC;

VACUUM — The Garbage Collector

What VACUUM Does

1. Mark dead tuples reusable — tuples with xmax committed and not visible to any active transaction.
2. Freeze old tuples — set xmin = FrozenTransactionId for tuples older than vacuum_freeze_min_age.
3. Update visibility map — track all-visible pages for index-only scans.
4. Update statistics — for planner (if analyze option).
5. Truncate empty pages — return disk space to OS (if FULL or end-of-table).

VACUUM Variants

Autovacuum Configuration

# postgresql.conf
autovacuum = on                    # Enable (never turn off!)
autovacuum_max_workers = 4         # Parallel workers
autovacuum_naptime = 30s           # Check interval
autovacuum_vacuum_threshold = 50   # Min dead tuples
autovacuum_vacuum_scale_factor = 0.2  # 20% of table
autovacuum_analyze_threshold = 50
autovacuum_analyze_scale_factor = 0.1
autovacuum_vacuum_cost_delay = 2ms # Throttle I/O
autovacuum_vacuum_cost_limit = -1  # Use vacuum_cost_limit

# Per-table overrides (for large tables)
ALTER TABLE large_table SET (autovacuum_vacuum_scale_factor = 0.01);
ALTER TABLE large_table SET (autovacuum_vacuum_threshold = 1000);

Visibility Map & Index-Only Scans

Pages where all tuples are visible to all transactions are marked in the visibility map (VM). VACUUM updates VM. Index-only scans can skip heap fetch if VM bit set — huge speedup for read-heavy workloads.

Index Behavior Under MVCC

Index Entries Point to Tuple Versions

Every index entry stores ctid (heap pointer). On UPDATE:

1. New heap tuple inserted (new ctid).
2. New index entries inserted for all indexes.
3. Old index entries remain (point to dead tuple) — cleaned by VACUUM.

HOT (Heap-Only Tuples) — If UPDATE doesn’t change indexed columns and there’s space on same page, PostgreSQL creates a HOT chain: new tuple points to old via t_ctid. No new index entries. VACUUM cleans HOT chains automatically.

Index Bloat

Dead index entries accumulate until VACUUM. Monitor:

-- Index bloat estimation (pgstattuple extension)
SELECT * FROM pgstatindex('idx_name');

-- Or approximate via pg_class
SELECT relname, pg_size_pretty(pg_relation_size(oid)) AS size,
       pg_size_pretty(pg_total_relation_size(oid) - pg_relation_size(oid)) AS toast_idx_size
FROM pg_class WHERE relkind = 'i';

Concurrency Patterns & Best Practices

Pattern 1: Optimistic Locking with Version Column

-- Application-level optimistic locking
UPDATE accounts SET balance = balance - 100, version = version + 1
WHERE id = 1 AND version = 5;
-- If 0 rows affected → concurrent modification, retry

Pattern 2: SELECT FOR UPDATE / FOR SHARE

-- Lock selected rows (blocking)
SELECT * FROM orders WHERE id = 123 FOR UPDATE;        -- Exclusive lock
SELECT * FROM orders WHERE id = 123 FOR SHARE;         -- Shared lock
SELECT * FROM orders WHERE id = 123 FOR UPDATE SKIP LOCKED;  -- Skip locked (queue workers)
SELECT * FROM orders WHERE id = 123 FOR NO KEY UPDATE; -- Weaker lock (allows concurrent KEY UPDATE)

Pattern 3: Advisory Locks for Application Coordination

-- Session-level (released on disconnect)
SELECT pg_advisory_lock(12345);
SELECT pg_advisory_unlock(12345);

-- Transaction-level (released on commit/abort)
SELECT pg_advisory_xact_lock(12345);

-- Use for: distributed locks, schema migrations, cache invalidation coordination

Pattern 4: Avoiding Hot Rows — Partition, Shard, or Batch

Single-row contention (e.g., counter table) kills throughput. Solutions:

• Partitioned counters — multiple rows, sum on read.
• Async accumulation — write to log table, aggregate periodically.
• Advisory locks — serialize at application level.

Production Tuning for High Concurrency

Monitoring Queries

-- Active transactions & ages
SELECT pid, usename, state, backend_xmin, age(backend_xmin) AS xmin_age,
       query_start, state_change, LEFT(query, 100)
FROM pg_stat_activity
WHERE backend_xmin IS NOT NULL
ORDER BY xmin_age DESC;

-- Long-running transactions (block vacuum)
SELECT pid, usename, age(backend_xmin) AS xmin_age, query
FROM pg_stat_activity
WHERE backend_xmin IS NOT NULL AND age(backend_xmin) > 1000000;

-- Dead tuple accumulation
SELECT relname, n_dead_tup, n_live_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 dead_pct DESC;

Common Issues & Fixes

TL;DR — Key Takeaways

• MVCC = tuple versions (xmin/xmax) + snapshots. Readers never block writers.
• UPDATE = DELETE + INSERT (new ctid). Dead tuples cleaned by VACUUM.
• VACUUM is mandatory — reclaims space, freezes XIDs, updates visibility map.
• XID wraparound prevented by freezing (autovacuum_freeze_max_age=200M). Monitor age(datfrozenxid).
• Indexes point to heap tuples. HOT updates avoid index writes. Dead index entries cleaned by VACUUM.
• Tune: shared_buffers, work_mem, maintenance_work_mem, checkpoint_completion_target=0.9.
• Use FOR UPDATE SKIP LOCKED for queues, advisory locks for app coordination.

Next in series: Redis Clustering Patterns — Cluster mode, sharding, and high-availability topologies.