Postgres VACUUM Explained: MVCC, Dead Tuples, Autovacuum, and Table Bloat

Postgres VACUUM is the maintenance process that reclaims space left behind by Postgres's concurrency model. Every UPDATE and DELETE in PostgreSQL leaves a dead tuple — an old row version that is no longer visible but still occupies disk. VACUUM finds those dead tuples and marks their space reusable. Skip it, tune it wrong, or let a long transaction block it, and tables slowly fill with garbage. Queries that used to be fast start scanning bloat, and the "database is slow" incident begins. This guide is a supporting deep-dive in the Devgains database cluster; the Postgres performance pillar covers indexes and query plans, and this piece explains the maintenance layer underneath them.

Quick answer: what does VACUUM actually do?

• Reclaims dead tuples. Marks space from deleted/updated rows as reusable so the table stops growing forever.
• Updates the visibility map. Records which pages contain only all-visible rows, which is what makes index-only scans possible.
• Prevents transaction ID wraparound. Freezes very old rows so Postgres never runs out of 32-bit transaction IDs — a hard safety requirement, not an optimization.
• Refreshes planner statistics (when run as VACUUM ANALYZE or via autovacuum's analyze pass) so the query planner makes good choices.

Plain VACUUM reclaims space for reuse by the same table. It does not return disk to the operating system — only VACUUM FULL does, and it rewrites the whole table under a heavy lock.

Why it matters: MVCC and dead tuples

PostgreSQL uses MVCC (Multi-Version Concurrency Control) so readers never block writers and writers never block readers. It achieves this by never overwriting a row in place. When you update a row, Postgres writes a new version and marks the old one as expired for transactions that started later. When you delete a row, it just marks the row expired. Nothing is physically removed at the moment of the write.

Each of those expired row versions is a dead tuple. Until VACUUM runs, dead tuples:

• take up pages on disk, inflating table size;
• get scanned by sequential scans and (partially) by index scans, wasting I/O;
• keep index entries pointing at them, so indexes bloat too.

This is the core trade-off of MVCC: you get excellent concurrency, but you inherit a cleanup bill. VACUUM is how that bill gets paid.

Architecture: how a VACUUM pass works

A regular (non-FULL) VACUUM runs concurrently with your workload and roughly does this:

1. Scan the heap for pages that might contain dead tuples, guided by the visibility map so it can skip pages already known to be all-visible.
2. Collect dead tuple IDs into memory (bounded by maintenance_work_mem).
3. Clean matching index entries so no index still points at a dead tuple.
4. Mark heap space free in each page's free space map so future inserts/updates can reuse it.
5. Update the visibility map and freeze old tuples that have crossed the freeze age.

The key mental model: VACUUM recycles space inside the table; it does not shrink the file. A 10 GB table that is 60% dead tuples stays roughly 10 GB on disk after a plain VACUUM — but that 6 GB of freed space is now available for new rows, so the table stops growing until you exceed it again. Steady-state, that is exactly what you want.

Autovacuum: the daemon that does this for you

You almost never run VACUUM by hand in production. The autovacuum daemon watches table activity and launches vacuum + analyze workers automatically. It triggers on a table when the estimated number of dead tuples crosses a threshold:

dead_tuples > autovacuum_vacuum_threshold
            + autovacuum_vacuum_scale_factor * reltuples

With the defaults (threshold = 50, scale_factor = 0.2), a table is vacuumed once roughly 20% of its rows are dead. That default is fine for small tables and terrible for large ones: on a 100-million-row table, 20% is 20 million dead tuples before cleanup even starts. For big, write-heavy tables, lower the scale factor per table:

-- Vacuum this table once ~2% of rows are dead, not 20%.
ALTER TABLE orders SET (
  autovacuum_vacuum_scale_factor = 0.02,
  autovacuum_vacuum_threshold    = 5000
);

Per-table settings beat global ones: your hot orders table and a tiny countries lookup table have completely different needs. Tune the tables that actually churn.

Diagnosing table bloat

Bloat is the gap between how big a table is and how big it would be if it held only live rows. The fastest signal is the dead-tuple count in pg_stat_user_tables:

SELECT relname,
       n_live_tup,
       n_dead_tup,
       round(n_dead_tup::numeric
             / nullif(n_live_tup + n_dead_tup, 0), 3) AS dead_ratio,
       last_autovacuum
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 10;

Read it like this:

• High n_dead_tup with a recent last_autovacuum — autovacuum is keeping up; fine.
• High dead_ratio and an old or null last_autovacuum — cleanup is not happening. Something is blocking it or the thresholds are too high for this table's write volume.
• Table size that only grows and never plateaus — classic bloat; queries scan more pages every week.

The most common reason autovacuum "isn't working" is not autovacuum at all — it's a long-running transaction. VACUUM can only remove a dead tuple if no transaction could still need to see it. One idle-in-transaction session, one hours-long analytics query, or one forgotten BEGIN holds back the xmin horizon and freezes cleanup for the entire database. Hunt for them:

SELECT pid, state, now() - xact_start AS runtime, query
FROM pg_stat_activity
WHERE state <> 'idle' AND xact_start IS NOT NULL
ORDER BY xact_start
LIMIT 5;

Kill or fix the oldest offenders and VACUUM immediately catches up on the backlog.

VACUUM vs VACUUM FULL vs ANALYZE

VACUUM FULL genuinely shrinks the file, but it takes an ACCESS EXCLUSIVE lock — nothing can read or write the table while it runs, and it needs enough free disk to write a full copy. For a large production table, prefer an online tool like pg_repack, which rebuilds the table without the long exclusive lock.

Step-by-step: fixing a bloated table

1. Confirm the bloat. Check dead_ratio and table size trend in pg_stat_user_tables. Rule out a long transaction first with pg_stat_activity.

2. Fix the blocker, not the symptom. If an idle-in-transaction session is holding the xmin horizon, end it. Bloat that keeps returning after VACUUM is almost always an application leaving transactions open.

3. Run a targeted vacuum with visibility.

   VACUUM (VERBOSE, ANALYZE) orders;

   VERBOSE prints how many dead tuples were removed and how many index entries were cleaned — proof the pass did real work.

4. Reclaim the file only if you must. If the table ballooned from a one-time bulk delete and won't refill soon, run pg_repack (online) or VACUUM FULL orders (during a maintenance window) to hand the disk back to the OS.

5. Tune so it doesn't recur. Lower the per-table autovacuum_vacuum_scale_factor for hot tables so cleanup starts at ~2% dead rows instead of 20%.

Best practices

• Tune autovacuum per table, aggressively for high-write tables. Global defaults are a starting point, not an answer.
• Monitor n_dead_tup and last_autovacuum and alert when the dead ratio stays high with stale cleanup — that is your early bloat warning.
• Treat long transactions as bugs. Set a statement_timeout and an idle_in_transaction_session_timeout so no session can silently block VACUUM forever.
• Reserve VACUUM FULL for maintenance windows, or use pg_repack online instead.
• Watch wraparound. Alert on age(datfrozenxid) approaching autovacuum_freeze_max_age; wraparound-prevention vacuums are non-negotiable and can be expensive if deferred.

Common mistakes

• Disabling autovacuum to reduce load. It only postpones — and multiplies — the work.
• Assuming plain VACUUM shrinks the file. It reclaims space inside the table; only VACUUM FULL/pg_repack returns disk.
• Ignoring idle-in-transaction sessions. They quietly freeze cleanup database-wide.
• Running VACUUM FULL on a live hot table. The ACCESS EXCLUSIVE lock is an outage.
• Forgetting ANALYZE after big data changes. Stale statistics push the planner toward bad plans even when the table isn't bloated — see the Postgres performance guide for how the planner reads those stats.

Takeaways

• MVCC gives Postgres great concurrency but leaves dead tuples behind on every UPDATE/DELETE.
• VACUUM reclaims dead tuples for reuse, maintains the visibility map, and prevents transaction ID wraparound.
• Autovacuum automates this; the fix for "it's behind" is usually more aggressive per-table tuning, not disabling it.
• Bloat is the gap between real and ideal table size — diagnose it with pg_stat_user_tables, and check for long-running transactions first.
• Plain VACUUM recycles space; only VACUUM FULL or pg_repack returns disk to the OS.

FAQ

What is VACUUM in PostgreSQL? VACUUM is a maintenance operation that removes dead tuples — the expired row versions left by MVCC after UPDATE and DELETE — so their space can be reused, while also updating the visibility map and preventing transaction ID wraparound.

Why does Postgres create dead tuples? Because of MVCC. Postgres never overwrites a row in place; it writes a new version and marks the old one expired so concurrent transactions still see a consistent snapshot. Those snapshots are the same mechanism behind transaction isolation levels — each isolation level is really a rule about which snapshot your transaction reads. The expired versions are dead tuples that VACUUM later cleans up.

Does VACUUM lock the table? A regular VACUUM runs concurrently with reads and writes using only a light lock. Only VACUUM FULL takes an ACCESS EXCLUSIVE lock that blocks all access while it rewrites the table.

Why isn't autovacuum reclaiming space? The most common cause is a long-running or idle-in-transaction session holding back the xmin horizon, which prevents removal of dead tuples database-wide. Thresholds set too high for a large table are the next most common cause.

What is table bloat? Table bloat is the extra disk space a table occupies because of accumulated dead tuples that haven't been vacuumed and reused. Bloated tables force queries to scan more pages, slowing them down.

Conclusion

VACUUM is the price Postgres pays for MVCC's excellent concurrency — and it's a price you can pay cheaply with the right autovacuum tuning, or expensively as a 3 a.m. bloat incident. Keep dead tuples visible in your monitoring, tune autovacuum per table for your hot write paths, and treat long transactions as the bugs they are. Do that and bloat becomes a non-event. From here, the Postgres performance pillar shows how clean tables plus the right indexes and connection pooling keep the whole database fast under load.

References

• PostgreSQL Documentation — Routine Vacuuming
• PostgreSQL Documentation — Concurrency Control (MVCC)
• PostgreSQL Documentation — The Statistics Collector: pg_stat_all_tables
• PostgreSQL Documentation — VACUUM command reference
• pg_repack — Online table reorganization