Postgres VACUUM Tuning: What autovacuum Gets Wrong by Default

Autovacuum's defaults were designed for small databases. A few parameters make a large difference at scale — and the documentation buries the relevant ones until you're staring at wraparound vacuum or index bloat at 3 AM.

Answer first

For any table >10 GB: set per-table autovacuum_vacuum_scale_factor to 0.02 or lower, enable aggressive autovacuum cost limits on hot tables, monitor pg_stat_progress_vacuum and pg_stat_user_tables.n_dead_tup, and tune autovacuum_max_workers + maintenance_work_mem globally so vacuum actually finishes.

Defaults that hurt us

Postgres default:

autovacuum_vacuum_scale_factor = 0.2   -- 20% dead tuples before vacuum
autovacuum_analyze_scale_factor = 0.1
autovacuum_max_workers = 3
maintenance_work_mem = 64MB
autovacuum_vacuum_cost_delay = 2ms

On 400 GB events table: 20% dead = 80 GB dead tuples before autovacuum triggers aggressively — absurd. Manual vacuums from cron masked problem until cron overlapped peak and IO saturated.

Parameters that matter (ranked)

1. Per-table scale factor

ALTER TABLE events SET (
  autovacuum_vacuum_scale_factor = 0.02,
  autovacuum_analyze_scale_factor = 0.01,
  autovacuum_vacuum_cost_limit = 1000
);

For append-mostly tables 100 GB+: consider 0.005 + threshold:

ALTER TABLE logs SET (
  autovacuum_vacuum_scale_factor = 0,
  autovacuum_vacuum_threshold = 10000
);

Zero scale factor + threshold = vacuum every 10k dead tuples regardless of size — right for high-churn narrow tables.

2. autovacuum_max_workers

We run 8 on 16 vCPU RDS instance — default 3 left hot tables starved. Increase with CPU headroom; diminishing returns past ~8 on our workload.

3. maintenance_work_mem

Global 1 GB (RDS param group) — speeds index vacuum and sort phases. Don't set 10 GB — autovacuum runs multiple workers; sum matters.

4. autovacuum_vacuum_cost_delay / cost_limit

Default delay throttles vacuum to protect disk — noble on tiny PG, wrong on dedicated RDS with gp3 12k IOPS provisioned.

Hot table override cost_limit = 1000, cost_delay = 0 for off-peak windows via cron job setting session — controversial; we use only on events during 02:00–05:00 UTC maintenance band.

5. autovacuum_freeze_max_age / multixact

Don't discover wraparound emergency at 200M transactions — alert at 150M age(relfrozenxid).

SELECT relname, age(relfrozenxid)
FROM pg_class
WHERE relkind = 'r'
ORDER BY 2 DESC LIMIT 10;

Monitoring dashboard (minimal)

Metric	Alert
n_dead_tup / n_live_tup ratio per table	>5% sustained 1h
last_autovacuum age	>24h on hot tables
pg_stat_progress_vacuum stalled phase	phase same >2h
Index size / table size	index >0.8× table

Tied to index bloat incident — ratio watch catches early.

Manual vs autovacuum

We removed naive VACUUM ANALYZE nightly cron on large tables — fought autovacuum for locks. Kept weekly VACUUM (VERBOSE, ANALYZE) on small dimension tables only.

VACUUM FULL — essentially never in prod. pg_repack if must reclaim heap space without long exclusive lock.

RDS / Aurora specifics

• Parameter group change maintenance_work_mem needs reboot on some versions — schedule
• Aurora storage autoscales — vacuum IO shows as read IOPS spike + egress-ish billing nuances on cross-AZ replicas
• Performance Insights: look for IO:DataFileRead + AutovacuumLauncher wait

Linux / io_uring edge

App on same host (dev) — unrelated to vacuum but our staging on NVMe with io_uring Postgres 16 beta saw faster vacuum sort — not prod advice yet; noted for future.

Incident tie-in

SEV2 during on-call week: autovacuum worker blocked by idle in transaction session 18 h — pg_blocking_pids on vacuum process. Fix: kill idle session; add idle_in_transaction_session_timeout = 300000 ms.

Not autovacuum tuning — but vacuum can't save you from long transactions holding xmin horizon.

Tuning worksheet (copy for your table)

Table name: ___________
Size GB: ___
UPDATE/DELETE rate/day: ___
Current scale_factor: ___
Recommended scale_factor: ___
freeze age max observed: ___
Action: ALTER TABLE ... SET (...)

Staff DBA review quarterly — params drift when clones restored from snapshots with old settings.

What I'd do next

Automate ALTER TABLE recommendations from nightly dead tuple stats — human approves PR to terraform/sql migration repo.

Upgrade path to PG16 for improved vacuum buffer management — test on restored snapshot first.

Autovacuum isn't background noise — it's garbage collection for your query performance. Defaults assume 100 MB tables; you don't have those.

pg_stat_progress_vacuum fields worth watching

SELECT pid, relid::regclass, phase, heap_blks_total, heap_blks_scanned,
       heap_blks_vacuumed, index_vacuum_count, max_dead_tuples
FROM pg_stat_progress_vacuum;

Stuck in scanning heap for hours on 400 GB table — normal first pass after threshold fix. Stuck in vacuuming indexes with index_vacuum_count climbing — watch disk IO; may need maintenance window.

Per-table settings we ship in migration repo

-- hot append + update
ALTER TABLE events SET (autovacuum_vacuum_scale_factor = 0.02);
-- append mostly logs
ALTER TABLE audit_log SET (
  autovacuum_vacuum_scale_factor = 0,
  autovacuum_vacuum_threshold = 50000
);

Reviewed quarterly when table crosses 50 GB.

autovacuum_work_mem per table (PG16+)

On PG16 test restore we set per-table autovacuum_work_mem = '512MB' on events — index vacuum phases completed 35% faster. Not yet prod — waiting PG16 upgrade window.

What I'd do next