Autovacuum was running. Your table still bloated.

Welcome to Issue #8 of Vacuum & Analyze. This one is about VACUUM and autovacuum. If you want the earlier groundwork, Issue #4 covered why autovacuum ignores your biggest table and Issue #3 covered maintenance_work_mem and why vacuum can crawl through multiple passes.

A table takes 4,000 UPDATEs per second at peak. After a week it has grown from 50 GB to 85 GB, and the live row count hasn't changed. Sequential scans that took 12 seconds now take 22. Autovacuum is enabled and its logs show it running. Someone suggests VACUUM FULL to shrink it. There's a catch: VACUUM FULL locks the table against all reads and writes while it rewrites it, and this app can't go down.

Both reflexes, "autovacuum is on so we're fine" and "run VACUUM FULL to get the space back", rest on misconceptions about what these tools do.

Misconception #1: "Autovacuum is enabled, so bloat is handled"

Autovacuum doesn't run on a schedule. It fires when a table's dead tuples cross a threshold:

threshold = autovacuum_vacuum_threshold + autovacuum_vacuum_scale_factor × reltuples

The defaults are 50 and 0.2. That is "wait until 20% of the table is dead." On a 10-million-row table it triggers at about 2 million dead tuples. At an assumed 200 dead tuples per second, that's roughly every 2.8 hours, a tolerable cadence for many workloads.

Now take a billion-row table:

threshold = 50 + 0.2 × 1,000,000,000 = 200,000,050 dead tuples
time to trigger at 2,000 dead tuples/sec ≈ 27.8 hours
dead space before it even starts ≈ 50 GB (at 250 bytes per row)

The defaults aren't broken. They assume a 20% tolerance band, and that band grows linearly with table size. At a billion rows, 20% is tens of gigabytes of garbage sitting in every sequential scan, in your buffer cache, and in your index scans.

The deeper problem is that defaults can't know your workload. Two applications can have tables with the same schema and the same size, but different write rates, traffic patterns and tolerance for bloat. One may need autovacuum to fire every few minutes and the other every few hours. A single global setting can't be right for both, which is why per-table tuning exists.

Consider a scenario: a 420-million-row payments table on default settings, taking about 6,500 UPDATEs per second. Autovacuum fires every 3.6 hours, each run clearing about 80 million dead tuples. The table sits at 142 GB against 68 GB of live data. Autovacuum was working. It was just waiting far too long between runs.

Misconception #2: "Once VACUUM runs, I get the disk space back"

Regular VACUUM reclaims space inside pages so future inserts and updates can reuse it. It does not shrink the file. A table that ballooned to 120 GB stays a 120 GB file after every dead tuple is gone. That is why you can see n_dead_tup near zero on a table that is still 75 GB larger than a fresh pg_dump | pg_restore would make it.

The tempting fix is VACUUM FULL. It takes an AccessExclusiveLock for the entire rewrite, which blocks reads as well as writes. It needs enough free disk to hold a compacted copy of the table and its rebuilt indexes, and it produces a WAL spike that your replicas have to absorb. How long the rewrite takes depends on table size, storage IOPS, available CPU and how many indexes need rebuilding. A small table might take under a minute, and a table in the hundreds of gigabytes can take tens of minutes or more. Either way, the table is unavailable for the whole rewrite.

The options, ranked by how much they disrupt production:

• Regular VACUUM plus new inserts. Free space gets reused over time. It's free and has no downtime, but it's slow.
• pg_repack. It rewrites the table online and only takes the exclusive lock for the final swap, which is milliseconds. This is the default choice for production.
• VACUUM FULL. Use it for small tables or when you have a real maintenance window.

The diagnostic that actually matters

First, find the tables where the default trigger is far too high for their size:

SELECT s.relname,
       s.n_dead_tup,
       c.reltuples::bigint            AS est_rows,
       (50 + 0.2 * c.reltuples)::bigint AS default_trigger_at,
       c.reloptions,
       s.last_autovacuum
FROM pg_stat_user_tables s
JOIN pg_class c ON c.oid = s.relid
ORDER BY s.n_dead_tup DESC
LIMIT 20;

reloptions holds any per-table settings you've applied with ALTER TABLE ... SET (...), such as a custom autovacuum_vacuum_scale_factor. If it's NULL, the table is using the global defaults. A big table with an enormous default_trigger_at and a NULL reloptions is the pattern to look for: nobody has tuned autovacuum for it.

Then check whether something is holding the xmin horizon back. If it is, VACUUM can't remove tuples no matter how often it runs:

SELECT pid, state,
       now() - xact_start AS xact_age,
       age(backend_xmin)  AS xmin_age
FROM pg_stat_activity
WHERE backend_xmin IS NOT NULL
ORDER BY age(backend_xmin) DESC
LIMIT 5;

One more caution. n_dead_tup is a cheap counter that can drift. pgstattuple physically scans the table and gives you the real dead-tuple percentage and free space, but it reads every page, and unlike VACUUM it isn't throttled. On a large table that is heavy I/O, and on constrained storage it can slow your application down noticeably or, in a bad case, stall it. Run it on one table at a time, off-peak or against a replica or restored copy, and for big tables consider pgstattuple_approx, which skips pages the visibility map marks all-visible. Use n_dead_tup for alerting and pgstattuple for confirming.

The practical shape of a fix

• Set per-table thresholds on your big, hot tables. For a billion-row table:

  ALTER TABLE event_log SET (
      autovacuum_vacuum_scale_factor = 0.01,
      autovacuum_vacuum_threshold    = 10000
  );
  

That moves the trigger from 200 million dead tuples to about 10 million. At 2,000 dead tuples per second it fires in roughly 83 minutes instead of 27 hours, and bloat before the trigger drops from about 50 GB to about 2.5 GB.

• Give autovacuum room to work. By default, workers share one cost budget of 200 with a 2 ms delay, which is deliberately conservative. On fast storage, raise autovacuum_vacuum_cost_limit to 1000–2000, or set a per-table autovacuum_vacuum_cost_delay = 0 for your most critical tables. Watch I/O latency after you change it.
• Kill the horizon holders. Set idle_in_transaction_session_timeout and alert on backend_xmin age and replication slot lag. A tuned autovacuum can't remove tuples that a stale snapshot still needs.
• For manual VACUUM after a bulk delete, raise maintenance_work_mem. With too little memory, VACUUM cycles through its phases repeatedly and rescans every index each time. Fifty million dead tuples need about 300 MB to fit in one pass.
• Fix the pipeline before you compact. Reclaim historical space with pg_repack only after the cause is addressed, or the bloat comes right back.

The mental model

VACUUM has three phases: scan the heap, clean the indexes, clean the heap. Autovacuum is the scheduler that decides when to run them, and its default schedule is proportional to table size. That is fine for small tables and too lenient for big ones.

Two questions cover most bloat incidents:

• Is VACUUM firing often enough? That's a threshold and cost-budget question.
• Can it actually remove what it finds? That's a horizon question.

And remember that even a perfect VACUUM only makes space reusable. Shrinking the file is a separate operation with a separate price.

— Gio