PostgreSQL MVCC Explained: Dead Tuples, VACUUM and Idle Transactions

So here is how MVCC works, in outline. Locking every row a transaction reads is not practical on a busy server: with thousands of transactions in flight, readers and writers would spend their time waiting for each other, and the hottest rows would set the pace for everything. PostgreSQL skips that. Each transaction works from a snapshot, a fixed view of the data as it stood when the transaction started (or, at the default settings, when each of its statements started). When a query changes a row, PostgreSQL writes a new version of the row and leaves the old one in place for any transaction whose snapshot still points at it. Readers see their version, writers write theirs, and nobody waits.

A quick reminder about transactions

Moving 100 from one account to another is two changes: subtract from one row, add to another. If the server dies between them, or a second transfer squeezes in between them, money is created or lost. Making an application guard against every such case on its own is hard, so databases take the job: a transaction is a group of statements the database promises to treat as one unit. You open one with BEGIN, and end it with COMMIT to keep every change or ROLLBACK to drop every change. A statement sent on its own is a transaction of one.

The promise has a well-known name, ACID, four guarantees that PostgreSQL makes for every transaction:

The third one is the hard one, because of how many transactions there are. Every program talking to the database holds a connection, which PostgreSQL calls a session; PostgreSQL allows 100 by default, busy systems run thousands, and at any moment many of them are inside a transaction touching the same tables. Keeping each of those transactions undisturbed by the others, without making them wait for each other, is the job MVCC does.

A change never overwrites the row

The tests in this post ran on PostgreSQL 18.6 in Docker, limited to 4 CPUs and 4 GB of memory, on an accounts table of 1,000,000 rows (an id, an owner, a balance, a timestamp and a note).

Two sessions, A and B. Session A starts a transaction set to keep one view of the data for its whole duration (how that is set is one of the things simplified away here) and reads account 42: the balance is 187.89. Session B adds 100 to the same account and commits. B’s update did not wait for A; it finished in 5.56 ms. B now reads 287.89, while A, reading again inside its transaction, still gets 187.89. Once A commits and reads again, it gets 287.89 like everyone else.

Both answers can be true at once because of how a row is stored. PostgreSQL keeps each table as a file of fixed-size pages, 8 kB each by default, and a page holds as many row versions as fit. Every row version carries three hidden columns, and they exist so that a reader can tell which version is meant for it without asking anyone. ctid is the version’s physical address (page number, position on the page), which is how an index entry points at it. xmin is the number of the transaction that created the version: every transaction that writes gets a number, handed out in order, so xmin says when the version came into being. xmax is the number of the transaction that replaced or deleted it, 0 while none has. They can be selected like any other column, and reading them alongside the balance showed the same account existing twice after B’s update:

1
2
3
4
5

SELECT ctid, xmin, xmax, balance FROM accounts WHERE id = 42;

-- session A, inside its transaction      -- session B, or any new session
--  ctid    | xmin | xmax | balance       --  ctid       | xmin | xmax | balance
-- (0,42)   |  754 |  756 |  187.89       -- (17241,18)  |  756 |    0 |  287.89

The new version, created by B’s transaction 756, went to page 17,241, near the end of the table. The old version stayed at (0,42), on page 0, now marked xmax 756: replaced by transaction 756.

Those stamps are what a snapshot is checked against. A snapshot is not a copy of the data but a record of which transactions had committed at a certain moment (documentation), and a query applies one rule to every version it meets: the version is visible if its xmin transaction counts as committed in the snapshot and its xmax transaction, if there is one, does not. That is the whole of MVCC’s reading side. A’s snapshot was taken before 756 started, so for A the version at (0,42) is visible and the one at (17241,18) is not; a new session’s snapshot has 756 as committed, and the same rule picks the other version.

