PostgreSQL autovacuum tuning, explained without the jargon

Of all the PostgreSQL internals that cause production problems, autovacuum tuning is probably the one with the worst ratio of "how much it matters" to "how rarely people understand it." This is the explanation I wish I'd had before learning it through a slow, table-bloat-shaped incident.

Why vacuum exists at all

PostgreSQL doesn't overwrite rows in place when you update or delete them. Instead, it marks the old row version as dead and writes a new one. This is what makes its MVCC concurrency model work — readers never block writers — but it means dead rows pile up on disk until something cleans them up. That something is vacuum.

Autovacuum is the background process that does this automatically. When it's keeping pace with your write volume, it's invisible. When it isn't, dead rows accumulate faster than they're cleaned, and you get table and index bloat: tables that are mostly empty space, scans that take longer than they should, and eventually, a transaction ID wraparound warning that nobody wants to see in their logs at the same time as a holiday.

Symptoms of an undertuned autovacuum

Before changing settings, confirm this is actually your problem. The clearest signal is comparing a table's actual row count against its size on disk:

Checking table bloat

SELECT relname, n_live_tup, n_dead_tup,
       round(n_dead_tup::numeric / greatest(n_live_tup, 1), 3) AS dead_ratio
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 10;

A dead_ratio consistently above 0.1–0.2 on a busy table is worth investigating. Above 0.5, autovacuum is meaningfully behind, and query plans on that table are probably already worse than they should be.

The four settings that matter

PostgreSQL ships with defaults tuned for small, lightly-loaded databases from over a decade ago. They're conservative enough to be wrong for most production workloads today. These four are the ones worth changing globally in postgresql.conf:

postgresql.conf

autovacuum_vacuum_scale_factor = 0.05      # default: 0.2
autovacuum_vacuum_cost_limit = 2000        # default: 200
autovacuum_max_workers = 4                 # default: 3
autovacuum_naptime = 15s                   # default: 1min

• vacuum_scale_factor controls what fraction of a table's rows need to be dead before autovacuum triggers. The default of 0.2 means a 10-million-row table waits for 2 million dead rows — far too much for a write-heavy table. Dropping it to 0.05 triggers vacuum earlier and more often, in smaller, cheaper passes.
• vacuum_cost_limit caps how much I/O work autovacuum can do before pausing to avoid starving other queries. The default of 200 is appropriate for spinning disks from 2010. On modern SSD or NVMe storage, raising it lets vacuum actually keep up.
• max_workers sets how many vacuum processes can run concurrently. If you have several large, busy tables, three isn't always enough to keep all of them in good shape simultaneously.
• naptime is how often the autovacuum launcher checks whether anything needs vacuuming. The default minute is fine for quiet databases; on busy ones, 15 seconds catches bloat before it accumulates.

Per-table overrides for hot tables

Global settings are a reasonable baseline, but the table receiving 200 writes per second deserves different treatment than the one updated twice a day. Postgres lets you override settings per table:

Per-table autovacuum tuning

ALTER TABLE events SET (
  autovacuum_vacuum_scale_factor = 0.01,
  autovacuum_vacuum_cost_limit = 5000
);

For an events or audit-log style table with continuous inserts and few updates, you typically want frequent, cheap vacuum passes rather than the global default. I've found this single change resolves most "why is this one table slow" tickets without needing to touch anything else.

Monitoring bloat going forward

Once tuned, the thing worth watching isn't bloat in isolation — it's whether autovacuum is keeping pace. pg_stat_progress_vacuum shows currently running vacuum operations, and comparing last_autovacuum timestamps against write volume per table tells you whether the frequency is actually matching the workload.

I run a small Grafana panel against these stats for every client database, alerting if any table's dead_ratio crosses 0.3 — by that point it's still cheap to fix, rather than a multi-hour VACUUM FULL during a maintenance window.