autovacuum_vacuum_max_threshold

Definition PG 18 manual

Specifies the maximum number of updated or deleted tuples needed to trigger a VACUUM in any one table, i.e., a limit on the value calculated with autovacuum_vacuum_threshold and autovacuum_vacuum_scale_factor. The default is 100,000,000 tuples. If -1 is specified, autovacuum will not enforce a maximum number of updated or deleted tuples that will trigger a VACUUM operation. This parameter can only be set in the postgresql.conf file or on the server command line; but the setting can be overridden for individual tables by changing storage parameters.

How it works

Maximum number of tuple updates or deletes prior to vacuum. A configuration reload applies a new value; existing work already in flight is not retroactively changed.

The ordinary automatic-vacuum threshold is the base term plus scale factor times reltuples, capped by autovacuum_vacuum_max_threshold from PG18. It reacts to dead tuples from updates/deletes, permits per-table overrides, and is separate from mandatory anti-wraparound vacuuming.

Monitor and change autovacuum_vacuum_max_threshold together with autovacuum_vacuum_threshold, autovacuum_vacuum_scale_factor, autovacuum_vacuum_insert_threshold. Validate on the relevant server role and real workload, then use its sighup context to choose session change, reload, or restart; a historical boot default is not the current effective value.

Operational considerations

Changing the global value while a table storage parameter overrides it.

Treating reltuples and cumulative change statistics as exact real-time counts.

Blaming a threshold without checking long transactions, replication slots, and worker saturation.

Buying short-term quiet by deferring maintenance until wraparound failsafe activates.

Workload guidance

OLAP: Run explicit ANALYZE/VACUUM after bulk loads instead of waiting only for proportional triggers; plan freezing and visibility-map advancement separately for append-only partitions.

OLTP: Tune autovacuum_vacuum_max_threshold from table size, change rate, and maintenance SLA, using per-table thresholds for large/hot relations. Observe trigger intervals, dead tuples, and ANALYZE/VACUUM duration.

SMALL: Start with upstream. Fixed thresholds dominate on small tables; after changes, confirm worker and I/O headroom.