PostgreSQL Performance & Query Optimization · Lesson

Tuple Visibility, xmin, and xmax

Decode row version headers to understand why dead tuples accumulate and scans slow down.

Tuple Visibility, xmin, and xmax is a free PostgreSQL Performance & Query Optimization lesson on CoddyKit — lesson 1 of 4. You can read the complete lesson below for free — then practise it hands-on in the browser with a built-in code editor and a 24/7 AI tutor. It is part of the PostgreSQL Performance & Query Optimization learning path, one of 4 lessons in the course, and your progress syncs across the web and the CoddyKit app.

Every Row Carries Hidden Bookkeeping

In PostgreSQL, a row is never just your columns. Each physical row version (a tuple) carries hidden system columns in its header that decide who is allowed to see it.

xmin — the transaction ID that inserted this tuple version.
xmax — the transaction ID that deleted or updated it (0 if still live).
ctid — the physical location (block, offset) of the tuple.

These fields are the foundation of MVCC: multiple versions of the same logical row can coexist on disk, each visible to a different set of transactions.

SELECT xmin, xmax, ctid, * FROM accounts WHERE id = 42;

MVCC: Readers Don't Block Writers

MVCC (Multi-Version Concurrency Control) means an UPDATE does not overwrite data in place. Instead it writes a new tuple version and marks the old one as expired by stamping its xmax.

The benefit: a long-running SELECT keeps reading the old version while a concurrent writer creates a new one. No locks between readers and writers.

The cost: the old version is not erased. It becomes a dead tuple once no transaction can still see it — and dead tuples are exactly what makes scans slow over time.

Reading xmin and xmax Directly

You can inspect the visibility header by selecting the hidden columns explicitly. They are not returned by *, so you must name them.

After an INSERT, xmin holds the inserting transaction's ID and xmax is 0 (meaning: not deleted, fully live).

The txid_current() function shows your current transaction ID, which lets you correlate what you see in xmin.

BEGIN;
INSERT INTO accounts (id, balance) VALUES (42, 100);
SELECT txid_current();          -- e.g. 5012
SELECT xmin, xmax FROM accounts WHERE id = 42;  -- xmin=5012, xmax=0
COMMIT;

An UPDATE Is a Delete Plus Insert

Watch what an UPDATE does to the headers. The original tuple gets its xmax set to the updating transaction, and a brand-new tuple is written with a fresh xmin.

The ctid changes too: the new version lives at a different physical location. The old version still occupies space until vacuum reclaims it.

SELECT ctid, xmin, xmax, balance FROM accounts WHERE id = 42;
-- ctid=(0,1)  xmin=5012  xmax=0    balance=100

UPDATE accounts SET balance = 150 WHERE id = 42;

SELECT ctid, xmin, xmax, balance FROM accounts WHERE id = 42;
-- ctid=(0,2)  xmin=5040  xmax=0    balance=150
-- the old (0,1) tuple now has xmax=5040 and is invisible to new snapshots

Snapshots Decide Visibility

Whether a tuple is visible to you depends on your transaction's snapshot: the set of transactions that had committed when your statement (or transaction, under REPEATABLE READ) began.

The simplified visibility rule for a tuple is:

Its xmin must be committed and visible to your snapshot (the insert happened-before you).
Its xmax must be not committed-and-visible (the delete has not happened from your point of view), or be 0.

This is why two sessions can see different balances for the same id at the same wall-clock moment.

Hint Bits and the txid_status Helper

Checking "is transaction X committed?" on every tuple read would be expensive. PostgreSQL caches the answer in per-tuple hint bits set on first access after commit, so later reads skip the commit-log (clog) lookup.

This is why the first scan after a big write batch can be slower than later ones: it is setting hint bits and dirtying pages. You can query commit status of an xid explicitly:

SELECT txid_status(5012);   -- 'committed', 'aborted', 'in progress', or NULL

Counting Dead Tuples per Table

Dead tuples are old versions whose xmax is committed and older than every active snapshot — nobody can see them anymore, but they still occupy pages.

PostgreSQL tracks an estimate in the statistics views. This is your first stop when a table's scans have quietly gotten slower:

SELECT relname,
       n_live_tup,
       n_dead_tup,
       round(n_dead_tup::numeric
             / NULLIF(n_live_tup + n_dead_tup, 0), 3) AS dead_ratio,
       last_autovacuum
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 10;

Why Dead Tuples Slow Down Scans

A sequential or index scan must physically visit every tuple on each heap page it reads, then test visibility against the snapshot. Dead tuples are still on the page, so they:

inflate the number of pages a scan must read (table bloat);
cost CPU for visibility checks that always fail;
reduce the effectiveness of index-only scans because the visibility map has fewer all-visible pages.

