PostgreSQL DBA Part 11: MVCC, xmin/xmax and Row Visibility

This is Part 11 of the PostgreSQL DBA series, and the first part about storage and maintenance. In Part 10 we covered connections and sessions, including the "idle in transaction" sessions that DBAs are told to watch. This part explains why those sessions matter, by showing how PostgreSQL keeps several versions of the same row at once.

The short answer: PostgreSQL never overwrites a row in place. An UPDATE writes a new copy of the row and marks the old copy as expired. Every row copy carries two hidden numbers, xmin (the transaction that created it) and xmax (the transaction that deleted or replaced it), and each query uses a snapshot to decide which copies it is allowed to see. This design is called MVCC, Multiversion Concurrency Control.

By the end of this part you will be able to read xmin, xmax and ctid on any table, look inside a data page with pageinspect, explain why readers never block writers, and find the session that stops VACUUM from cleaning up dead rows.

What you will learn

• What a tuple (row version) is, and why an UPDATE creates a new one
• How the hidden columns xmin, xmax, cmin and ctid work, with real output
• How a snapshot decides which row versions a query can see
• How Read Committed and Repeatable Read use snapshots differently
• What dead tuples are, and how a long open transaction stops VACUUM from removing them

Prerequisites

• Parts 1 to 10 of this series, especially Part 4 (psql) and Part 10 (pg_stat_activity).
• One Ubuntu server (db1 in this series) with PostgreSQL 18 from the PGDG repository, cluster "18 main" running on port 5432.
• Two terminal windows (two SSH sessions) on db1. Several steps need two sessions open at the same time. We call them Terminal 1 and Terminal 2.
• sudo access, so you can run psql as the postgres operating system user.

How it works

Imagine a shared notice board. When someone wants to change a notice, they do not erase it. They pin a new version next to it and write "replaced" on the old one, with their name. Anyone who started reading before the change can still read the old version. Anyone who arrives later reads the new one. Once nobody is reading the old version any more, a cleaner takes it down. In PostgreSQL the notices are rows, the names are transaction IDs, and the cleaner is VACUUM.

Here are the terms you will see in the rest of this part:

A row version is visible to your query when two things are true. First, the transaction in its xmin committed before your snapshot was taken. Second, its xmax is 0, or the transaction in xmax had not committed when your snapshot was taken (it is still running, or it rolled back). Your own transaction's changes are always visible to you.

This is why readers never block writers in PostgreSQL. A reader does not wait for the writer. It simply reads the older row version that its snapshot can see, while the writer works on the new one.

The cost of this design is extra copies. Every UPDATE and DELETE leaves an old tuple behind. Those old tuples take space until VACUUM removes them, and VACUUM can only remove a tuple when no snapshot in the cluster might still need it. Part 12 covers VACUUM itself; this part shows you the row versions it works on.

Setting up the lab

Step 1: Create a lab database and a small table

Where: on db1, Terminal 1, as the postgres user. Start psql, create a database called mvcclab, connect to it and create a table of bank accounts with three rows.

sudo -u postgres psql

CREATE DATABASE mvcclab;
\c mvcclab
CREATE TABLE accounts (
    id      integer PRIMARY KEY,
    owner   text    NOT NULL,
    balance numeric NOT NULL
);
INSERT INTO accounts VALUES
    (1, 'Asha',  5000),
    (2, 'Ravi',  3000),
    (3, 'Meera', 7000);

CREATE DATABASE
You are now connected to database "mvcclab" as user "postgres".
CREATE TABLE
INSERT 0 3

Check it worked: the prompt now starts with mvcclab=#, and SELECT count(*) FROM accounts; returns 3.

Step 2: Read the hidden system columns

Where: Terminal 1, in psql. System columns are not returned by SELECT *; you have to name them.

SELECT xmin, xmax, ctid, * FROM accounts ORDER BY id;

 xmin | xmax | ctid  | id | owner | balance
------+------+-------+----+-------+---------
  782 |    0 | (0,1) |  1 | Asha  |    5000
  782 |    0 | (0,2) |  2 | Ravi  |    3000
  782 |    0 | (0,3) |  3 | Meera |    7000

