Postgres MVCC Explained: Row Versions, Dead Tuples and VACUUM

The disk fills up even though the row count stays flat. In Postgres MVCC, a DELETE or UPDATE leaves the old row on disk, and only a cleanup job called VACUUM can reclaim that space.

Picture a common setup. A food delivery app keeps one row per order in an orders table. The status is set four times: placed on INSERT, then accepted, picked up and delivered by three UPDATEs. The setup is made up, but the growth pattern is real.

Each status change writes a new copy of the row. So one delivered order has left three old copies behind. On a busy day of 10 lakh (1 million) orders, that is 30 lakh old copies waiting for cleanup.

If cleanup keeps up, the space is reused and nobody notices. If it falls behind, the table swells, queries read more pages and backups get bigger. This guide follows one order, number 101, through that whole life.

You need five words: transaction, transaction ID, tuple, page and snapshot. Each one gets a plain meaning here, tied to order 101.

• Transaction: a group of statements that succeed or fail together. A single UPDATE on its own is also a transaction.
• Transaction ID (XID): a number Postgres hands out from one counter for the whole server. A transaction gets one only when it first writes something.
• Tuple: one stored version of a row. Order 101 is one logical row, but after four status changes it has had four tuples.
• Page: the fixed-size block a table is stored in, usually 8 kB. A tuple's address, called its ctid, is its page number plus its slot on that page.
• Snapshot: a note a query takes when it starts, listing which transactions had finished. It decides which tuples that query is allowed to see.

Postgres MVCC is the way PostgreSQL lets many sessions read and write the same table at once. Instead of overwriting rows, it keeps several versions and shows each query the one that fits its snapshot.

The PostgreSQL docs put the benefit plainly: reading never blocks writing and writing never blocks reading. A report that scans orders does not wait for the app that is updating them, and the app does not wait for the report. Table-level commands such as VACUUM FULL are a separate story, covered later.

Here is an everyday picture. Think of a school register written in ink. You never rub out a mark; you strike it through and write the new mark below it, with the date.

Each line is a tuple, the date you wrote it is xmin, and the date you struck it through is xmax. A reader looking back at last Monday reads only the lines that were valid that day. Where the picture breaks: a register keeps every line forever, but Postgres removes struck lines once no reader can still need them.

What an UPDATE really writes

An UPDATE works like a DELETE plus an INSERT done in one step. The old tuple gets its xmax set to the updating transaction's ID. A new tuple is written with that same ID as its xmin.

The old tuple also stores a pointer, t_ctid, to the newer version. That forms a chain from oldest to newest, which you can see in the diagram below.

Watching versions with xmin, xmax and ctid

Every table has hidden system columns. You can select xmin, xmax and ctid by name, even though SELECT * never shows them. Run this in a test database, not on production.

CREATE TABLE orders (id int PRIMARY KEY, status text, rider text)
  WITH (autovacuum_enabled = off);
INSERT INTO orders VALUES (101, 'placed', NULL);
SELECT ctid, xmin, xmax, id, status FROM orders;
-- (0,1) | 804 | 0 | 101 | placed

UPDATE orders SET status = 'accepted' WHERE id = 101;
SELECT ctid, xmin, xmax, id, status FROM orders;
-- (0,2) | 805 | 0 | 101 | accepted

The autovacuum_enabled = off setting only stops background cleanup on this demo table, so you can watch the old tuples. Your XID numbers will differ. What should match is the pattern: the ctid moved and xmin changed to the updating transaction's ID.

The plain SELECT shows only the live tuple. To see the old one too, use pageinspect, an extension from PostgreSQL's standard contrib set that reads raw pages. Some packages install it separately, and it needs a superuser.

CREATE EXTENSION IF NOT EXISTS pageinspect;
SELECT lp, lp_flags, t_xmin, t_xmax, t_ctid
FROM heap_page_items(get_raw_page('orders', 0));
-- lp | lp_flags | t_xmin | t_xmax | t_ctid
--  1 |        1 |    804 |    805 | (0,2)
--  2 |        1 |    805 |      0 | (0,2)

Slot 1 is the placed tuple, now stamped with xmax 805 and pointing to slot 2. Slot 2 is the accepted tuple. Both sit on page 0, taking space.

Now finish the order with the last two status changes. The rider column gets a value at pickup. The next sections use the table in this state.

