7 min read

Why autovacuum stops running and how to diagnose it

Autovacuum not running means dead tuples accumulating, table bloat growing, and eventually query plans degrading. Here is how to find out why it stopped.

What autovacuum does

Autovacuum is a background daemon that reclaims storage from dead tuples. Every UPDATE and DELETE in Postgres creates a dead tuple — the old row version — that remains in the table until VACUUM removes it. Without VACUUM, tables grow indefinitely, sequential scans slow down because they must read dead tuples, and eventually transaction ID wraparound threatens database integrity.

Autovacuum also runs ANALYZE to update planner statistics so the query planner can choose good execution plans.

On a healthy database, autovacuum runs silently in the background. When it falls behind, the symptoms accumulate slowly: table sizes grow, queries get slower, and eventually you start seeing degraded plans on tables the planner thinks are smaller than they are.

How to check if autovacuum is keeping up

SELECT
  relname,
  n_dead_tup,
  n_live_tup,
  ROUND(100.0 * n_dead_tup / NULLIF(n_live_tup + n_dead_tup, 0), 1) AS dead_pct,
  last_autovacuum,
  last_autoanalyze,
  autovacuum_count
FROM pg_stat_user_tables
WHERE n_live_tup > 1000
ORDER BY n_dead_tup DESC
LIMIT 20;

For each table: - n_dead_tup — dead tuples waiting for cleanup. Above 20% of total rows is elevated. Above 50% is severe. - last_autovacuum — when autovacuum last ran on this table. NULL or more than a few hours old on an actively-written table means autovacuum is not keeping up. - autovacuum_count — total times autovacuum has run. If this has not increased over time on an active table, autovacuum is not running on it at all.

Reason 1: autovacuum is throttled by cost delay

Autovacuum has a built-in throttle to avoid saturating I/O. After reading or writing a fixed amount of data, it sleeps for a configurable delay.

SELECT name, setting, unit
FROM pg_settings
WHERE name IN (
  'autovacuum_vacuum_cost_delay',
  'autovacuum_vacuum_cost_limit',
  'autovacuum_max_workers'
);

autovacuum_vacuum_cost_delay defaults to 2ms on Postgres 14+, but 20ms on older versions. At 20ms delay with the default cost limit, autovacuum can process approximately 200 kB/s — far below what a busy table needs.

Fix: lower the cost delay for specific tables without a database restart:

-- Per-table override
ALTER TABLE high_traffic_table
  SET (autovacuum_vacuum_cost_delay = 2,
       autovacuum_vacuum_scale_factor = 0.01);

The autovacuum_vacuum_scale_factor = 0.01 triggers autovacuum when dead tuples exceed 1% of the table (rather than the default 20%), so it runs more frequently before bloat accumulates.

Reason 2: autovacuum is waiting for a lock

Autovacuum needs a ShareUpdateExclusiveLock on the table it is vacuuming. This conflicts with ALTER TABLE and other DDL operations. If a long-running DDL holds an ACCESS EXCLUSIVE lock, autovacuum waits.

Check whether autovacuum workers are blocked:

SELECT
  pid,
  wait_event_type,
  wait_event,
  state,
  LEFT(query, 80) AS query,
  application_name
FROM pg_stat_activity
WHERE application_name = 'autovacuum worker';

If you see autovacuum workers with wait_event_type = 'Lock', identify what is blocking them:

SELECT pid, pg_blocking_pids(pid) AS blocked_by, query
FROM pg_stat_activity
WHERE cardinality(pg_blocking_pids(pid)) > 0
  AND application_name = 'autovacuum worker';

Long-running migrations or ALTER TABLE commands are the most common cause. Autovacuum will retry when the lock is released.

Reason 3: insufficient autovacuum workers

By default, Postgres runs a maximum of 3 autovacuum workers. On a database with many tables, this is often insufficient. If all 3 workers are busy on large tables, smaller tables do not get vacuumed.

-- How many workers are running right now
SELECT COUNT(*)
FROM pg_stat_activity
WHERE application_name = 'autovacuum worker';

-- How many tables are waiting
SELECT COUNT(*)
FROM pg_stat_user_tables
WHERE n_dead_tup > 1000
   OR (last_autovacuum IS NULL AND n_live_tup > 1000);

If the worker count is frequently at the maximum, increase it in postgresql.conf (requires a reload, not a restart on Postgres 14+):

autovacuum_max_workers = 6

On RDS, set this in the parameter group. Each worker consumes some shared memory and I/O capacity, so do not set this arbitrarily high — 5 to 8 is typically sufficient.

Reason 4: a long-running transaction holds the XID horizon

This is the most dangerous scenario. Postgres uses 32-bit transaction IDs that wrap around after roughly 2.1 billion transactions. The "horizon" is the oldest active XID. Autovacuum cannot freeze tuples older than the horizon, so if a transaction stays open for days, the XID horizon stops advancing and you accumulate XIDs toward wraparound.

Check your wraparound risk:

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

If xid_age exceeds 1.5 billion, you are in the warning zone. Postgres logs warnings at 1.6 billion and refuses writes at ~1.9 billion to prevent data corruption. At that point you must run VACUUM FREEZE on all tables under superuser access — it is not a fun situation.

The fix for the horizon problem is always the same: identify and terminate the long-running transaction that is holding the old XID. See our post on "idle in transaction" sessions for the detection query.

Running VACUUM manually

For immediate relief on a specific table, run VACUUM manually. This does not interfere with autovacuum:

VACUUM (ANALYZE, VERBOSE) public.your_table;

ANALYZE updates planner statistics. VERBOSE prints progress output so you can see how much dead tuple space was reclaimed. This is a non-blocking operation — reads and writes continue during the vacuum.

insightral PG-R02: autovacuum lag detection

insightral's rule PG-R02 fires when a table has accumulated dead tuples beyond the configured threshold, last_autovacuum is stale, or autovacuum workers appear blocked. The finding includes the table name, dead tuple count, last vacuum time, and the blocking session (if any).

Summary

1. Query pg_stat_user_tables sorted by n_dead_tup DESC. Tables with dead_pct above 20% and a stale last_autovacuum are not being vacuumed adequately. 2. Diagnose the most likely cause: cost throttle, lock contention, or insufficient workers. 3. For immediate relief, run VACUUM ANALYZE table_name manually — it is non-blocking and safe at any time. 4. Lower autovacuum_vacuum_cost_delay and autovacuum_vacuum_scale_factor on specific high-traffic tables with per-table storage parameters. 5. Monitor age(datfrozenxid) in pg_database — if it grows above 1.5 billion, escalate immediately.