Skip to main content

Check and avoid transaction ID wraparound

The PostgreSQL® transaction control mechanism assigns a transaction ID to every row that is modified in the database; these IDs control the visibility of that row to other concurrent transactions.

The transaction ID is a 32-bit number, where 2 billion (2 thousand million) IDs are always in the "visible past" and the remainder (about 2.2 billion) are reserved for future transactions and not visible to the running transaction. To avoid a transaction wraparound and having old, existing rows invisible when more transactions are created, PostgreSQL® requires an occasional cleanup and "freezing" of old rows.

Automatic or manual vacuuming

You can manually trigger a cleanup by executing VACUUM FREEZE, but the autovacuum also does this automatically once a configured number of transactions have been created since the last freeze.

Check the autovacuum frequency

Aiven for PostgreSQL® sets that number to scale according to the database size, up to 1.5 billion transactions (which leaves 500 million transaction IDs available before a forced freeze), to avoid unnecessary churn for stable data in existing tables. To check your transaction freeze limits, run the following command in your PostgreSQL® instance:

show autovacuum_freeze_max_age

This shows you the number of transactions that trigger autovacuum to start freezing old rows.

Configuring client applications

Some applications may not automatically adjust their configuration based on the actual PostgreSQL® configuration and may show unnecessary warnings. For example, PgHero's default settings trigger an alert once 500 million transactions have been created, while the correct behavior might be to trigger an alert after 1.5 billion transactions. The transaction_id_danger setting controls this behavior, and changing the value from 1500000000 (1,500,000,000 or 1.5 billion) to 500000000 (500,000,000 or 500 million) would make it warn you when appropriate.