UPDATE orders SET status = 'picked_up', rider = 'R-17' WHERE id = 101;
UPDATE orders SET status = 'delivered' WHERE id = 101;

In simple terms, a tuple is visible when its creating transaction counts as finished and committed in your snapshot. Its xmax must be empty, rolled back, or still unfinished for you. Your own transaction's earlier changes are visible to you too.

You can look at a snapshot with pg_current_snapshot(). It prints three parts joined by colons: xmin, xmax and a list of transactions still running. In our run it printed 750:750: with an empty list.

• Snapshot xmin: the lowest transaction ID still running. Anything below it has either committed or rolled back.
• Snapshot xmax: one past the highest finished transaction ID. Anything at or above it counts as not finished, so its changes are invisible.
• In-progress list: IDs between the two that were still running, so their changes are invisible too.

These snapshot fields share names with the tuple stamps but mean different things. Tuple xmin and xmax say who created and replaced a version. Snapshot xmin and xmax mark the edges of which transactions count as finished.

How long a snapshot lasts depends on the isolation level. Under the default READ COMMITTED, each statement takes a fresh snapshot. Under REPEATABLE READ, the first statement's snapshot is kept until the transaction ends. The guide on transaction isolation levels covers what each level allows.

Because some other transaction might still need the old version. Postgres cannot know at write time whether a report that started a second ago is about to read order 101 as placed.

So DELETE only stamps xmax, and UPDATE stamps xmax and writes a new tuple. Once the change commits, the old tuple counts as a dead tuple. VACUUM can remove it only after every snapshot that could still see it has gone.

You can count them per table in pg_stat_user_tables. After the three UPDATEs in our test, it showed one live tuple and three dead ones.

SELECT relname, n_live_tup, n_dead_tup, n_tup_upd, n_tup_hot_upd,
       last_vacuum, last_autovacuum
FROM pg_stat_user_tables
WHERE relname = 'orders';
-- orders | 1 | 3 | 3 | 3 | (null) | (null)

These numbers lag a little. Each session sends its counts when it goes idle, at most once a second, so a transaction still running is not counted yet.

Dead tuples are not just wasted disk. Sequential scans still read the pages that hold them. Index entries that point at them can linger too, so index scans may visit tuples only to skip them.

Plain VACUUM removes dead tuples and marks their space free for new tuples in the same table. VACUUM FULL writes a fresh, compact copy of the table and gives the old space back to the operating system.

Run VACUUM (VERBOSE) on the orders table and read the tuples line. In our run it said: 3 removed, 1 remain, 0 are dead but not yet removable. The next section shows when that last number is not zero.

VACUUM (VERBOSE) orders;
-- tuples: 3 removed, 1 remain, 0 are dead but not yet removable

We also tested both on a 1,00,000-row table with a 100-character note column. A full-table UPDATE grew the table file from 13 MB to 27 MB. The results are in the table below.

The last two rows show the one exception. Plain VACUUM can shrink the file only when whole pages at the end of the table are empty. It also needs a brief exclusive lock to do that.

Locks are the real difference. Plain VACUUM takes a SHARE UPDATE EXCLUSIVE lock, and in our test an UPDATE ran normally while it worked. VACUUM FULL takes an ACCESS EXCLUSIVE lock, which blocks even plain reads.

In our test, VACUUM FULL first waited for an open reader to finish. While it waited, a brand new SELECT queued behind it and timed out. VACUUM FULL also needs spare disk for the new copy until it finishes.

Autovacuum, the background process that runs VACUUM for you, cleans a table once its dead tuples pass 50 plus 20 percent of its row count. Those are the defaults as of 23 October 2026.

The 50 is autovacuum_vacuum_threshold and the 20 percent is autovacuum_vacuum_scale_factor (0.2). The row count is the planner's estimate in pg_class.reltuples. The launcher checks each database once per autovacuum_naptime, one minute by default.

We checked the formula on PostgreSQL 16.15 with a 1,000-row table, so the trigger was 50 + 0.2 x 1,000 = 250. With 240 dead tuples nothing happened. At 260, autovacuum ran within seconds and n_dead_tup dropped to 0.

We lowered autovacuum_naptime to one second only to make that test fast. Leave it at the default unless you have measured a reason.

Why big tables wait too long

The 20 percent part grows with the table. Say the orders table holds 1 crore (10 million) rows. Autovacuum then waits for about 20 lakh (2 million) dead tuples before it starts.

