PostgreSQL Performance Guides

PostgreSQL's autovacuum daemon exists to reclaim storage from dead tuples — rows that have been updated or deleted and are no longer visible to any active transaction. When autovacuum falls behind, tables bloat, IOPS spike, and queries slow down as the planner navigates through ever-larger data files. On large RDS production instances with millions of rows per table, the default autovacuum settings will almost certainly be inadequate.

This guide explains the key parameters, how to identify when autovacuum is failing to keep up, and what to change in your RDS parameter group.

Why the defaults are wrong for production

The default autovacuum is throttled aggressively to avoid impacting production workloads on modest hardware. The critical setting is autovacuum_vacuum_cost_delay, which defaults to 2ms in PostgreSQL 13+ (it was 20ms before that). Every time vacuum reads or writes a "cost unit" of data, it sleeps for this duration. The problem: on a modern NVMe-backed RDS instance (gp3, io1), your disks can handle thousands of IOPS, but autovacuum is sleeping 2ms between every 200 cost units.

Diagnosing autovacuum lag

Run this query to find tables where autovacuum is falling behind:

SELECT
  schemaname || '.' || relname                          AS table_name,
  n_dead_tup,
  n_live_tup,
  round(n_dead_tup::numeric / nullif(n_live_tup + n_dead_tup, 0) * 100, 1) AS dead_pct,
  last_autovacuum,
  last_autoanalyze,
  pg_size_pretty(pg_total_relation_size(relid)) AS total_size
FROM pg_stat_user_tables
WHERE n_dead_tup > 10000
ORDER BY n_dead_tup DESC
LIMIT 20;

A dead_pct above 10% is concerning. Above 25% means autovacuum is significantly lagging and you'll be seeing table bloat affect query plans. Above 50% is a critical situation requiring immediate manual VACUUM ANALYZE.

Key parameters — and why the defaults fail

There are six parameters that control how aggressively autovacuum runs. Each has a PostgreSQL default tuned for modest, general-purpose hardware — not the NVMe-backed, high-write RDS instances that run production SaaS or e-commerce workloads. Understanding what each parameter controls tells you where the problem is; calibrating the right values requires profiling your specific workload.

Per-table calibration matters more than instance-wide defaults

PostgreSQL supports per-table autovacuum parameters set directly via ALTER TABLE ... SET (…) storage options. This is where real-world tuning lives — instance-wide parameter group changes set a baseline, but your highest-write tables (order items, events, audit logs) almost always need their own tighter thresholds, while low-write reference tables need looser ones to avoid wasting IOPS.

Getting per-table overrides right requires knowing each table's write volume, average row width, vacuum history from pg_stat_user_tables, and how it competes with other tables for the autovacuum worker pool. Set them too aggressively across the board and you trade bloat-driven IOPS for vacuum-driven IOPS — often with no net saving on io1/io2 provisioned storage.

How to validate the diagnostic output

The diagnostic query above tells you that you have a problem. To understand how severe it is, re-run it after noting the current dead_pct and last_autovacuum timestamp for your top tables. If the dead count is growing faster than autovacuum is clearing it — or if last_autovacuum is hours old on a high-write table — your autovacuum configuration is falling behind your workload.

In CloudWatch, a rising Read IOPS trend on a stable-traffic instance is the signature of accumulating bloat: PostgreSQL is scanning increasingly large data files to find live rows. Once you've confirmed the pattern, the fix requires calibrated parameter changes — not a generic recipe.