PostgreSQL's transaction control mechanism assigns a transaction ID to every row that's 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 old, existing rows from being invisible when more transactions are created, PostgreSQL requires occasional cleanup and "freezing" of old rows. This can be done manually by executing VACUUM FREEZE but it's also done automatically by the autovacuum daemon once a configured number of transactions has been created since the last point of freeze.

In Aiven, we've set the the limit to scale according to the database size to up to 1.5 billion transactions (which will leave 500 million transaction IDs available before a forced freeze) to avoid unnecessary churn of stable data in existing tables. To check your transaction freeze limits, you can execute show autovacuum_freeze_max_age in your PG instance: after this many transactions have been created autovacuum will start freezing old rows.

Some applications may not automatically adjust their configuration based on actual PostgreSQL configuration and may show unnecessary warnings. For example PGHero's default settings alert once 500 million transactions have been created while the correct behavior might be to 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 when appropriate.

Did this answer your question?