Understanding VACUUM in PostgreSQL: MVCC, Dead Tuples, and How to Prevent Table Bloat

There’s a symptom quite often encountered by teams managing long-lived PostgreSQL databases: the table size on disk keeps swelling even though the actual row count is relatively stable, queries that used to be fast slowly get slower without any schema changes, and one day a strange warning appears about “transaction ID wraparound” that sounds scary. Almost all of these symptoms lead back to one maintenance process that’s often ignored until it becomes a real problem: VACUUM. This article discusses VACUUM from its root cause — how PostgreSQL stores data through the MVCC model so dead tuples can accumulate — to how autovacuum works behind the scenes, the performance impact when this process is late, and concrete steps you can take so large tables stay healthy in the long term.

What Is MVCC — The Foundation of Why Vacuum Is Needed

To understand why VACUUM is needed at all, you first need to understand the storage model PostgreSQL uses: MVCC (Multi-Version Concurrency Control). The basic principle is that PostgreSQL never directly overwrites a data row on UPDATE, and doesn’t physically delete rows immediately on DELETE.

When you run an UPDATE, PostgreSQL marks the old row version as “no longer valid” (by storing the transaction number that invalidated it), then writes the new row version as a separate entry in storage. The old row doesn’t disappear immediately — it physically remains, it’s just that new transactions won’t see it as a valid version.

flowchart TD
    A["Original row: id=1, balance=1000 (version 1)"] -->|UPDATE balance=1500| B["Version 1 marked invalid"]
    A --> C["Version 2 created: id=1, balance=1500"]
    B --> D[Dead Tuple - still on disk, no longer valid]
    C --> E[Live Tuple - the currently valid version]

Why is PostgreSQL designed this way, instead of directly overwriting data in place? The reason is concurrency without heavy locking. Imagine transaction A is reading a particular row, while transaction B simultaneously updates the same row. If PostgreSQL overwrote data directly, transaction A risks reading half-changed data, or would have to wait (lock) until transaction B finishes. With MVCC, transaction A can still read the old version matching the snapshot from when its transaction started, while transaction B writes its new version independently — both run without blocking each other.

The trade-off of this elegant design is: old versions that are no longer valid don’t just disappear. They keep occupying physical space on disk until a process explicitly cleans them up. That process is called VACUUM.

What Is a Dead Tuple and How It Accumulates

A dead tuple is the term for old row versions that are no longer valid — the result of UPDATE or DELETE — but haven’t been cleaned from physical storage yet. Every UPDATE essentially produces one dead tuple (the old version) and one new live tuple (the current version). Every DELETE produces one dead tuple with no replacement live tuple.

Imagine a concrete scenario: a wallet_balance table with one million rows, where each row is UPDATEd on average one hundred times per day to record balance changes. In a single day, that produces one hundred million dead tuples — one hundred times more than the number of live rows you actually need. Without a cleanup process, the physical space this table occupies will far exceed its actual data size.

flowchart LR
    subgraph Page Storage Before Vacuum
        A[Live Tuple] 
        B[Dead Tuple]
        C[Dead Tuple]
        D[Live Tuple]
        E[Dead Tuple]
    end

PostgreSQL stores data in units called pages (typically 8KB per page). When dead tuples pile up in a page, the space usable for new rows shrinks, forcing PostgreSQL to allocate new pages more often than it should — this is the root of the phenomenon called table bloat, discussed in more detail in the performance section.

It’s important to understand that dead tuples aren’t a bug or an error — they’re a natural consequence of the MVCC design. Problems only arise when these dead tuples are never cleaned up regularly.

What VACUUM Actually Does

VACUUM does several jobs at once, and it’s important to understand that not all of those jobs have the same impact on the physical file size on disk.

Marking Dead Tuple Space as Reusable

This is the main function of standard VACUUM. When VACUUM runs, it scans the table, identifies dead tuples that can no longer be needed by any transaction (because all transactions that might still see the old version have finished), then marks the space they occupy as reusable for subsequent INSERTs or UPDATEs.

The crucial point here: standard VACUUM doesn’t return that space to the operating system. The table file size on disk stays the same as before VACUUM ran — what changes is that the empty space from dead tuples can now be reused internally by PostgreSQL for new data, instead of PostgreSQL having to allocate new space at the end of the file.

Updating the Visibility Map

PostgreSQL stores a structure called the visibility map — a map recording which pages have all their tuples already “visible” to all active transactions, so they don’t need to be rechecked for new transactions. VACUUM updates this map, which also helps speed up index-only scans because PostgreSQL can directly trust the index data without needing to check the main table for pages already marked fully visible.

