VACUUM: Freezing - Approaching Multixact ID Wraparound

Impact

Multixact ID space utilization is high and approaching wraparound.

This is about multixact IDs, not about normal transaction IDs. A multixact ID is an internal identifier used to support row locking by multiple transactions. Multixact IDs are commonly used with SELECT ... FOR UPDATE queries.

Postgres runs autovacuums regularly and this helps keeping multixact ID space utilization low by freezing old multixact IDs. There is also the anti-wraparound autovacuum specifically for freezing, when the regular autovacuums are either not freezing aggressively enough or simply not running. This anti-wraparound autovacuum will be triggered when utilization (age) exceeds the threshold, specified in autovacuum_multixact_freeze_max_age.

In order to avoid wraparound failure, old multixact IDs must be frozen by VACUUMs. The more old multixact IDs need to be frozen, the more expensive VACUUM becomes. It can take a really long time to finish and potentially cause overall performance degradation. The anti-wraparound autovacuum holds a SHARE UPDATE EXCLUSIVE lock, which can block DDL statements.

If multixact ID space utilization reaches to 99.85% (3M multixact IDs left), the system will shut down. This is to prevent any data corruption from happening by running out multixact IDs. Resolving this requires manual intervention.

With Postgres 14+, there is a special type of failsafe VACUUM which takes extraordinary measures to avoid the shutdown. While this is very useful, you still want to avoid this as a failsafe autovacuum ignores resource utilization constraints and can have significant performance impact.

Common Causes

• Ineffectual autovacuum settings

  Autovacuums, especially anti-wraparound autovacuums, are meant to freeze old multixact IDs and keep sufficient multixact IDs available in order to prevent wraparound from happening. When autovacuum is turned off, or autovacuum settings are not well suited to actual database usage, it is possible that autovacuum is not able to keep up with freezing old multixact IDs. Make sure that autovacuum is turned on, and revisit the configuration settings to ensure that autovacuum will keep multixact ID space utilization under control. You can check out the configuration settings related to freezing in Freezing page in VACUUM Advisor.

• Blocked autovacuums

  Even if autovacuum settings are adequate, it is possible that autovacuums themselves are blocked by something, hence freezing (to reduce multixact ID space utilization) is not able to keep up. The typical blocker is a long running transaction, or a transaction holding some locks that could cancel autovacuums. It is important to make sure that there is no such transactions so that VACUUMs can make freezing progress. Check for any long-running transactions and their lock state on the Connections page.