Autovacuum Thresholds, Scale Factors, Worker Capacity, and Per-Table Tuning

Learning outcomes

After Lesson 1, ServiceHub understands what vacuum does. The next question is operational: when does PostgreSQL decide to vacuum or analyze a table automatically, and what happens when many tables become eligible at once? Autovacuum is not a single periodic “cleanup job”; a launcher wakes regularly, evaluates work, and starts workers subject to cluster-wide capacity and per-table thresholds. The right tuning unit is usually the workload/table, not a copied global percentage.

1. The threshold model: fixed work plus table-relative work

For update/delete-driven vacuum, the familiar trigger is conceptually:

SELECT current_setting('autovacuum_vacuum_threshold')::numeric AS vacuum_base,       current_setting('autovacuum_vacuum_scale_factor')::numeric AS vacuum_scale,       current_setting('autovacuum_analyze_threshold')::numeric AS analyze_base,       current_setting('autovacuum_analyze_scale_factor')::numeric AS analyze_scale,       current_setting('autovacuum_vacuum_insert_threshold')::numeric AS insert_base,       current_setting('autovacuum_vacuum_insert_scale_factor')::numeric AS insert_scale;

The ordinary dead-tuple vacuum threshold is approximately vacuum_base + vacuum_scale × reltuples. The analyze threshold similarly combines its base and scale factor against estimated table rows. Current PostgreSQL also has a separate insert-trigger calculation so insert-heavy tables can receive vacuum work even when they have few updates/deletes; its scaled component is based on the estimated size of unfrozen table pages rather than treating every historical page equally.

2. Calculate a table-specific decision from evidence

DROP TABLE IF EXISTS app.ch09_churn;CREATE TABLE app.ch09_churn (    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,    tenant_id integer NOT NULL,    state text NOT NULL,    touched_at timestamptz NOT NULL DEFAULT clock_timestamp());INSERT INTO app.ch09_churn(tenant_id, state)SELECT (g % 100) + 1, 'open'FROM generate_series(1, 100000) AS g;ANALYZE app.ch09_churn;SELECT c.reltuples::bigint AS estimated_rows,       s.n_live_tup, s.n_dead_tup,       s.n_mod_since_analyze,       s.last_autovacuum, s.last_autoanalyzeFROM pg_class AS cJOIN pg_stat_all_tables AS s ON s.relid = c.oidWHERE c.oid = 'app.ch09_churn'::regclass;

WITH cfg AS (  SELECT current_setting('autovacuum_vacuum_threshold')::numeric AS v_base,         current_setting('autovacuum_vacuum_scale_factor')::numeric AS v_scale,         current_setting('autovacuum_analyze_threshold')::numeric AS a_base,         current_setting('autovacuum_analyze_scale_factor')::numeric AS a_scale), t AS (  SELECT greatest(reltuples, 0)::numeric AS rows_est  FROM pg_class  WHERE oid = 'app.ch09_churn'::regclass)SELECT v_base + v_scale * rows_est AS approx_dead_tuple_vacuum_trigger,       a_base + a_scale * rows_est AS approx_analyze_triggerFROM cfg CROSS JOIN t;

This calculation is an explanatory approximation from catalog/configuration values. Autovacuum also considers insert-trigger behavior, anti-wraparound obligations, reloptions, disabled settings, and timing/capacity. Do not build an external scheduler that assumes this single arithmetic expression is the complete launcher algorithm.

WITH examples(rows_est) AS (  VALUES (10000::numeric), (1000000::numeric), (1000000000::numeric)), cfg AS (  SELECT current_setting('autovacuum_vacuum_threshold')::numeric AS base,         current_setting('autovacuum_vacuum_scale_factor')::numeric AS scale)SELECT rows_est,       base + scale * rows_est AS approx_dead_tuple_triggerFROM examples CROSS JOIN cfgORDER BY rows_est;

The result is useful because it translates a percentage into an absolute number of changed tuples. The same configured scale factor can therefore produce radically different cleanup latency on tables of different sizes.

3. Launcher, workers, and capacity

SELECT name, setting, unit, context, sourceFROM pg_settingsWHERE name IN (  'autovacuum','autovacuum_naptime','autovacuum_max_workers',  'autovacuum_vacuum_cost_delay','autovacuum_vacuum_cost_limit',  'log_autovacuum_min_duration')ORDER BY name;

autovacuum_max_workers constrains ordinary concurrent worker capacity; autovacuum_naptime influences how often the launcher considers databases. Cost delay/limit shape how aggressively vacuum workers consume I/O-related cost budget. The practical failure mode is backlog: tables accumulate work faster than workers can process it.

