0Pricing
PostgreSQL Performance & Query Optimization · Lección

Visibilidad de tuplas, xmin y xmax

Descodifique las cabeceras de las versiones de fila para comprender por qué se acumulan tuplas muertas y se ralentizan los escaneos.

Visibilidad de tuplas, xmin y xmax es una lección gratuita de PostgreSQL Performance & Query Optimization en CoddyKit. Esta es la lección 1 de 4. Puedes leer la lección completa abajo gratuitamente — luego la practicas en el navegador con un editor de código integrado y un tutor de IA 24/7. Forma parte de la ruta de aprendizaje de PostgreSQL Performance & Query Optimization, y tu progreso se sincroniza en la web y la app de CoddyKit. El curso de PostgreSQL Performance & Query Optimization incluye 4 lecciones en total.

Partes de esta lección aún no han sido traducidas y se muestran en inglés.

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.

Preguntas frecuentes

¿La lección «Visibilidad de tuplas, xmin y xmax» es gratis?

Sí — el texto completo de «Visibilidad de tuplas, xmin y xmax» es gratis para leer aquí en la web. Para practicarla de forma interactiva (editor de código integrado y tutor de IA 24/7) y desbloquear el resto del curso de PostgreSQL Performance & Query Optimization, actualiza a CoddyKit PRO. El curso de PostgreSQL Performance & Query Optimization incluye 4 lecciones en total.

¿Qué aprenderé en «Visibilidad de tuplas, xmin y xmax»?

Descodifique las cabeceras de las versiones de fila para comprender por qué se acumulan tuplas muertas y se ralentizan los escaneos. Practicas PostgreSQL Performance & Query Optimization con código real que ejecutas directamente en el navegador, y un tutor de IA 24/7 responde tus preguntas mientras trabajas en la lección.

¿Necesito experiencia previa para empezar PostgreSQL Performance & Query Optimization?

No se requiere experiencia previa. PostgreSQL Performance & Query Optimization en CoddyKit está estructurado para principiantes hasta estudiantes avanzados, así que puedes empezar aquí o desde el inicio y avanzar a tu ritmo. Esta es la lección 1 de 4.

¿Cuánto tiempo toma la lección «Visibilidad de tuplas, xmin y xmax»?

La mayoría de las lecciones de CoddyKit toman alrededor de 5–10 minutos. Cada una es compacta e interactiva, así que avanzas constantemente y retomas exactamente por donde dejaste en la web y la app.

¿Puedo escribir y ejecutar código en esta lección de PostgreSQL Performance & Query Optimization?

Sí. Cada lección de PostgreSQL Performance & Query Optimization incluye un editor de código integrado, así que escribes y ejecutas código real directamente en tu navegador y obtienes retroalimentación instantánea de IA — sin configuración local necesaria.

Todas las lecciones de este curso

  1. Visibilidad de tuplas, xmin y xmax
  2. Actualizaciones HOT y cadenas de tuplas solo de heap
  3. Mapa de visibilidad y escaneos solo de índice
  4. Generación de WAL y amplificación de escritura
← Volver a PostgreSQL Performance & Query Optimization