That is a lot of bloat to carry. The usual fix is a per-table setting, which overrides the server default for one table only.

ALTER TABLE orders SET (
  autovacuum_vacuum_scale_factor = 0.02,
  autovacuum_vacuum_threshold = 1000,
  fillfactor = 90
);

With these values, a 1-crore-row table is cleaned after about 2,01,000 dead tuples instead of 20 lakh. The fillfactor part is explained in the HOT section below.

What changed in PostgreSQL 18

PostgreSQL 18 added autovacuum_vacuum_max_threshold, with a default of 100,000,000 dead tuples. The trigger becomes the smaller of that cap and the old formula, so huge tables do not wait forever.

With the default scale factor, the cap only matters above roughly 50 crore (500 million) rows. PostgreSQL 16 and 17 have no such cap. As of 23 October 2026, PostgreSQL 18 is the newest major release, and PostgreSQL 19 is at release candidate 1.

Autovacuum also has insert-based triggers, autovacuum_vacuum_insert_threshold (1,000) and its scale factor (0.2). In PostgreSQL 18 that scale factor applies only to the part of the table not yet frozen, a term explained in the wraparound section.

VACUUM can remove a dead tuple only if no running transaction could still see it. One old snapshot, or one old transaction ID, holds back cleanup for every table in the database.

The VERBOSE output shows this as tuples that are dead but not yet removable. It also prints a removable cutoff, the oldest transaction ID cleanup must respect.

We tested four kinds of open session on PostgreSQL 16.15. In each case a second session updated all 1,000 rows of a table, and then we ran VACUUM while the first session was still open. Guess which ones blocked cleanup before you read the results.

• REPEATABLE READ transaction that ran one SELECT, then sat idle: blocked. All 1,000 dead tuples were not yet removable, because the transaction keeps its snapshot.
• READ COMMITTED transaction that ran one SELECT, then sat idle: did not block. Between statements it held no snapshot and had no transaction ID.
• READ COMMITTED transaction that inserted one row into a different table, then sat idle: blocked. Its transaction ID was still open, so cleanup could not pass it.
• A single SELECT still running for five seconds: blocked while it ran. A statement keeps its snapshot until it finishes.

In every blocked case, the same VACUUM removed all 1,000 tuples right after the open session ended. The third case surprises people, because the open write touched a completely different table.

Three less obvious holders do the same thing, according to the PostgreSQL docs. They are old prepared transactions, stale replication slots, and replicas running with hot_standby_feedback on.

Finding the session that blocks cleanup

Look for sessions with an old backend_xmin, the oldest transaction ID its snapshots still need, or an old backend_xid, its own transaction ID. The age() function turns either into how many transactions old it is.

SELECT pid, state, xact_start, backend_xid, backend_xmin,
       age(backend_xmin) AS xmin_age
FROM pg_stat_activity
WHERE (backend_xid IS NOT NULL OR backend_xmin IS NOT NULL)
  AND pid != pg_backend_pid()
ORDER BY xact_start;

SELECT slot_name, active, age(xmin) AS xmin_age, age(catalog_xmin) AS catalog_xmin_age
FROM pg_replication_slots;

SELECT gid, prepared, age(transaction) AS xid_age FROM pg_prepared_xacts;

A row with state idle in transaction and an old xact_start is the classic culprit. It is often an app that opened a transaction and then waited on a slow network call.

To stop it happening again, set idle_in_transaction_session_timeout, which ends sessions left idle inside a transaction. Its default is 0, meaning off. PostgreSQL 17 and later also offer transaction_timeout, which caps a whole transaction.

ALTER ROLE app_user SET idle_in_transaction_session_timeout = '5min';

Here app_user stands for your application's database role. New sessions for that role pick up the setting, and a session that breaks the limit is ended, so the app must reconnect and retry.

A HOT (heap-only tuple) update writes the new tuple on the same page and adds no new index entry. Postgres can do this only when the update changes no indexed column and the page has room.

BRIN indexes, which store summaries of block ranges, are the exception. Since PostgreSQL 16, changing a column covered only by BRIN still allows a HOT update.

In the orders table only id is indexed. So all three status updates were HOT, and pg_stat_user_tables showed n_tup_hot_upd equal to n_tup_upd. The pageinspect flags showed HEAP_HOT_UPDATED on the old tuples and HEAP_ONLY_TUPLE on the new ones.

When we changed id, the indexed column, the update was not HOT. That update needed a new index entry as well as a new tuple.

