Vacuum & Bloat Demystified
PostgreSQL never overwrites a row in place when you UPDATE or DELETE it.
Instead it leaves the old version behind and writes a new one, which is the foundation of how the database gives every transaction a consistent snapshot of the data.
That old version becomes a dead tuple, and dead tuples are the raw material of what this section calls bloat.
Vacuum and bloat sound like two separate topics, but they are really one mechanism viewed from opposite ends: vacuum is the cleanup process, and bloat is what accumulates when cleanup falls behind.
This page builds the mental model the rest of the vacuum-bloat-storage section assumes you already have.
Summary
- Core Idea: PostgreSQL's MVCC design leaves dead row versions behind on every update and delete, and vacuum is the background process that reclaims that space and protects the cluster from transaction ID exhaustion.
- Why It Matters: Skipping or mistuning vacuum leads to bloated tables, slower index scans, wasted disk, and in the worst case an emergency shutdown to prevent transaction ID wraparound.
- Key Concepts: dead tuple, MVCC, visibility map, autovacuum, freeze horizon, bloat.
- When to Use: Every production table needs this mental model - it explains why tables grow after deletes, why index-only scans sometimes stop working, and why disabling autovacuum is almost always the wrong call.
- Limitations / Trade-offs: Vacuum reclaims space for reuse inside the existing file, but it does not shrink the file on disk in the common case, and aggressive vacuum settings trade I/O and CPU for cleanliness.
- Related Topics: MVCC visibility, autovacuum tuning, transaction ID wraparound, table and index bloat.
Foundations
MVCC, short for multi-version concurrency control, is the reason PostgreSQL can let one transaction read a table while another updates it without either one blocking the other. Every row version carries hidden metadata recording which transaction created it and, if it has been superseded, which transaction deleted it.
When a transaction reads a table, PostgreSQL hands it the row versions that were visible at the moment its snapshot was taken, ignoring versions created afterward and versions already deleted beforehand.
That is powerful for concurrency, but it means a DELETE does not actually erase anything the instant it runs.
The old row version stays physically present in the table's file until nothing could possibly still need it, at which point it becomes a dead tuple eligible for cleanup. Vacuum is the process that walks a table's pages, identifies dead tuples no open transaction can see anymore, and marks that space reusable for future inserts and updates. A simple analogy is a hotel that never demolishes a checked-out room: it just marks the room "vacant" so the next guest can move in, and vacuum is the housekeeping pass that flips a room's status from occupied to vacant.
Crucially, vacuum's job is to make space reusable inside the existing building, not to tear down and rebuild the building smaller.
That single distinction explains almost every surprise beginners hit with vacuum and bloat.
Mechanics & Interactions
Vacuum's most visible job is reclaiming dead tuple space, but its most safety-critical job is preventing transaction ID wraparound, and both responsibilities run through the same scan.
PostgreSQL transaction IDs are a finite, wrapping counter, and every row's visibility metadata is stamped with the transaction ID that created it.
If the counter were allowed to wrap while old rows still referenced an unfrozen transaction ID, those rows could suddenly appear to come from the future and vanish from view.
Vacuum prevents that by periodically freezing old row versions, stamping them as permanently visible so their original transaction ID no longer matters.
Autovacuum decides when to act on a table using a simple threshold formula: it fires once dead tuples exceed a base threshold plus a scale factor times the table's live row count.
That formula is why autovacuum_vacuum_scale_factor matters more as a table grows - the default threshold is trivial on a 10-row table, but on a 50-million-row table it lets tens of millions of dead rows pile up before autovacuum even considers acting.
-- Conceptual trigger check autovacuum performs per table:
-- runs when n_dead_tup >= vacuum_threshold + vacuum_scale_factor * n_live_tup
SELECT relname, n_live_tup, n_dead_tup, last_autovacuum
FROM pg_stat_user_tables
WHERE relname = 'orders';The visibility map is the other half of the mechanics story, tracking which pages contain only tuples visible to every transaction.
A page marked fully visible in that map lets an index-only scan answer a query from the index alone, skipping a trip to the heap entirely.
Vacuum is what updates the visibility map, so a table that goes too long without vacuum loses index-only scan eligibility even when its indexes are otherwise healthy.
Long-running transactions interact badly with all of this, because vacuum cannot remove a dead tuple that an older transaction's snapshot might still need to see.
A single idle-in-transaction session left open for hours can hold back the freeze horizon for an entire table, and sometimes an entire database, no matter how aggressively autovacuum is tuned.
Bloat itself is simply the accumulation of dead tuples and page fragmentation that outpaces vacuum's ability to clean it up, and it shows up differently in tables than in indexes.
Advanced Considerations & Applications
Table bloat and index bloat are not the same failure, and they are rarely fixed by the same tool.
Table bloat means pages carry dead tuples that vacuum can mark reusable in place, which keeps the file size stable once vacuum catches up.
Index bloat is structural: a B-tree page that has been split and partially emptied by deletes does not automatically merge back into a compact tree, so an index can stay bloated even after the underlying table is clean.
That asymmetry is why a table's on-disk size can look fine while its indexes quietly balloon to several times their necessary size. TOAST storage adds another wrinkle, since large column values move out of the main table into a separate TOAST table with its own independent vacuum and bloat lifecycle. At scale, the practical question shifts from "is vacuum running" to "is vacuum keeping pace," and that is a capacity problem as much as a configuration problem.
A table under heavy update or delete churn needs a lower scale factor and a higher cost limit than the cluster-wide defaults, because those defaults are tuned for the median table, not the busiest one.
PostgreSQL 18.4 still exposes pg_stat_progress_vacuum for watching an in-flight vacuum's phase and progress, which matters when deciding whether to wait it out or intervene.
When bloat has already happened, the remediation options trade availability against thoroughness in different ways.
| Approach | Strength | Weakness | Best Fit |
|---|---|---|---|
Plain VACUUM | Online, no exclusive lock, safe to run anytime | Does not shrink the file on disk | Routine dead-tuple cleanup |
VACUUM FULL | Fully compacts the table to its minimum size | Takes an access-exclusive lock, blocking all access | Rare, maintenance-window-only shrink |
pg_repack | Rebuilds the table online with minimal locking | Needs temporary extra disk space during rebuild | Production shrink without downtime |
REINDEX CONCURRENTLY | Rebuilds a bloated index without blocking reads/writes | Fixes index bloat only, not table bloat | Bloated indexes on live tables |
| Partitioning + detach | Drops old data instantly with no vacuum needed on removed rows | Requires a partitioning key matching the retention pattern | Time-series or append-heavy tables |
Disabling autovacuum on a busy table to "reduce load" is one of the more expensive mistakes a team can make, because dead tuples and unfrozen rows keep accumulating invisibly until performance degrades sharply or the wraparound safeguard forces PostgreSQL into read-only mode.
Managed cloud providers generally leave autovacuum on by default and expose the same per-table GUCs, so this mental model transfers directly whether the cluster is self-hosted or managed.
Common Misconceptions
- "DELETE frees disk space immediately." The row becomes a dead tuple first, and only vacuum makes that space reusable, and even then the file rarely shrinks - a misconception that comes from how most other software treats deletion as immediate and final.
- "VACUUM FULL is just a more thorough VACUUM." It is a fundamentally different operation that rewrites the entire table under an exclusive lock, not a stronger version of routine vacuum, which is why it should never be a default response to bloat.
- "Autovacuum running means bloat is impossible." Autovacuum running does not guarantee it is keeping pace with the write rate, and a table can still bloat steadily if dead-tuple growth outruns vacuum frequency.
- "Turning off autovacuum improves performance." It removes visible I/O in the short term while quietly building up dead tuples and wraparound risk that cost far more to fix later than the vacuum overhead ever cost.
- "Vacuum is only about reclaiming space." Freezing rows to prevent transaction ID wraparound is at least as important as space reclamation, and PostgreSQL forces an aggressive vacuum for that reason even on a table with no bloat problem.
FAQs
Why doesn't DELETE shrink my table on disk?
Because PostgreSQL's MVCC design leaves the old row version in place as a dead tuple; vacuum later marks that space reusable for new rows, but reusing space inside the file is not the same as returning bytes to the operating system.
What is the difference between VACUUM and VACUUM FULL?
- Plain
VACUUMreclaims dead tuple space for reuse without an exclusive lock, so normal reads and writes continue. VACUUM FULLrewrites the entire table into a new, compact file under an access-exclusive lock that blocks all access until it finishes.
How does autovacuum decide when to run on a table?
It compares the table's dead tuple count against a threshold formula, a base threshold plus a scale factor multiplied by live row count, and triggers a vacuum once dead tuples cross that line.
Why does index-only scan stop working on a table I thought was fine?
Index-only scans depend on the visibility map showing a page as fully visible to all transactions, and only vacuum updates that map, so a table that has gone too long without vacuum loses that eligibility even if the index itself is healthy.
Is table bloat the same problem as index bloat?
No - table bloat is dead tuple space vacuum can reclaim in place, while index bloat is structural fragmentation from B-tree page splits that vacuum alone typically cannot undo, which is why bloated indexes often need REINDEX CONCURRENTLY separately.
What is transaction ID wraparound and why does vacuum prevent it?
PostgreSQL's transaction ID counter is finite and wraps, so vacuum periodically freezes old row versions to make their visibility independent of that counter, avoiding a scenario where old rows would appear to come from the future once the counter wraps.
Can a long-running transaction really block vacuum on an entire table?
Yes - vacuum cannot remove a dead tuple that an older transaction's snapshot might still need, so one idle-in-transaction session left open for hours can hold back cleanup and freezing across the whole table it touched.
Should I ever disable autovacuum on a table?
Almost never for the whole table - disabling it removes the safeguard against both bloat and wraparound, and the standard approach is tuning per-table settings like scale factor and cost limit instead of turning it off.
What does TOAST have to do with bloat?
Large column values move out of the main table into a separate TOAST table with its own independent vacuum and bloat lifecycle, so a table's main heap can look clean while its TOAST storage is bloated, or the reverse.
How do I know if vacuum is actually keeping up with my write load?
Watch n_dead_tup relative to n_live_tup and last_autovacuum in pg_stat_user_tables over time - a ratio that keeps climbing or a last_autovacuum that keeps falling further behind both signal vacuum is losing ground.
What is the least disruptive way to fix a bloated production table?
pg_repack is generally the safest choice, since it rebuilds the table online with far less locking than VACUUM FULL, at the cost of needing temporary extra disk space during the rebuild.
Does this vacuum model apply the same way on managed cloud PostgreSQL?
Yes - managed providers run the same PostgreSQL vacuum and autovacuum machinery under the hood and expose the same per-table GUCs, so the mental model and tuning levers described here transfer directly.
Related
- Vacuum Basics - hands-on commands and monitoring queries for everyday vacuum work
- Autovacuum Tuning - scale factors and cost limits for keeping vacuum ahead of write load
- Transaction ID Wraparound - the freeze mechanism and wraparound emergency response
- Table & Index Bloat - measuring bloat and choosing a remediation path
- MVCC Visibility - the row-version mechanism that makes dead tuples inevitable
Stack versions: This page was written for PostgreSQL 18.4 (stable 18, maintenance 17).