PostgreSQL VACUUM Tuning: A Comprehensive Guide

A fintech client came to us with a PostgreSQL cluster where queries that normally ran in 50ms were intermittently spiking to 8 seconds with no obvious cause. No slow queries in the log. No lock contention. But the pg_stat_user_tables showed one table with 14 million dead tuples and autovacuum hadn't touched it in three days. The culprit: autovacuum cost delay was set so conservatively that the vacuum process was throttling itself to near-zero on a high-write table. One configuration change later, dead tuple accumulation dropped to under 100K, and the latency spikes disappeared.

VACUUM is PostgreSQL's most important background process, and also the most commonly misconfigured. This guide covers what VACUUM actually does under the hood, how to tune autovacuum for real-world write loads, and how to diagnose and fix bloat before it becomes an incident.

How VACUUM Works: The MVCC Foundation

PostgreSQL uses Multi-Version Concurrency Control (MVCC). When you UPDATE a row, PostgreSQL doesn't modify it in place — it writes a new version of the row and marks the old version as dead. When you DELETE a row, it's marked dead but not immediately removed. These dead row versions are called dead tuples.

Dead tuples cause three problems:

1. Storage bloat — tables grow larger than the live data requires, causing more I/O per query
2. Index bloat — indexes also hold references to dead tuples, slowing down index scans
3. Transaction ID wraparound — PostgreSQL uses 32-bit transaction IDs; at 2 billion transactions, they wrap around. A table with unfrozen tuples older than ~2 billion XID ago becomes unreadable to protect consistency. PostgreSQL will force a VACUUM FREEZE (which takes the table offline) if you get too close.

VACUUM reclaims space from dead tuples and updates the visibility map (which allows index-only scans to skip heap fetches). VACUUM FREEZE additionally updates the transaction ID on old tuples to a special "frozen" value that's immune to wraparound.

-- See dead tuple counts per table
SELECT
  schemaname,
  relname AS table_name,
  n_live_tup,
  n_dead_tup,
  ROUND(n_dead_tup::numeric / NULLIF(n_live_tup + n_dead_tup, 0) * 100, 1) AS dead_pct,
  last_autovacuum,
  last_autoanalyze
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 20;

-- Tables approaching wraparound danger zone (act on > 1.5 billion)
SELECT
  relname,
  age(relfrozenxid) AS xid_age,
  pg_size_pretty(pg_total_relation_size(oid)) AS size
FROM pg_class
WHERE relkind = 'r'
ORDER BY age(relfrozenxid) DESC
LIMIT 20;

Why Autovacuum Defaults Fail High-Write Tables

Autovacuum runs automatically based on a threshold: it triggers when dead tuples exceed autovacuum_vacuum_threshold + autovacuum_vacuum_scale_factor × table_rows. The defaults:

autovacuum_vacuum_threshold = 50         # trigger after 50 dead tuples
autovacuum_vacuum_scale_factor = 0.2     # plus 20% of table row count
autovacuum_vacuum_cost_delay = 2ms       # pause between I/O bursts
autovacuum_vacuum_cost_limit = 200       # I/O cost units per burst

For a table with 10 million rows, vacuum triggers when dead tuples exceed: 50 + (0.2 × 10,000,000) = 2,000,050 dead tuples. That's 2 million dead rows before autovacuum even starts. And once it starts, cost_delay=2ms with cost_limit=200 throttles it to roughly 10MB/s — slow enough to fall behind any serious write workload.

The solution is per-table autovacuum overrides for high-write tables:

-- Per-table autovacuum tuning (override without changing global settings)
ALTER TABLE orders SET (
  autovacuum_vacuum_scale_factor = 0.01,    -- trigger at 1% dead tuples (not 20%)
  autovacuum_vacuum_threshold = 1000,        -- minimum 1000 dead rows
  autovacuum_vacuum_cost_delay = 0,          -- no throttling on this table
  autovacuum_vacuum_cost_limit = 800,        -- higher I/O budget
  autovacuum_analyze_scale_factor = 0.005    -- update stats more frequently
);

-- Verify settings took effect
SELECT relname, reloptions
FROM pg_class
WHERE relname = 'orders';

Cost Delay: The Throttle That Kills Autovacuum

The autovacuum_vacuum_cost_delay parameter is the most impactful tuning knob for autovacuum performance, and the most commonly set too high.

The idea is correct: vacuum shouldn't monopolize I/O. But the default of 2ms between each 200-cost I/O burst limits autovacuum to roughly 10MB/s on an SSD that can do 500MB/s. For a busy orders table accumulating dead tuples faster than vacuum can clean them, 2ms delay means vacuum will never catch up.

