midCore PostgreSQLFree in fullGrounded in authoritative sources

How does VACUUM relate to MVCC, and what happens if it falls behind?

Almost everyone answers the first half of this correctly and then stops one step short. VACUUM frees space is true and unremarkable. The answer that scores explains why the space existed in the first place, which means starting from the fact that PostgreSQL does not overwrite a row on update. From there the interviewer usually pushes on what happens when cleanup falls behind, and this is where the strongest signal lives: whether you reach for autovacuum settings, or whether you go looking for whatever is holding the cleanup boundary in place. This page lays out a ninety-second version that gets from MVCC to the on-call symptom without a detour, and flags the wording that quietly gets the mechanism wrong.

What the interviewer is scoring

They score whether you reach past 'it frees disk space' to bloat, the cleanup horizon, and autovacuum backlog symptoms.

In short: A strong answer connects dead tuples, storage bloat, xid horizon pressure, and degraded read performance.

Say this: the 90-second answer

First person, as you would speak it

VACUUM exists because an UPDATE in PostgreSQL does not overwrite anything.

When I update a row, the engine writes a new version and leaves the old one in place, because a transaction with an older snapshot may still be entitled to read it. That is what keeps ordinary readers and writers out of each other's way. The bill for it is dead row versions sitting on heap pages after nobody can see them any more.

VACUUM is how that bill gets paid. It walks the table, finds versions older than every snapshot still running, and marks that space reusable for future rows. I am careful with the wording: ordinary VACUUM makes space reusable inside the relation, it does not usually hand pages back to the operating system. VACUUM FULL does, by rewriting the table under an ACCESS EXCLUSIVE lock, which I would not run in business hours on anything large.

When cleanup falls behind, tables and indexes swell and scans read more pages for the same live rows. The cause is nearly always a transaction holding the cleanup boundary back, so I hunt the oldest transaction before I touch an autovacuum setting.

Hold these beats, not the script

  1. 1Updates do not overwrite, they add a version
  2. 2VACUUM makes space reusable, FULL rewrites
  3. 3A long transaction holds the cleanup boundary back
  4. 4Hunt the holder before tuning autovacuum

How I reason through it

The depth a second question reaches, and where the claim stops.

The horizon is the part worth being precise about, and it has a name: the xmin horizon. VACUUM can only remove a row version once no snapshot that still exists could need it. One session that opened a transaction and read something an hour ago pins that boundary for the whole cluster, so cleanup stalls everywhere, not only on the tables that session touched.

That is why dead tuples climbing while autovacuum is clearly running is not a tuning problem. Raising the cost limit makes autovacuum work harder at removing nothing, and it is a common way to turn a stuck horizon into a stuck horizon plus an I/O problem.

Freezing is a second job VACUUM performs, and it is availability work rather than space work: it marks old row versions as unconditionally visible so their original transaction IDs can be recycled. That is a different property from a page being marked all-visible in the visibility map, even though vacuum maintains both.

What changes the response: if the oldest holder is a replication slot or a prepared transaction rather than a backend, cancelling a session achieves nothing. The holder has to be repaired or removed by whoever owns it.

Why this answer scores

The interviewer is listening for causality. Bloat is a symptom, and the candidates who go straight to autovacuum settings are describing a lever rather than a diagnosis. Naming the horizon, and naming which holders you cannot simply cancel, is what separates someone who has cleaned this up from someone who has read about it.

What they ask next

Autovacuum is running constantly and dead tuples keep climbing. What now?
That combination points at the horizon, not at throughput. Find the oldest transaction, the oldest replication slot position, and any prepared transactions. Vacuum cannot remove versions that any of those might still need.
Why not just run VACUUM FULL?
It takes ACCESS EXCLUSIVE for the duration and needs room for a full second copy of the table. It is a maintenance-window operation. If the table will grow back into the space, ordinary vacuum has already done the useful part.
Does an idle connection hold the horizon?
Idle does not; idle in transaction does, once that transaction has taken a snapshot. That distinction is exactly what the state column in the activity view is for.

How this answer is usually lost

  • Saying VACUUM returns space to the operating system as the general case.
  • Describing a long transaction as holding the horizon 'forward'; it holds it back, keeping it old.
  • Conflating freezing with the visibility map's all-visible flag.
  • Jumping to cost-limit tuning before establishing that anything is actually removable.

Do not memorise

  • Default autovacuum threshold and scale-factor values.
  • The visibility-map details, unless the interviewer asks about index-only scans.

From the lab to the room

Hold a transaction open in one session, churn a table in another, and watch dead tuples rise while vacuum removes nothing. Close the first session and watch it drain. That thirty-second experience is what makes the horizon explanation sound lived-in.

How to reason through it

  • Tie VACUUM to MVCC dead row versions, cleanup, not a generic defrag tool.
  • Separate reusable space inside the heap from returning space to the OS (VACUUM FULL).
  • Call out the cleanup horizon: a long transaction holds it back and stalls removal cluster-wide.
  • Name symptoms: bloat, slower scans, autovacuum backlog, wraparound pressure.

What I would verify

  • Find the oldest running transaction before touching any autovacuum setting; its backend_xmin is what pins the horizon.
  • Separate 'autovacuum is not running' from 'autovacuum runs and removes nothing'.
  • Check whether the holder is a backend, a replication slot, or a prepared transaction; only the first can be cancelled.
  • Compare dead-tuple estimates against a physical measurement before calling it bloat.

Follow-ups they push on

  • Autovacuum is running constantly and dead tuples keep climbing. What now?
  • Why not just run VACUUM FULL?
  • Does an idle connection hold the horizon?

Worked responses are above, inside the answer.

Concepts tested

MVCCVACUUMCleanup horizonStorage bloat

Learn it, run it, then say it

Three steps, in order. Nothing here is a detour.

Questions that go with this one