Tuning Autovacuum for Production in PostgreSQL

Autovacuum has come up in almost every module of this series, because it's the background process that keeps Postgres healthy: reclaiming dead tuples, updating planner statistics, and preventing transaction ID wraparound. The default settings work fine for small and medium databases. On a busy production database they quietly fall behind, and a table's dead tuples pile up faster than autovacuum removes them, until queries slow and disk balloons. Tuning autovacuum is how you keep it ahead of the write load, and it's one of the highest-value operational skills for running Postgres at scale.

This is part 37 of the PostgreSQL Masterclass, following backups and PITR.

What autovacuum actually does

Three jobs, worth separating:

• Vacuuming: reclaiming space from dead tuples (the expired row versions from MVCC) so it can be reused.
• Analyzing: refreshing the column statistics the query planner relies on, which we saw matter in the query-optimization article.
• Freezing: marking very old rows to prevent transaction ID wraparound, the safety mechanism from the MVCC article.

Autovacuum runs these automatically per table, triggered by how much the table has changed. The tuning question is whether it triggers often enough and works fast enough on your busiest tables.

When the defaults fall behind

Autovacuum triggers a vacuum on a table when the number of dead tuples exceeds a threshold, and by default that threshold is proportional to the table's size:

autovacuum_vacuum_threshold = 50
autovacuum_vacuum_scale_factor = 0.2   -- 20% of the table

The 0.2 scale factor means a table is vacuumed after roughly 20% of its rows are dead. On a small table that's frequent enough. On a 100-million-row table, waiting for 20 million dead tuples before vacuuming is far too late: the bloat is already huge and the vacuum, when it finally runs, is enormous. The default that's fine at small scale is actively harmful at large scale.

The fix is to lower the scale factor for large, busy tables so they're vacuumed more often, in smaller increments. You can set this per table, which is the right approach, since you tune the few hot tables rather than the whole cluster:

ALTER TABLE events SET (
  autovacuum_vacuum_scale_factor = 0.02,   -- vacuum at 2% dead, not 20%
  autovacuum_vacuum_threshold = 1000
);

Making autovacuum work faster

Triggering more often only helps if autovacuum can keep up. It's deliberately throttled by default so it doesn't overwhelm the disk (the "cost delay" mechanism), but on modern hardware that throttle is often too conservative, and autovacuum runs slower than it needs to. Two knobs matter:

autovacuum_vacuum_cost_limit raises how much work autovacuum does before pausing. autovacuum_vacuum_cost_delay lowers how long it pauses. Raising the limit and lowering the delay lets autovacuum move faster on capable hardware:

autovacuum_vacuum_cost_limit = 2000    -- default 200; do more work per cycle
autovacuum_vacuum_cost_delay = 2ms     -- default 2ms in modern versions

Also make sure you have enough autovacuum workers for the number of tables that need attention at once:

autovacuum_max_workers = 5             -- default 3

The goal is autovacuum that keeps pace with your write rate, so dead tuples are cleaned shortly after they're created rather than accumulating.

Watch it, don't guess

Tune based on measurement. pg_stat_user_tables shows the dead tuples and last autovacuum time per table, which tells you exactly which tables are falling behind:

SELECT relname,
       n_live_tup, n_dead_tup,
       round(100 * n_dead_tup / nullif(n_live_tup + n_dead_tup, 0), 1) AS dead_pct,
       last_autovacuum
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 20;

A table with a high dead-tuple percentage and an old last_autovacuum is one that needs a lower scale factor. This query is where autovacuum tuning starts: find the tables that are behind, and tune those specifically.

The wraparound alarm

There's one autovacuum-related number you should actively monitor: transaction ID age. If autovacuum can't freeze old rows fast enough, a database approaches transaction ID wraparound, and Postgres takes increasingly drastic protective action, eventually refusing writes to protect data. This is rare and almost always the result of autovacuum being blocked (by a long-running transaction, as we discussed, or by being turned off), but it's serious enough to alert on:

SELECT datname, age(datfrozenxid) AS xid_age
FROM pg_database ORDER BY xid_age DESC;

Watch this age, and if it climbs toward the wraparound limit, find what's stopping autovacuum, usually a stuck transaction, before it becomes an incident.

Never turn it off

The tempting-but-wrong move when autovacuum seems to cause load is to disable it. Don't. A database without vacuuming accumulates unbounded bloat and marches toward wraparound, and the eventual manual cleanup is far more painful than the background load ever was. If autovacuum is causing problems, the answer is to tune it (more frequent, faster, more workers), not to remove it.

Autovacuum tuning is mostly about the hot tables: lower their scale factor so they're vacuumed often, raise the cost limit so vacuum runs fast enough to keep up, add workers if many tables need attention, and monitor dead tuples and transaction age to know it's working. Get it right and the bloat and wraparound problems that plague neglected Postgres databases simply don't happen.

Next, we broaden from this one subsystem to production monitoring overall: the metrics that tell you a Postgres database is healthy, and the ones that warn you before it isn't.