PostgreSQL MVCC and Vacuum: Why Your 10GB Table Uses 50GB

The PostgreSQL MVCC system is, in a word, elegant. Readers never block writers, and vice versa.

However, this elegance carries a significant maintenance cost. Yes, I am talking about dead tuples.

You see, every UPDATE and DELETE leaves behind row versions that are no longer visible to any transaction. And yet, they still occupy your disk space.

Confusing, right?

Now, if you do not understand how these internal systems work, your tables will begin to bloat and your queries will slow down. Eventually you will begin to wonder why a 10GB table is using five times that much space on disk.

I will help you answer this.

Where do these hidden rows come from?

Let me explain the basics. In PostgreSQL, an UPDATE does not modify a row in place. It writes a new version of the row and marks the old one as dead. A DELETE marks the row as dead without writing a new version.

Each row version carries two transaction IDs:

• xmin: the transaction that created this version
• xmax: the transaction that deleted or superseded this version (0 if still live)

So, when you run a query, PostgreSQL checks those values against your transaction's snapshot to decide which versions you are allowed to see. A snapshot is a frozen picture of the database at one moment, and the rules that produce it depend on your transaction isolation level: whether you see the data as it stood when your statement began, or whether other people's commits appear while you are still reading.

Old versions that are invisible to every active transaction are dead tuples.

But this design has a hidden implication. Even a simple UPDATE users SET last_login = NOW() WHERE id = 1 creates one. If that runs on every login and you have 10,000 logins per minute, you are producing 10,000 dead tuples per minute on one table. Without aggressive vacuum tuning, it bloats fast.

-- Check dead tuple counts
SELECT relname, n_dead_tup, n_live_tup,
       round(n_dead_tup::numeric / NULLIF(n_live_tup, 0) * 100, 1) AS dead_pct
FROM pg_stat_user_tables
WHERE n_dead_tup > 1000
ORDER BY n_dead_tup DESC;

How does the database clean up the mess?

VACUUM reclaims the space dead tuples occupy. It has two modes, and they behave very differently.

Regular VACUUM

• Marks dead tuple space as reusable within the table
• Does not return space to the operating system
• Does not hold exclusive locks, so it runs alongside reads and writes
• Updates the visibility map, which index-only scans depend on
• Updates the free space map, so new inserts can reuse what it reclaimed

VACUUM FULL

• Rewrites the entire table and physically compacts it
• Returns space to the operating system
• Holds an exclusive lock on the table for the whole duration
• Is essentially unusable on a production table during business hours

This is all fine and dandy, but in practice you will almost always want regular VACUUM, and you reserve VACUUM FULL for a maintenance window on a severely bloated table.

If you need an alternative to VACUUM FULL, look into pg_repack. It is an extension that rewrites a table without holding an exclusive lock for the full duration. I personally find it an absolute lifesaver on production systems where you cannot afford downtime, yet you desperately need to reclaim your disk space.

Why do open transactions stall the cleanup?

Of course, there is a catch. VACUUM can only reclaim dead tuples that are invisible to all active transactions.

So if a transaction started two hours ago and is still open, every dead tuple created in those two hours is safe from vacuum. The oldest active transaction ID sets a floor, and vacuum cannot touch anything newer than that.

This is one of the most common causes of unexplained bloat. The usual culprits:

• Idle transactions: a developer opens psql, runs BEGIN, and walks away. That one open transaction pins the vacuum horizon.
• Long-running reports: a dashboard query that takes 30 minutes holds vacuum back for 30 minutes.
• Connection pool leaks: an application that opens transactions and fails to close them on error paths.

-- Find long-running transactions blocking vacuum
SELECT pid, usename, state, query_start,
       age(backend_xid) AS xid_age,
       now() - query_start AS query_duration
FROM pg_stat_activity
WHERE state != 'idle'
  AND backend_xid IS NOT NULL
ORDER BY query_start ASC
LIMIT 10;

Set idle_in_transaction_session_timeout to kill idle transactions after a threshold, five minutes for example. That alone prevents most accidental stalls.

How does autovacuum decide when to run?

Autovacuum is PostgreSQL's background process that runs VACUUM for you. It triggers on this:

threshold = autovacuum_vacuum_threshold + autovacuum_vacuum_scale_factor × n_live_tup

With the defaults, threshold 50 and scale factor 0.2, a table with 10,000 rows is vacuumed after 2,050 dead tuples: 50 + 0.2 × 10,000.

But there is a problem with these percentage-based thresholds. You guessed it, it is scaling. A table with 100 million rows will not be vacuumed until it holds 20 million dead tuples, which can be hundreds of gigabytes of bloat sitting on disk before anything happens.

Tuning for high-write tables

We established that the defaults are quite conservative. So, if you have a table taking heavy writes, you will need to intervene manually:

