How Does a Database Clean Up After Itself?

In the previous post we learned MVCC pull off something that sounds impossible: hundreds of transactions reading and writing the same table at once, none of them waiting on the others. The trick was that Postgres never overwrites a row. An UPDATE stamps the old version dead and appends a fresh one; a DELETE only stamps. Every transaction reads through its own tiny snapshot and sees exactly the version that was true for its moment in time.

We ended on a loose thread: all those dead versions are still sitting on disk. Committing didn't remove them. That's the bill MVCC runs up, and this post is about paying it.

We'll go through what those leftovers actually cost you (it's not what most people assume), what VACUUM does about them, how autovacuum decides when to bother, the one thing that can stop cleanup dead across your entire database, and the 32-bit counter that will shut Postgres down rather than let it lose your data.

The bill: dead versions pile up

Start with the blunt fact:

Dead versions do not vanish when a transaction commits. They stay in the table's pages, taking up space, until something comes along and reclaims them.

This is bloat — the table's files growing larger than the data actually in them. Note where the cost lands. As we saw last time, a SELECT creates nothing at all, so bloat comes entirely from the write side. And because an update copies the whole row, a one-byte change to a wide row still leaves a full-size corpse behind.

The natural reaction is "so it wastes some disk, and disk is cheap." That reaction is wrong, and the reason why is the most useful thing in this post.

Bloat is a performance cost, not just a storage cost

Here's the part that makes bloat matter: a scan physically reads the live rows plus every un-vacuumed dead version, and runs the visibility check on each one. Dead tuples aren't skipped for free — the engine has to read them to discover they're dead.

A table with 100 live rows, each dragging five old versions nobody cleaned up, holds 600 physical tuples. A sequential scan reads and visibility-checks all 600 to hand you 100 rows.

Six times the pages pulled off disk, six times the visibility arithmetic, for exactly the same answer. So the takeaway from last post gets an addendum: reads create nothing, but they pay the toll for dead versions nobody cleaned up. VACUUM is a performance feature, not a housekeeping chore.

Why this isn't fatal in practice

If that were the whole story every busy Postgres database would grind to a halt within a week. Three things keep it in check:

1. Cleanup bounds it. In a healthy table, autovacuum keeps dead versions trimmed, so the physical tuple count stays close to the live row count. The "100 rows, 600 tuples" blowup is what happens when cleanup can't keep up — and later in this post we'll see the single most common reason it can't. Steady state is a small percentage of dead tuples, not a 6× multiplier.
2. Indexes sidestep full scans entirely. WHERE id = 1 on an indexed column jumps more or less straight to the matching tuples, so you only pay the read-everything cost on full-table scans — not on the indexed lookups that make up most production queries.
3. Hint bits make each check cheap. The visibility rule needs to know whether a given transaction committed, which normally means consulting the commit log. The first transaction to examine a tuple after its creator or deleter committed writes a hint bit onto the tuple caching that answer, so every later scan skips the lookup. The per-tuple cost is small — it's the sheer count of tuples that hurts.

The failure mode

Put it together and you get the classic "my Postgres suddenly got slow" incident. Cleanup falls behind → bloat balloons → sequential scans read mostly garbage, and even indexed lookups slow down as index entries accumulate pointing at layers of dead tuples. The answer is almost always the same two suspects: check for bloat, and check for long-running transactions. We'll get to why those two are the same problem shortly.

VACUUM: taking out the garbage

VACUUM finds dead tuples — versions invisible to every possible current and future snapshot — and reclaims their space. That definition is doing quiet, heavy work in the phrase every possible snapshot, and we'll unpack it properly in a moment. First, what it does with what it finds, because there are two very different commands here and confusing them is a good way to take an outage.

Plain VACUUM — routine and gentle

This is what autovacuum runs constantly in the background, and what you get when you type VACUUM accounts;.

• It marks the space held by dead tuples as free for reuse.
• It takes only a light lock — normal reads and writes keep running alongside it.
• It usually does not shrink the file on disk, and does not hand space back to the operating system.

That last point is where people get confused, so let's be precise about it.

Where does the freed space actually go?

That free space does not be available to OS. Other table can't use it. It only be available for that table only. That table's new entry can only use that space.

