vacuum_multixact_freeze_table_age

Definition PG 18 manual

VACUUM performs an aggressive scan if the table's pg_class.relminmxid field has reached the age specified by this setting. An aggressive scan differs from a regular VACUUM in that it visits every page that might contain unfrozen XIDs or MXIDs, not just those that might contain dead tuples. The default is 150 million multixacts. Although users can set this value anywhere from zero to two billion, VACUUM will silently limit the effective value to 95% of autovacuum_multixact_freeze_max_age, so that a periodic manual VACUUM has a chance to run before an anti-wraparound is launched for the table. For more information see Section 24.1.5.1.

How it works

Sets the MultiXact age at which VACUUM switches to an aggressive scan so it can advance a table’s relminmxid. It is a user-context setting.

An aggressive MXID scan visits every page not already all-frozen and processes eligible MultiXacts. PostgreSQL caps the effective value at 95% of autovacuum_multixact_freeze_max_age.

Monitor mxid_age(relminmxid), mxid_age(datminmxid), and pg_multixact storage. The XID horizon relfrozenxid is controlled by vacuum_freeze_table_age instead.

Operational considerations

Monitoring relfrozenxid instead of the MultiXact horizon relminmxid.

Using autovacuum_freeze_max_age rather than autovacuum_multixact_freeze_max_age for the 95% cap.

Copying an XID-age policy into an MXID workload with a very different consumption rate.

Ignoring pg_multixact member-space pressure while age still appears comfortable.

Workload guidance

OLAP: Most read-only analytics consume few MXIDs, but concurrent loaders can differ. Schedule scans from measured mxid_age and member-space growth rather than copying XID settings.

OLTP: Choose an MXID threshold that leaves enough time to complete aggressive scans before autovacuum_multixact_freeze_max_age. Measure workloads with heavy shared row locking.

SMALL: Keep the default and monitor relminmxid/datminmxid. A small database can still consume MXIDs rapidly through row-locking patterns.