Updating Statistics for the Query Planner

VACUUM (especially the VACUUM ANALYZE variant) updates the data distribution statistics the query planner uses to build execution plans — like estimates of how many rows match a given condition. Stale statistics can make the planner choose poor execution strategies, for example picking a sequential scan when an index scan would be far more efficient, because the planner thinks the number of matching rows is far larger than reality.

Preventing Transaction ID Wraparound

This is the most critical VACUUM function and the most often ignored until it becomes a serious problem — discussed in more detail in its own section later, because its impact can completely cripple the database if ignored too long.

VACUUM Variants

PostgreSQL provides several VACUUM command variants with different locking trade-offs and effects.

-- Standard VACUUM: marks space reusable, doesn't lock the table for read/write
VACUUM wallet_balance;

-- VACUUM ANALYZE: standard vacuum + updates planner statistics at once
VACUUM ANALYZE wallet_balance;

-- VACUUM FULL: actually returns space to the OS, but fully locks the table
VACUUM FULL wallet_balance;

-- VACUUM FREEZE: standard vacuum + freezes transaction IDs more aggressively
VACUUM FREEZE wallet_balance;

Standard VACUUM runs as a non-blocking operation — it can run alongside normal SELECT, INSERT, UPDATE, and DELETE without fully locking the table, although it still needs a light lock (ACCESS SHARE) that can conflict with DDL operations like ALTER TABLE.

VACUUM FULL works in a completely different way: it rewrites the entire table into a new, more compact file (without any dead tuples at all), then replaces the old file with the new one. This truly shrinks the file size on disk, but it requires a full exclusive lock on the table during the process — no read or write operations can happen until it finishes. For large tables, this can take a long time and must be scheduled outside peak hours.

Because VACUUM FULL locks the table exclusively, running it during peak hours on a large table can hold back every query against that table — including simple SELECTs — until the process finishes. For large tables that need their size reduced without downtime, consider extensions like pg_repack which perform a similar operation without a prolonged exclusive lock.

Autovacuum — How It Works and Its Configuration

Running VACUUM manually all the time isn’t practical, which is why PostgreSQL has a background process called autovacuum that runs automatically based on the table’s change threshold.

By default, autovacuum is triggered when the number of changed rows (via UPDATE or DELETE) on a table exceeds a certain threshold, calculated from a combination of a fixed value and a proportion of the table’s row count.