A 10 GB table that frees 6 GB internally is still 10 GB on disk — but it can now absorb roughly 6 GB of new data before it has to grow again. The space is recycled internally, withing that table only, not returned to the global pool.

And that's usually completely fine. In steady state, new inserts and updates consume the space cleanup frees, the two roughly balance, and the table simply stops growing. A table that has "stabilised at 10 GB" isn't a problem to solve — it's the system working. (One exception worth knowing: if the dead space happens to sit in a run of completely empty pages at the very end of the file, plain VACUUM can chop the tail off and give that back to the global pool. It's opportunistic, and you can't count on your garbage being politely arranged at the end.)

VACUUM FULL — the heavy hammer

VACUUM FULL is a different operation wearing a confusingly similar name. It rewrites the entire table into a new, compact file with no dead space, swaps it in, and deletes the old one.

• It genuinely returns disk space to the OS — 10 GB can become 3 GB.
• It takes an ACCESS EXCLUSIVE lock, so nobody can read or write that table while it runs. Not "writes are slower." Nobody.
• It needs enough free disk to hold a second copy of the table during the rewrite.

Treat VACUUM FULL as a maintenance-window tool for one-off damage — you deleted 90% of a huge table and actually need the disk back. It is never the answer to routine bloat. And if you need the compaction without the outage, pg_repack does essentially the same job while keeping the table online.

Autovacuum: the part you actually tune

You rarely type VACUUM yourself. Autovacuum runs in the background: it wakes up every autovacuum_naptime (default: one minute) and asks each table a single question — "have you accumulated enough dead tuples to be worth cleaning?"

The threshold is a formula:

threshold = autovacuum_vacuum_threshold
          + autovacuum_vacuum_scale_factor × (estimated rows in table)

With the defaults — threshold 50, scale factor 0.2 — a table gets vacuumed once its dead tuples exceed 50 + 20% of its row count. The running count lives in pg_stat_user_tables.n_dead_tup.

The scale-factor trap

That 20% is a fraction, and fractions scale with the thing they multiply. Read the formula again with a big table in mind:

• 1,000-row table → vacuum at ~250 dead tuples. Sensible.
• 1,000,000,000-row table → vacuum at ~200 million dead tuples. Catastrophic.

A billion-row table on defaults is allowed to accumulate two hundred million dead versions before anyone lifts a finger. Every sequential scan is dragging that carcass around the whole time.

The fix is per-table, and it's one line:

ALTER TABLE big_hot_table SET (autovacuum_vacuum_scale_factor = 0.02);

Lower the scale factor on your big, hot tables so they get cleaned eagerly instead of waiting for a percentage that only made sense when the table was small.

The other knobs worth knowing

• autovacuum_vacuum_cost_limit / autovacuum_vacuum_cost_delay — throttle how hard cleanup hits your disks. The defaults are deliberately timid; on capable hardware, raising the cost limit lets vacuum actually finish rather than falling permanently behind.
• autovacuum_max_workers — how many tables can be vacuumed in parallel. If you have many large tables, three workers is a queue.
• autovacuum_vacuum_insert_threshold — triggers vacuum on insert-heavy, rarely-updated tables too. There's barely any garbage in an append-only table, but there's still freezing to do, which is the second half of this post.
• autovacuum_freeze_max_age — forces a vacuum for wraparound safety no matter how few dead tuples there are. This one is a safety net, not a tuning dial.

The goal of tuning: vacuum often enough that bloat stays small and freezing keeps pace, but gently enough that it doesn't starve your queries of I/O. Big and hot → be aggressive. Giant and static → mostly just care about the freeze schedule.

The xmin horizon: why VACUUM sometimes can't delete anything

Now back to that phrase we skipped: cleanup can only remove versions that are dead to every possible snapshot. How would it possibly know that?

The naive approach is to ask. For each dead tuple, walk every running transaction and check whether it still needs this particular version. With thousands of connections and millions of row versions, that's an absurd amount of work to redo continuously.

Postgres doesn't do that. It uses one number.

One number per connection, then take the minimum

