Eric Campbell 6 min read Databases

PostgreSQL VACUUM: Dead Tuple Cleanup

PostgreSQL uses Multi-Version Concurrency Control (MVCC) to handle concurrent transactions without locking readers and writers against each other. This elegant system has a cost: when you UPDATE or...

Key Insights

  • PostgreSQL’s MVCC creates dead tuples on every UPDATE and DELETE, requiring VACUUM to reclaim space and prevent table bloat that degrades query performance
  • Autovacuum handles cleanup automatically but often needs tuning for write-heavy workloads—default thresholds can leave high-churn tables bloated for hours
  • Long-running transactions block VACUUM from cleaning up dead tuples, creating a snowball effect where bloat accumulates faster than it can be removed

Understanding Dead Tuples in PostgreSQL

PostgreSQL uses Multi-Version Concurrency Control (MVCC) to handle concurrent transactions without locking readers and writers against each other. This elegant system has a cost: when you UPDATE or DELETE a row, PostgreSQL doesn’t immediately remove the old data. Instead, it marks the old tuple (row version) as dead and creates a new version for UPDATEs.

This behavior is fundamental to MVCC. A transaction that started before your UPDATE needs to see the old data to maintain consistency. But once all transactions that could see the old tuple have completed, that dead tuple becomes garbage—it’s taking up space without serving any purpose.

Dead tuples accumulate quickly in write-heavy tables. An e-commerce order status table updated thousands of times per minute can generate gigabytes of dead tuples daily. This bloat degrades performance in multiple ways: queries scan more disk pages than necessary, indexes grow larger, and the buffer cache becomes less efficient.

Here’s how to see dead tuples accumulating:

-- Create a test table
CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    status VARCHAR(20),
    updated_at TIMESTAMP
);

-- Insert initial data
INSERT INTO orders (status, updated_at)
SELECT 'pending', NOW()
FROM generate_series(1, 100000);

-- Check initial stats
SELECT schemaname, relname, n_live_tup, n_dead_tup, last_vacuum, last_autovacuum
FROM pg_stat_user_tables
WHERE relname = 'orders';

-- Perform updates that create dead tuples
UPDATE orders SET status = 'shipped', updated_at = NOW()
WHERE id <= 50000;

-- Check again - you'll see dead tuples
SELECT schemaname, relname, n_live_tup, n_dead_tup, last_vacuum, last_autovacuum
FROM pg_stat_user_tables
WHERE relname = 'orders';

After the UPDATE, you’ll see approximately 50,000 dead tuples. Those old “pending” versions are still occupying space, waiting for VACUUM to clean them up.

How VACUUM Works

VACUUM is PostgreSQL’s garbage collector. It scans tables to identify dead tuples that no running transaction can see, marks that space as reusable, and updates the visibility map and free space map so future operations can reuse the space.

Regular VACUUM doesn’t return space to the operating system—it marks it as available within the table file. This is usually what you want because the space will likely be needed again soon. VACUUM also updates table statistics and prevents transaction ID wraparound, a critical maintenance task.

VACUUM FULL takes a more aggressive approach: it rewrites the entire table, compacting it and returning freed space to the OS. This requires an exclusive lock, blocking all operations on the table. Use it sparingly.

Here’s VACUUM in action:

-- Check table size before vacuum
SELECT pg_size_pretty(pg_relation_size('orders')) AS table_size,
       pg_size_pretty(pg_total_relation_size('orders')) AS total_size;

-- Run vacuum with verbose output
VACUUM VERBOSE orders;

-- Check dead tuples again
SELECT n_dead_tup FROM pg_stat_user_tables WHERE relname = 'orders';

