VACUUM: Freezing - Approaching Transaction ID Wraparound
Every 30 minutes
Detects when transaction ID space utilization on the server is more than
50% and creates an issue with severity "warning". Escalates to "critical" once utilization exceeds
80%. Resolves once utilization falls below the threshold.
This check is enabled by default. These parameters can be tuned in the Configure section of the Alerts & Check-Up page.
Transaction ID space utilization is high and approaching wraparound.
Postgres runs autovacuums regularly and this helps keeping transaction ID space utilization low by freezing old transaction IDs. There is also the anti-wraparound autovacuum specific 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
In order to avoid wraparound failure, old transaction IDs must be frozen by VACUUMs. The more old transaction IDs need to be frozen, the more expensive VACUUM becomes. It can take a really long time to finish and potentially causes overall performance degradation. The anti-wraparound autovacuum holds a
SHARE UPDATE EXCLUSIVE lock, which can block DDL statements.
If transaction ID space utilization reaches to 99.85% (3M transaction IDs left), the system will shut down. This is to prevent any data corruption from happening by running out transaction 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.
Ineffectual autovacuum settings
Autovacuums, especially anti-wraparound autovacuums, are meant to freeze old transaction IDs and keep sufficient transaction 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 transaction IDs. Make sure that autovacuum is turned on, and revisit the configuration settings to ensure that autovacuum will keep transaction ID space utilization under control. You can check out the configuration settings related to freezing in Freezing page in VACUUM Advisor.
Even if autovacuum settings are adequate, it is possible that autovacuums themselves are blocked by something, hence freezing (to reduce transaction 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.
Couldn't find what you were looking for or want to talk about something specific?
Start a conversation with us →