1. Every live backend has a slot in shared memory, surfaced to you through pg_stat_activity.
2. Each backend advertises a single value: the xmin of its current snapshot — the oldest transaction ID whose effects it might still need to see.
3. Postgres takes the minimum of every advertised xmin, across all backends, prepared transactions, and replication slots. That minimum is OldestXmin — the vacuum horizon.
4. When cleanup runs, it doesn't inspect who's doing what. It compares each dead tuple against that one number.

That's what makes it cheap: a single MIN() over a shared array, computed once, instead of a per-tuple interrogation of every transaction in the system.

The rule

A dead tuple may be removed only if the transaction that killed it is older than the horizon.

xmax < OldestXmin → safe to remove xmax >= OldestXmin → must be kept

If even one transaction's advertised xmin sits at or below a tuple's xmax, that transaction's snapshot predates the deletion — meaning it could still legitimately read the old version. Cleanup leaves it alone.

A worked example

Three transactions are running right now:

min(100, 400, 600) = 100. The horizon is set entirely by the migration job, because it's the oldest thing still alive.

Now check two dead versions:

• A row killed by transaction 60. 60 < 100 → below the horizon. Every live snapshot already treats that deletion as settled history. Reclaimed.
• A row killed by transaction 300. 300 >= 100 → above the horizon. The migration job's snapshot predates the deletion, so it might still need to read the pre-delete version. Kept.

That second row stays wedged in the table, bloating it, for as long as the migration job keeps running — no matter how many thousands of unrelated transactions have long since finished with it.

The failure mode: one forgotten transaction

Here's the sting. Anything that advertises an old xmin and refuses to let it go drags the horizon backwards, and cleanup across the entire database loses the ability to remove anything newer than that point. The usual culprits:

• An idle-in-transaction session — somebody ran BEGIN; and walked away without committing or rolling back.
• A long-running analytics query — a multi-hour report pinning one old snapshot the whole time.
• A prepared transaction (two-phase commit) left uncommitted — and this one won't even show up in pg_stat_activity.
• A stalled replication slot — physical with hot_standby_feedback, or logical decoding — pinning a catalog_xmin in exactly the same way.

One forgotten BEGIN; can bloat your entire database, even though the transaction itself touches nothing.

That's the connection promised earlier: "check for bloat" and "check for long-running transactions" aren't two separate debugging steps. The second one is usually the cause of the first.

How to catch it

Find the backends holding an old snapshot:

SELECT pid, state, xact_start, backend_xmin,
       now() - xact_start AS age, query
FROM pg_stat_activity
WHERE state IN ('idle in transaction', 'active')
ORDER BY backend_xmin;

The row at the top — lowest backend_xmin, oldest xact_start — is your suspect.

Then check the two culprits that don't appear in that view at all:

-- Prepared (two-phase commit) transactions left uncommitted
SELECT * FROM pg_prepared_xacts;

-- Replication slots holding back the horizon
SELECT slot_name, active, xmin, catalog_xmin FROM pg_replication_slots;

The fix

• Commit or roll back promptly. Never hold a transaction open across user think-time or a slow external API call.
• Set idle_in_transaction_session_timeout so forgotten sessions get killed automatically.
• Monitor replication slots and clean up ones that are unused or stalled.
• Run long analytics queries on a replica — or at minimum, know that they're pinning the horizon while they run.

Transaction ID wraparound: VACUUM's other job

Everything up to here has been about space. This last part is about correctness — and it's the reason VACUUM isn't optional even on a table you write once and never touch again.

Every tuple carries xmin and xmax, and every visibility decision is a comparison between those numbers and your snapshot: did this transaction happen before that one? Every row you've ever read was handed to you on the strength of that comparison.

Now the uncomfortable detail. Those numbers are 32-bit. There are only about 4.2 billion of them — a finite supply, handed out one at a time, and a busy database works through them faster than you'd guess.

Which raises a question with no comfortable answer: what happens to the comparison when the supply runs out? To see why that's dangerous rather than merely tidy, it helps to know exactly how a transaction gets its number in the first place.

Where the numbers come from

A transaction grabs the next number from an ever-increasing counter. It's worth spelling out properly now, because the specifics are what make the ending dangerous.

There is one XID counter for the entire Postgres instance — not one per table, not one per database. It lives in shared memory, and it only ever counts upward: hand out 100, then 101, then 102, and never look back.

