Per-Table Autovacuum Settings for Hot Tables #

A queue table with fifty thousand rows turns over its entire contents every few minutes. A hundred-million-row events table receives a steady append. The same global autovacuum settings govern both, and they are wrong for each in opposite directions: the queue table accumulates dead rows between runs that arrive far too late, and the events table waits for twenty million changes before anyone updates its statistics.

Autovacuum’s effect on plans is covered in autovacuum and plan stability. This page is about the storage parameters that fix the mismatch table by table.

The Condition #

The trigger for autovacuum on a table is a threshold plus a fraction of the table’s size:

vacuum threshold  = autovacuum_vacuum_threshold  + autovacuum_vacuum_scale_factor  × reltuples
analyze threshold = autovacuum_analyze_threshold + autovacuum_analyze_scale_factor × reltuples

With the defaults — thresholds of 50, scale factors of 0.2 and 0.1 — the fraction dominates for anything but a tiny table. That produces two failure modes:

A third, less visible problem is cost limiting. Autovacuum pauses after accumulating autovacuum_vacuum_cost_limit worth of page access, sleeping for autovacuum_vacuum_cost_delay. On a busy table with fast storage those defaults make the worker much slower than the table’s rate of change, so it never catches up regardless of how often it starts.

All three are addressed with per-table storage parameters, set with ALTER TABLE … SET (…), which override the global values for that table only.

One global setting, two wrong answers On the left, global defaults applied to a fifty thousand row queue table trigger vacuum after ten thousand dead rows, which is many table turnovers, and to a hundred million row events table trigger analyze after ten million changes. On the right, per-table settings trigger vacuum on the queue after one thousand rows and analyze on the events table after fifty thousand. global defaults queue: vacuum at 10,050 dead events: analyze at 10,000,050 bloat on one, stale stats on the other per-table settings queue: vacuum at ~1,050 dead events: analyze at ~50,000 both kept current the scale factor is what scales wrongly

Annotated Evidence #

Which tables are falling behind:

SELECT relname,
       n_live_tup, n_dead_tup,
       round(100.0 * n_dead_tup / nullif(n_live_tup, 0), 1) AS dead_pct,
       n_mod_since_analyze,
       last_autovacuum, last_autoanalyze
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC LIMIT 5;
   relname   | n_live_tup | n_dead_tup | dead_pct | n_mod_since_analyze | last_autovacuum   | last_autoanalyze
-------------+------------+------------+----------+---------------------+-------------------+------------------
 job_queue   |      48204 |     412008 |    854.9 |              182044 | 2026-09-15 09:12  | 2026-09-15 09:12
 events      |  104820044 |    1840220 |      1.8 |             8412004 | 2026-09-14 02:40  | 2026-09-10 23:18
-- job_queue: 8.5 dead rows per live row — vacuum is far behind
-- events: 8.4 million modifications since the last analyze, five days ago

What that does to a plan on the queue table:

Index Scan using job_queue_state_idx on job_queue  (actual time=84.204..312.668 rows=40 loops=1)
  Index Cond: (state = 'pending'::text)
  Buffers: shared hit=18402 read=4120
-- 22,522 buffers to return 40 rows: the index and heap are mostly dead tuples

And the settings that fix each:

ALTER TABLE job_queue SET (
  autovacuum_vacuum_scale_factor  = 0.02,
  autovacuum_vacuum_threshold     = 1000,
  autovacuum_analyze_scale_factor = 0.02,
  autovacuum_vacuum_cost_delay    = 0     -- no throttling on a small hot table
);

ALTER TABLE events SET (
  autovacuum_analyze_scale_factor = 0.0,
  autovacuum_analyze_threshold    = 50000,
  autovacuum_vacuum_scale_factor  = 0.01
);
Three signals from pg_stat_user_tables Three items are annotated. Dead tuples far exceeding live tuples indicates vacuum cannot keep up with the table's turnover. Modifications since the last analyze in the millions indicates the analyze scale factor is too coarse for the table size. A last autovacuum timestamp days old on a busy table indicates cost limiting or worker starvation. n_dead_tup ≫ n_live_tup vacuum cannot keep up n_mod_since_analyze in millions analyze scale factor too coarse last_autovacuum days old, table busy cost limit or worker starvation check all three before changing any global setting

