postgres XID wraparound - ghdrako/doc_snipets GitHub Wiki

Usage Transaction ID:

  • is a 32-bit integer assigned to each transaction in PostgreSQL.
  • It tracks the relative timing of transactions, helping PostgreSQL determine the visibility of rows to specific transactions.
  • they can represent around 4.2 billion (2^32) transactions. After reaching this limit, the XID counter resets to zero.

PostgreSQL assigns an identifier (XID) to each transaction and those are used as the basis for the MVCC mechanism. We just write into the main data area (or heap) of the table and each tuple we write has an xmin (the XID that inserted it) and an xmax (if deleted, the XID that deleted it).

You can find out the current transaction ID via:

SELECT txid_current();

XIDs are unsigned 32-bit integers, so they have a range of approximately 4.3 billion values.

Checking the age of XIDs:

SELECT datname, age(datfrozenxid) AS xid_age
FROM pg_database;

This query shows the age of the oldest transaction (in terms of XIDs) in each database. If the age approaches 2 billion, action is needed to prevent wraparound.

Checking per-table XID status:

SELECT relname, age(relfrozenxid) AS xid_age
FROM pg_class
WHERE relkind = 'r'
ORDER BY age(relfrozenxid) DESC;

This shows the age of the oldest unfrozen XID for each table, sorted by age. Tables with very old XIDs should be vacuumed to prevent wraparound.

Avoiding XID Wraparound Issues

  • Ensure the Autovacuum is Running
  • Run Manual VACUUM if Necessary VACUUM FREEZE table_name;
  • Monitor XID Age Regularly - Use the pg_database and pg_class system views to monitor the age of XIDs and ensure they remain far from the wraparound limit.
  • Handle Long-Running Transactions Carefully