Articles
Transaction ID Wraparound in Postgres | Product Blog • Sentry
PostgreSQL: Documentation: 9.4: Routine Vacuuming
- Postgres MVCC relies on 32-bit transaction ID (XID)
- XID tracks row versions and determines which row version can be seen by a particular transaction
- If dealing with tens of thousands of transactions per second, XID max value can be reached and value will be rounded off.
This will result in data from past, appearing to be in future and this can result in data corruption
- XID can be viewed as lying on a circle or circular buffer.
As long as the end of that buffer does not jump past front, the system will function properly
- To prevent, vacuum process is also responsible for "freezing" row versions that are of certain age (tens of millions of transactions by default)
- there are failure modes which prevent it from freezing extremely old tuples and the oldest unfrozen tuple limits the number of past IDs that are visible to a transaction (only two billion past IDs are visible)
- If the remaining XID count reaches one million, the database will stop accepting commands and must be restarted in single-user mode to recover
- Therefore, it is extremely important to monitor remaining XIDs so that your database never gets into this state.
- The Remaining XIDs can be fetched with this query
SELECT power(2, 31) - age(datfrozenxid) AS remaining
FROM pg_database
WHERE datname = current_database();