0Pricing
PostgreSQL Performance & Query Optimization · 课时

元组可见性、xmin 与 xmax

解读行版本头信息，了解死元组为何不断累积以及扫描为何变慢。

元组可见性、xmin 与 xmax 是 CoddyKit 上的免费 PostgreSQL Performance & Query Optimization 课时。 这是第 1 节课，共 4 节。 你可以在下方免费阅读本课时的完整内容 — 然后在浏览器中使用内置代码编辑器和全天候 AI 导师进行实践。 这是 PostgreSQL Performance & Query Optimization 学习路径的一部分，你的进度在网页和 CoddyKit 应用中同步。 PostgreSQL Performance & Query Optimization 课程共包含 4 节课。

本课时的部分内容尚未翻译，以英文显示。

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.

常见问题解答

「元组可见性、xmin 与 xmax」课时是免费的吗？

是的 — 「元组可见性、xmin 与 xmax」的完整文本可在网页上免费阅读。要进行交互式练习（内置代码编辑器和全天候 AI 导师）并解锁 PostgreSQL Performance & Query Optimization 课程的其余内容，请升级到 CoddyKit PRO。 PostgreSQL Performance & Query Optimization 课程共包含 4 节课。

「元组可见性、xmin 与 xmax」这节课中我会学到什么？

解读行版本头信息，了解死元组为何不断累积以及扫描为何变慢。 你通过在浏览器中直接运行的动手代码来练习 PostgreSQL Performance & Query Optimization，全天候 AI 导师会在你学习这节课的过程中回答你的问题。

学习 PostgreSQL Performance & Query Optimization 需要有经验吗？

无需任何先前经验。CoddyKit 上的 PostgreSQL Performance & Query Optimization 课程适合初学者到高级学习者，你可以从这里开始或从头开始，按照自己的节奏学习。 这是第 1 节课，共 4 节。

「元组可见性、xmin 与 xmax」课时需要多长时间？

大多数 CoddyKit 课程大约需要 5–10 分钟。每节课都很精短且互动，所以你能稳步进步，并在网页和应用中从离开的地方继续。

我能在这节 PostgreSQL Performance & Query Optimization 课中编写并运行代码吗？

能。每节 PostgreSQL Performance & Query Optimization 课都包含内置代码编辑器，你可以在浏览器中直接编写并运行真实代码，并获得即时 AI 反馈 — 无需本地设置。

此课程中的所有课时

  1. 元组可见性、xmin 与 xmax
  2. HOT 更新与仅堆元组链
  3. 可见性映射与仅索引扫描
  4. WAL 生成与写放大
← 返回 PostgreSQL Performance & Query Optimization