CONCEPT Cited by 1 source

Postgres autovacuum

Definition

Autovacuum is a Postgres background process that automatically runs VACUUM (and ANALYZE) against tables that have accumulated enough dead tuples to warrant cleanup. It replaces the legacy practice of scheduling manual VACUUM cron jobs and is enabled by default in modern Postgres.

Key configuration

Parameter	Default	What it controls
autovacuum_naptime	1 min	Launcher sleep between database checks
autovacuum_vacuum_threshold	50	Minimum dead tuples before eligible
autovacuum_vacuum_scale_factor	0.2	Additional fraction of table size
autovacuum_vacuum_cost_limit	200	I/O cost budget per iteration
autovacuum_vacuum_cost_delay	2 ms	Sleep when cost budget exhausted

A table becomes eligible when n_dead_tup > autovacuum_vacuum_threshold + autovacuum_vacuum_scale_factor * reltuples (Source: sources/2026-04-11-planetscale-keeping-a-postgres-queue-healthy).

When autovacuum is ineffective

Autovacuum running on schedule is necessary but not sufficient. It can fail to make progress in three distinct ways:

1. Horizon-pinned — an active transaction keeps the MVCC horizon fixed, so autovacuum scans the table but finds nothing it can legally reclaim. This is the mixed-workload queue failure mode: many short queries complete, many dead tuples accumulate, autovacuum runs, but cleanup rate is zero because some other transaction (or chain) is always active.
2. Table-locked — an explicit lock (DDL, some extension behaviours) blocks vacuum from touching the table.
3. Under-budgeted — on extremely high-churn tables, autovacuum_vacuum_cost_limit + autovacuum_vacuum_cost_delay can throttle vacuum below the dead-tuple production rate even when the horizon is free.

Mode 1 is the most common and most misdiagnosed. Tuning cost limits won't help; the only fix is to free the horizon (by limiting concurrent long-running transactions — see patterns/workload-class-resource-budget).

Common tuning knobs for queue tables

For high-write-churn tables like job queues, operators commonly:

• Lower per-table autovacuum_vacuum_scale_factor (e.g. to 0.01) so vacuum triggers on churn rather than accumulated dead tuples.
• Increase autovacuum_vacuum_cost_limit to give vacuum more I/O budget per pass.
• Decrease autovacuum_vacuum_cost_delay for more aggressive work.

These reduce the floor on how far behind vacuum gets under normal conditions but do not protect against horizon pinning from other workloads on the cluster.

Seen in

• sources/2026-04-11-planetscale-keeping-a-postgres-queue-healthy — Griggs explicitly names autovacuum tuning as the insufficient answer to queue degradation: "you may attempt to tune Postgres' autovacuum settings, such as autovacuum_vacuum_cost_delay and autovacuum_vacuum_cost_limit, to improve the frequency and effectiveness of the operation. But in our imagined scenario, it's not the job queue's throughput we wish to fix; it's how other workloads negatively affect it." Autovacuum had to be given a window to run by throttling overlapping analytics queries — which no autovacuum config knob could do.