autovacuum_freeze_max_age

Definition PG 18 manual

Specifies the maximum age (in transactions) that a table's pg_class.relfrozenxid field can attain before a VACUUM operation is forced to prevent transaction ID wraparound within the table. Note that the system will launch autovacuum processes to prevent wraparound even when autovacuum is otherwise disabled.

Vacuum also allows removal of old files from the pg_xact subdirectory, which is why the default is a relatively low 200 million transactions. This parameter can only be set at server start, but the setting can be reduced for individual tables by changing table storage parameters. For more information see Section 24.1.5.

How it works

Age at which to autovacuum a table to prevent transaction ID wraparound. The value is fixed when the server starts, so changing it requires a restart.

Reaching the max age forces an aggressive autovacuum even if ordinary autovacuum is disabled; a per-table override can only lower the global ceiling. Safety must be assessed across every database/table age together with long transactions and replication slots.

Monitor and change autovacuum_freeze_max_age together with vacuum_freeze_min_age, vacuum_freeze_table_age, vacuum_failsafe_age. Validate on the relevant server role and real workload, then use its postmaster context to choose session change, reload, or restart; a historical boot default is not the current effective value.

Operational considerations

Converting transaction age to days without using the actual transaction rate.

Raising the ceiling to hide blocked or underprovisioned vacuum.

Monitoring only the current database instead of every database and table.

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

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

Workload guidance

OLAP: Proactively VACUUM (FREEZE) newly loaded or static partitions in batch windows and reserve I/O time for full scans. Convert age budgets using peak transaction rate, not a wall-clock guess.

OLTP: Calibrate autovacuum_freeze_max_age against the oldest XID/MXID age in every database and measured vacuum completion rate. Remove long transactions, stale slots, and blocked workers; never raise ages merely to hide a backlog.

SMALL: Upstream defaults are usually safest. A small system still needs anti-wraparound maintenance; monitor every database, not only the application database.