Failures and antipatterns · Advanced

Transaction ID wraparound protection

Understand finite XID ordering and freezing, then investigate age horizons and maintenance progress without disabling protections.

Ask Logeia about this articleTry the three questionsReview your answers

Updated

On this page
  1. A smaller number is not necessarily older
  2. Wraparound and freezing
  3. Find what holds back cleanup
  4. Do not suppress protective maintenance
  5. Ask Logeia about this articleCheck your understanding

A smaller number is not necessarily older

PostgreSQL’s ordinary transaction IDs use a finite, cyclic number space. Visibility therefore cannot be understood as an unrestricted integer comparison in which every smaller number is older. Vacuum freezing makes sufficiently old insertion histories safe to treat as permanently past. If maintenance falls too far behind, wraparound threatens the interpretation of old row versions. This is a distinct maintenance requirement from reclaiming space occupied by dead tuples. Disabling autovacuum because a table seems small or because maintenance consumes resources can therefore create a serious correctness-related operational risk.

Wraparound and freezing

Picture a deliberately tiny circular counter: after its maximum value, new transactions receive small numerical values while old transactions still have large ones. Real PostgreSQL uses ordinary 32-bit XIDs with defined comparison rules and freezes old tuple information to keep the history interpretable. Assess risk using the age of database and relation frozen-XID horizons together with maintenance progress. A raw count of transactions currently active is not the same quantity. This counter illustration explains the problem without pretending that its invented small range models every implementation detail or protective threshold.

sql
SELECT datname, age(datfrozenxid) AS xid_age
FROM pg_database
ORDER BY xid_age DESC;

Find what holds back cleanup

A read-only starting point is age(datfrozenxid) in pg_database, followed by investigating the oldest relevant databases and relations. Long transactions, retained snapshots, prepared transactions and replication slots can hold horizons back in different ways. Connect a suspected holder to its actual retained xmin or maintenance effect rather than assigning blame merely because a slot exists. Low dead-tuple counts do not prove that freezing is unnecessary. Inspect maintenance logs and progress as well, since a vacuum repeatedly blocked or interrupted can look present without making the progress required to protect old data.

Do not suppress protective maintenance

Respond to warnings by checking space, locks and maintenance logs, then following the procedure for the running PostgreSQL version to let appropriate VACUUM work finish. Repeatedly cancelling anti-wraparound autovacuum or manually altering system-catalog horizon values hides protections instead of fixing their cause. Severe conditions can trigger protective restrictions on writes; clearing an error message alone is not a recovery plan. VACUUM FULL is a different and disruptive operation, not the routine substitute for freezing maintenance. Establish the blocker, preserve service safety and verify that the oldest relevant horizons actually advance after the intervention.

Reading and practice are recorded separately in this browser, for each language.

Learn this in order

Understand the systems behind the web · STEP 13

Choose a learning path