All three rows were written by one INSERT, so they share xmin 782 (your number will be different; it depends on how many transactions your cluster has run). xmax is 0 because nobody has deleted or updated them. The ctid values show they sit in page 0 of the table, in items 1, 2 and 3.

Watching an UPDATE create a new row version

Step 3: Update a row inside an open transaction

Where: Terminal 1, in psql. Start a transaction, take 1000 from Asha, and do not commit yet. pg_current_xact_id() shows the XID of the current transaction.

BEGIN;
UPDATE accounts SET balance = balance - 1000 WHERE id = 1;
SELECT pg_current_xact_id();
SELECT xmin, xmax, ctid, * FROM accounts ORDER BY id;

BEGIN
UPDATE 1
 pg_current_xact_id
--------------------
                783
(1 row)

 xmin | xmax | ctid  | id | owner | balance
------+------+-------+----+-------+---------
  783 |    0 | (0,4) |  1 | Asha  |    4000
  782 |    0 | (0,2) |  2 | Ravi  |    3000
  782 |    0 | (0,3) |  3 | Meera |    7000

Inside its own transaction (XID 783), Terminal 1 sees Asha's new balance. Look at the ctid: the row moved from (0,1) to (0,4). That is the new tuple, and its xmin is 783. The psql prompt changes to mvcclab=*#; the star means a transaction is open.

Step 4: Read the same rows from a second session

Where: on db1, Terminal 2, as the postgres user. Leave Terminal 1 as it is.

sudo -u postgres psql mvcclab

SELECT xmin, xmax, ctid, * FROM accounts ORDER BY id;

 xmin | xmax | ctid  | id | owner | balance
------+------+-------+----+-------+---------
  782 |  783 | (0,1) |  1 | Asha  |    5000
  782 |    0 | (0,2) |  2 | Ravi  |    3000
  782 |    0 | (0,3) |  3 | Meera |    7000

Terminal 2 still sees Asha with 5000, at the old address (0,1). This tuple now has xmax 783: transaction 783 has replaced it, but 783 has not committed, so the old version is still the visible one for everyone else. The query did not wait for Terminal 1 to finish. This is MVCC in action: a reader and a writer worked on the same row at the same time without blocking each other.

Step 5: Look at the snapshot Terminal 2 uses

Where: Terminal 2. pg_current_snapshot() returns the current snapshot as text in the form xmin:xmax:xip_list.

SELECT pg_current_snapshot();

 pg_current_snapshot
---------------------
 783:783:
(1 row)

Read it with the definitions from the documentation:

So for Terminal 2, XID 782 (the INSERT) is committed and visible, and XID 783 (the UPDATE) is invisible. That is exactly what Step 4 showed. On a busy server the xip_list is usually full of numbers.

Step 6: Commit and read again

Where: commit in Terminal 1, then query in Terminal 2.

COMMIT;

SELECT xmin, xmax, ctid, * FROM accounts ORDER BY id;

 xmin | xmax | ctid  | id | owner | balance
------+------+-------+----+-------+---------
  783 |    0 | (0,4) |  1 | Asha  |    4000
  782 |    0 | (0,2) |  2 | Ravi  |    3000
  782 |    0 | (0,3) |  3 | Meera |    7000

Terminal 2 took a new snapshot for this query, and XID 783 is now committed, so it sees the new version at (0,4). The old version at (0,1) is still in the file. It is now a dead tuple: no new snapshot will ever see it again.

Looking inside the data page with pageinspect

Step 7: Install pageinspect and list every row version

A normal SELECT only shows the versions your snapshot can see. To see all of them, including dead ones, use the pageinspect extension. It ships with the postgresql-18 package on Ubuntu, so there is nothing extra to install with apt.

Where: Terminal 2, in psql, connected to mvcclab as postgres.

CREATE EXTENSION pageinspect;
SELECT lp, lp_flags, t_xmin, t_xmax, t_ctid
  FROM heap_page_items(get_raw_page('accounts', 0));

CREATE EXTENSION
 lp | lp_flags | t_xmin | t_xmax | t_ctid
----+----------+--------+--------+--------
  1 |        1 |    782 |    783 | (0,4)
  2 |        1 |    782 |      0 | (0,2)
  3 |        1 |    782 |      0 | (0,3)
  4 |        1 |    783 |      0 | (0,4)
