Preventing Transaction ID Wraparound
Understand how PostgreSQL's 32-bit transaction IDs can wrap around, why aggressive vacuuming prevents it, and how to monitor and avoid the dreaded wraparound shutdown.
What is Transaction ID Wraparound?
PostgreSQL labels every row version with the transaction ID (XID) that created it. XIDs are 32-bit, so there are only about 4 billion of them. They are compared in a circular fashion, and if old rows are not frozen, the comparison can break.
Why It is Dangerous
If XIDs wrap before old rows are frozen, recent rows could appear to be in the future and become invisible. To protect your data, PostgreSQL will refuse new writes before that happens.
All lessons in this course
- Understanding MVCC and VACUUM
- Autovacuum Configuration and Tuning
- Transaction Isolation Levels Impact
- Preventing Transaction ID Wraparound