HOT has a second benefit. The docs say middle versions in a HOT chain can be removed during normal work, even by SELECTs, without waiting for VACUUM. After our VACUUM, slot 1 became a redirect pointing straight to the live tuple in slot 4. Slot 1 stays because the index entry still points at it.

Lowering fillfactor makes HOT more likely. A fillfactor of 90 tells INSERTs to fill pages only to 90 percent, keeping room for updated tuples on the same page. The default is 100.

Transaction IDs are 32-bit numbers, so they run out after about 4 billion and start again. Without care, very old rows would suddenly look like they came from the future and vanish from queries.

Postgres compares XIDs in a circle: about 2 billion IDs count as the past, and 2 billion as the future. The docs say every table must be vacuumed at least once every 2 billion transactions.

The fix is freezing. VACUUM marks old tuples as frozen, meaning visible to every current and future transaction. A frozen tuple no longer depends on its xmin number.

In our test, VACUUM (FREEZE) set the frozen flag on a tuple, and the table's relfrozenxid moved forward. The xmin column still showed the old number. Since version 9.4, freezing sets a flag bit and keeps the original xmin.

Each table records relfrozenxid, the oldest unfrozen XID it may hold. When its age passes autovacuum_freeze_max_age, 200 million by default, autovacuum runs an anti-wraparound vacuum. It does this even if autovacuum is turned off.

SELECT datname, age(datfrozenxid) AS xid_age
FROM pg_database ORDER BY xid_age DESC;

SELECT relname, age(relfrozenxid) AS xid_age
FROM pg_class WHERE relkind = 'r'
ORDER BY xid_age DESC LIMIT 5;

With default settings, autovacuum freezes each table as its age nears 200 million, so on a healthy server these ages usually stay near or below that. If they keep climbing past that, something is blocking cleanup, usually one of the holders from the earlier section.

The docs describe the last two alarms. Up to PostgreSQL 18, Postgres logs a warning with about 40 million transactions left; the draft PostgreSQL 19 notes raise this to 100 million. Under 3 million, it refuses to hand out new transaction IDs. Only read-only transactions can start, but VACUUM still runs normally, and that is how you recover.

Start from the cause, not the symptom. Running VACUUM FULL on a swollen table helps for a day, but the table swells again if the reason cleanup fell behind is still there.

1. 1

   Measure the table and its dead tuples

   Check pg_relation_size for the table and n_dead_tup in pg_stat_user_tables. Note last_autovacuum, so you know whether cleanup runs at all.

2. 2

   Run VACUUM (VERBOSE) once

   Read the tuples line. A large number of tuples dead but not yet removable means something is holding cleanup back.

3. 3

   Find the holder

   Query pg_stat_activity, pg_replication_slots and pg_prepared_xacts for old xmin or XID ages. End or fix the oldest one.

4. 4

   Tune autovacuum for hot tables

   Lower autovacuum_vacuum_scale_factor per table for big, busy tables. Check that HOT updates are common and drop unused indexes on changing columns.

5. 5

   Add timeouts

   Set idle_in_transaction_session_timeout for application roles, so a forgotten transaction cannot hold cleanup for hours.

6. 6

   Shrink only if you must

   Plain VACUUM lets new rows reuse the space, which is often enough. Use VACUUM FULL only in a planned window, since it blocks every read and write on that table. The draft PostgreSQL 19 notes add REPACK (CONCURRENTLY), which rebuilds a table without blocking reads and writes.

Lead with the trade-off: MVCC buys non-blocking reads, and pays for them with old versions that need cleanup. Then walk through one row's life, as below.

1. 1

   State the goal

   Readers and writers should not wait for each other. Each query should see a consistent picture of the data.

2. 2

   Describe the mechanism

   Updates write new tuples stamped with xmin and xmax. A snapshot decides which tuple each query sees.

3. 3

   Name the cost

   Old tuples become dead tuples. They take disk, slow scans and keep index entries until VACUUM removes them.

4. 4

   Explain cleanup

   Autovacuum triggers at 50 plus 20 percent of rows by default. Tune it per table for large, busy tables.

5. 5

   Show the failure mode

   A long-running statement, or an idle transaction holding a snapshot or a transaction ID, holds back cleanup for the whole database. Timeouts and monitoring limit it.

6. 6

   Close with limits

   Mention HOT updates for cheaper updates and freezing to avoid transaction ID wraparound.