Postgres Autovacuum Is Not a Set-and-Forget Feature

What Bloat Actually Costs You

PostgreSQL uses MVCC (Multi-Version Concurrency Control). Every UPDATE creates a new row version; the old version stays on-disk until VACUUM reclaims it. Every DELETE leaves a dead tuple in place until VACUUM removes it. Autovacuum is the background process that does this cleanup.

When autovacuum falls behind, dead tuples accumulate. A table that should be 10GB grows to 30GB — not because you have more data, but because dead tuple overhead triples the physical size. Sequential scans read all 30GB. Index scans visit pages containing mostly dead tuples. Query planner statistics become skewed as the dead tuple count inflates apparent row counts. Buffer cache efficiency drops because you're caching mostly garbage.

The symptom pattern is familiar: a production Postgres instance that runs fine for the first few months after launch, then gradually slows. Response times creep up. Random slow queries that clear up after a manual VACUUM. A team that suspects hardware or connection pooling, not bloat. By the time they investigate, critical tables are at 40-60% bloat ratio and the manual fix is a VACUUM FULL that requires a maintenance window.

Understanding the Default Autovacuum Trigger

Autovacuum triggers a vacuum on a table when the number of dead tuples exceeds a threshold defined by two parameters:

-- Default autovacuum trigger formula:
-- autovacuum_vacuum_threshold + (autovacuum_vacuum_scale_factor * reltuples)
-- = dead tuples required to trigger autovacuum

-- Defaults:
autovacuum_vacuum_threshold = 50        -- base count
autovacuum_vacuum_scale_factor = 0.2    -- 20% of table row count

-- Example: table with 10 million rows
-- Trigger = 50 + (0.2 * 10,000,000) = 2,000,050 dead tuples
-- That's 2 million dead tuples before autovacuum runs

On a high-churn table with 10 million rows updating 100,000 rows per minute, you accumulate 2 million dead tuples in 20 minutes. Autovacuum then kicks off, but with default autovacuum_vacuum_cost_delay = 2ms and autovacuum_vacuum_cost_limit = 200, it throttles itself aggressively to avoid impacting foreground queries. On a heavily loaded system, a single autovacuum worker may take 30-45 minutes to finish vacuuming a large high-churn table — by which time another 4-6 million dead tuples have accumulated. Autovacuum is perpetually behind.

Diagnosing the Current State

Before tuning, measure the actual bloat in your database:

-- Table bloat estimate (requires pgstattuple extension for precision,
-- but this query gives a reasonable estimate without it)
SELECT
  schemaname,
  tablename,
  pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) AS total_size,
  pg_size_pretty(pg_relation_size(schemaname||'.'||tablename)) AS table_size,
  n_dead_tup,
  n_live_tup,
  ROUND(100.0 * n_dead_tup / NULLIF(n_live_tup + n_dead_tup, 0), 1) AS dead_pct,
  last_vacuum,
  last_autovacuum,
  last_analyze,
  last_autoanalyze
FROM pg_stat_user_tables
WHERE n_live_tup > 100000
ORDER BY n_dead_tup DESC
LIMIT 20;

Tables with dead_pct above 10% are candidates for tuning. Above 20% and you're seeing performance impact. Above 40% — particularly on frequently-queried tables — the immediate fix is a manual VACUUM ANALYZE before you worry about configuration.

Also check whether autovacuum is being blocked by long-running transactions:

-- Long-running transactions that block autovacuum
SELECT
  pid,
  usename,
  application_name,
  state,
  EXTRACT(EPOCH FROM (now() - xact_start)) / 60 AS xact_age_minutes,
  LEFT(query, 100) AS query_preview
FROM pg_stat_activity
WHERE xact_start IS NOT NULL
  AND state != 'idle'
  AND EXTRACT(EPOCH FROM (now() - xact_start)) > 300  -- older than 5 minutes
ORDER BY xact_start;

A long-running transaction (ORMs that hold transactions open, idle-in-transaction connections) prevents autovacuum from advancing the dead tuple cleanup past the transaction's snapshot horizon. No amount of autovacuum tuning fixes this — the fix is eliminating long-running transactions at the application layer.

Per-Table Autovacuum Settings for High-Churn Tables

The most effective approach is not to change global autovacuum settings (which affect every table in every database), but to override them at the table level for the specific tables that need aggressive vacuuming.

-- Aggressive autovacuum for a high-churn OLTP table
-- Example: orders table with ~5M rows, high UPDATE rate
ALTER TABLE orders SET (
  autovacuum_vacuum_scale_factor = 0.01,    -- trigger at 1% dead tuples (vs 20%)
  autovacuum_vacuum_threshold = 1000,       -- lower base threshold
  autovacuum_analyze_scale_factor = 0.005,  -- analyze at 0.5% changes
  autovacuum_vacuum_cost_delay = 0,         -- no throttling for this table
  autovacuum_vacuum_cost_limit = 800        -- higher I/O budget per worker
);

-- For extremely hot tables (e.g., a sessions or events table)
-- where even 1% bloat matters:
ALTER TABLE user_sessions SET (
  autovacuum_vacuum_scale_factor = 0.005,
  autovacuum_vacuum_threshold = 500,
  autovacuum_analyze_scale_factor = 0.002,
  autovacuum_vacuum_cost_delay = 0,
  autovacuum_vacuum_cost_limit = 1000,
  autovacuum_vacuum_insert_scale_factor = 0.05  -- PG13+: also vacuum on inserts
);

