Autovacuum Tuning: Stop It From Slowing You Down

Autovacuum Is Not the Enemy

Almost every “PostgreSQL got slow” story an Oracle DBA runs into after a migration traces back to one subsystem: autovacuum. It has no equivalent in Oracle’s world, so the instinct is to blame it — or worse, turn it off. Don’t. Autovacuum is the process that keeps a PostgreSQL database healthy, and when it seems to be slowing you down, the fix is almost always to let it run more often, not less. This is a tuning problem, not a bug.

Why It Exists: MVCC and Dead Tuples

PostgreSQL uses MVCC (multi-version concurrency control), which means an UPDATE or DELETE does not overwrite a row in place. It writes a new version and leaves the old one behind as a dead tuple. Readers that started before the change still see the old version; the new version is for everyone after. Those dead tuples pile up until something reclaims the space. That something is VACUUM, and autovacuum is the daemon that runs it for you.

Neglect it and two things happen. Tables and indexes bloat — physical size grows far beyond the live data — so every scan reads more pages. And planner statistics go stale, so query plans drift. The symptom is a database that was fast at cutover and is mysteriously crawling a month later.

When Autovacuum Wakes Up

Autovacuum checks each table on a cycle (autovacuum_naptime, default 1 minute) and vacuums a table once its dead tuples cross a threshold defined by two settings:

threshold = autovacuum_vacuum_threshold
          + autovacuum_vacuum_scale_factor * reltuples

# defaults:
autovacuum_vacuum_threshold    = 50
autovacuum_vacuum_scale_factor = 0.2   # 20% of the table

The trap is the scale factor. At 0.2, a 100-million-row table must accumulate 20 million dead tuples before autovacuum touches it — so it runs rarely, and when it does, it runs long and hard. That single default is the most common cause of the “autovacuum is hammering my big table” complaint.

The Two Changes That Fix Most Problems

1. Lower the scale factor on large tables

You want big tables vacuumed on a fixed row count, not a percentage. Set per-table storage parameters so small and large tables get different treatment:

-- vacuum this big table every ~50k dead rows, not every 20%
ALTER TABLE orders SET (
  autovacuum_vacuum_scale_factor  = 0.0,
  autovacuum_vacuum_threshold     = 50000,
  autovacuum_analyze_scale_factor = 0.0,
  autovacuum_analyze_threshold    = 50000
);

2. Let it work faster

Autovacuum deliberately throttles itself so it does not swamp your I/O — through a cost-based delay. On modern SSD-backed servers the defaults are far too timid. Raise the cost limit and shrink the delay so a vacuum finishes in minutes instead of hours:

# postgresql.conf — modern SSD defaults
autovacuum_vacuum_cost_limit = 2000    # default 200 — 10x more work per cycle
autovacuum_vacuum_cost_delay = 2ms     # default 2ms in PG12+, was 20ms
autovacuum_max_workers       = 5       # more tables in parallel
autovacuum_naptime           = 15s     # check more often

The One You Cannot Ignore: Wraparound

Beyond space, vacuum does something non-negotiable: it freezes old rows to prevent transaction ID wraparound. PostgreSQL’s transaction counter is finite; if a database never freezes, it will eventually shut down to protect itself. You never want to meet the “database is not accepting commands to avoid wraparound” error in production. Watch the age of your oldest un-frozen data and make sure vacuum keeps up:

SELECT relname,
       age(relfrozenxid) AS xid_age,
       n_dead_tup,
       last_autovacuum
FROM   pg_stat_user_tables
JOIN   pg_class USING (relname)
ORDER  BY age(relfrozenxid) DESC
LIMIT  20;

Watch, Don’t Guess

Tune from evidence. pg_stat_user_tables tells you dead-tuple counts and when each table was last vacuumed; the pgstattuple extension measures real bloat. If a table shows high n_dead_tup and an old last_autovacuum, autovacuum is falling behind on it — lower its threshold. Pair this with pg_stat_statements to connect bloat to the queries that feel it.