PostgreSQL Performance & Query Optimization · Lesson

Tuple Visibility, xmin, and xmax

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

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.

All lessons in this course

← Back to PostgreSQL Performance & Query Optimization