What are xmin and xmax on a PostgreSQL row?
They are hidden system columns recording the transaction ID that created the row version (xmin) and the one that deleted or replaced it (xmax). MVCC uses them to decide which versions a given snapshot may see.
Explain it simply
Every version of a row carries two stamps, like a library book with "issued by" and "withdrawn by" slips. xmin is the ID of the transaction that inserted this version. xmax is the ID of the transaction that deleted it, or updated it into a newer version. If xmax is 0, nobody has removed it.
When you query, PostgreSQL compares these stamps with your snapshot: a version is visible if its creator committed before your snapshot and its remover had not.
Example
CREATE TABLE t (n int);
INSERT INTO t VALUES (1);
SELECT xmin, xmax, n FROM t;
-- xmin | xmax | n
-- 801 | 0 | 1
UPDATE t SET n = 2;
SELECT xmin, xmax, n FROM t;
-- xmin | xmax | n
-- 802 | 0 | 2 (a new version; the old one is dead but still on disk)An UPDATE writes a new row version and marks the old one with an xmax. The old version stays until VACUUM removes it. That is why updates grow tables if vacuum is not keeping up.
What to say in the interview
"xmin is the creating transaction ID and xmax the deleting or updating one. Combined with snapshots they decide visibility. An UPDATE is really insert-new-version plus mark-old-version, and VACUUM later reclaims the dead versions."
Common mistakes
- Believing UPDATE changes a row in place.