(4 rows)

The page holds four tuples for three logical rows. Item 1 is Asha's old version: xmax 783, and its t_ctid points forward to (0,4), the newer version. Item 4 is the current version, and its t_ctid points to itself because nothing newer exists. This forward pointer is how PostgreSQL follows a row from an old version to the newest one.

Check it worked: SELECT extname FROM pg_extension WHERE extname = 'pageinspect'; returns one row.

Rollbacks and the meaning of xmax

Step 8: Delete a row and roll back

Where: Terminal 1. Delete Ravi's row, note the XID, then change your mind.

BEGIN;
DELETE FROM accounts WHERE id = 2;
SELECT pg_current_xact_id();
ROLLBACK;
SELECT xmin, xmax, ctid, * FROM accounts ORDER BY id;

BEGIN
DELETE 1
 pg_current_xact_id
--------------------
                785
(1 row)

ROLLBACK
 xmin | xmax | ctid  | id | owner | balance
------+------+-------+----+-------+---------
  783 |    0 | (0,4) |  1 | Asha  |    4000
  782 |  785 | (0,2) |  2 | Ravi  |    3000
  782 |    0 | (0,3) |  3 | Meera |    7000

Ravi's row is back, as expected, but its xmax is now 785, the XID of the rolled-back DELETE. A DELETE does not remove anything; it only stamps xmax. A ROLLBACK does not clean up the stamp either. It just marks transaction 785 as aborted. The documentation says this directly: a nonzero xmax on a visible row usually means the deleting transaction has not committed yet, or the deletion was rolled back. (XID 784 was used by the CREATE EXTENSION in Step 7.)

Step 9: Check commit status with pg_xact_status

Where: Terminal 1. PostgreSQL records whether each transaction committed or aborted in the pg_xact folder of the data directory (you met it in Part 3). pg_xact_status() reads that record.

SELECT pg_xact_status('783') AS update_xact,
       pg_xact_status('785') AS delete_xact;

 update_xact | delete_xact
-------------+-------------
 committed   | aborted
(1 row)

This is the second half of the visibility check. Seeing xmax 785 is not enough; PostgreSQL also asks whether 785 committed. It did not, so Ravi's row stays visible. The function returns in progress, committed or aborted for recent transactions, and NULL for transactions so old that their status has been discarded.

Snapshots and isolation levels

When a snapshot is taken depends on the transaction isolation level. PostgreSQL's default is Read Committed, which takes a new snapshot for every statement. Repeatable Read takes one snapshot at the first query or data-changing statement of the transaction (not at BEGIN) and keeps it until the end.

Step 10: Read Committed sees new commits between statements

Where: run the commands in the order shown, switching between the two terminals.

-- Terminal 2
BEGIN;
SELECT balance FROM accounts WHERE id = 3;   -- returns 7000

-- Terminal 1 (autocommit, commits at once)
UPDATE accounts SET balance = balance + 500 WHERE id = 3;

-- Terminal 2, same transaction
SELECT balance FROM accounts WHERE id = 3;
COMMIT;

 balance
---------
    7500
(1 row)

COMMIT

The same query, inside one transaction, returned 7000 and then 7500. The second statement got a fresh snapshot in which Terminal 1's update was already committed. This is called a nonrepeatable read, and Read Committed allows it.

Step 11: Repeatable Read keeps one snapshot

-- Terminal 2
BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT balance FROM accounts WHERE id = 3;   -- returns 7500

-- Terminal 1
UPDATE accounts SET balance = balance + 500 WHERE id = 3;

-- Terminal 2, same transaction
SELECT balance FROM accounts WHERE id = 3;
UPDATE accounts SET balance = balance - 100 WHERE id = 3;
ROLLBACK;

 balance
---------
    7500
(1 row)

ERROR:  could not serialize access due to concurrent update
ROLLBACK

The second SELECT still returned 7500, even though the committed value is now 8000. Terminal 2 keeps reading the old row version that its snapshot can see. When it tried to update that row, PostgreSQL refused: a Repeatable Read transaction cannot modify a row that another transaction changed after its snapshot was taken. The documentation's advice is to roll back and retry the whole transaction.