-- For comparison, check size after VACUUM FULL (don't do this in production!)
VACUUM FULL orders;
SELECT pg_size_pretty(pg_relation_size('orders')) AS table_size_after_full;

The VERBOSE output shows you exactly what VACUUM is doing: pages scanned, tuples removed, and whether the table could be truncated. Regular VACUUM typically completes in seconds to minutes, while VACUUM FULL can take hours on large tables.

Autovacuum Configuration and Tuning

PostgreSQL’s autovacuum daemon automatically runs VACUUM on tables that need it. By default, it triggers when a table has 50 dead tuples plus 20% of its rows modified. For a 1 million row table, that’s 200,050 dead tuples before autovacuum kicks in.

This default is conservative, designed for general workloads. High-throughput tables need more aggressive settings. A table receiving 10,000 updates per minute could accumulate massive bloat waiting for autovacuum.

Check your current autovacuum settings:

-- Global autovacuum settings
SELECT name, setting, unit, short_desc
FROM pg_settings
WHERE name LIKE 'autovacuum%'
ORDER BY name;

-- Key settings to examine:
-- autovacuum_vacuum_threshold (default: 50)
-- autovacuum_vacuum_scale_factor (default: 0.2)
-- autovacuum_max_workers (default: 3)
-- autovacuum_naptime (default: 1min)

For write-heavy tables, override settings at the table level:

-- Make autovacuum more aggressive for a specific table
ALTER TABLE orders SET (
    autovacuum_vacuum_threshold = 100,
    autovacuum_vacuum_scale_factor = 0.05
);

-- This means: vacuum when 100 + 5% of rows are dead
-- For 1M rows: vacuum at 50,100 dead tuples instead of 200,050

-- For extremely high-churn tables
ALTER TABLE order_status_log SET (
    autovacuum_vacuum_threshold = 1000,
    autovacuum_vacuum_scale_factor = 0.01,
    autovacuum_vacuum_cost_delay = 10
);

The cost delay settings control how aggressively VACUUM runs. Lower delays mean faster cleanup but more I/O impact. Tune based on your workload and I/O capacity.

Monitoring VACUUM Activity

You can’t tune what you don’t measure. PostgreSQL provides several views for monitoring vacuum activity.

-- When was each table last vacuumed?
SELECT schemaname, relname,
       last_vacuum,
       last_autovacuum,
       n_dead_tup,
       n_live_tup,
       round(100.0 * n_dead_tup / NULLIF(n_live_tup + n_dead_tup, 0), 2) AS dead_pct
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 20;

-- What's currently being vacuumed?
SELECT pid, datname, relid::regclass AS table_name,
       phase,
       heap_blks_total,
       heap_blks_scanned,
       heap_blks_vacuumed,
       round(100.0 * heap_blks_scanned / NULLIF(heap_blks_total, 0), 2) AS pct_complete
FROM pg_stat_progress_vacuum;

-- Are autovacuum workers running?
SELECT pid, usename, state, wait_event_type, query_start, query
FROM pg_stat_activity
WHERE query LIKE '%autovacuum%'
  AND pid != pg_backend_pid();

These queries help you identify tables that need attention and verify that autovacuum is actually running. If you see tables with high dead tuple counts and no recent vacuum activity, you have a problem.

Common VACUUM Problems and Solutions

Problem 1: Autovacuum Can’t Keep Up

If tables accumulate bloat despite autovacuum running, you need more aggressive settings or more workers. Check if autovacuum is constantly busy:

-- Identify bloated tables (requires pgstattuple extension)
CREATE EXTENSION IF NOT EXISTS pgstattuple;

SELECT schemaname, tablename,
       pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) AS size,
       round(100.0 * dead_tuple_percent, 2) AS dead_pct
FROM pg_stat_user_tables
JOIN LATERAL pgstattuple(schemaname||'.'||tablename) ON true
WHERE n_live_tup > 10000
ORDER BY dead_tuple_percent DESC
LIMIT 10;

Problem 2: Long-Running Transactions

VACUUM can’t remove tuples that any transaction might still need to see. Long-running transactions block cleanup:

-- Find old transactions blocking vacuum
SELECT pid, usename, state, 
       age(backend_xid) AS xid_age,
       age(backend_xmin) AS xmin_age,
       now() - xact_start AS duration,
       query
FROM pg_stat_activity
WHERE backend_xmin IS NOT NULL
ORDER BY age(backend_xmin) DESC
LIMIT 10;

Transactions with high xmin_age prevent VACUUM from cleaning up. Identify and terminate them if necessary.

Problem 3: Transaction ID Wraparound

PostgreSQL’s transaction IDs are 32-bit integers that wrap around. VACUUM prevents this catastrophe by freezing old tuples:

-- Check transaction ID age
SELECT datname, age(datfrozenxid) AS xid_age,
       2147483647 - age(datfrozenxid) AS xids_remaining
FROM pg_database
ORDER BY age(datfrozenxid) DESC;

-- Tables closest to needing emergency vacuum
SELECT schemaname, relname, age(relfrozenxid) AS xid_age
FROM pg_class
JOIN pg_stat_user_tables ON pg_class.oid = relid
ORDER BY age(relfrozenxid) DESC
LIMIT 10;

If xid_age approaches 200 million, investigate immediately. PostgreSQL will force autovacuum at 200 million and shut down at 2 billion.

Best Practices and Performance Tips

Schedule manual VACUUM during maintenance windows for critical tables. Don’t rely solely on autovacuum:

#!/bin/bash
# vacuum_critical_tables.sh

psql -d mydb <<EOF
VACUUM ANALYZE orders;
VACUUM ANALYZE order_items;
VACUUM ANALYZE inventory;
EOF

Combine VACUUM with ANALYZE to update query planner statistics simultaneously:

-- Single command for both operations
VACUUM ANALYZE orders;

-- Or configure autovacuum to do both
ALTER TABLE orders SET (
    autovacuum_vacuum_scale_factor = 0.05,
    autovacuum_analyze_scale_factor = 0.02
);

Avoid VACUUM FULL in production. Use pg_repack instead for online table rebuilds:

# pg_repack rebuilds tables without exclusive locks
pg_repack -d mydb -t orders

Monitor vacuum I/O impact during peak hours:

-- Check vacuum I/O cost settings
SHOW autovacuum_vacuum_cost_delay;
SHOW autovacuum_vacuum_cost_limit;

-- Reduce I/O impact during business hours (postgresql.conf)
-- autovacuum_vacuum_cost_delay = 20ms (default: 2ms)

Set realistic expectations. VACUUM is maintenance overhead, not optional. Budget for it in your I/O capacity planning. A write-heavy database might spend 10-20% of I/O on vacuum operations. That’s normal and necessary.

The key to VACUUM success is proactive monitoring and tuning. Don’t wait for bloat to cause performance problems. Establish baseline metrics, set up alerts for unusual dead tuple accumulation, and adjust autovacuum settings based on actual workload patterns. PostgreSQL gives you the tools—use them.

Liked this? There's more.

Every week: one practical technique, explained simply, with code you can use immediately.