Tune Autovacuum for a High-Churn Table

Watch dead tuples accumulate, compute the actual churn rate, set per-table scale factors and cost limits, and verify bloat stays flat.

~30 mindatabasepostgresoperations

Task

Diagnose why autovacuum never keeps up with a high-churn table, set table-level parameters to match the churn rate, and confirm the fix statistically.

When to use this

The events table churns 20 percent of its rows daily. Autovacuum runs but never finishes before the next burst, dead tuples climb all week, and Friday queries crawl.

Prerequisites

PostgreSQL with autovacuum enabled and pg_stat_user_tables visible
A representative monitoring window of several days
Authority to ALTER TABLE storage parameters

Steps

1. Measure the dead tuple accumulation rate.

SELECT n_live_tup, n_dead_tup, last_autovacuum FROM pg_stat_user_tables WHERE relname = 'events';

Expected output: Sampling twice an hour for a day shows n_dead_tup growing by, say, forty thousand per hour, quantifying the churn.

2. Compute when the default threshold triggers.

-- default: vacuum when dead > 0.2 * live + 50
-- with live = 10,000,000 that is 2,000,050 dead tuples
-- at 40k/hour churn: trigger fires after ~50 hours

Expected output: The arithmetic shows the default fires roughly every two days, far too rarely for the daily churn pattern.

3. Set table-level parameters matched to the churn.

ALTER TABLE events SET (
  autovacuum_vacuum_scale_factor = 0.01,
  autovacuum_vacuum_threshold = 10000,
  autovacuum_vacuum_cost_limit = 2000,
  autovacuum_vacuum_cost_delay = 1
);

Expected output: The table now vacuums at one percent dead tuples with a generous cost budget, roughly every seventy minutes of churn.

4. Watch vacuum duration under the new settings.

SELECT relname, autovacuum_count, n_dead_tup FROM pg_stat_user_tables WHERE relname = 'events';

Expected output: autovacuum_count climbs steadily while n_dead_tup oscillates in a low band instead of ratcheting upward.

5. Confirm bloat no longer accumulates.

SELECT pg_size_pretty(pg_total_relation_size('events')); -- trend it weekly

Expected output: Table size stabilizes within a small band; the Friday slowdown disappears because dead pages are reclaimed continuously.

Troubleshooting

Problem

Vacuum never completes before the next burst

Solution

The global cost limit throttles it into eternity; raise the per-table cost limit and lower the delay, and verify with pg_stat_progress_vacuum that runs actually finish.

Problem

Long transactions freeze vacuum progress

Solution

A transaction older than the dead tuples prevents cleanup; find it in pg_stat_activity and shorten it, or move the long reader to a replica.

Problem

Tuning helps one table starves the rest

Solution

Aggressive per-table settings consume the global worker budget; raise autovacuum_max_workers or keep aggressive settings for the few tables that need them only.

Limitations

Per-table tuning needs reevaluation as the table grows or the workload shifts; trend the stats rather than setting once.
Autovacuum reclaims space for reuse but does not shrink files; size stabilization, not reduction, is the success criterion.

Vacuum arithmetic beats vacuum folklore

Autovacuum is not mysterious: it triggers when dead tuples exceed scale factor times live tuples plus threshold, and it progresses at a pace bounded by cost limit and delay. For a table with ten million rows, the default twenty percent means two million dead tuples before the first vacuum. Writing the arithmetic down for your actual churn rate tells you exactly why Friday is slow and which two numbers to change.

-- The trigger arithmetic
-- vacuum when: dead > scale * live + threshold
-- default:     dead > 0.2 * 10,000,000 + 50 = 2,000,050
-- churn 40k/h: fires every ~50 hours

ALTER TABLE events SET (
  autovacuum_vacuum_scale_factor = 0.01,
  autovacuum_vacuum_threshold = 10000,
  autovacuum_vacuum_cost_limit = 2000,
  autovacuum_vacuum_cost_delay = 1
);

The cost dial controls completion

Triggering more often is useless if every run gets throttled to a crawl. The cost limit and delay decide how fast a vacuum actually scans, and the defaults are famously conservative. Raising the per-table cost limit and cutting delay to a millisecond lets each triggered run finish before the next burst of churn arrives.

Trend, do not snapshot

A single reading of n_dead_tup proves nothing; the shape over days does. Healthy tuning shows dead tuples oscillating in a low band with autovacuum_count climbing steadily, and table size flat. A ratcheting dead-tup line means some vacuum never finishes, and pg_stat_progress_vacuum will name it while it runs.