Tune autovacuum around a table's write pattern

PostgreSQL 18 scope. The SQL in this article was run on PostgreSQL 18.6 against synthetic fixtures.

Autovacuum tuning starts with the table's rate of change, not a universal percentage copied from another database. A large table with frequent updates can accumulate substantial dead tuples before a scale-factor trigger is reached. A small hot table may need frequent attention despite its modest size. An append-only table has a different maintenance profile again.

The useful question is whether cleanup starts soon enough and completes fast enough for this table's workload. A trigger is an eligibility threshold, not a reservation guaranteeing immediate worker service.

Translate settings into a concrete hypothesis

For update and delete activity, the familiar threshold model combines a base threshold with a fraction of the table's estimated row count. PostgreSQL 18 also exposes a maximum threshold that caps this trigger calculation. Inspect the actual version's settings and per-table overrides before doing arithmetic.

SELECT name, setting, unit, source
FROM pg_settings
WHERE name IN (
  'autovacuum_vacuum_threshold',
  'autovacuum_vacuum_scale_factor',
  'autovacuum_vacuum_max_threshold',
  'autovacuum_vacuum_insert_threshold',
  'autovacuum_vacuum_insert_scale_factor',
  'autovacuum_analyze_threshold',
  'autovacuum_analyze_scale_factor',
  'autovacuum_max_workers',
  'autovacuum_worker_slots'
)
ORDER BY name;

Suppose a hypothetical table has ten million estimated rows, a base threshold of 1,000, and a scale factor of 0.01. Before applying any maximum cap, the arithmetic threshold is 101,000 changed tuples. This is an illustrative calculation, not a measured trigger count. Statistics are approximate, scheduling is asynchronous, and the table can keep changing while waiting for a worker.

Now connect that number to observed churn. If the application creates dead versions rapidly, a threshold that looks small as a percentage can still permit a large cleanup backlog. Conversely, lowering it drastically can cause frequent scans with little useful work.

Inspect one table's lifecycle

The following read-only queries assume an application table named public.task_queue; replace it deliberately with the actual target.

SELECT relid::regclass AS relation,
       n_live_tup, n_dead_tup, n_mod_since_analyze,
       n_ins_since_vacuum,
       last_autovacuum, autovacuum_count,
       last_autoanalyze, autoanalyze_count
FROM pg_stat_user_tables
WHERE relid = 'public.task_queue'::regclass;

SELECT relname, reltuples, relpages, reloptions
FROM pg_class
WHERE oid = 'public.task_queue'::regclass;

Treat the row counts as estimates and note statistics reset times when interpreting cumulative counters. One snapshot cannot establish a rate. Collect samples over representative busy and quiet periods, including bulk imports and backlog recovery.

Inspect active vacuum progress to distinguish “not eligible,” “waiting for service,” and “already working.” A long duration can reflect a large table, constrained I/O, index cleanup, or blockers. Lowering the threshold does not solve every one of those causes.

Reference lifecycle. Eligibility, worker availability, cleanup progress, and version removability are separate gates.

Apply a narrowly scoped experiment

In an authorized staging experiment, a per-table change might look like this:

ALTER TABLE public.task_queue SET (
  autovacuum_vacuum_threshold = 1000,
  autovacuum_vacuum_scale_factor = 0.01,
  autovacuum_analyze_threshold = 1000,
  autovacuum_analyze_scale_factor = 0.02
);

These values are demonstration inputs, not production recommendations. Record the prior reloptions so the experiment can restore the exact previous state. RESET removes an override and returns to inherited defaults; it does not necessarily restore an earlier explicit override.

Vacuum and analyze solve different problems. Vacuum supports tuple reuse, visibility maintenance, and freezing. Analyze refreshes the planner's data model. A queue with rapidly shifting state distribution may need statistics refreshed on a different cadence from physical cleanup.

Insert-driven vacuum triggers matter for append-only tables even when updates and deletes are rare. Visibility-map coverage and freezing still need maintenance. Do not conclude that a table with almost no dead tuples needs no vacuum activity.

Check what prevents useful cleanup

A long-lived snapshot, replication retention horizon, or prepared transaction can restrict removal. More workers or more frequent triggers cannot remove versions that remain potentially visible. Establish whether retained horizons are involved before escalating resource settings.

Worker capacity and cost-based throttling also interact. Several busy tables can compete for service. Increasing concurrency can improve throughput only if the storage and CPU budget can sustain it. Otherwise, more workers may increase contention and degrade application latency without clearing the backlog faster.

Partitioned designs require attention to actual storage-bearing partitions and parent-level statistics. Do not assume that changing an abstract parent setting or observing one parent counter describes maintenance on every child. Verify the inheritance and per-relation behavior for the operation being tuned.

Define success as a bounded cycle

A successful experiment shows that dead-tuple growth stays bounded, cleanup finishes within the workload's cycle, planner estimates remain useful, and application latency remains acceptable. Include index size and WAL or replica effects when they are material. A lower dead-tuple estimate immediately after one vacuum is not enough.

Track time between eligibility and start where observable, duration, work completed, and the rate at which new versions appear during the run. If cleanup repeatedly finishes only after the next backlog has accumulated, the issue may be capacity rather than a threshold.

Keep anti-wraparound maintenance separate in the review. Safety-driven vacuum can run for reasons unrelated to the ordinary dead-tuple trigger and must not be disabled to make a noisy graph look quieter. Table-specific tuning should make routine maintenance more predictable while preserving those safety mechanisms.

The best setting is a documented hypothesis about one table: its size, churn, required visibility freshness, and available maintenance capacity. Validate that hypothesis through several real workload cycles before generalizing it to the rest of the database.

Sources and review scope

• Vacuum configuration
• Table storage parameters
• Monitoring table statistics
• Vacuum progress reporting