A transaction takes a number from it lazily, on its first write — the moment it actually needs to stamp xmin on a tuple. That detail matters more than it looks:

• BEGIN; UPDATE accounts … — has to label the new row version, so it draws an XID.
• BEGIN; SELECT …; COMMIT; — never writes anything, so it never needs a permanent label. Postgres gives it a temporary internal id instead, and it consumes no XID at all.

So the counter is driven by your write traffic, not your query volume. A reporting replica grinding through a million SELECTs doesn't advance it by one.

Writes add up quickly, though. Push 1,000 write-transactions per second through a database and 4.2 billion XIDs is roughly fifty days of runway. At ten thousand a second it's five. Lapping the counter isn't a theoretical edge case — busy systems do it routinely.

What happens when the counter hits the end

Eventually the counter hands out 4,294,967,295 — the largest value 32 bits can hold. There is no next number. So what does the transaction after that one get?

It goes back to the beginning and starts over. The next transaction gets 3.

Why 3 and not 0? The first three values are reserved for special meanings — 0 means "invalid", 1 is used while the database is being bootstrapped, and 2 is the "always in the past" marker tied to freezing, which we'll get to shortly. Normal XIDs live from 3 upward, so the counter steps over those three as it rolls around.

And now look at the state you're in. The counter is issuing 3, 4, 5, 6… while rows inserted last month are sitting untouched on disk stamped with xmin = 4000000000. Both numbers are live. Both are valid. And nothing in either one records which lap of the counter it came from.

Why that breaks the comparison

Compare those two as plain integers and the arithmetic betrays you. 5 < 4000000000, so the transaction that started seconds ago is judged older than one that finished last month. Turn that around and you get the damage: that ancient, perfectly valid, committed row now looks like it was created by a transaction from the future — one that, as far as your snapshot is concerned, hasn't happened yet.

And you already know what the visibility rule does with a row from the future. It hides it. So the row silently disappears from every query that touches it.

Sit with that for a second, because it's the entire reason this section exists. Not an error. Not a crash. Not a corrupt-page warning in the log. Your SELECT just quietly returns fewer rows than it should, and nothing anywhere tells you.

Wraparound is not a capacity problem. It's a silent data-loss problem.

A clarification, now that you'd actually ask it

This is where people usually take away the wrong lesson, so let's be explicit: 4.2 billion is not a lifetime cap on transactions. Because the counter rolls over and keeps going, your database can run trillions of them over its life.

The 32 bits limit how many XIDs can exist as distinct labels at one time — like house numbers on a very long circular street. Reusing the number 5 is completely fine. Reusing it while some ancient row still believes it is 5's elder is the problem.

So the danger was never running out of numbers. It's that comparison stops meaning what it used to mean.

Postgres's first defence: a circle, not a line

Postgres never compares XIDs as plain integers. It compares them modularly, on a circle.

Picture all 4.2 billion XIDs laid out around a clock face. From wherever "now" sits, Postgres treats the ~2 billion XIDs behind it as the past and the ~2 billion ahead as the future. "Is A older than B" stops being "is A's integer smaller" and becomes "is A inside the past-half arc, relative to B."

On a circle there is no absolute smallest number, so the counter rolling over doesn't flip any orderings. Our broken example now comes out right: walking backwards from 5, you reach 4000000000 after only ~295 million steps — far short of the two billion that would push it into the "future" half — so it is correctly judged older. Exactly as it should be.

That repairs the arithmetic completely. But it comes with a condition attached, and the condition is the whole story.

The condition: nothing may fall more than half a lap behind

The circle repairs the arithmetic, but it buys you a window, not a permanent guarantee. To see the edge of that window, you need the one rule the circle actually runs on:

Postgres decides "past or future" by asking which way round the circle is shorter.

While a row sits less than half a lap behind you, the shortest route to it is backwards — so Postgres reads it as the past, correctly. But once it falls more than half a lap behind, the shortest route to it becomes forwards. And a thing you reach by going forwards is, as far as the circle is concerned, ahead of you. In the future.

