MVCC and VACUUM: Why Deleting a Million Rows Made Your…

Prefer to watch? ▶ The 2-minute version ✈ Telegram

Someone deletes a million old rows to free up disk. They watch the table afterwards and it is bigger than before.

Nothing is broken. They just met the machinery that lets every read in the database run without ever waiting for a write — and got the invoice for it.

To see why, you have to start one level below the concept everybody quotes.

The floor: an UPDATE does not overwrite the row

Here is the thing that explains all of it.

When your database runs an UPDATE, it does not go to that row on disk and change the bytes. It writes a new version of the row somewhere else, and stamps the old one dead as of transaction 91.

Both versions are now sitting in the table.

Why go to that trouble? Because of a query that started a second ago and is still running. That query must keep seeing the world it started in. If the update had overwritten the bytes, the reader would either have to be blocked until the writer finished, or watch its own data change underneath it mid-scan.

So the database keeps both, and lets each reader figure out which one is theirs.

Every row therefore carries two hidden columns you never wrote:

xmin — the transaction that created this version.
xmax — the transaction that killed it (empty while it is still live).

You can see them:

SELECT xmin, xmax, id, status FROM orders WHERE id = 4711;

 xmin | xmax |  id  | status
------+------+------+---------
   87 |   91 | 4711 | pending
   91 |    0 | 4711 | shipped

One logical row. Two physical rows. This is the whole idea, and everything below is a consequence of it.

Snapshots: how a reader knows which version is its own

Each query — more precisely, each transaction, depending on your isolation level — takes a snapshot. A snapshot is essentially just a number plus a short list of transactions that were still in flight when it was taken.

The visibility rule is then almost embarrassingly simple. A row version is visible to you if:

its xmin committed before your snapshot, and
its xmax is empty, or belongs to a transaction that had not committed before your snapshot.

Run that against the two versions of order 4711 above. A reader with snapshot 89 sees version (87, 91) — created before it, killed by a transaction that had not happened yet — so it reads pending. A reader with snapshot 95 sees (91, 0) and reads shipped. Same table, same instant, two different truths, both correct.

Nothing locked. That is the payoff, and it is worth saying precisely, because the slogan gets repeated without its meaning:

Readers never block writers, and writers never block readers.

A reader never waits for a writer, because the version the reader needs is still physically sitting there. A writer never waits for a reader, because it is appending a new version rather than fighting for the old one. Two writers touching the same row still serialise — MVCC does not make write conflicts disappear — but the read path, which is most of your traffic, stops queueing entirely.

That is a genuinely large win. Now the bill.

A DELETE frees nothing

If an UPDATE is "insert new, stamp old", what is a DELETE?

It is only the stamp. The database writes xmax = 104 onto the row and moves on. It does not free a byte. It cannot — some transaction older than 104 may still legitimately need to read that row.

So DELETE FROM orders WHERE created_at < '2024-01-01' on a million rows produces:

a million rows that are now dead tuples — invisible to every new query, still fully present on disk,
a WAL record for each one,
and, on a table that was also taking ordinary update churn, a total of something like 3,400,000 dead tuples.

Live rows: unchanged at 2,000,000. File on disk: 1.2 GB → 4.8 GB.

That is the moment people file the bug report.

Operation	What happens on disk	Space freed immediately
`INSERT`	New version written, `xmin` = your txn	—
`UPDATE`	New version written and old one stamped `xmax`	None — the table grows by one row
`DELETE`	Old version stamped `xmax`. Nothing else.	None
`VACUUM`	Dead tuples marked reusable inside the same file	None returned to the OS; space is reusable internally
`VACUUM FULL`	Whole table rewritten into a fresh file	All of it — under an `ACCESS EXCLUSIVE` lock

Bloat charges you twice

The disk number is the visible half. The other half is worse:

Scans read the corpses. A sequential scan has to walk 4.8 GB of pages to find 1.2 GB of live rows. Your buffer cache fills with dead tuples. The query that used to fit in memory now does not.
Indexes still point into them. Every index entry for a dead tuple is still an index entry. The index grows, gets deeper, and every lookup that lands on a dead pointer has to go to the heap to find out it was wasted work.

So the table did not merely get bigger. It got slower in two independent ways, and neither of them shows up as an error.

VACUUM: the janitor, and the part everyone misses

The cleanup job is VACUUM, usually running automatically as autovacuum. What it does is scan for tuples that nobody can see any more and mark that space as reusable.

Run it and the dead-tuple count drops to zero. Then you check the file size, and it is still 4.8 GB.

This is the single most misunderstood fact about the whole system:

🔴 VACUUM reclaims space for reuse inside the same file. It does not hand it back to the disk.

Which is the right trade, once you see the alternative. Handing space back to the operating system means moving live rows down to fill the holes and truncating the file — that is VACUUM FULL, and it rewrites the entire table while holding an ACCESS EXCLUSIVE lock. Nothing reads it. Nothing writes it. On a 40 GB table that is an outage, not a maintenance task. (pg_repack exists to do the same job online, at the cost of temporarily needing room for a second copy.)

For a table with steady churn, the ordinary VACUUM is what you actually want anyway. That 3.6 GB of reusable space is where the next 3.6 GB of updates will be written, instead of extending the file further. The table reaches a steady state at some size larger than its live set. That is not a leak. That is the working set of a versioned store.

The failure that actually pages people

Everything so far is fine. Here is the one that ruins a night.

A dead tuple can only be cleaned once nobody can still see it. That condition is global, not local. The database computes an oldest visible transaction horizon across the whole system, and refuses to remove any version newer than it — because some transaction out there might still legally read it.

So a single forgotten open transaction pins every dead row in the database.

An idle BEGIN; in a colleague's psql window from Tuesday. A connection-pool client that opened a transaction, hit an exception, and never issued a COMMIT or ROLLBACK. A read replica with hot_standby_feedback = on running a long analytics query, holding the primary's horizon back from the other end of the network. An abandoned replication slot.

And the symptom is not an error. The symptom is autovacuum running all night and reclaiming nothing, while the disk fills. You find out from the pager.

The first thing to look at is therefore not the table. It is the horizon:

-- who is holding the horizon back?
SELECT pid, state, backend_xmin,
       now() - xact_start AS txn_age, query
  FROM pg_stat_activity
 WHERE backend_xmin IS NOT NULL
 ORDER BY age(backend_xmin) DESC
 LIMIT 5;

-- replication slots do it too, without any session attached
SELECT slot_name, active, xmin, catalog_xmin FROM pg_replication_slots;

The oldest backend_xmin — usually a session sitting in idle in transaction — is your culprit far more often than any vacuum tuning parameter. Two defences worth having on by default: idle_in_transaction_session_timeout so a forgotten BEGIN cannot live forever, and an alert on the age of the oldest transaction rather than only on disk usage, so you hear about it hours before the disk does.

The same bill, in an AI stack

If your database is also your vector store — and for a lot of RAG systems it is, via pgvector — this stops being a Postgres trivia question and becomes a running cost, because embedding workloads are unusually good at generating dead tuples.

Re-embedding is an UPDATE storm. Swap the embedding model, or re-chunk a corpus, and you rewrite the embedding column on every row. Each one is insert-new-plus-stamp-old, on rows carrying a 1536-dimension vector — roughly 6 KB each before overhead. A million re-embedded chunks is gigabytes of dead tuples, generated in one batch job that looked like an in-place update.
The index pays the most. An HNSW or IVFFlat index is far more expensive per entry than a B-tree, and it inherits the same problem: entries for dead tuples are still entries, and a bloated vector index is both larger in RAM and slower to traverse. Recall stays correct; latency does not stay flat.
Long-running ingestion pins everything. A nightly job that opens one transaction and streams a corpus through it for two hours is, from the horizon's point of view, exactly the forgotten BEGIN. Batch the commits.
And the reason you wanted MVCC is still true. During a re-index, your retrieval traffic keeps reading the old versions without blocking. That is the feature. Just budget for the disk it costs, and monitor autovacuum on the embeddings table specifically — it is usually the one that needs a more aggressive autovacuum_vacuum_scale_factor than the default, because the default is tuned for small rows.