-- Check current global settings
SHOW autovacuum_vacuum_cost_delay;
SHOW autovacuum_vacuum_cost_limit;

-- For SSDs: set delay to 0 or 2ms maximum
-- For HDDs: 10-20ms is appropriate
-- Global change (requires postgresql.conf edit + reload):
ALTER SYSTEM SET autovacuum_vacuum_cost_delay = '2ms';
ALTER SYSTEM SET autovacuum_vacuum_cost_limit = 400;
SELECT pg_reload_conf();

-- Check if autovacuum is currently running on any tables
SELECT
  pid,
  datname,
  query,
  now() - pg_stat_activity.query_start AS duration
FROM pg_stat_activity
WHERE query LIKE 'autovacuum%'
ORDER BY duration DESC;

Monitoring Autovacuum Effectively

-- Tables with the most dead tuples right now
SELECT
  schemaname || '.' || relname AS table,
  n_dead_tup,
  n_live_tup,
  last_autovacuum,
  last_autovacuum_count,
  autovacuum_count
FROM pg_stat_user_tables
WHERE n_dead_tup > 10000
ORDER BY n_dead_tup DESC
LIMIT 20;

-- Tables that autovacuum hasn't touched recently (> 12 hours)
SELECT
  relname,
  n_dead_tup,
  last_autovacuum,
  EXTRACT(EPOCH FROM (now() - last_autovacuum)) / 3600 AS hours_since_vacuum
FROM pg_stat_user_tables
WHERE n_dead_tup > 1000
  AND (last_autovacuum IS NULL OR last_autovacuum < now() - interval '12 hours')
ORDER BY n_dead_tup DESC;

-- Autovacuum worker activity
SELECT
  schemaname,
  relname,
  last_autovacuum,
  last_autoanalyze,
  vacuum_count,
  autovacuum_count,
  analyze_count,
  autoanalyze_count
FROM pg_stat_user_tables
ORDER BY last_autovacuum ASC NULLS FIRST
LIMIT 20;

-- Table bloat estimate (rough but fast)
SELECT
  schemaname,
  relname,
  pg_size_pretty(pg_total_relation_size(schemaname||'.'||relname)) AS total_size,
  pg_size_pretty(pg_relation_size(schemaname||'.'||relname)) AS table_size,
  ROUND(100 * pg_relation_size(schemaname||'.'||relname)::numeric /
    NULLIF(pg_total_relation_size(schemaname||'.'||relname), 0), 1) AS table_pct
FROM pg_stat_user_tables
ORDER BY pg_total_relation_size(schemaname||'.'||relname) DESC
LIMIT 20;

Alert thresholds to set in your monitoring

Running VACUUM Manually

-- Standard VACUUM: reclaim dead tuples, update visibility map
VACUUM orders;

-- VACUUM with analysis update (stats for query planner)
VACUUM ANALYZE orders;

-- VACUUM VERBOSE: see exactly what happened
VACUUM VERBOSE orders;
-- Output includes: pages scanned, dead tuples removed, index cleanups

-- VACUUM FREEZE: reset XID on old tuples (do this before wraparound approaches)
VACUUM FREEZE orders;

-- Check what's currently running
SELECT pid, query, now() - query_start AS duration
FROM pg_stat_activity
WHERE query LIKE '%VACUUM%'
ORDER BY duration DESC;

-- Cancel a stuck vacuum
SELECT pg_cancel_backend(pid)
FROM pg_stat_activity
WHERE query LIKE '%VACUUM%';

VACUUM FULL: when to use it (rarely)

VACUUM FULL rewrites the entire table from scratch, returning space to the OS. It requires an exclusive lock on the table for the entire duration — minutes to hours on large tables. In most production scenarios, this is unacceptable.

-- VACUUM FULL: rewrites table, requires exclusive lock (use pg_repack instead)
VACUUM FULL orders;  -- DON'T do this in production without a maintenance window

-- Better alternative: pg_repack (online, no long lock)
-- Install: https://github.com/reorg/pg_repack
pg_repack -h localhost -d mydb -t orders  -- reclaims bloat without locking

-- When pg_repack is appropriate:
-- 1. Table has significant physical bloat (2x+ its live data size)
-- 2. You've fixed the underlying autovacuum config so bloat won't recur
-- 3. You have a maintenance window (repack uses disk space for temp copy)

Transaction ID Wraparound: The Emergency You Want to Avoid

