Databases
Vacuum and bloat: interview questions and how to answer them
MVCC makes updates create new row versions rather than overwriting, and vacuum is what reclaims the old ones once no transaction can still see them.
Written and reviewed by Sahil Srivastav
What it actually is
PostgreSQL never updates a row in place. An UPDATE writes a new version of the row and marks the old one dead; a DELETE just marks it dead. This is what lets readers continue seeing a consistent snapshot without blocking writers — the core MVCC bargain — and the dead versions are the bill for it.
Vacuum is the process that collects them. It scans for row versions no longer visible to any open transaction and marks that space reusable within the table. Note the boundary carefully: ordinary VACUUM makes space reusable for future rows in that table, it does not shrink the file and return space to the operating system. Bloat is what you have when dead space accumulates faster than it is reclaimed and reused.
The second job of vacuum matters more than the space. It freezes old transaction ids to prevent wraparound, and it maintains the visibility map that makes index-only scans possible. So under-vacuuming does not merely waste disk — it degrades query plans and, left long enough, threatens to force the database into a protective shutdown.
Why it matters in production
Because bloat degrades everything quietly. A table with 70% dead space reads 70% more pages for the same rows, so sequential scans slow, the buffer cache holds less useful data, and indexes grow alongside. None of this produces an error. It produces a database that is gradually slower for no reason anyone can point at, and the usual response — adding indexes — makes the write side worse.
And because the single most common cause is not a vacuum configuration problem at all. It is an application bug: one long-running or idle-in-transaction session pins a snapshot, and vacuum cannot remove any row version newer than it — across the whole database, not just the tables that session touched. This is the connection between "someone left a transaction open" and "unrelated hot tables are bloating", and it is the thing interviews most want you to understand.
How it works
Visibility decides what can be removed
A dead tuple can only be reclaimed once it is invisible to every snapshot that could still be taken — meaning older than the oldest running transaction. The oldest open transaction therefore sets a floor on reclamation for the entire database. A transaction open for an hour means an hour of dead tuples retained everywhere.
Autovacuum triggers on a ratio, which scales badly
Autovacuum fires when dead tuples exceed a threshold plus a fraction of the table — by default 20%. On a small table that is frequent; on a hundred-million-row table, 20% is twenty million dead rows before it even starts, and then the vacuum is enormous. Large, heavily-updated tables usually need a lower per-table autovacuum_vacuum_scale_factor rather than the global default.
VACUUM versus VACUUM FULL
Plain VACUUM is online and non-blocking and makes space reusable within the table. VACUUM FULL rewrites the table to actually return space to the filesystem, and takes an ACCESS EXCLUSIVE lock for the entire rewrite — which on a large table is an outage. When you genuinely must reclaim disk, pg_repack does the equivalent without the long lock.
Freezing and wraparound
Transaction ids are 32-bit and wrap. Vacuum marks sufficiently old rows as frozen so they remain visible after a wrap. If freezing falls far enough behind, PostgreSQL emits escalating warnings and ultimately refuses new writes to protect the data. Reaching that point is a serious incident and is essentially always caused by vacuum having been blocked for a very long time.
The visibility map links vacuum to query plans
Vacuum marks pages all-visible, and index-only scans rely on that map to skip the heap fetch. An under-vacuumed table loses index-only scans and silently gets slower plans — a concrete reason bloat is a query-performance issue and not just a storage one.
Implementing it
Monitor the oldest transaction age as a first-class metric. It is the earliest and clearest signal, and it points at the application bug rather than at vacuum settings.
Set idle_in_transaction_session_timeout in production so an application bug cannot become a database-wide reclamation failure. It is containment, not a fix, and every production database should have it.
Tune autovacuum per table rather than globally. Large, update-heavy tables need a much lower scale factor; small ones are fine on defaults.
Track dead tuple counts and the ratio of dead to live per table via pg_stat_user_tables, and alarm on tables where the ratio keeps climbing — that is bloat accumulating faster than it is being reclaimed.
-- The first query to run. This number, not vacuum settings, is usually the bug.
SELECT max(now() - xact_start) AS oldest_transaction
FROM pg_stat_activity WHERE xact_start IS NOT NULL;
-- Where bloat is accumulating
SELECT relname, n_live_tup, n_dead_tup,
round(100.0 * n_dead_tup / nullif(n_live_tup + n_dead_tup, 0), 1) AS dead_pct,
last_autovacuum
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC LIMIT 20;
-- Large update-heavy table: the 20% default is far too lax
ALTER TABLE events SET (autovacuum_vacuum_scale_factor = 0.02);
-- Containment so an open transaction cannot block reclamation indefinitely
ALTER SYSTEM SET idle_in_transaction_session_timeout = '30s';Interview questions and how to answer them
Why does an UPDATE create garbage in PostgreSQL?
Because MVCC writes a new row version and marks the old one dead rather than overwriting in place. That is what lets concurrent readers keep a consistent snapshot without blocking the writer. The dead versions are the cost of that design, and vacuum is the mechanism that eventually reclaims them.
Autovacuum is running but the table keeps growing. What is wrong?
Almost certainly a long-running or idle-in-transaction session. Vacuum can only remove versions invisible to every open snapshot, so the oldest transaction sets a floor on what is reclaimable — across the whole database, not just the tables that session touched. Check max(now() - xact_start) before touching any vacuum setting.
What is transaction id wraparound?
Transaction ids are 32-bit and eventually wrap around. Vacuum freezes old rows so they stay visible past a wrap. If freezing falls too far behind, PostgreSQL warns and finally refuses new writes to avoid data appearing to vanish. It is a protective shutdown, and reaching it means vacuum has been blocked for a very long time.
When would you run VACUUM FULL?
Rarely, and not casually, because it rewrites the table under an ACCESS EXCLUSIVE lock — an outage on anything large. It is the only way to return space to the filesystem, so it fits a one-off cleanup after a massive delete, in a maintenance window. pg_repack achieves the same result without the long lock and is usually the better answer.
How does bloat affect query performance?
Two ways. More pages hold the same live rows, so scans read more and the cache holds proportionally less useful data. And an under-vacuumed table has a stale visibility map, which disables index-only scans and forces heap fetches. So it is a planning and I/O problem, not only a disk-usage one.
Answers that lose the round
- Assuming an UPDATE overwrites the row, so there is nothing to clean up
- Treating bloat as a vacuum tuning problem when the cause is a long open transaction
- Running VACUUM FULL on a large production table and taking an exclusive lock for the rewrite
- Leaving the default 20% scale factor on very large tables
- Disabling autovacuum because it "causes load", which converts a steady cost into an eventual crisis
- Not knowing that under-vacuuming disables index-only scans and degrades plans
- Ignoring wraparound warnings until the database refuses writes
FAQ
Do other databases have this problem?
Any MVCC engine has a version-cleanup problem; the design differs. InnoDB keeps old versions in a rollback segment and purges them, so the symptom is a growing undo log rather than table bloat, and a long transaction blocks purge in the same way. The lesson — long transactions are expensive — transfers.
Is HOT update relevant here?
Yes, and it is a useful optimisation to know. If an update changes no indexed column and the new version fits on the same page, PostgreSQL can avoid writing new index entries and can reuse space within the page. It is one reason indexing columns you update frequently has a cost beyond the index maintenance itself.
Should I ever disable autovacuum?
Essentially never globally. Pausing it for a specific table during a bulk load, then vacuuming explicitly afterwards, is legitimate. Turning it off because it causes load converts a continuous manageable cost into a wraparound emergency later.
How do I measure bloat accurately?
pg_stat_user_tables gives dead versus live tuple counts, which is enough for trend monitoring and alarms. For a precise figure of wasted space, pgstattuple scans the table and reports it exactly — accurate but expensive, so it is a diagnostic rather than a monitoring tool.