Step-by-Step Resolution #

  1. Find the outliers with the query above: high dead-tuple ratios, large modification counts since analyze, and old timestamps on active tables.

  2. Classify each table. Small and hot (queues, session tables, counters) needs frequent vacuum and no throttling. Large and append-mostly needs frequent analyze and moderate vacuum. Large and update-heavy needs both, plus attention to cost limits.

  3. Set an absolute threshold with a near-zero scale factor for large tables. autovacuum_analyze_scale_factor = 0.0 with autovacuum_analyze_threshold = 50000 means “analyze after fifty thousand changes”, independent of size — which is what you actually want.

  4. Lower the vacuum scale factor for hot small tables, and set autovacuum_vacuum_cost_delay = 0 so the worker runs at full speed. The table is small; there is nothing to throttle.

  5. Raise the global cost limit if many tables are throttled: autovacuum_vacuum_cost_limit = 2000 and autovacuum_vacuum_cost_delay = 2ms suit modern storage far better than the historical defaults.

  6. Check worker availability. autovacuum_max_workers limits concurrency; if all workers are permanently busy, tables queue behind each other regardless of their own settings.

  7. Verify with the same query after a day: dead-tuple ratios should fall and last_autoanalyze should be recent on every table that matters.

Effect of per-table settings on job_queue With global defaults the queue table holds 855 percent dead tuples relative to live rows. Lowering the vacuum scale factor to 0.02 brings it to 62 percent. Adding a threshold of 1000 rows brings it to 18 percent. Removing the cost delay brings it to 6 percent. global defaults 855% dead scale factor 0.02 62% dead + threshold 1000 18% dead + cost delay 0 6% dead same workload throughout; only the settings changed

Before and After #

-- BEFORE: global defaults
Index Scan using job_queue_state_idx  (actual time=84.204..312.668 rows=40 loops=1)
  Buffers: shared hit=18402 read=4120

-- AFTER: per-table autovacuum settings
Index Scan using job_queue_state_idx  (actual time=0.042..0.118 rows=40 loops=1)
  Buffers: shared hit=44

Reading the settings back #

Storage parameters live in pg_class.reloptions, and it is worth confirming what is actually set rather than what someone intended:

SELECT relname, reloptions FROM pg_class WHERE reloptions IS NOT NULL ORDER BY relname;

Two subtleties catch people out. Partitioned tables have no storage of their own, so setting autovacuum parameters on the parent has no effect on the partitions — each partition is a table with its own settings, and new partitions created by a scheduler inherit whatever the creation template gives them, which is usually nothing. Automating the settings into the partition-creation function is the only reliable approach. And TOAST tables have their own parameters, set through toast.autovacuum_* on the parent, which matter for tables with large text or JSON columns where most of the churn is in the TOAST relation rather than the main one.

When the settings are not the problem #

Three situations look like misconfigured thresholds and are not, and changing settings for them wastes effort.

The first is a long-running transaction or an abandoned replication slot holding back the oldest visible snapshot. Vacuum cannot remove tuples newer than the oldest transaction that might still see them, so it runs, reports success, and removes nothing. The signal is dead tuples growing while last_autovacuum updates frequently; pg_stat_activity for xact_start and pg_replication_slots for a stale restart_lsn confirm it.

The second is worker starvation. With three workers and hundreds of tables needing attention, a table can be correctly configured and still wait hours for a worker. The signal is a long list of tables all overdue, rather than one; raising autovacuum_max_workers helps only if the I/O budget allows it.

The third is prepared transactions left uncommitted, which hold their snapshots indefinitely and produce exactly the first symptom with no visible session. pg_prepared_xacts shows them, and on most systems any row in it is a bug.

The other habit worth forming is documenting why each override exists. A autovacuum_vacuum_cost_delay = 0 set during an incident three years ago, on a table that has since grown a hundredfold, is now a source of I/O storms rather than a fix.

Common Pitfalls #

Tuning globally to fix one table. Everything else gets more aggressive vacuuming. Diagnostic signal: I/O rising across the system after a settings change. Fix: per-table parameters.

Leaving the analyze scale factor at 0.1 on large tables. Statistics age for weeks. Diagnostic signal: n_mod_since_analyze in the millions. Fix: absolute threshold, zero scale factor.

Setting parameters on a partitioned parent. They apply to nothing. Diagnostic signal: partitions with default behaviour despite the parent being configured. Fix: set them per partition, in the creation path.

Ignoring the cost limit. Frequent triggering does not help if the worker is throttled to a crawl. Diagnostic signal: autovacuum running continuously yet dead tuples growing. Fix: raise the limit, lower the delay.

Frequently Asked Questions #

How do I make autovacuum run more often on one table? #

Set per-table storage parameters with ALTER TABLE … SET, lowering autovacuum_vacuum_scale_factor and autovacuum_analyze_scale_factor, or setting them to zero and using absolute thresholds instead. They override the global values for that table only.

Why does a large table have stale statistics despite autovacuum running? #

The analyze threshold is a fraction of the table size, so a hundred-million-row table needs ten million modifications before autoanalyze triggers at the default scale factor. Use an absolute threshold with a zero scale factor.

Do autovacuum settings on a partitioned table apply to its partitions? #

No. A partitioned table has no storage, so its settings do nothing; each partition needs its own. Set them in whatever code creates partitions.

Up: Autovacuum and Plan Stability