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 IDs are always in the "visible past" and the remainder 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. You can do this manually by executing VACUUM FREEZE, but the autovacuum also does this automatically once a configured number of transactions have been created since the last freeze.

Aiven for PostgreSQL sets the the limit 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.

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 to 500000000 would make it warn you when appropriate.

Did this answer your question?