Check it worked: SELECT balance FROM accounts WHERE id = 3; in either terminal now returns 8000.

Dead rows, VACUUM and the transaction that blocks cleanup

Now the part that matters most on a production server. A dead tuple can be removed only when every snapshot in the cluster has moved past it. One forgotten open transaction can keep thousands of dead tuples alive, and the tables keep growing.

Step 12: Hold an old snapshot and create dead rows

Where: Terminal 1 first runs a plain VACUUM so we start clean. Then Terminal 2 opens a Repeatable Read transaction and reads (this fixes its snapshot) and just stays open, like a forgotten session in an application. Then Terminal 1 updates every row.

-- Terminal 1
VACUUM accounts;

-- Terminal 2: open a transaction and leave it open
BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT sum(balance) FROM accounts;

-- Terminal 1
UPDATE accounts SET balance = balance + 100;
SELECT n_live_tup, n_dead_tup, n_tup_upd, n_tup_hot_upd
  FROM pg_stat_user_tables
 WHERE relname = 'accounts';

 n_live_tup | n_dead_tup | n_tup_upd | n_tup_hot_upd
------------+------------+-----------+---------------
          3 |          3 |         6 |             6
(1 row)

The statistics view counts 3 live rows and 3 dead rows (estimates, maintained by the statistics system). n_tup_upd is the total number of rows updated so far (3 earlier plus 3 now), and n_tup_hot_upd shows that all of them were HOT updates, explained in Step 14.

Step 13: Run VACUUM VERBOSE and read the result

Where: Terminal 1, while Terminal 2 is still open.

VACUUM (VERBOSE) accounts;

INFO:  vacuuming "mvcclab.public.accounts"
INFO:  finished vacuuming "mvcclab.public.accounts": index scans: 0
pages: 0 removed, 1 remain, 1 scanned (100.00% of total), 0 eagerly scanned
tuples: 0 removed, 6 remain, 3 are dead but not yet removable
removable cutoff: 789, which was 1 XIDs old when operation ended
frozen: 0 pages from table (0.00% of total) had 0 tuples frozen
visibility map: 0 pages set all-visible, 0 pages set all-frozen (0 were all-visible)
index scan not needed: 0 pages from table (0.00% of total) had 0 dead item identifiers removed
...
VACUUM

Two lines tell the story. 3 are dead but not yet removable: VACUUM found the dead tuples but was not allowed to remove them. removable cutoff: 789: VACUUM may only remove tuples deleted by transactions older than XID 789, because some session still has a snapshot that started at 789. The update that created the dead tuples was XID 789 itself, so its old versions must stay.

Step 14: Find the session holding back cleanup

Where: Terminal 1. The backend_xmin column of pg_stat_activity is each session's xmin horizon: the oldest XID its snapshot still needs. The session with the oldest backend_xmin is the one blocking VACUUM.

SELECT pid, state, backend_xmin,
       age(backend_xmin) AS xmin_age,
       now() - xact_start AS xact_age
  FROM pg_stat_activity
 WHERE backend_xmin IS NOT NULL
   AND pid <> pg_backend_pid()
 ORDER BY age(backend_xmin) DESC;

 pid  |        state        | backend_xmin | xmin_age |    xact_age
------+---------------------+--------------+----------+-----------------
 3437 | idle in transaction |          789 |        1 | 00:00:07.694148
(1 row)

Terminal 2 (PID 3437) is idle in transaction with backend_xmin 789, exactly the removable cutoff VACUUM reported. age() gives how many XIDs old the value is. On this quiet lab server it is 1; on a busy production server an abandoned session can reach millions. pid <> pg_backend_pid() hides your own session from the list.

To release it, ask the application owner to commit or roll back, or end the session with pg_terminate_backend(3437) as shown in Part 10. In the lab, just end the transaction:

COMMIT;

VACUUM (VERBOSE) accounts;

INFO:  vacuuming "mvcclab.public.accounts"
INFO:  finished vacuuming "mvcclab.public.accounts": index scans: 0
pages: 0 removed, 1 remain, 1 scanned (100.00% of total), 0 eagerly scanned
tuples: 3 removed, 3 remain, 0 are dead but not yet removable
removable cutoff: 790, which was 0 XIDs old when operation ended
new relfrozenxid: 789, which is 7 XIDs ahead of previous value
...
VACUUM

