Autovacuum Is Not Magic: What It Does, What It Misses, and When to Help It

Three months into a project, a query that had always been fast started taking four seconds. The table had the same indexes. The query had not changed. Nobody had deployed anything near that code path in weeks.

I checked pg_stat_user_tables and found n_dead_tup sitting at 1.8 million on a table with 200,000 live rows. Autovacuum had not run on it in nine days. The table was seeing a lot of updates, the autovacuum thresholds were at their defaults, and the defaults were not aggressive enough to keep up.

That was my introduction to the fact that "autovacuum handles it" is true most of the time, and the exceptions have real consequences.

What autovacuum is actually doing

PostgreSQL uses MVCC (multi-version concurrency control) for its transaction isolation. When you update a row, the old version is not immediately overwritten. It stays in the table as a dead tuple until something comes along to clean it up. When you delete a row, same thing: the row is marked dead, not removed.

Autovacuum is the process that removes dead tuples and makes the space reusable. It also runs ANALYZE to update the statistics that the query planner uses.

It runs in the background, driven by thresholds. By default, it fires when the number of dead tuples exceeds autovacuum_vacuum_threshold (50) plus autovacuum_vacuum_scale_factor (0.2) times the table's live row count. On a 200,000-row table, that's 50 + 40,000 = 40,050 dead tuples before autovacuum fires.

If your table sees high update volume, you can accumulate 40,000 dead tuples quickly.

What bloat means in practice

Table bloat is the gap between the physical size of the table and the size of the data it actually contains. A table with 200,000 live rows that has accumulated and reclaimed space from 2 million dead tuples is physically larger than it needs to be. That matters for sequential scans, which have to read more pages to get the same data.

Index bloat is similar and sometimes worse. Indexes get fragmented as rows are updated and the old entries stick around. An index that is 80% bloat is doing four times as much work as it needs to.

You can check the live-to-dead ratio with:

SELECT 
  relname,
  n_live_tup,
  n_dead_tup,
  round(n_dead_tup::numeric / nullif(n_live_tup + n_dead_tup, 0) * 100, 1) AS dead_pct,
  last_vacuum,
  last_autovacuum,
  last_analyze,
  last_autoanalyze
FROM pg_stat_user_tables
WHERE n_live_tup > 1000
ORDER BY n_dead_tup DESC;

A dead percentage above 20% on a large table is worth paying attention to. Above 50% on a busy table means autovacuum is not keeping up.

When autovacuum misses

The defaults work for tables that don't see heavy write traffic. They fail silently on a few specific cases.

High-write tables are the most common. If you're inserting and updating a table at high volume, the default scale factor will let dead tuples accumulate faster than autovacuum removes them. The cost throttling built into autovacuum, which deliberately yields to user queries, makes this worse.

Large tables with small change rates are another case. A table with ten million rows needs 2,000,050 dead tuples before autovacuum fires at the default scale factor. That is a lot of dead tuples.

Tables with long-running transactions elsewhere are a third case. Autovacuum cannot remove dead tuples that are still visible to open transactions. If you have long-running reports or stuck transactions, they can pin the vacuum horizon and prevent cleanup.

The symptoms look like: queries getting slower without an obvious cause, table size growing faster than data growth, EXPLAIN ANALYZE showing more rows scanned than you'd expect.

How to tune it

You can tune autovacuum globally in postgresql.conf or per-table with ALTER TABLE. Per-table settings are usually the right answer, because the tables that need aggressive vacuuming are specific.

For a high-write table that's accumulating dead tuples faster than the defaults handle:

ALTER TABLE orders SET (
  autovacuum_vacuum_scale_factor = 0.01,
  autovacuum_vacuum_threshold = 100,
  autovacuum_analyze_scale_factor = 0.01,
  autovacuum_analyze_threshold = 100
);

This fires autovacuum when dead tuples exceed 100 plus 1% of the table, rather than 50 plus 20%. On a 200,000-row table, that's firing at 2,100 dead tuples instead of 40,050.

The cost of more frequent autovacuum is IO. That's real but usually acceptable compared to the cost of bloated tables.

When to run VACUUM ANALYZE manually

After bulk operations. If you delete a million rows or bulk-load a large import, autovacuum will eventually clean it up. If you need the space reclaimed now, or if the query planner needs updated statistics before the next autovacuum cycle runs, do it yourself:

VACUUM ANALYZE tablename;

After identifying that autovacuum is behind. If pg_stat_user_tables shows a high dead tuple count and last_autovacuum was days ago, don't wait.

A warning about VACUUM FULL: it takes an exclusive lock on the table and rewrites it completely. On a large table under load, this causes an outage. VACUUM without FULL is online and safe. Use VACUUM FULL only in a maintenance window when you need to reclaim disk space that regular vacuuming cannot return.

What to actually monitor

Check pg_stat_user_tables regularly on your high-write tables. The columns worth watching are n_dead_tup, n_live_tup, last_autovacuum, and last_autoanalyze. If n_dead_tup is climbing and last_autovacuum is more than a few hours old on a busy table, autovacuum is not keeping up.

Autovacuum is not broken. It is calibrated for a workload that may not be yours. The defaults are conservative because conservative is safe for most cases. If your case is not most cases, you have to say so explicitly. The table that was fast last month and is slow now, with nothing changed, is usually telling you that autovacuum had something to say and nobody was listening.