ALTER TABLE hot_table SET (
    autovacuum_vacuum_scale_factor = 0.01,    -- 1% instead of 20%
    autovacuum_vacuum_threshold = 1000,
    autovacuum_analyze_scale_factor = 0.005
);

Three more parameters are worth knowing:

• autovacuum_max_workers: default 3. Raise it when many large tables compete for vacuum time. Each worker vacuums one table at a time, so 3 workers and 50 tables needing attention is a backlog.
• autovacuum_vacuum_cost_delay: how aggressively vacuum runs. Lower is more aggressive. The default is 2ms; try 0 for high-write workloads. This is the single most impactful parameter here.
• autovacuum_naptime: how often the launcher looks for work. Default is 1 minute.

Should every table use the same strategy?

No. Not all tables are created equal. Find your hottest tables, the ones taking the most writes per second, and tune those individually. Tables with low write rates can comfortably keep the defaults.

Here is a practical approach I highly recommend: query pg_stat_user_tables weekly, take the top 10 by n_dead_tup, and add per-table tuning for anything that keeps appearing.

What is the worst case?

Let's start with the fact that PostgreSQL uses 32-bit transaction IDs, which wrap around after roughly 4 billion transactions. Without vacuum, old transaction IDs become ambiguous and the database can no longer tell whether a row was created in the past or the future.

That is transaction ID wraparound, and it is the worst case, because PostgreSQL shuts down to prevent data corruption.

VACUUM prevents it by freezing old transaction IDs, marking them as definitively in the past. The autovacuum_freeze_max_age setting, default 200 million transactions, is what triggers the aggressive anti-wraparound vacuum.

-- Monitor wraparound risk
SELECT datname,
       age(datfrozenxid) AS xid_age,
       current_setting('autovacuum_freeze_max_age') AS freeze_max_age
FROM pg_database
ORDER BY age(datfrozenxid) DESC;

If xid_age approaches 2 billion, then you have a serious problem on your hands, and I highly recommend monitoring this metric closely. Anti-wraparound vacuum is aggressive and non-interruptible, it consumes serious I/O and CPU, and if it starts on a high-traffic table during peak hours you will feel it. The way to avoid that is to keep regular vacuum running often enough that anti-wraparound never has to trigger.

Do indexes suffer from the same problem?

They do. When a heap tuple is marked dead, the index entries pointing at it are not removed immediately. Indexes accumulate dead entries, which increases scan times and wastes disk.

Regular VACUUM cleans up index entries that point to dead heap tuples, but if it cannot keep up with the write volume, index bloat keeps growing. REINDEX rebuilds an index from scratch and holds a lock; REINDEX CONCURRENTLY, available from PostgreSQL 12, rebuilds without blocking writes.

-- Estimate index bloat
SELECT
    schemaname, tablename, indexname,
    pg_size_pretty(pg_relation_size(indexrelid)) AS index_size,
    idx_scan AS index_scans
FROM pg_stat_user_indexes
JOIN pg_index ON pg_index.indexrelid = pg_stat_user_indexes.indexrelid
ORDER BY pg_relation_size(indexrelid) DESC
LIMIT 20;

If an index is much larger than you would expect for the number of live rows, it is probably bloated. Compare it against the table size: an index larger than its own table is a strong signal. Once bloat starts costing you query time, EXPLAIN ANALYZE is how you confirm it: look for sequential scans on tables that should be using an index, and check the Buffers: output for inflated I/O.

How do you keep an eye on all this?

These are the queries I actually use to track bloat and check that the background work is keeping up.

-- Tables most in need of vacuum
SELECT schemaname, relname, last_vacuum, last_autovacuum,
       n_dead_tup, n_live_tup
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 20;
 
-- Check if autovacuum is keeping up
SELECT relname,
       last_autovacuum,
       autovacuum_count,
       n_dead_tup
FROM pg_stat_user_tables
WHERE n_dead_tup > 10000
ORDER BY n_dead_tup DESC;
 
-- Table bloat: compare actual size vs estimated live data size
SELECT relname,
       pg_size_pretty(pg_total_relation_size(relid)) AS total_size,
       n_live_tup,
       n_dead_tup,
       last_autovacuum
FROM pg_stat_user_tables
ORDER BY pg_total_relation_size(relid) DESC
LIMIT 20;

Key takeaways

• MVCC creates dead tuples on every UPDATE and DELETE. This is by design.
• VACUUM reclaims dead tuple space. Autovacuum does it automatically, but the defaults are conservative.
• Long-running transactions pin the vacuum horizon. Set idle_in_transaction_session_timeout to prevent accidental stalls.
• Tune autovacuum aggressively for high-write tables: lower scale_factor, increase max_workers, reduce cost_delay.
• Monitor dead tuple counts and transaction ID age. Wraparound prevention is critical.
• Use VACUUM FULL, pg_repack, or REINDEX CONCURRENTLY only during maintenance windows.
• Table bloat is a symptom. Fix the root cause: make sure vacuum runs often enough and fast enough.