The general rule, whatever the workload: anything that rewrites a whole column across a whole table is a bloat event. Backfills, re-embeddings, schema migrations that touch every row, GDPR deletion sweeps. Plan the vacuum, not just the write.

The verdict

The alternative to MVCC is not "no cost". It is making writers wait for readers — a lock-based system, cheaper on disk and considerably more miserable to operate, where one slow report can stall your write path. MVCC is the better trade for very nearly everyone.

You just have to know what you are paying in, and it is three things: disk, index bloat, and a background job you are obliged to monitor.

So: do not fight the model. Do not reach for VACUUM FULL on a schedule, do not read a table that is larger than its live set as corruption, and do not tune autovacuum before you have checked whether anything can be vacuumed at all. Delete in batches with commits between them. Alert on the age of your oldest open transaction. Expect a churny table to settle at some multiple of its live size and treat that number as normal.

Your database never deletes anything at the moment you tell it to. It writes a tombstone and hires a janitor.

40 GB of table holding 2 GB of live rows is not corruption. It is the invoice for concurrency.

References and further reading

On the floor — row versions, xmin/xmax, and why an UPDATE is an insert

PostgreSQL Documentation, Ch. 13: Concurrency Control — the primary source for the visibility rules and what each isolation level actually promises; the definition this article's snapshot section is built on.
Bruce Momjian, MVCC Unmasked — the clearest walkthrough of the hidden system columns and how a row version is judged visible, with the on-disk picture drawn out.
Joseph M. Hellerstein, Michael Stonebraker & James Hamilton, Architecture of a Database System (Foundations and Trends in Databases, 2007) — where the storage layer and the transaction layer meet, and why version storage is a design axis rather than an implementation detail.

On snapshots and what they do and do not guarantee

Hal Berenson, Phil Bernstein, Jim Gray, Jim Melton, Elizabeth O'Neil & Patrick O'Neil, A Critique of ANSI SQL Isolation Levels (SIGMOD, 1995) — the paper that defined snapshot isolation formally and showed which anomalies it still permits. The right next read if this article left you wondering what a snapshot cannot protect you from.
Jim Gray & Andreas Reuter, Transaction Processing: Concepts and Techniques (Morgan Kaufmann, 1993) — the canonical treatment of the lock-based alternative, which is what makes the trade in the verdict concrete rather than rhetorical.

On the bill — dead tuples, bloat, and the janitor

PostgreSQL Documentation, §25.1: Routine Vacuuming — states directly that ordinary VACUUM makes space available for re-use rather than returning it to the operating system, and covers the autovacuum tuning knobs.
PostgreSQL Documentation: VACUUM — the FULL variant, its ACCESS EXCLUSIVE lock, and the disk-space requirement for the rewrite.
Yingjun Wu, Joy Arulraj, Jiexi Lin, Ran Xian & Andrew Pavlo, An Empirical Evaluation of In-Memory Multi-Version Concurrency Control (VLDB, 2017) — a systematic comparison of version storage, garbage collection and index-management schemes across MVCC implementations; the best single source for why garbage collection, not the versioning itself, is where these systems live or die.
pg_repack — the online alternative to VACUUM FULL, and its own trade-off: no long exclusive lock, but room for a second copy of the table.

On the AI/vector-store section

pgvector — index types, build and maintenance behaviour for HNSW and IVFFlat; the reference for why a re-embedding pass is an index event and not only a table event.

If a reference you'd expect is missing, say so in the comments and I'll add it.

Watch the reel: MVCC and VACUUM — why deleting a million rows made your table bigger