Parameter · Vacuuming / Automatic Vacuuming
autovacuum_multixact_freeze_max_age
Multixact age at which to autovacuum a table to prevent multixact wraparound.
- Default:
400000000 - Type: integer
- Range: 10000 … 2000000000
- Applies: Restart required
In plain words
When several transactions lock the same row (for example foreign-key checks with SELECT … FOR KEY SHARE), PostgreSQL stores a MultiXact ID in the row. These IDs are 32-bit too and need freezing. Past this age (400 million) autovacuum forces a vacuum of the table.
For DBAs and operators
Compared with mxid_age(pg_class.relminmxid). There is a second trigger: the member storage in pg_multixact/members is limited too. When more than about half of it is used, autovacuum lowers the effective freeze age automatically, so multixact anti-wraparound vacuums can start well before 400M. Per table it can only be lowered.
SELECT datname, mxid_age(datminmxid) FROM pg_database ORDER BY 2 DESC;
SELECT oid::regclass, mxid_age(relminmxid) FROM pg_class
WHERE relkind IN ('r','m','t') ORDER BY 2 DESC LIMIT 10;
-- disk use: du -sh $PGDATA/pg_multixact/*Heavy MultiXact use comes from foreign keys on busy parent rows, SELECT … FOR SHARE, and savepoints combined with row locks. Near the limit: WARNING: database "app" must be vacuumed before 10000000 more MultiXactIds are used.
How to change it
Change in postgresql.conf or ALTER SYSTEM, then restart the server.
See it in context
XID wraparound and freezing SLRU caches Locking and deadlocks
Related parameters
vacuum_multixact_freeze_table_age vacuum_multixact_freeze_min_age vacuum_multixact_failsafe_age autovacuum_freeze_max_age