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

PostgreSQL's MVCC system is elegant: readers never block writers, writers never block readers. But that elegance comes with a maintenance cost: dead tuples. Every UPDATE and DELETE creates row versions that are no longer visible to any transaction but still occupy disk space. If you don't understand MVCC and vacuum, your tables will bloat, your queries will slow down, and you'll wonder why your 10GB table is using 50GB of disk.

How MVCC Creates Dead Tuples

In PostgreSQL, an UPDATE doesn't modify a row in place. It creates a new version of the row and marks the old version as dead. A DELETE marks the row as dead without creating 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)

When you run a query, PostgreSQL checks these values against your transaction's snapshot to determine which row versions are visible, and the snapshot rules depend on your transaction isolation level. Old versions that are invisible to all active transactions are "dead tuples."

This has a non-obvious implication: even a simple UPDATE users SET last_login = NOW() WHERE id = 1 creates a dead tuple. If this query runs on every login and you have 10,000 logins per minute, you're creating 10,000 dead tuples per minute on the users table. Without aggressive vacuum tuning, this table will bloat quickly.

-- 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;

What Vacuum Does

VACUUM reclaims space occupied by dead tuples. It has two modes:

Regular VACUUM

• Marks dead tuple space as reusable within the table
• Does not return space to the operating system
• Does not hold exclusive locks: runs concurrently with reads and writes
• Updates the visibility map (used for index-only scans)
• Updates the free space map (so new inserts can reuse reclaimed space)

VACUUM FULL

• Rewrites the entire table, physically compacting it
• Returns space to the operating system
• Holds an exclusive lock on the table for the entire duration
• Essentially unusable on production tables during business hours

In practice, you almost always want regular VACUUM. Reserve VACUUM FULL for maintenance windows on severely bloated tables. An alternative to VACUUM FULL is pg_repack, an extension that rewrites tables without holding an exclusive lock for the full duration. It's a lifesaver for production systems where you can't afford downtime but need to reclaim disk space.

The Long-Running Transaction Problem

VACUUM can only reclaim dead tuples that are invisible to all active transactions. If you have a transaction that started 2 hours ago and is still open, every dead tuple created in the last 2 hours is safe from vacuum. The oldest active transaction ID effectively sets a floor: vacuum cannot clean up anything newer than that transaction.

This is one of the most common causes of unexpected table bloat. Common culprits:

• Idle transactions: a developer opens psql, runs BEGIN, then walks away. That open transaction pins the vacuum horizon.
• Long-running reports: a dashboarding query that takes 30 minutes holds back vacuum for the entire duration.
• Connection pool leaks: an application that opens transactions but doesn't 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 automatically kill idle transactions after a threshold (e.g., 5 minutes). This prevents accidental vacuum stalls.

Autovacuum: Your First Line of Defense

Autovacuum is PostgreSQL's background process that runs VACUUM automatically. It triggers based on:

threshold = autovacuum_vacuum_threshold + autovacuum_vacuum_scale_factor × n_live_tup

With defaults (threshold = 50, scale_factor = 0.2), a table with 10,000 rows triggers vacuum after 2,050 dead tuples (50 + 0.2 × 10,000).

The problem with percentage-based thresholds is scaling. A table with 100 million rows won't trigger vacuum until it has 20 million dead tuples. That's potentially hundreds of gigabytes of bloat before vacuum kicks in.

Tuning for High-Write Tables

The defaults are conservative. For tables with heavy write activity:

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
);

Also consider:

• autovacuum_max_workers: default is 3. Increase if you have many large tables competing for vacuum time. Each worker can only vacuum one table at a time, so with 3 workers and 50 tables needing vacuum, you have a backlog.
• autovacuum_vacuum_cost_delay: controls how aggressively vacuum runs. Lower values = more aggressive. Default is 2ms; try 0 for high-write workloads. This is the single most impactful tuning parameter.
• autovacuum_naptime: how often the launcher checks for tables needing vacuum. Default is 1 minute.

Per-Table Tuning

Not all tables need the same vacuum strategy. Identify your hottest tables (the ones with the most writes per second) and tune them individually. Leave the defaults for tables with low write rates.

A practical approach: query pg_stat_user_tables weekly, identify the top 10 tables by n_dead_tup, and add per-table tuning for any table that consistently appears.

Transaction ID Wraparound

PostgreSQL uses 32-bit transaction IDs, which wrap around after ~4 billion transactions. Without vacuum, old transaction IDs become ambiguous: the database can't tell if a row was created in the past or the future. This is transaction ID wraparound, and it's the worst-case scenario: PostgreSQL will shut down to prevent data corruption.

VACUUM prevents wraparound by "freezing" old transaction IDs: marking them as definitively in the past. The autovacuum_freeze_max_age setting (default: 200 million transactions) triggers 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, you have a problem. Monitor this metric. Anti-wraparound vacuum is aggressive and non-interruptible. It will consume significant I/O and CPU. If it kicks in on a high-traffic table during peak hours, you'll feel it. The way to prevent this is to keep regular vacuum running frequently enough that anti-wraparound never triggers.

Index Bloat

Dead tuples affect indexes too. When a heap tuple is marked dead, index entries pointing to it are not immediately removed. Over time, indexes accumulate dead entries, increasing scan times and disk usage.

Regular VACUUM cleans up index entries pointing to dead heap tuples. But if vacuum can't keep up, index bloat grows. The REINDEX command rebuilds an index from scratch, but holds a lock. REINDEX CONCURRENTLY (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'd expect for the number of live rows, it's likely bloated. Compare the index size against the table size; an index that's larger than the table itself is a strong signal.

Practical Monitoring Queries

-- 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 handles this automatically, but 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.

Related Reading

• PostgreSQL Concurrency Control: Isolation Levels, Locks, and Real-World Race Conditions: how transaction snapshots and isolation levels interact with the MVCC visibility rules above. The snapshot model is what makes a long-running SELECT block your vacuum horizon.
• PostgreSQL EXPLAIN ANALYZE: How to Read Query Plans and Fix Slow Queries: when bloat starts hurting query performance, EXPLAIN ANALYZE is how you confirm it. Look for sequential scans on tables that should use indexes, and check Buffers: output for inflated I/O.