VACUUM performs an aggressive scan if the table’s pg_class.relminmxid field has reached the age specified by this setting.
At a glance
| Property | Value |
|---|---|
| Parameter | vacuum_multixact_freeze_table_age |
| Category | Vacuuming |
| Default | 150 |
| Value type | integer |
| Change scope | Per-session (SET) |
| Available in | PostgreSQL 12, 13, 14, 15, 16, 17, 18, 19 (added in 12) |
What it does
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 vacuum_for_multixact_wraparound.
(Description quoted from the official PostgreSQL documentation.)
How to apply a change
Can be set per session with SET, per role/database with ALTER ROLE/DATABASE ... SET, or globally in postgresql.conf.
Inspect the current value and source with SHOW vacuum_multixact_freeze_table_age; or SELECT name, setting, unit, context, source FROM pg_settings WHERE name = 'vacuum_multixact_freeze_table_age';.
Tuning guidance
Lower it to force multixact freezing scans earlier on lock-heavy workloads approaching multixact wraparound; keep it below autovacuum_multixact_freeze_max_age.
Reference
PostgreSQL documentation — vacuum_multixact_freeze_table_age.