Trigger threshold (default):
  autovacuum_vacuum_threshold = 50 (rows)
  autovacuum_vacuum_scale_factor = 0.2 (20% of the table's total rows)

Formula: threshold = autovacuum_vacuum_threshold + (autovacuum_vacuum_scale_factor * table_row_count)

For a table with ten thousand rows, autovacuum triggers after roughly 2,050 changed rows (50 + 20% of 10,000). For a table with one hundred million rows, the threshold becomes twenty million changed rows — a number that sounds large, but for a table with a very high write rate, twenty million changes can happen in a matter of hours, so autovacuum is triggered rarely relative to the actual dead tuple accumulation rate.

flowchart TD
    A[Data changes continuously monitored per table] --> B{Changed rows > threshold?}
    B -- Not yet --> A
    B -- Yes --> C[Autovacuum worker scheduled]
    C --> D[VACUUM runs in the background]
    D --> A

When the Default Configuration Isn’t Enough

For tables with very high write patterns — like log tables, sessions, or message queues that are constantly INSERTed and DELETEd — the default configuration often makes autovacuum run too rarely relative to the dead tuple accumulation rate, so bloat still forms between autovacuum cycles. The solution is setting these parameters specifically per table, not changing the global default that applies to every table in the database.

-- Lower the threshold specifically for a very high write rate table
ALTER TABLE message_queue SET (
    autovacuum_vacuum_scale_factor = 0.01,
    autovacuum_vacuum_threshold = 1000
);

With the scale factor lowered to 1%, autovacuum will be triggered far more often on this specific table, keeping dead tuples from piling up massively before being cleaned.

Performance: The Impact of Late or Ignored Vacuum

Table Bloat

When dead tuples accumulate faster than vacuum cleans them, the table experiences bloat — its physical size on disk far exceeds the actual data size it should need. Table bloat directly impacts performance because sequential scans must scan more pages whose contents are mostly irrelevant dead tuples, and even index scans become less efficient because data that’s logically “close together” is now spread across more physical pages due to bloat.

You can detect signs of bloat through PostgreSQL’s built-in statistics:

-- See the dead tuple to live tuple ratio per table
SELECT
    relname AS table_name,
    n_live_tup AS live_rows,
    n_dead_tup AS dead_rows,
    ROUND(n_dead_tup::numeric / NULLIF(n_live_tup, 0) * 100, 2) AS dead_percentage
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 10;

For a more precise analysis of actual bloat size (not just the dead tuple ratio), extensions like pgstattuple give concrete numbers for what percentage of a table’s physical space is actually “wasted” by bloat.

CREATE EXTENSION IF NOT EXISTS pgstattuple;

SELECT * FROM pgstattuple('wallet_balance');
-- Output includes: table_len, dead_tuple_percent, free_percent, etc.

Index Bloat as a Side Effect

Bloat doesn’t only happen in the main table, but also in its indexes. Every time a row is UPDATEd, the index entries for that row also need updating, and old index entries pointing to dead tuples pile up in a similar way. Bloated indexes make index scans less efficient — the planner has to pass over more irrelevant entries before finding the sought data, even though in theory an index should be far faster than a sequential scan.

Queries Slow Down Without Schema Changes

The symptom teams complain about most often is: the same query, the same schema, but performance degrades drastically over time without any code changes. In many cases, the root cause is bloat accumulated because autovacuum doesn’t keep up with the data change rate — not because the actual data volume grew significantly.

Transaction ID Wraparound — The Most Serious Risk

Among all of VACUUM’s functions, preventing transaction ID wraparound is the most critical, because its impact isn’t just slow performance, but a database that stops accepting new transactions entirely.

PostgreSQL gives every transaction a transaction ID (XID) — a number that keeps increasing every time a new transaction starts, used as part of the MVCC mechanism to determine which row versions are visible to which transactions. The problem is that XIDs in PostgreSQL are stored as 32-bit numbers, meaning their values can “run out” and wrap back to the beginning (wraparound) after about two billion transactions.

flowchart LR
    A[XID keeps increasing with every transaction] --> B{Nearing the 2 billion limit?}
    B -- Not yet, routine VACUUM runs --> C[Old XIDs frozen, safe]
    B -- Yes, VACUUM ignored for a long time --> D[Wraparound risk]
    D --> E[Database enters protection mode: refuses new transactions]

If wraparound truly happens without handling, old rows could suddenly appear “from the future” to the MVCC mechanism — a serious data integrity problem. To prevent this, PostgreSQL has a protection mechanism: when the system detects a database nearing the wraparound limit without adequate vacuuming, it will refuse new transactions entirely until an emergency VACUUM is run — a situation that effectively makes the database read-only or even completely unable to accept new connections for writing purposes.

VACUUM prevents this through a process called freezing — marking old rows that are certainly visible to all transactions as “frozen”, so they’re no longer counted in the running XID calculations, effectively “stopping the clock” for those rows. VACUUM FREEZE performs this process more aggressively than regular VACUUM.

-- Check how close a database is to the wraparound limit
SELECT
    datname,
    age(datfrozenxid) AS oldest_transaction_age
FROM pg_database
ORDER BY age(datfrozenxid) DESC;

If the age(datfrozenxid) value approaches the autovacuum_freeze_max_age parameter (default around 200 million), that’s a warning sign that freezing hasn’t been aggressive enough and needs attention before approaching the critical 2 billion limit.

Wraparound protection isn’t just a performance warning — once the database hits its critical limit, PostgreSQL will refuse to run any new transactions at all to prevent data corruption, forcing administrators to run an emergency VACUUM while the system is already unable to serve normal traffic. Preventing this situation through routine monitoring is far cheaper than handling it after it happens.

Autovacuum vs Manual VACUUM — When to Step In

RELYING ON AUTOVACUUM IS ENOUGH if:
  ✓ the table's write patterns are relatively stable and not extreme
  ✓ routine monitoring shows the dead tuple ratio stays under control
  ✓ the default or per-table tuned configuration matches the data change rate

MANUAL INTERVENTION IS NEEDED if:
  ✗ you just did a massive bulk delete/update (millions of rows at once)
  ✗ planner statistics feel stale after significant data changes (run VACUUM ANALYZE)
  ✗ bloat is already severe and the file size truly needs shrinking (VACUUM FULL/pg_repack)
  ✗ age(datfrozenxid) is approaching the wraparound warning threshold

Common Anti-Patterns

-- ✗ Turning off autovacuum without a replacement strategy
ALTER TABLE big_table SET (autovacuum_enabled = false);
-- Dead tuples pile up without limit, risk of severe bloat and wraparound

-- ✓ If you need tighter control, tune the parameters instead of disabling entirely
ALTER TABLE big_table SET (
    autovacuum_vacuum_scale_factor = 0.05,
    autovacuum_vacuum_cost_delay = 2
);


-- ✗ Running VACUUM FULL during peak hours on a large table
VACUUM FULL orders;  -- fully locks the orders table during high traffic

-- ✓ Schedule VACUUM FULL in a maintenance window, or use pg_repack
-- which doesn't need a prolonged exclusive lock
-- (run via CLI: pg_repack --table=orders outside peak hours)


-- ✗ Ignoring bloat monitoring until performance already feels severely degraded
-- (no query/dashboard monitoring n_dead_tup periodically)

-- ✓ Routine dead tuple ratio monitoring as part of standard observability
SELECT relname, n_dead_tup, n_live_tup
FROM pg_stat_user_tables
WHERE n_dead_tup > 10000
ORDER BY n_dead_tup DESC;

VACUUM Best Practices

1. Routine Monitoring, Not Reactive

Make checking pg_stat_user_tables and age(datfrozenxid) part of the standard observability dashboard, not something checked only after a performance complaint. Early detection is far cheaper to handle than bloat that’s already severe.

2. Tune Autovacuum Per Table, Not Globally

Tables with very different write characteristics (write-heavy log tables vs rarely-changing reference tables) should each have their own adjusted autovacuum parameters, rather than relying on one global setting forced onto all tables.

3. Schedule VACUUM FULL or pg_repack in a Maintenance Window

For bloat cases that are already severe and truly need the file size reduced, plan the execution during low-traffic hours, and consider pg_repack as an alternative that doesn’t require full downtime for large production tables.

4. Run VACUUM ANALYZE After Large Data Operations

After data migrations, bulk imports, or massive bulk deletes, run VACUUM ANALYZE explicitly instead of waiting for the next autovacuum cycle, so planner statistics are updated promptly and subsequent queries get accurate execution plans.

5. Don’t Disable Autovacuum Without an Equivalent Replacement

If you have a strong reason to disable autovacuum on a particular table (for example very precise timing control), make sure there’s a replacement mechanism running VACUUM on a schedule with equivalent discipline — don’t leave that table without any vacuum at all.

6. Monitor Freeze Age Specifically for Long-Lived Databases

For databases running for years with high transaction volumes, freeze age nearing the critical limit is a real risk, not just theory. Set up explicit alerts when age(datfrozenxid) passes a certain percentage of autovacuum_freeze_max_age.

A short checklist for quick reference:

ROUTINE MONITORING:
  □ Dashboard monitors n_dead_tup / n_live_tup per table
  □ Alert active for age(datfrozenxid) approaching the critical threshold
  □ Periodic review of tables with the highest bloat

CONFIGURATION:
  □ Autovacuum parameters tuned per table for write-heavy tables
  □ No table with autovacuum_enabled = false without an equivalent replacement
  □ autovacuum_freeze_max_age is understood and not left at default without evaluation

SEVERE BLOAT HANDLING:
  □ VACUUM FULL/pg_repack scheduled in a maintenance window, not peak hours
  □ VACUUM ANALYZE run explicitly after massive data operations
  □ Clear escalation plan if freeze age approaches the critical limit

Summary

• PostgreSQL uses MVCC — UPDATE/DELETE don’t overwrite data directly, but mark old versions invalid and write separate new versions, producing dead tuples that accumulate in storage.
• VACUUM marks dead tuple space as internally reusable — not returning space to the OS — while also updating the visibility map, planner statistics, and preventing transaction ID wraparound.
• VACUUM FULL truly shrinks the physical file size but needs a full exclusive lock; standard VACUUM is non-blocking but doesn’t shrink files.
• Autovacuum runs automatically based on data change thresholds, but the default configuration is often not aggressive enough for very high write rate tables — it needs per-table tuning.
• Table bloat and index bloat are the direct result of dead tuples not cleaned up in time, causing queries to slow down without schema or actual data volume changes.
• Transaction ID wraparound is the most serious risk — if left alone, PostgreSQL will refuse new transactions entirely to prevent data corruption.
• Don’t disable autovacuum without an equivalent replacement, and schedule VACUUM FULL/pg_repack in a maintenance window for severe bloat cases, not during high-traffic hours.
• Routine monitoring via pg_stat_user_tables and age(datfrozenxid) is far cheaper than handling severe bloat or emergency wraparound after the fact.