The key levers:

• autovacuum_vacuum_scale_factor: The fraction of table rows that must be dead before autovacuum triggers. Dropping this from 0.2 to 0.01 on a 5M-row table changes the trigger from 1,000,050 dead tuples to 51,000 — nearly 20x more responsive.
• autovacuum_vacuum_cost_delay: Setting this to 0 removes the intentional throttling. Autovacuum runs at full speed for this table. Only appropriate for tables where you've verified the I/O impact is acceptable.
• autovacuum_vacuum_cost_limit: Higher values let each autovacuum worker consume more I/O budget per cycle before sleeping. Combine with lower cost_delay for aggressive cleanup.

Global Configuration Changes Worth Making

While per-table settings handle the hot tables, the global defaults benefit from adjustment on any production OLTP system:

-- postgresql.conf changes for production OLTP
-- (apply via ALTER SYSTEM or parameter group on RDS/Aurora)

-- More autovacuum workers (default is 3, fine for small deployments)
autovacuum_max_workers = 6

-- Lower global scale factor so medium-sized tables get attention earlier
autovacuum_vacuum_scale_factor = 0.05   -- default 0.2

-- Reduce cost delay globally — modern SSDs don't need 2ms throttling
autovacuum_vacuum_cost_delay = 1        -- default 2ms; use 0 for NVMe

-- Higher cost limit means each worker does more work per cycle
autovacuum_vacuum_cost_limit = 400      -- default 200

-- Freeze rows more aggressively to prevent XID wraparound emergencies
autovacuum_freeze_max_age = 500000000   -- default 200000000 (200M)
-- This gives you more headroom before forced anti-wraparound vacuums

-- Log slow autovacuums for visibility
log_autovacuum_min_duration = 1000      -- log any autovacuum taking >1 second

The log_autovacuum_min_duration setting is particularly useful. Once enabled, your Postgres logs will show autovacuum completions with timing and page counts. You can immediately see which tables are consuming the most autovacuum time and how often they're being vacuumed.

The Transaction ID Wraparound Problem

Bloat is uncomfortable. Transaction ID wraparound is an emergency. PostgreSQL uses 32-bit transaction IDs. After approximately 2.1 billion transactions, XID wraps around — and Postgres will shut down the database rather than allow data corruption from XID confusion.

Postgres has two safety mechanisms: autovacuum anti-wraparound vacuums (triggered when a table's age approaches autovacuum_freeze_max_age), and an emergency shutdown at vacuum_freeze_max_age + some safety margin. If autovacuum falls sufficiently behind on freezing tuples, you can end up in a situation where Postgres forces emergency anti-wraparound vacuums on your entire database at an inconvenient time — or worse, refuses connections until you run VACUUM manually.

-- Check database and table age relative to wraparound limit
SELECT
  datname,
  age(datfrozenxid) AS db_age,
  2147483647 - age(datfrozenxid) AS xids_remaining
FROM pg_database
ORDER BY age(datfrozenxid) DESC;

-- Per-table age — focus on tables where age is high and n_dead_tup is high
SELECT
  schemaname,
  relname,
  age(relfrozenxid) AS table_age,
  2147483647 - age(relfrozenxid) AS xids_until_forced_vacuum,
  pg_size_pretty(pg_total_relation_size(schemaname||'.'||relname)) AS size
FROM pg_stat_user_tables
JOIN pg_class ON pg_class.relname = pg_stat_user_tables.relname
ORDER BY age(relfrozenxid) DESC
LIMIT 20;

If any table shows an age above 1.5 billion (xids_remaining below 600 million), schedule a maintenance VACUUM FREEZE for that table before autovacuum does it for you at the worst possible time. On large tables, VACUUM FREEZE can take hours and will hold an AccessShareLock during that time — you want to control when it runs.

Bloat Remediation When You're Already Behind

If you're reading this because you already have significant bloat and autovacuum can't keep up, the options are:

VACUUM ANALYZE (online, no lock): Reclaims dead tuples and updates statistics. Does not shrink the file on disk — free space is made available for future inserts/updates but the OS-level file size doesn't decrease. Zero downtime. Run this first to stop the bleeding.

-- Run VACUUM ANALYZE on the worst-offending tables
-- Can run while the database is live
VACUUM ANALYZE orders;
VACUUM ANALYZE user_events;
VACUUM ANALYZE sessions;

VACUUM FULL (offline, exclusive lock): Rewrites the table to a new file, reclaiming all dead space. File size shrinks to match live data. Requires an exclusive lock — all queries against that table wait. Only appropriate during a maintenance window. For large tables, use pg_repack instead, which does the same rewrite online without blocking reads.

-- pg_repack: online table repack without exclusive lock
-- Install: CREATE EXTENSION pg_repack;
pg_repack -h localhost -U postgres -d mydb -t orders
pg_repack -h localhost -U postgres -d mydb -t user_events

After remediating existing bloat, the per-table autovacuum configuration changes above will prevent recurrence. Monitor pg_stat_user_tables.n_dead_tup and set an alert if dead_pct exceeds 10% on any table over 100K rows — that's your early warning before bloat becomes a performance problem.