PostgreSQL uses 32-bit transaction IDs. At approximately 2 billion transactions, the counter wraps. To prevent a table from becoming unreadable, PostgreSQL starts warning (and eventually refusing writes) as XID age approaches 2 billion. At 1.6 billion, it logs warnings. Above 1.8 billion, it starts forced VACUUMs. Above ~2 billion, PostgreSQL will refuse to start with "database is not accepting connections to avoid wraparound data loss."

-- The single most important query for wraparound monitoring
SELECT
  datname,
  age(datfrozenxid) AS db_xid_age,
  pg_size_pretty(pg_database_size(datname)) AS db_size
FROM pg_database
ORDER BY age(datfrozenxid) DESC;

-- 1 billion = time to investigate
-- 1.5 billion = schedule VACUUM FREEZE immediately
-- 1.8 billion = emergency

-- Per-table wraparound danger
SELECT
  n.nspname AS schema,
  c.relname AS table,
  age(c.relfrozenxid) AS xid_age,
  pg_size_pretty(pg_total_relation_size(c.oid)) AS size
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind = 'r'
  AND age(c.relfrozenxid) > 500000000  -- > 500M XIDs
ORDER BY age(c.relfrozenxid) DESC;

-- Emergency: manually freeze a table that's approaching wraparound
VACUUM FREEZE ANALYZE high_xid_table;

To avoid ever reaching emergency wraparound, ensure autovacuum_freeze_max_age is set appropriately (default 200 million XIDs) and that autovacuum is actually keeping up with your write rate.

Index Bloat

VACUUM doesn't always reclaim index bloat — it marks index pages as available for reuse, but the index doesn't physically shrink. On tables with many updates/deletes, indexes can grow to 2–5× their optimal size, making every index scan slower.

-- Estimate index bloat (requires pgstattuple extension)
CREATE EXTENSION IF NOT EXISTS pgstattuple;

SELECT
  indexrelid::regclass AS index_name,
  pg_size_pretty(pg_relation_size(indexrelid)) AS index_size,
  round(pgstatindex(indexrelid::regclass).avg_leaf_density, 1) AS leaf_density_pct
FROM pg_index
WHERE indrelid = 'orders'::regclass
ORDER BY pg_relation_size(indexrelid) DESC;

-- leaf_density < 70% → significant bloat
-- leaf_density < 50% → severe bloat, rebuild index

-- Rebuild bloated index without locking (PostgreSQL 12+)
REINDEX INDEX CONCURRENTLY orders_user_id_idx;

-- Rebuild all indexes on a table concurrently
REINDEX TABLE CONCURRENTLY orders;

Production Autovacuum Configuration

Recommended starting point for a high-write PostgreSQL instance (adjust based on your I/O capacity):

# postgresql.conf — autovacuum settings for production OLTP

# Worker count: increase if you have many tables with active write workloads
autovacuum_max_workers = 6          # default: 3

# Trigger thresholds
autovacuum_vacuum_threshold = 50    # start after 50 dead tuples
autovacuum_vacuum_scale_factor = 0.05   # + 5% of table (down from 20%)
autovacuum_analyze_threshold = 50
autovacuum_analyze_scale_factor = 0.02  # + 2% of table for analyze

# I/O throttling — reduce cost_delay for SSDs
autovacuum_vacuum_cost_delay = 2ms  # 0ms for NVMe SSDs
autovacuum_vacuum_cost_limit = 400  # up from 200

# Freeze settings
autovacuum_freeze_max_age = 150000000   # vacuum-freeze every 150M XIDs
vacuum_freeze_min_age = 50000000
vacuum_freeze_table_age = 100000000

-- Per-table overrides for your busiest tables (apply without restart)
ALTER TABLE orders SET (
  autovacuum_vacuum_scale_factor = 0.01,
  autovacuum_vacuum_cost_delay = 0,
  autovacuum_vacuum_cost_limit = 1000
);

ALTER TABLE events SET (
  autovacuum_vacuum_scale_factor = 0.005,
  autovacuum_vacuum_threshold = 500,
  autovacuum_vacuum_cost_delay = 0
);

-- Reload config after postgresql.conf changes
SELECT pg_reload_conf();

Working with JusDB on PostgreSQL Performance

VACUUM tuning is straightforward once you know what to look for — but the symptoms (slow queries, intermittent spikes, growing storage) look identical to a dozen other PostgreSQL problems. Diagnosing which tables are the issue, whether bloat or wraparound is the root cause, and setting the right per-table parameters takes experience with real production workloads.

We tune PostgreSQL autovacuum as part of our PostgreSQL consulting and managed database SRE work. If your queries are inexplicably slow or your PostgreSQL storage is growing faster than your data should explain, reach out.

Related reading: PostgreSQL Explained | MySQL Performance Tuning | Database Schema Design Fundamentals