Now VACUUM removed the 3 dead tuples. The relfrozenxid line is about transaction ID freezing, which Part 14 covers.

Check it worked: run the pg_stat_user_tables query from Step 12 again. It now shows n_dead_tup = 0.

Long open transactions are not the only thing that can hold the horizon back. Old prepared transactions (pg_prepared_xacts) and stale replication slots (pg_replication_slots) can do the same; later parts on wraparound and replication show how to find them.

Step 15: See what VACUUM and HOT left on the page

Where: Terminal 1, as postgres (pageinspect needs a superuser).

SELECT lp, lp_flags, lp_off, t_xmin, t_xmax, t_ctid
  FROM heap_page_items(get_raw_page('accounts', 0));
SELECT xmin, xmax, ctid, * FROM accounts ORDER BY id;

 lp | lp_flags | lp_off | t_xmin | t_xmax | t_ctid
----+----------+--------+--------+--------+--------
  1 |        2 |      7 |        |        |
  2 |        2 |      5 |        |        |
  3 |        2 |      8 |        |        |
  4 |        0 |      0 |        |        |
  5 |        1 |   8152 |    789 |      0 | (0,5)
  6 |        0 |      0 |        |        |
  7 |        1 |   8112 |    789 |      0 | (0,7)
  8 |        1 |   8072 |    789 |      0 | (0,8)
(8 rows)

 xmin | xmax | ctid  | id | owner | balance
------+------+-------+----+-------+---------
  789 |    0 | (0,7) |  1 | Asha  |    4100
  789 |    0 | (0,5) |  2 | Ravi  |    3100
  789 |    0 | (0,8) |  3 | Meera |    8100

This is the result of HOT (heap-only tuple) updates. An update is HOT when it changes no indexed column (we only changed balance, and the only index is on id) and the new version fits on the same page. With HOT, PostgreSQL does not add new entries to the primary key index. The index still points to items 1, 2 and 3, and those items are now redirects (lp_flags 2) to the current versions in items 7, 5 and 8 (lp_off holds the target item number for a redirect). Items 4 and 6 held earlier row versions that nobody needs any more; they were removed completely and are unused (lp_flags 0), ready for new rows.

HOT matters for performance. Updates that also change an indexed column must add index entries for every new version, which costs more and leaves more to clean up. Compare n_tup_hot_upd with n_tup_upd in pg_stat_user_tables to see how often your updates are HOT.

Step 16: Clean up the lab

Where: quit psql in Terminal 2 with \q. Then, in Terminal 1, connect to another database and drop the lab database.

\c postgres
DROP DATABASE mvcclab;

You are now connected to database "postgres" as user "postgres".
DROP DATABASE

If Terminal 2 is still connected you get ERROR: database "mvcclab" is being accessed by other users with DETAIL: There is 1 other session using the database. Quit the other session and try again. Check it worked: SELECT datname FROM pg_database WHERE datname = 'mvcclab'; returns 0 rows.

Common mistakes and how to fix them

Using ctid as a row identifier

ctid looks like a handy unique address, but Step 3 showed it change from (0,1) to (0,4) after a single UPDATE. It also changes after VACUUM FULL. Code that saves a ctid and uses it later can hit the wrong row or no row. Fix: identify rows by the primary key. Use ctid only inside one statement, for example in a one-off duplicate cleanup.

Reading a nonzero xmax as "this row is deleted"

Step 8 left Ravi's row visible with xmax 785. A nonzero xmax only means some transaction tried to delete or lock the row; that transaction may still be running, or may have rolled back. Row locks such as SELECT ... FOR UPDATE also set xmax. Fix: do not build logic on xmax. If you are investigating, check the XID with pg_xact_status().

ERROR: could not serialize access due to concurrent update

This appears in Repeatable Read or Serializable transactions when you update or lock a row that someone else changed after your snapshot was taken (Step 11). It is expected behaviour, not a bug. Fix: the application must catch the error (SQLSTATE 40001), roll back and retry the whole transaction. If the code cannot retry, use Read Committed for that work.