How long a snapshot lives is the one setting that matters for the rest of this post. By default, every statement takes a fresh snapshot and drops it when it finishes, so an idle transaction holds none, and A’s second read would have seen B’s change. Session A asked for the other behaviour: one snapshot at its first statement, kept until the transaction ends. PostgreSQL has a whole setting, with four levels, for how much a transaction may see of others; this post simplifies it to those two behaviours, and the isolation levels post covers the rest.

Transactions look different in this light. A transaction does not hold its changes back until COMMIT; its versions go onto pages as it writes them, stamped with its number, and what COMMIT or ROLLBACK decides is whether that number counts for the snapshots taken afterwards. INSERT, UPDATE and DELETE all work through the two stamps, and none of them removes anything:

So an UPDATE is a delete and an insert done together: page 0 had no room for a second copy of account 42, so the new version went to the end of the table and the primary key index got a second entry pointing at it. (When no indexed column changes and the page has room, PostgreSQL keeps the new version on the same page and skips the index entry, a HOT update.) A DELETE writes no new version at all. The pageinspect extension shows what that leaves behind, because it lists every version on a page whether a query can see it or not. On a two-row table, after deleting row 2 and then inserting row 3 in a transaction that rolled back:

1
2
3
4
5
6
7
8
9

-- PostgreSQL 18.6
CREATE EXTENSION pageinspect;
SELECT lp, t_xmin, t_xmax, t_ctid FROM heap_page_items(get_raw_page('t', 0));

 lp | t_xmin | t_xmax | t_ctid
----+--------+--------+--------
  1 |    755 |      0 | (0,1)
  2 |    755 |    756 | (0,2)     -- deleted by 756, still on the page
  3 |    757 |      0 | (0,3)     -- inserted by 757, which rolled back

SELECT * FROM t returned only row 1. The other two had only stopped showing up in queries.

COMMIT and ROLLBACK, then, move no data. When transaction 756 committed, PostgreSQL rewrote neither page; it recorded that 756 had committed, in the transaction commit status data (pg_xact), and every snapshot taken after that counted it. A ROLLBACK records the opposite and leaves the versions where they are: a rolled-back INSERT leaves its row on the page, visible to nobody, and a rolled-back UPDATE leaves both versions behind, the new one invisible and the old one still current with a nonzero xmax.

Every version an UPDATE, a DELETE or a rolled-back transaction leaves behind is a real row on a real page, and nothing described so far ever removes one. MySQL’s InnoDB makes the opposite choice, changing the row in place and keeping the old value in a separate undo log, which the MySQL MVCC post measures on the same table.

Where PostgreSQL still waits

MVCC removes one kind of waiting, readers waiting for writers, and leaves the others in place. To see which, session A did something and then held its transaction open for 2 seconds before committing, while session B tried something else, each case on a fresh 1,000-row table:

The plain read returned the committed balance without waiting. Everything that wanted to change the row, or to lock it, waited until A committed: row-level locks “do not affect data querying; they block only writers and lockers to the same row” (documentation). SELECT ... FOR UPDATE is a locking read and behaves like a writer; it waited, then returned the value A had just committed rather than an older snapshot. (What B’s own update does after its wait is where lost updates come from, and the isolation levels post measures it.)

Every one of those waits lasted as long as A’s transaction, not A’s statement. Run once more with A’s update sent without BEGIN, so that it committed on its own, B waited 9.2 ms instead of 1,710; same statement, same idle connection afterwards. A row lock is held until the transaction that took it ends, and PostgreSQL stores it in the row version itself, in that same xmax field, which is why it “doesn’t remember any information about modified rows in memory, so there is no limit on the number of rows locked at one time.” It also locks only rows that exist: the insert of id 1001 went through even though A had locked everything above 990, because PostgreSQL has no locks on gaps. (MySQL at its default settings made the same insert wait the full 2 seconds.)

