Postgres Autovacuum, When Defaults Fail and How to Fix Them

TL;DR — Autovacuum defaults assume balanced read/write workloads. They fail silently on hot tables. Drop autovacuum_vacuum_scale_factor per-table for high-churn tables. Watch pg_stat_user_tables weekly. Bloat is the silent killer of Postgres performance.

After memory tuning, the next item on the checklist is autovacuum. It’s a process that runs in the background, cleans up dead rows, and reclaims space. When it’s working, you don’t think about it. When it isn’t, your indexes bloat, query plans degrade, and a table that “should” be fast becomes mysteriously slow.

Autovacuum is one of the few Postgres subsystems where the defaults are genuinely wrong for most production workloads. Not subtly wrong — actually wrong. This post explains why, and what to do about it.

What autovacuum actually does

Two jobs:

1. VACUUM: marks dead tuples (rows superseded by UPDATEs or DELETEs) as reusable space. Without this, tables grow forever and indexes accumulate stale pointers.
2. ANALYZE: refreshes the planner’s statistics about column distributions. Without this, the planner makes bad join-order and access-method decisions.

It runs them automatically based on thresholds:

• VACUUM triggers when dead tuples exceed autovacuum_vacuum_threshold + autovacuum_vacuum_scale_factor × n_live_tup
• ANALYZE triggers when changed rows exceed autovacuum_analyze_threshold + autovacuum_analyze_scale_factor × n_live_tup

Defaults:

autovacuum_vacuum_threshold = 50
autovacuum_vacuum_scale_factor = 0.2     # 20% of live rows
autovacuum_analyze_threshold = 50
autovacuum_analyze_scale_factor = 0.1    # 10% of live rows

A 10M-row table will only autovacuum when 2M (20%) rows are dead. By the time autovacuum kicks in, you’ve got 2M dead tuples bloating storage and indexes. On a write-heavy hot table, that’s an hour of accumulated bloat before any cleanup happens.

Spotting the problem

The single most useful query for diagnosing autovacuum health:

SELECT
  schemaname || '.' || relname AS table,
  n_live_tup,
  n_dead_tup,
  round(n_dead_tup * 100.0 / NULLIF(n_live_tup + n_dead_tup, 0), 1) AS dead_pct,
  last_autovacuum,
  last_autoanalyze,
  autovacuum_count,
  pg_size_pretty(pg_total_relation_size(relid)) AS size
FROM pg_stat_user_tables
WHERE n_dead_tup > 1000
ORDER BY dead_pct DESC NULLS LAST
LIMIT 20;

What you’re looking for:

• dead_pct over 20% on any sizeable table = autovacuum isn’t keeping up
• last_autovacuum hours stale on a known-hot table = autovacuum may be skipping or failing
• autovacuum_count = 0 after weeks of writes = autovacuum has never run on this table

If you see the first one regularly, your default autovacuum_vacuum_scale_factor is wrong for your workload.

Index bloat — the downstream symptom

When tables bloat, indexes bloat worse. The B-tree never reclaims space the way a heap can. Bloated indexes are bigger, slower to scan, and consume more shared_buffers for the same logical content.

Quick approximation of bloat (the rigorous version uses pgstattuple):

SELECT
  schemaname || '.' || indexrelname AS index,
  pg_size_pretty(pg_relation_size(indexrelid)) AS index_size,
  pg_size_pretty(pg_relation_size(relid))      AS table_size,
  idx_scan
FROM pg_stat_user_indexes
ORDER BY pg_relation_size(indexrelid) DESC
LIMIT 20;

If an index is anywhere close to its table’s size and the table doesn’t have wide rows, suspect bloat. REINDEX INDEX CONCURRENTLY <name>; rebuilds it without taking a lock (Postgres 12+).

The fix: per-table autovacuum settings

Don’t change global settings. Set per-table overrides for the tables that actually need them. A high-churn notifications.message_log (lots of UPDATEs) vs a low-churn customers (mostly INSERTs) want different settings.