ERROR: must be superuser to use raw page functions

You get this when a normal role calls get_raw_page. Fix: run pageinspect queries as the postgres superuser on a lab or test server. Do not hand out superuser just for this.

ERROR: column name "xmin" conflicts with a system column name

System column names are reserved in every table, and quoting them does not help. CREATE TABLE audit_log (id integer, xmin integer); fails with this error. Fix: choose another name, such as min_value.

Dead rows keep growing even though autovacuum runs

VACUUM reports "dead but not yet removable" and the table keeps growing. The usual cause is a session that has sat idle in transaction for hours. Fix: run the backend_xmin query from Step 14, end the oldest session, and set idle_in_transaction_session_timeout for application roles, as shown in Part 10. Then check pg_prepared_xacts and pg_replication_slots for old entries.

Calling pg_current_xact_id() in read-only code

pg_current_xact_id() assigns a new XID if the transaction does not have one yet, so calling it in a read-only transaction uses up an XID for nothing. Fix: use pg_current_xact_id_if_assigned(), which returns NULL instead of assigning one. (The older txid_current() function does the same as pg_current_xact_id() and is kept only for backward compatibility.)

Practice exercise

1. In the lab table, open a transaction in Terminal 1 and run SELECT * FROM accounts WHERE id = 3 FOR UPDATE;. Without committing, read xmin and xmax of that row from Terminal 2. What do you see, and why?
2. Update Meera's row three times in a row (three separate autocommit UPDATEs), then use heap_page_items to count how many tuples are on page 0 before and after a VACUUM.

Quick reference

# Open psql as the postgres user (use two terminals for the MVCC tests)
sudo -u postgres psql mvcclab

# Read the hidden system columns of every row
sudo -u postgres psql -d mvcclab -c "SELECT xmin, xmax, ctid, * FROM accounts ORDER BY id;"

# Show the current snapshot as xmin:xmax:xip_list
sudo -u postgres psql -d mvcclab -c "SELECT pg_current_snapshot();"

# Check whether a transaction committed or aborted (use an XID from your own tests)
sudo -u postgres psql -d mvcclab -c "SELECT pg_xact_status('783');"

# List every tuple on page 0, including dead ones (superuser, lab only)
sudo -u postgres psql -d mvcclab -c "CREATE EXTENSION IF NOT EXISTS pageinspect;"
sudo -u postgres psql -d mvcclab -c "SELECT lp, lp_flags, t_xmin, t_xmax, t_ctid FROM heap_page_items(get_raw_page('accounts', 0));"

# Live and dead tuple counts, total and HOT updates
sudo -u postgres psql -d mvcclab -c "SELECT n_live_tup, n_dead_tup, n_tup_upd, n_tup_hot_upd FROM pg_stat_user_tables WHERE relname = 'accounts';"

# Vacuum one table with a detailed report
sudo -u postgres psql -d mvcclab -c "VACUUM (VERBOSE) accounts;"

# Find the sessions holding the oldest snapshots (any database)
sudo -u postgres psql -c "SELECT pid, datname, state, backend_xmin, age(backend_xmin) FROM pg_stat_activity WHERE backend_xmin IS NOT NULL ORDER BY age(backend_xmin) DESC;"

# Remove the lab database (lab only; quit other sessions first)
sudo -u postgres psql -c "DROP DATABASE mvcclab;"

Summary and what's next

• PostgreSQL never updates a row in place: UPDATE writes a new tuple and stamps the old one's xmax
• xmin is the creating XID, xmax the deleting or locking XID, ctid the physical address that changes on update
• A snapshot (xmin:xmax:xip_list) decides which tuples a query sees, so readers never block writers
• Read Committed takes a snapshot per statement; Repeatable Read keeps one per transaction and can raise could not serialize access
• DELETE and ROLLBACK leave old tuples behind; commit status lives in pg_xact
• VACUUM can only remove dead tuples older than the oldest snapshot; find the blocker with backend_xmin in pg_stat_activity
• HOT updates avoid new index entries when no indexed column changes and the page has room

In Part 12 we look at VACUUM and autovacuum: how they work and how to tune them.