Your kitchen clock has this exact flaw. It's 3 o'clock, and something happened at 8. Going backwards, that was 7 hours ago. Going forwards, it's 5 hours away. Five is shorter — so the clock face insists the event is coming up, not long gone. A 12-hour clock simply cannot express "more than 6 hours ago." In precisely the same way, the XID circle cannot express "more than half a lap ago."

Half a lap is 2,147,483,648 transactions — exactly half of 4,294,967,296. That's where the "about 2 billion" figure keeps coming from.

So let's watch one row cross that line. You insert a row when the counter reads 1,000. Its xmin is 1000 — and this is the part that matters, it will stay 1000 for as long as that row exists.

Now other people use the database. The row never changes. The counter does:

Look at that last line, because it is the entire disaster in one row of a table. Nothing happened to your data. Nobody updated it, deleted it, or even read it. Some unrelated transactions ran elsewhere in the database, the counter ticked one step past a threshold, and a perfectly good row became invisible to every query that touches it.

Why this lands on VACUUM

Notice what actually caused the damage there. Not the row — the clock.

A row doesn't have to do anything to get into trouble. Its xmin is stamped once, at insert, and never changes again. But "now" keeps marching forward every time anyone writes anything, anywhere in the database.

So the gap widens on its own, forever, purely as a side effect of the database being used. The row is standing perfectly still; the cliff edge is walking toward it.

That flips the intuition you'd expect. A table you loaded once and never touched again is not the safe case — it's the most exposed one. Rows that get updated regularly are constantly rewritten with fresh XIDs, and each rewrite resets their distance to zero. Rows that just sit there never get that reset. An archive table nobody has written to since last year is the textbook victim.

So something has to walk the table on a schedule and pull old rows out of the comparison game entirely, before they reach the line. That something is VACUUM, and the act is called freezing.

Freezing

Freezing is how a row gets taken off the circle for good.

When VACUUM meets a live tuple whose xmin is old enough, it flips a couple of status bits in the tuple's header — the infomask field sitting in that ~23-byte header of the tuple. Those bits say exactly one thing:

"This row is visible to everyone. Don't compare its XID — just show it."

And that's the whole trick. Look at what it does and doesn't do: freezing does not move the row to a safer number, and it doesn't renew it or reset its age. It removes the row from the comparison entirely. A frozen tuple no longer has a position on the circle at all, so it cannot drift into the future half — no matter how many times the counter wraps around it.

The problem was never the row. It was the comparison. So freezing deletes the comparison.

(Historically Postgres did this by literally overwriting xmin with 2 — the reserved "frozen" value we ran into when the counter rolled over. Modern versions set the header flags instead and leave the original number in place, but at read time the effect is identical.)

So how old is "old enough"? That's vacuum_freeze_min_age, which defaults to 50 million transactions. Set that against the ~2.1 billion line from a moment ago and the margin is enormous — Postgres starts freezing rows roughly forty times earlier than it strictly has to. Falling off this cliff takes sustained neglect, not bad luck.

One catch, and you've already met it. A row can only be frozen once it's visible to every possible snapshot — which is the very same OldestXmin horizon from earlier in this post. So that forgotten BEGIN; doesn't only block dead-tuple cleanup and inflate your bloat. It blocks freezing too, holding the whole database's freeze progress still while the counter keeps marching on regardless. The two failure modes in this post are really one failure mode wearing two hats.

VACUUM has two jobs, not one: reclaim dead tuples (that's bloat), and freeze ancient live tuples (that's wraparound safety).

And that retroactively explains two autovacuum knobs from earlier that had nothing to do with garbage:

• autovacuum_freeze_max_age — triggers a vacuum on age alone, ignoring the dead-tuple count completely.
• autovacuum_vacuum_insert_threshold — gets insert-only tables visited at all. A table that's never updated produces almost no dead tuples, so it would never trip the dead-tuple threshold, and would sit there unvisited while its rows aged toward the cliff.

Neither of those exists to save space. They exist to make sure freezing happens.

If freezing falls behind

Postgres treats this as seriously as it deserves. As the oldest un-frozen XID creeps toward the line, it escalates in three stages:

1. It fires forced anti-wraparound autovacuums — which ignore your tuning, run on tables that would otherwise be skipped entirely, and won't politely step aside just because the table is busy.
2. It starts warning loudly in the logs, counting down the transactions you have left.
3. Finally it refuses to start new transactions and shuts the database down, requiring recovery with VACUUM in single-user mode.

Step 3 is exactly as brutal as it sounds: a hard production outage, and not a quick one on a large database. But hold it up against the alternative — rows quietly vanishing from query results, no error, no warning, no way to know until someone notices the numbers don't add up. Seen that way it's plainly the right trade. Postgres would rather stop than lie to you.

The practical takeaway is smaller than the drama suggests: this isn't something you tune, it's something you don't let happen. Keep autovacuum healthy — which mostly means not starving it of I/O and not pinning the horizon with forgotten transactions — and you will never meet any of the three stages.

A sibling counter: MultiXact IDs

One term so it doesn't ambush you later. When multiple transactions lock the same row at once, Postgres allocates a MultiXact ID to track the group. It's also 32-bit, and it has its own identical wraparound-and-freezing story, with its own settings and its own warnings. Same shape of problem, same shape of solution.

See it on your own machine

Reading about dead tuples is one thing; watching the counter move is another. All of this takes two minutes in psql.

Watch bloat appear, then clean it up:

CREATE TABLE accounts (id int PRIMARY KEY, balance int);
INSERT INTO accounts SELECT g, 100 FROM generate_series(1, 1000) g;

-- churn the table a few times
UPDATE accounts SET balance = balance + 1;
UPDATE accounts SET balance = balance + 1;
UPDATE accounts SET balance = balance + 1;

SELECT n_live_tup, n_dead_tup FROM pg_stat_user_tables WHERE relname = 'accounts';
VACUUM accounts;
SELECT n_live_tup, n_dead_tup FROM pg_stat_user_tables WHERE relname = 'accounts';

Watch a long transaction pin the horizon. In session A:

BEGIN;
SELECT 1;   -- do nothing else; just leave it open

Then in session B, churn the table again and try to vacuum it. The dead tuples won't go away, because session A's snapshot is holding the horizon back:

UPDATE accounts SET balance = balance + 1;
VACUUM accounts;
SELECT n_dead_tup FROM pg_stat_user_tables WHERE relname = 'accounts';  -- still there

SELECT pid, state, backend_xmin, now() - xact_start AS age
FROM pg_stat_activity
WHERE backend_xmin IS NOT NULL
ORDER BY backend_xmin;

Commit or roll back in session A, vacuum again, and watch them finally disappear. That's the entire incident from the "failure mode" section, reproduced on a laptop.

Bringing it all together

• Dead versions don't vanish on commit. They sit in the table's pages as bloat until something reclaims them, and the cost lands entirely on the write side.
• Bloat is a performance problem, not just a storage one: a scan reads and visibility-checks every dead version on its way to the live ones. It's kept survivable by regular cleanup, by indexes avoiding full scans, and by hint bits making each check cheap.
• Plain VACUUM frees space for that table to reuse, takes a light lock, and usually doesn't shrink the file. VACUUM FULL rewrites the table and returns space to the OS, but takes an ACCESS EXCLUSIVE lock — maintenance windows only, or use pg_repack.
• Autovacuum triggers on threshold + scale_factor × rows. The default 20% scale factor is far too lax for very large tables — lower it per-table on the big, hot ones.
• Cleanup can only remove tuples killed before the xmin horizon, the minimum snapshot xmin advertised across every backend, prepared transaction, and replication slot. One long-running or idle-in-transaction session drags that horizon back and blocks cleanup database-wide — it's the number one cause of runaway bloat.
• HOT updates avoid index writes entirely when no indexed column changed and the page has room, which is why fillfactor and your choice of indexed columns affect write cost.
• Wraparound is a silent data-loss risk, not a capacity limit. 32-bit XIDs are reused forever, and comparison works on a circle (~2 billion past, ~2 billion future) — which holds only as long as nothing falls further behind than that. An untouched row drifts toward that edge on its own, because its xmin never changes while "now" keeps moving. So freezing takes ancient live rows out of the comparison entirely: VACUUM's second job, and the reason insert-only tables still need it. If freezing falls behind, Postgres shuts down rather than hand you wrong answers.