t83714
commented
2 months ago Seems possible for VACUUM FREEZE takes long time ACCESS EXCLUSIVE lock
The fact that a VACUUM other than VACUUM FULL could take an ACCESS EXCLUSIVE lock was a surprise to me. However, the Postgres docs say this can happen when the TRUNCATE option is enabled (which it is by default). This is so that the VACUUM command can return disk space to the OS by truncating the end of relations. This is done in the function lazytruncateheap().
https://blog.summercat.com/postgres-vacuum-taking-an-access-exclusive-lock.html
What this PR does
Fixes: Added
FREEZEto PostgreSQL DB VACUUM routineIn 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 aSHARE 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.
Checklist