SELECT pid, datname, usename, state, backend_type,       wait_event_type, wait_event, queryFROM pg_stat_activityWHERE backend_type ILIKE '%autovacuum%'   OR query ILIKE 'autovacuum:%'ORDER BY pid;

An empty result only means no worker is visible at that instant. It does not prove autovacuum is disabled. Combine worker snapshots with table timestamps/counters and logs.

4. Per-table tuning: change the table, not the whole cluster

Assume app.ch09_churn is known to receive frequent small updates and its dead-tuple backlog is operationally meaningful. Lowering only this table's thresholds is safer than globally imposing the same policy on archival tables.

ALTER TABLE app.ch09_churn SET (  autovacuum_vacuum_threshold = 50,  autovacuum_vacuum_scale_factor = 0.02,  autovacuum_analyze_threshold = 50,  autovacuum_analyze_scale_factor = 0.01);SELECT reloptionsFROM pg_classWHERE oid = 'app.ch09_churn'::regclass;

UPDATE app.ch09_churnSET state = CASE WHEN id % 7 = 0 THEN 'closed' ELSE 'active' END,    touched_at = clock_timestamp()WHERE id <= 15000;SELECT pg_stat_clear_snapshot();SELECT n_live_tup, n_dead_tup, n_mod_since_analyze,       last_autovacuum, autovacuum_count,       last_autoanalyze, autoanalyze_countFROM pg_stat_all_tablesWHERE relid = 'app.ch09_churn'::regclass;

Autovacuum timing is asynchronous. Do not write a lab that sleeps for a fixed number of seconds and claims a worker “must” have run. Observe over time, or use manual VACUUM (ANALYZE) when you need a deterministic teaching boundary.

SELECT p.pid, p.relid::regclass AS relation, p.phase,       p.heap_blks_total, p.heap_blks_scanned, p.heap_blks_vacuumed,       a.backend_type, a.wait_event_type, a.wait_eventFROM pg_stat_progress_vacuum AS pLEFT JOIN pg_stat_activity AS a USING (pid)ORDER BY p.pid;

A progress row answers “what phase and how much heap work has this currently running vacuum reported?” It does not tell you how many eligible tables are waiting behind it. Backlog assessment still needs table-level timestamps/counters and workload context.

5. Wrong approach: copy a global “0.01 everywhere” recipe

Scale-factor folklore ignores absolute table size, update rate, storage speed, query latency sensitivity, maintenance memory, worker contention, and transaction age. The same 1% means 100 tuples on a 10k-row table and 10 million tuples on a billion-row table.

SELECT relname, n_live_tup, n_dead_tup,       n_mod_since_analyze,       last_autovacuum, autovacuum_count,       last_autoanalyze, autoanalyze_countFROM pg_stat_all_tablesWHERE schemaname = 'app'ORDER BY n_dead_tup DESC NULLS LAST, n_mod_since_analyze DESC NULLS LAST;

A production tuning decision should document the workload, observed backlog, acceptable cleanup lag, worker saturation, I/O headroom, and rollback plan. If many unrelated tables are overdue simultaneously, worker capacity or global cost throttling may matter more than one table's threshold.

6. Production judgment: tune the bottleneck you measured

Lowering thresholds increases maintenance frequency; raising worker capacity can increase concurrent I/O; loosening cost throttling can shorten vacuum duration but compete more aggressively with foreground workload. Conversely, overly conservative maintenance can allow dead tuples, visibility work, statistics staleness, and XID age to accumulate. The correct balance is workload-specific and should be validated against latency, storage growth, worker backlog, vacuum duration, and transaction-age trends.

For a single hot table, begin with table reloptions and observe the outcome. Change cluster-wide worker/cost parameters only when evidence shows a shared capacity problem, and record whether each changed GUC requires reload or restart before scheduling the change.

7. Revert and clean up

ALTER TABLE app.ch09_churn RESET (  autovacuum_vacuum_threshold,  autovacuum_vacuum_scale_factor,  autovacuum_analyze_threshold,  autovacuum_analyze_scale_factor);DROP TABLE app.ch09_churn;

Authoritative references

These mechanisms are version-sensitive. Use the documentation for the PostgreSQL major you actually operate.

• PostgreSQL 18 — Autovacuum configuration
• PostgreSQL 18 — Routine Vacuuming / autovacuum daemon
• PostgreSQL 18 — pg_stat_all_tables