-- Hot table: vacuum aggressively
ALTER TABLE notifications.message_log SET (
  autovacuum_vacuum_scale_factor = 0.05,        -- vacuum at 5% dead, not 20%
  autovacuum_vacuum_threshold = 1000,
  autovacuum_analyze_scale_factor = 0.02,
  autovacuum_analyze_threshold = 500,
  autovacuum_vacuum_cost_limit = 1000           -- let it work faster
);

What changed:

• scale_factor 0.05 = vacuum at 5% dead tuples instead of 20%. On a 10M-row table, that’s 500K dead before vacuum, not 2M.
• vacuum_cost_limit = 1000 (up from default 200) = autovacuum is allowed to do 5× more I/O per cycle before sleeping. It finishes faster.

For mostly-read tables, defaults are usually fine. Don’t over-tune.

Long-running transactions block vacuum

A subtle gotcha: VACUUM cannot remove dead tuples newer than the oldest still-open transaction. If a backend holds a transaction open for hours (often a forgotten BEGIN in a psql session, or a long-running report query), every dead tuple created after it started is unvacuumable. Bloat accumulates regardless of how aggressive autovacuum is.

Find them:

SELECT pid, usename, application_name,
       now() - xact_start AS txn_age,
       state, query
FROM pg_stat_activity
WHERE state != 'idle'
  AND xact_start IS NOT NULL
  AND now() - xact_start > interval '5 minutes'
ORDER BY xact_start;

Kill the worst offenders (SELECT pg_terminate_backend(pid);) and confirm vacuum starts catching up. Long-term, find the application code that leaks transactions and fix it.

When autovacuum isn’t enough: manual VACUUM

For tables that have already bloated, autovacuum cleans up dead tuples but doesn’t reclaim disk space back to the OS. To shrink a bloated table:

-- Option 1: VACUUM FULL — locks the table, rewrites entirely
VACUUM FULL my_table;

-- Option 2: pg_repack extension — does the same without locking
SELECT pg_repack.repack_table('public.my_table');

VACUUM FULL is what you use during a maintenance window. pg_repack is what you use when you can’t afford the lock. Install pg_repack ahead of time so it’s available when you need it.

Monitoring

Set up alerts on three things:

-- 1. Tables with >30% dead tuples
SELECT count(*) FROM pg_stat_user_tables
WHERE n_dead_tup * 100.0 / NULLIF(n_live_tup + n_dead_tup, 0) > 30
  AND n_live_tup > 1000;

-- 2. Long-running transactions (>10 min)
SELECT count(*) FROM pg_stat_activity
WHERE state != 'idle' AND now() - xact_start > interval '10 minutes';

-- 3. Autovacuum workers near limit
SELECT count(*) FROM pg_stat_activity WHERE backend_type = 'autovacuum worker';
-- Compare to autovacuum_max_workers (default 3)

If #3 is constantly maxed out, autovacuum can’t run on enough tables in parallel. Raise autovacuum_max_workers.

Common Pitfalls

Disabling autovacuum because “it slows things down.” It doesn’t. Bloat is what slows things down. Disabling autovacuum guarantees bloat. If you genuinely need to disable it for a bulk-load operation, re-enable it the moment you’re done.

VACUUM FULL in production without a maintenance window. Locks the table. Bad day. Use pg_repack instead, or schedule the maintenance.

Tuning autovacuum_naptime lower hoping autovacuum runs more often. naptime only controls how often the launcher wakes up to consider tables. It doesn’t make autovacuum more aggressive on a given table. Use per-table scale_factor for that.

Forgetting to ANALYZE after a big DELETE or UPDATE. Stats lag, planner makes bad choices. Manual ANALYZE my_table; after bulk ops is fine and cheap.

Letting vacuum_cost_limit stay at 200 on SSD. That default was tuned for spinning disk. On SSD, autovacuum is artificially slow at 200. 1000–2000 is sane for modern storage.

Per-connection long transactions in monitoring tools. Some popular monitoring agents (looking at you, certain APM tools) open a connection and keep a transaction open for hours. Audit. Fix or quarantine.

Wrapping Up

Autovacuum tuning is the difference between a database that gets faster over time and one that mysteriously degrades. Defaults are wrong for write-heavy tables; per-table overrides are the right tool. Friday: PgBouncer for connection pooling — the last memory-saving win before we shift to indexes for the rest of the month.