Module A-2·32 min read

How an 18-hour idle-in-transaction session caused 17 GB of dead tuple bloat — and the MVCC mechanics behind it.

JJS

Written by Jatin Jain Saraf · Senior Software Engineer

Module 2 — MVCC: The Architecture That Makes Concurrency Possible (and Expensive)

Q: A high-volume OLTP system experiences intermittent, severe performance degradation on SELECT queries, despite low CPU and I/O utilization. pg_stat_statements shows high shared_blks_hit for the affected tables, and pgstattuple indicates a low dead_tuple_percent. What is the most likely root cause of this performance issue, given the MVCC architecture?

High transaction churn on hot rows, causing HeapTupleSatisfiesMVCC() to frequently perform expensive commit log lookups because hint bits are not consistently set or are invalidated by new row versions. — The problem describes high `shared_blks_hit` (data is in memory) but slow `SELECT`s, and low `dead_tuple_percent`. This points to CPU-bound work on visibility checks. Option C correctly identifies that even with low dead tuples, high transaction churn means new row versions are constantly being created. These new versions may not have their hint bits set yet, or hint bits might be frequently invalidated, forcing `HeapTupleSatisfiesMVCC()` to perform expensive commit log lookups for every candidate row version. This overhead, multiplied by millions of rows in a scan, can cause significant performance degradation. Option A is incorrect because `pgstattuple` explicitly shows low dead tuple percentage. Option B is incorrect because transaction ID wraparound leads to database shutdown, not intermittent SELECT slowness, and is a distinct issue from visibility check performance. Option D is incorrect because `t_ctid` chains are primarily relevant for navigating versions during updates or specific internal operations, not the primary cause of slow `SELECT` visibility checks.

Q: A financial application uses REPEATABLE READ for multi-statement reports. A transaction (T1) first reads an aggregate sum for a user's balance, then attempts to insert a new transaction for that user. Concurrently, another transaction (T2) inserts a different transaction for the *same* user and commits. T1 then attempts its insert and receives a `could not serialize access due to concurrent update` error. Why did T1 receive this error under REPEATABLE READ, and what would be the behavior if SERIALIZABLE was used instead?