A table that is 70% dead tuples does roughly 3x the I/O for the same number of live rows. The query plan looks identical; only the buffer counts grow.

EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM accounts WHERE balance > 1000;
-- Watch 'Buffers: shared hit/read' climb as dead tuples accumulate,
-- even though the row estimate and plan shape stay the same.

The xmin Horizon and Long Transactions

Vacuum can only remove a dead tuple if its xmax is older than the oldest snapshot still in use — the cluster's xmin horizon. A single idle-in-transaction session or a forgotten replication slot pins that horizon back.

The result: dead tuples pile up and vacuum reports work but reclaims nothing, because the rows are technically still potentially visible to that ancient transaction.

Hunt the culprit before it bloats the whole database:

SELECT pid, state, age(backend_xid) AS xid_age,
       age(backend_xmin) AS xmin_age,
       now() - xact_start AS xact_duration, query
FROM pg_stat_activity
WHERE backend_xmin IS NOT NULL
ORDER BY age(backend_xmin) DESC
LIMIT 5;

Freezing and the 32-bit XID Wraparound

Transaction IDs are 32-bit and wrap around. To keep old tuples permanently visible, vacuum eventually freezes them — conceptually marking xmin as "infinitely in the past" via the committed/frozen hint bits so they are visible to all future snapshots.

If freezing falls behind, the cluster approaches wraparound and PostgreSQL will force an aggressive anti-wraparound vacuum (or refuse new writes). Monitor the age of the oldest unfrozen xid per table:

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;

Putting It Together for Optimization

The practical workflow when scans on a hot, frequently-updated table degrade:

Confirm bloat: high n_dead_tup and dead ratio in pg_stat_user_tables.
Check the horizon: long transactions or stale replication slots in pg_stat_activity / pg_replication_slots.
Tune autovacuum for that table so it runs sooner on high-churn data.

Lowering the scale factor makes autovacuum trigger at a smaller absolute number of dead tuples, keeping pages clean and scans fast.

ALTER TABLE accounts SET (
  autovacuum_vacuum_scale_factor = 0.02,
  autovacuum_vacuum_threshold    = 1000
);
-- Autovacuum now triggers at ~2% dead rows + 1000, instead of the 20% default.

Quick Check: Diagnosing Bloat

A high-churn table's queries have slowed even though the plan is unchanged. pg_stat_user_tables shows a high n_dead_tup, but autovacuum runs frequently and the dead-tuple count never drops. What is the most likely root cause?

Recap: Headers Drive Performance

You now know how row version headers govern both correctness and speed:

xmin stamps the inserting transaction; xmax stamps the deleting/updating one. An UPDATE is a delete-plus-insert that leaves an old version behind.
Your snapshot plus these headers determine visibility — readers never block writers.
Old versions become dead tuples, causing bloat that inflates page reads and slows scans without changing the plan.
Vacuum reclaims them only past the xmin horizon; long transactions and replication slots block that, and freezing prevents XID wraparound.

Diagnose with pg_stat_user_tables and pg_stat_activity, then tune per-table autovacuum to keep hot tables clean.

Frequently asked questions

Is the “Tuple Visibility, xmin, and xmax” lesson free?

Yes — the full text of “Tuple Visibility, xmin, and xmax” is free to read here on the web, and the PostgreSQL Performance & Query Optimization course includes 4 lessons in total. To practise it interactively (a built-in code editor and a 24/7 AI tutor) and unlock the rest of the PostgreSQL Performance & Query Optimization course, upgrade to CoddyKit PRO.

What will I learn in “Tuple Visibility, xmin, and xmax”?

Decode row version headers to understand why dead tuples accumulate and scans slow down. You practise PostgreSQL Performance & Query Optimization with hands-on code you run directly in the browser, and a 24/7 AI tutor answers your questions as you work through the lesson.

Do I need any experience to start PostgreSQL Performance & Query Optimization?

No prior experience is required. PostgreSQL Performance & Query Optimization on CoddyKit is structured for beginners through advanced learners; this is — lesson 1 of 4, so you can start here or from the beginning and move at your own pace.

How long does the “Tuple Visibility, xmin, and xmax” lesson take?

Most CoddyKit lessons take about 5–10 minutes. Each one is bite-sized and interactive, so you make steady progress and pick up exactly where you left off across the web and the app.

Can I write and run code in this PostgreSQL Performance & Query Optimization lesson?

Yes. Every PostgreSQL Performance & Query Optimization lesson includes a built-in code editor, so you write and run real code right in your browser and get instant AI feedback — no local setup required.

All lessons in this course

← Back to PostgreSQL Performance & Query Optimization