The last two rows involve no row lock. Session A had only read one row, and at the default settings an idle transaction holds no snapshot, so by everything above it should cost nothing. But every statement also takes a lock on the table itself, held until the transaction ends; a plain SELECT takes the weakest kind, ACCESS SHARE, and “only an ACCESS EXCLUSIVE lock blocks a SELECT”, which is the lock most forms of ALTER TABLE need. So the ALTER waited for idle A, and a plain read from a third session then waited behind the queued ALTER. An idle transaction plus a schema change is one of the few ways to make plain reads wait in PostgreSQL.

VACUUM frees the space but keeps the file

MVCC is not free, and the bill turns up in the table file. A version that no snapshot can see anymore is called a dead tuple (tuple is PostgreSQL’s word for a row version), or dead row, and it takes up space until something removes it. Update all million accounts in one statement and the table gets a million new versions while the million old ones stay where they were. On disk, that took the table from 135 MB to 269 MB.

VACUUM is the command that removes dead rows, and in production the autovacuum background process runs it on its own. I turned autovacuum off for these runs, the one setting changed from the defaults, so that each cleanup step happens only when I run it; what follows applies to autovacuum just the same.

The index doubled along with the table, one new entry for every new version. VACUUM removed the dead rows, but the file stayed 269 MB: it “marks the space available for future reuse” and “will not return the space to the operating system” (documentation), apart from empty pages at the very end. The second full update reused that space and the table did not grow again, so a table that is updated steadily settles at some size above its live data and stays there, as long as VACUUM keeps up. VACUUM FULL rewrote the table at its original size in half a second, but it takes an ACCESS EXCLUSIVE lock, so on a busy table that is downtime for as long as the rewrite runs.

VACUUM has a second job, and it comes from the size of those transaction numbers. xmin and xmax are 32-bit integers, so the counter reaches about four billion and wraps around to zero. PostgreSQL compares the numbers in a circle, with two billion counting as the past and two billion as the future at any moment, and the documentation spells out what happens to a version whose xmin is left behind as the counter moves on: “it will suddenly appear to be in the future”, so its row becomes invisible, “in short, catastrophic data loss.” The fix is freezing: VACUUM marks versions old enough that every snapshot must see them as frozen, which takes them out of the comparison, and autovacuum forces this on any table with unfrozen versions older than autovacuum_freeze_max_age (200 million transactions by default), even where autovacuum is otherwise off. If freezing falls behind, PostgreSQL warns when forty million numbers remain, and with three million left it stops assigning new ones: writes fail until a VACUUM has run. Sentry lost most of a working day to exactly that on 20 July 2015.

What can hold VACUUM back

VACUUM, auto or manual, is bound by the same visibility rule as every reader: it may remove a version only once no open transaction could still see it, and it cannot freeze one either. So an open transaction can hold old versions in place, and the question is which kinds count. Session A opens a transaction, does one small thing and then does nothing, the way an application can leave a connection sitting inside BEGIN, while session B makes 100,000 single-row updates to 1,000 accounts, each its own transaction. Then VACUUM counts the dead rows it is not yet allowed to remove.

The last row is what snapshot lifetime predicts: A’s snapshot predates every one of B’s updates, so every old version is one A might still ask for. The third row is the one to remember. A transaction that has written anything carries a transaction number, and PostgreSQL keeps every version retired by a later-numbered transaction until that one finishes, snapshot or no snapshot. Updating one unrelated row and then waiting pinned all 100,000 dead rows, and every lookup of those accounts, from any session, read 104 pages instead of 4 until A committed. The next VACUUM then removed them in 30 ms.

What to watch

Two numbers tell you whether MVCC’s bill is being paid. n_dead_tup in pg_stat_user_tables is the count of dead rows per table; it should rise and fall as autovacuum visits, and a table where it only rises is one autovacuum cannot clean. pg_stat_activity shows who is stopping it: a session in state idle in transaction with an old xact_start is session A, and backend_xid or backend_xmin being set means it is pinning versions. idle_in_transaction_session_timeout, off by default, ends such sessions after a time you choose. The habit behind both is committing before waiting on anything outside the database, and in PostgreSQL that includes transactions that have written something, even at the default settings.