The error `could not serialize access due to concurrent update` is specifically triggered when a `REPEATABLE READ` transaction attempts to modify a row (or insert into a range) that was changed and committed by another transaction after T1's snapshot was taken. `SERIALIZABLE` would have detected a read/write dependency cycle and aborted T1 *before* the insert, possibly even on the initial `SELECT` if T2 had already committed. — Option B correctly explains the behavior. Under `REPEATABLE READ`, a transaction's snapshot is fixed at its start, ensuring consistent reads. However, if T1 attempts to *modify* data that has been concurrently modified and committed by T2 since T1's snapshot was taken, Postgres detects this conflict and raises a `could not serialize access due to concurrent update` error. This is not a deadlock. If `SERIALIZABLE` isolation were used, Postgres's Serializable Snapshot Isolation (SSI) would track read-write dependencies. It would likely detect a potential serialisation anomaly (e.g., T1 read data, T2 wrote data that T1's read depended on, and T1 is now writing) and abort T1 earlier, possibly even during its initial `SELECT` or before its `INSERT`, to prevent a non-serializable execution. Option A is incorrect because `REPEATABLE READ` prevents phantom reads within its snapshot, but the error is about an update conflict, and `SERIALIZABLE` is stricter, not more lenient. Option C is incorrect because the lesson explicitly states this error is not a deadlock. Option D is incorrect because MVCC databases like Postgres do not use range locks for `SELECT` statements in the way described; `SERIALIZABLE` uses predicate locking for dependency tracking, not to prevent concurrent inserts in this manner.

Q: A Postgres DBA observes that a critical `transactions` table has an `xid_age` of 1.8 billion, rapidly approaching the shutdown threshold. Autovacuum logs show frequent `WARNING: some dead tuples could not be removed due to recently active snapshots` messages for this table. The DBA has already increased `autovacuum_freeze_max_age` for the table to 100 million and confirmed autovacuum is running. What is the most effective immediate action to mitigate the impending transaction ID wraparound shutdown, and what is the most critical long-term preventative measure?

Immediate: Identify and terminate long-running transactions (specifically those with old `backend_xmin`) using `pg_stat_activity`. Long-term: Set `idle_in_transaction_session_timeout` globally and for specific roles. — Option B provides the correct immediate and long-term solutions. The `WARNING: some dead tuples could not be removed due to recently active snapshots` message, combined with a high `xid_age`, indicates that long-running transactions are holding back `VACUUM` (including `VACUUM FREEZE`) from processing old tuples and resetting `relfrozenxid`. The immediate action is to identify these blocking sessions via `pg_stat_activity` (looking for old `backend_xmin`) and terminate them. The most critical long-term preventative measure is to configure `idle_in_transaction_session_timeout` to automatically terminate sessions that remain idle within a transaction, preventing them from accumulating old `backend_xmin` values. Option A is incorrect because `VACUUM FULL` takes an `ACCESS EXCLUSIVE` lock, which is highly disruptive and not suitable for immediate mitigation on a critical production table. Increasing `autovacuum_vacuum_cost_limit` helps general vacuuming but doesn't address the blocking `backend_xmin` issue. Option C is incorrect because increasing `autovacuum_freeze_max_age` would *delay* the forced freeze, exacerbating the problem, and manual `VACUUM FREEZE` is a reactive measure that might still be blocked. Option D is incorrect because restarting the database does not reset the XID counter, and standard Postgres does not offer a direct '64-bit XID system' migration to solve this specific problem.

What this module covers: Multi-Version Concurrency Control is the single most consequential design decision in Postgres. It enables readers and writers to coexist without locks — but every write creates a new row version, every delete leaves a corpse, and every update creates two corpses. Left unmanaged, MVCC accumulates waste until it shuts your database down entirely. This is not hypothetical. Transaction ID wraparound has forced emergency downtime at major companies. You need to understand this system from first principles.

The Problem MVCC Solves

In a locking database (like MySQL with SERIALIZABLE using range locks, or early PostgreSQL), a SELECT that reads a row must acquire a read lock, preventing concurrent writes. A UPDATE must acquire a write lock, preventing concurrent reads and writes.

At scale, this serialises all access to hot rows — a disaster for any high-throughput system.

MVCC solves this with a different model: instead of blocking reads, create a new version of every modified row. Readers see the old version. Writers create the new version. Nobody blocks.

The cost is space: you accumulate old versions. And there is a hard limit on how many transaction IDs you can use before the version visibility system breaks down completely.

Transaction IDs: The Clock That Drives Everything

Every transaction in Postgres is assigned a Transaction ID (XID) — a 32-bit unsigned integer that increments monotonically. Transaction IDs are used to determine which row versions are visible to which transactions.

sql

-- See your current transaction ID
SELECT txid_current();
-- 4831728

-- See current transaction info
SELECT * FROM txid_current_snapshot();
-- txid_current_snapshot
-- ---------------------
-- 4831725:4831728:4831725,4831726

The snapshot format is xmin:xmax:xip_list:

xmin — all transactions with XID < this are committed and visible
xmax — all transactions with XID >= this are in-progress or haven't started and are invisible
xip_list — transactions between xmin and xmax that are still in-progress

The 32-bit limit

The XID counter is 32 bits — it wraps around at 2^32 = ~4.3 billion transactions. Postgres uses a concept of "transaction ID age": from any current transaction, XID values in the "past" half of the 32-bit circle are considered committed, and values in the "future" half are considered in-progress.

This means Postgres can only "see back" ~2.1 billion transactions at any time. When a row's t_xmin is more than ~2.1 billion transactions old, Postgres can no longer determine its visibility status — it becomes invisible to everything.

This is transaction ID wraparound, and it will shut down your database.

We will cover the mechanics and prevention in depth later in this module.

Row Versioning: xmin, xmax, and the Visibility Chain

Every row on disk carries two XID fields in its HeapTupleHeaderData:

t_xmin — the XID of the transaction that created this row version
t_xmax — the XID of the transaction that deleted or updated this row version (0 if still live)

What happens on INSERT

sql

BEGIN;
INSERT INTO transactions (hash, block_height, sender, amount, status, timestamp)
VALUES (decode('deadbeef', 'hex'), 18500000, '0xabc', 100.0, 'confirmed', now());
COMMIT; -- XID = 4831729

The inserted row has:

t_xmin = 4831729 (the inserting transaction)
t_xmax = 0 (not yet deleted)

What happens on UPDATE

sql

BEGIN; -- XID = 4831730
UPDATE transactions SET status = 'failed' WHERE hash = decode('deadbeef', 'hex');
COMMIT;

Postgres does not modify the existing row. Instead:

The old row gets t_xmax = 4831730 (marking it as deleted by this transaction)
A new row is inserted with t_xmin = 4831730, t_xmax = 0, and the new status value
The old row's t_ctid is updated to point to the new row's physical location

You now have two versions of the same logical row on disk:

text

Old version: t_xmin=4831729, t_xmax=4831730, status='confirmed'
New version: t_xmin=4831730, t_xmax=0,       status='failed'

The old version is now a dead tuple — no transaction started after 4831730 can see it, but it still occupies space.

What happens on DELETE

sql

BEGIN; -- XID = 4831731
DELETE FROM transactions WHERE hash = decode('deadbeef', 'hex');
COMMIT;

The row gets t_xmax = 4831731. No new row is created. The row becomes dead after all transactions that started before 4831731 complete.

Inspecting row versions live

sql

-- See xmin/xmax for rows in a page
SELECT
    t_xmin,
    t_xmax,
    t_ctid,
    t_infomask,
    substring(t_data::text, 1, 50) AS data_preview
FROM heap_page_items(get_raw_page('transactions', 0))
WHERE t_xmin IS NOT NULL
LIMIT 5;

text

 t_xmin  | t_xmax  |  t_ctid  | t_infomask | data_preview
---------+---------+----------+------------+--------------
 4831729 |       0 | (0,1)    |       2306 | \xdeadbeef...
 4831730 |       0 | (0,2)    |       2306 | \xdeadbeef...

After an update, you see both versions on the page. The old version's t_xmax would be non-zero.

Snapshots: How Visibility Is Determined

When a transaction reads data, it uses a snapshot to determine which row versions are visible. The snapshot captures the state of all active transactions at a specific moment.

The snapshot algorithm

For a row version to be visible to a transaction with snapshot (xmin, xmax, xip_list):

The row was inserted by a visible transaction when:

t_xmin < snapshot.xmin (committed before snapshot was taken) AND t_xmin's transaction committed

OR:

snapshot.xmin <= t_xmin < snapshot.xmax AND t_xmin is NOT in xip_list (committed between snapshot xmin and xmax)

AND the row was not deleted by a visible transaction:

t_xmax = 0 (never deleted)
OR t_xmax is in xip_list (still in-progress — the delete hasn't committed)
OR t_xmax >= snapshot.xmax (started after the snapshot was taken)

This logic is implemented in HeapTupleSatisfiesMVCC() in the Postgres source code. Every row access in every query runs this function for every candidate row version.

The performance implication

MVCC visibility checking runs for every row the executor evaluates — not just the rows returned. On a sequential scan of 100 million rows that returns 1000, visibility is checked 100 million times. Each check reads t_xmin, t_xmax, and potentially queries the commit log.

For committed transactions (the common case), Postgres caches the commit status in the tuple's t_infomask bits after the first check — the "hint bit" optimisation. But for very old transactions or tables that haven't been vacuumed, these hint bits may not be set, forcing a commit log lookup on every visibility check.

Isolation Levels: Where the Snapshot Is Taken

The difference between READ COMMITTED and REPEATABLE READ is not a conceptual one about what data you see. It is a mechanical one about when the snapshot is taken.

READ COMMITTED

A new snapshot is taken at the start of each statement within the transaction.

sql

BEGIN; -- transaction T1, isolation = READ COMMITTED (default)

SELECT COUNT(*) FROM transactions WHERE status = 'pending';
-- Snapshot taken: sees all rows committed before this statement
-- Result: 1000

-- Meanwhile, T2 commits a DELETE that removes 500 pending rows

SELECT COUNT(*) FROM transactions WHERE status = 'pending';
-- NEW snapshot taken: sees T2's committed delete
-- Result: 500  ← different result!

COMMIT;

This is not a bug — it is the defined behaviour of READ COMMITTED. Each statement gets a fresh view of committed data. This is why you can observe non-repeatable reads: the same query returns different results within the same transaction.

REPEATABLE READ

A snapshot is taken once at the start of the first statement in the transaction and reused for all subsequent statements.

sql

BEGIN ISOLATION LEVEL REPEATABLE READ; -- transaction T1

SELECT COUNT(*) FROM transactions WHERE status = 'pending';
-- Snapshot taken ONCE here: sees 1000

-- Meanwhile, T2 commits a DELETE that removes 500 pending rows

SELECT COUNT(*) FROM transactions WHERE status = 'pending';
-- SAME snapshot reused: T2's changes are invisible
-- Result: 1000  ← consistent with first read

COMMIT;

A REPEATABLE READ transaction always sees the same snapshot. However, if T1 tries to update a row that T2 has already modified and committed, T1 will get a serialisation error:

ERROR:  could not serialize access due to concurrent update

This is not the same as a deadlock. It is MVCC enforcing that the transaction's view of the world is no longer consistent with the actual committed state.

READ COMMITTED vs REPEATABLE READ in practice

For a blockchain indexer reading aggregate statistics alongside writes, READ COMMITTED is usually correct — you want each query to see the latest committed state. For a financial report that must be consistent across multiple queries (e.g. balance sheet that must balance), REPEATABLE READ is essential.

sql

-- Financial report that must be consistent
BEGIN ISOLATION LEVEL REPEATABLE READ;

SELECT SUM(amount) FROM transactions WHERE status = 'confirmed';
SELECT COUNT(*) FROM transactions WHERE status = 'confirmed';
-- Both queries see the same snapshot — the counts will be consistent

COMMIT;

SERIALIZABLE and SSI

SERIALIZABLE goes one step further. It not only takes a consistent snapshot, it also tracks read-write dependencies between concurrent transactions to detect non-serialisable execution orders.

sql

-- Transaction T1
BEGIN ISOLATION LEVEL SERIALIZABLE;
SELECT SUM(amount) FROM transactions WHERE sender = '0xabc';

-- Transaction T2 (concurrent)
BEGIN ISOLATION LEVEL SERIALIZABLE;
INSERT INTO transactions (sender, amount, ...) VALUES ('0xabc', 500, ...);
COMMIT;

-- T1 continues
INSERT INTO transactions (sender, amount, ...) VALUES ('0xabc', -500, ...);
-- ERROR: could not serialize access due to read/write dependencies among transactions

SSI detects that T1 read a value that T2 modified, creating a cycle in the dependency graph. One transaction must be aborted. The application must retry.

SSI is powerful but has a CPU overhead (predicate locking) and a retry burden. For most OLTP workloads, READ COMMITTED with careful application-level conflict handling is more practical.

The True Cost of MVCC: Dead Tuples and Heap Bloat

Every UPDATE and DELETE in Postgres leaves dead tuples on disk. This is not an edge case — it is the fundamental price of MVCC.

How bloat accumulates

Consider a table that receives 1,000 updates per second. Each update:

Marks the old row version as dead (sets t_xmax)
Inserts a new row version

After one hour: 3.6 million dead tuples. After one day: 86.4 million dead tuples.

Autovacuum reclaims this space, but only if it is tuned aggressively enough to keep up with the update rate. A misconfigured autovacuum on a high-write table will fall behind, and the table will grow without bound even if the number of live rows is constant.

Measuring bloat

sql

-- Using pgstattuple (requires pg_stat_extension)
CREATE EXTENSION pgstattuple;

SELECT
    table_len,
    tuple_count,
    tuple_len,
    tuple_percent,
    dead_tuple_count,
    dead_tuple_len,
    dead_tuple_percent,
    free_space,
    free_percent
FROM pgstattuple('transactions');

text

 table_len       | 10737418240  (10 GB)
 tuple_count     | 48000000
 tuple_len       | 9663676416   (9.0 GB)
 tuple_percent   | 90.0
 dead_tuple_count| 2400000
 dead_tuple_len  | 483328000    (461 MB)
 dead_tuple_percent | 4.5
 free_space      | 547356672    (522 MB)
 free_percent    | 5.1

4.5% of this table is dead tuples. On a 10GB table, that is 461MB of wasted space that autovacuum has not yet reclaimed.

The long-running transaction problem

Dead tuples cannot be reclaimed by VACUUM while any active transaction's snapshot can still "see" them. Even a committed snapshot (one taken before the deleting transaction) prevents dead tuple reclamation.

This is why long-running transactions cause bloat even without performing any writes:

sql

-- In session 1: start a long-running read transaction
BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT COUNT(*) FROM transactions;
-- Don't commit for 2 hours

-- In session 2: perform 10 million updates
UPDATE transactions SET status = 'archived' WHERE timestamp < now() - interval '1 year';
-- Creates 10 million dead tuples

-- VACUUM runs but cannot reclaim the dead tuples
-- because session 1's snapshot still references them
VACUUM transactions;
-- WARNING:  some dead tuples could not be removed due to recently active snapshots

To find the oldest transaction holding back vacuum:

sql

SELECT
    pid,
    usename,
    application_name,
    state,
    backend_xid,
    backend_xmin,
    now() - xact_start AS transaction_duration,
    query
FROM pg_stat_activity
WHERE backend_xmin IS NOT NULL
  AND now() - xact_start > interval '10 minutes'
ORDER BY xact_start;

Any session with a very old backend_xmin is preventing vacuum from cleaning up dead tuples. If these sessions are idle-in-transaction, they need to be terminated.

Transaction ID Wraparound: The Ticking Clock

The 32-bit XID counter will eventually reach 2^32. When it does, newly created rows will appear to be in the "future" relative to older rows — a logical impossibility that breaks all visibility calculations.

Postgres prevents this catastrophe through transaction ID freezing: periodically replacing old t_xmin values with a special value FrozenTransactionId (XID 2). Frozen rows are always visible to all transactions, bypassing the visibility check entirely.

The age of a relation

sql

-- How "old" is each table in terms of transaction IDs?
SELECT
    relname,
    age(relfrozenxid) AS xid_age,
    pg_size_pretty(pg_total_relation_size(oid)) AS size
FROM pg_class
WHERE relkind = 'r'
ORDER BY age(relfrozenxid) DESC
LIMIT 10;

text

     relname     | xid_age  |  size
-----------------+----------+--------
 transactions    | 1847263  | 10 GB
 blocks          |  934821  | 4 GB
 events          |  621044  | 2 GB

The xid_age is the number of transactions since the table's oldest unfrozen XID was created. When this exceeds autovacuum_freeze_max_age (default: 200 million), autovacuum is forced to run a freeze operation on the table.

The danger zone

text

Safe zone:      xid_age < 200,000,000
Warning zone:   xid_age > 500,000,000
Emergency zone: xid_age > 1,500,000,000
Shutdown:       xid_age > 2,100,000,000

When xid_age reaches ~2.1 billion — the true wraparound limit shown in the danger-zone table above, not the autovacuum_freeze_max_age setting (which only controls when autovacuum proactively triggers a freeze, default 200 million) — Postgres enters a safe-mode where it will refuse all new connections except to allow a superuser to run VACUUM FREEZE.

sql

-- Monitor the danger level across all databases
SELECT
    datname,
    age(datfrozenxid) AS database_age,
    round(age(datfrozenxid)::numeric / 2100000000 * 100, 1) AS pct_danger
FROM pg_database
ORDER BY age(datfrozenxid) DESC;

VACUUM FREEZE: the solution

sql

-- Force a freeze on a specific table (can be done online)
VACUUM FREEZE transactions;

-- Check remaining danger level
SELECT
    relname,
    age(relfrozenxid),
    pg_size_pretty(pg_total_relation_size(oid))
FROM pg_class
WHERE relname = 'transactions';

After VACUUM FREEZE, all tuples have t_xmin = FrozenTransactionId. The VM all-frozen bits are set. Future freeze vacuums skip these pages entirely.

Autovacuum freeze configuration

The key parameters:

sql

-- Per-table override for high-write tables
ALTER TABLE transactions SET (
    autovacuum_vacuum_cost_delay = 2,        -- ms, lower = more aggressive
    autovacuum_vacuum_cost_limit = 400,      -- higher = more work per vacuum pass
    autovacuum_freeze_max_age = 100000000,   -- trigger freeze at 100M transactions
    autovacuum_multixact_freeze_max_age = 100000000
);

For your most critical tables, set autovacuum_freeze_max_age well below the global default of 200 million. This triggers more frequent freeze vacuums and keeps you far from the danger zone.

MVCC and Index Overhead

Indexes in Postgres do not store MVCC visibility information. Every index entry points to a heap tuple, and heap tuple visibility must be checked at query time.

This has two consequences:

1. Index scans are slower than they appear. For every row an index scan returns, Postgres must visit the heap to check visibility. The only exception is Index-Only Scans when the Visibility Map confirms the page is all-visible (covered in Module 1).

2. Dead index entries accumulate. When a row is deleted or updated (creating a dead heap tuple), the corresponding index entries are not immediately removed. They remain in the index until VACUUM cleans them up. On a heavily-updated table with many indexes, dead index entries can make indexes significantly larger than the live data.

sql

-- Check index bloat for a specific table
SELECT
    indexrelid::regclass AS index_name,
    pg_size_pretty(pg_relation_size(indexrelid)) AS index_size,
    idx_scan AS scans,
    idx_tup_read AS tuples_read,
    idx_tup_fetch AS tuples_fetched
FROM pg_stat_user_indexes
WHERE relid = 'transactions'::regclass
ORDER BY pg_relation_size(indexrelid) DESC;

If tuples_read is much larger than tuples_fetched, the index is reading many dead entries that the heap then rejects — a sign the index needs vacuuming.

Production Example: The MVCC-Induced Bloat Incident

The situation: A blockchain indexer table starts at 8GB. After three months of production with no bloat issues, it grows to 28GB. pg_stat_user_tables shows only 50 million live rows — which should fit in about 10GB.

Step 1: Confirm bloat with pgstattuple

sql

SELECT
    dead_tuple_count,
    dead_tuple_percent,
    pg_size_pretty(dead_tuple_len::bigint) AS dead_size,
    free_percent
FROM pgstattuple('transactions');

text

 dead_tuple_count | dead_tuple_percent | dead_size | free_percent
------------------+--------------------+-----------+-------------
        18500000  |               65.2 | 17 GB     | 3.1

65% of the table is dead tuples. 17GB of dead rows.

Step 2: Find what is preventing vacuum

sql

SELECT
    pid,
    usename,
    state,
    backend_xmin,
    now() - xact_start AS age,
    left(query, 80) AS query
FROM pg_stat_activity
WHERE backend_xmin IS NOT NULL
ORDER BY backend_xmin;

text

  pid  | usename  | state             | backend_xmin |    age     
-------+----------+-------------------+--------------+------------
 23481 | analytics| idle in transaction| 4813245     | 18:24:09.8

An analytics session has been idle-in-transaction for 18 hours. Its snapshot (XID 4813245) predates all the dead tuples we want to reclaim. As long as this transaction is open, VACUUM cannot touch them.

Step 3: Terminate the blocking session

sql

-- First, check with the team that this is safe to terminate
SELECT pg_terminate_backend(23481);

-- Confirm it's gone
SELECT pid FROM pg_stat_activity WHERE pid = 23481;
-- (0 rows)

Step 4: Run VACUUM and verify

sql

VACUUM ANALYZE transactions;

-- Check bloat again
SELECT
    dead_tuple_count,
    dead_tuple_percent,
    pg_size_pretty(dead_tuple_len::bigint) AS dead_size
FROM pgstattuple('transactions');

text

 dead_tuple_count | dead_tuple_percent | dead_size
------------------+--------------------+-----------
                0 |                0.0 | 0 bytes

But the table is still 28GB. VACUUM reclaims space within pages (marks it as available for reuse) but does not return pages to the OS. The table will shrink over time as new inserts reuse the free space.

If you need the space returned to the OS immediately:

sql

-- WARNING: Takes ACCESS EXCLUSIVE lock. Not suitable for production tables.
-- Consider pg_repack instead (covered in Module 4)
VACUUM FULL transactions;

Step 5: Prevent recurrence with idle-in-transaction timeouts

sql

-- Set a global timeout for idle-in-transaction sessions
ALTER SYSTEM SET idle_in_transaction_session_timeout = '15min';
SELECT pg_reload_conf();

-- Or per role for analytics users
ALTER ROLE analytics SET idle_in_transaction_session_timeout = '5min';

-- Verify
SHOW idle_in_transaction_session_timeout;

With this setting, any session that remains idle in transaction for more than 15 minutes is automatically terminated. This is the most important MVCC-related configuration for any production database that has analytics or reporting queries running alongside writes.

Summary

Concept	Key Mechanic	Production Implication
XID	32-bit counter	Monitor age; freeze before wraparound
t_xmin / t_xmax	Row version timestamps	Every UPDATE creates dead tuples
Snapshot	Taken at statement or transaction start	Isolation level determines when
READ COMMITTED	New snapshot per statement	Can see non-repeatable reads
REPEATABLE READ	Snapshot at first statement	Consistent reads, serialisation errors
Dead tuples	Accumulate on every UPDATE/DELETE	Autovacuum must keep up
Long transactions	Hold back VACUUM	Set `idle_in_transaction_session_timeout`
XID wraparound	2.1B transaction limit	Monitor `age(relfrozenxid)`
Freeze	Sets t_xmin = FrozenXID	Enables VM all-frozen, safe from wraparound

In Module 3, we follow writes from application code all the way to durable storage — through the Write-Ahead Log. WAL is what makes MVCC crash-safe, what makes replication possible, and what explains your write amplification.

Next: Module 3 — Write-Ahead Logging: Durability, Replication, and the Price of Every Write →

Knowledge Check

A high-volume OLTP system experiences intermittent, severe performance degradation on SELECT queries, despite low CPU and I/O utilization. pg_stat_statements shows high shared_blks_hit for the affected tables, and pgstattuple indicates a low dead_tuple_percent. What is the most likely root cause of this performance issue, given the MVCC architecture?

A financial application uses REPEATABLE READ for multi-statement reports. A transaction (T1) first reads an aggregate sum for a user's balance, then attempts to insert a new transaction for that user. Concurrently, another transaction (T2) inserts a different transaction for the *same* user and commits. T1 then attempts its insert and receives a could not serialize access due to concurrent update error. Why did T1 receive this error under REPEATABLE READ, and what would be the behavior if SERIALIZABLE was used instead?

A Postgres DBA observes that a critical transactions table has an xid_age of 1.8 billion, rapidly approaching the shutdown threshold. Autovacuum logs show frequent WARNING: some dead tuples could not be removed due to recently active snapshots messages for this table. The DBA has already increased autovacuum_freeze_max_age for the table to 100 million and confirmed autovacuum is running. What is the most effective immediate action to mitigate the impending transaction ID wraparound shutdown, and what is the most critical long-term preventative measure?

Test your knowledge with more question sets

Sign in to keep reading

The rest of this module is free — sign in with Google to unlock it and track your progress.

PreviousModule A-1: The Storage Engine: Pages, Heaps, and the True Cost of a Row Next Module A-3: Write-Ahead Logging: Durability, Replication, and the Price of Every Write

Discussion

Join the discussion

Loading comments...