How can I avoid transaction ID wraparound effects that occur when using Amazon Relational Database Service (Amazon RDS) for PostgreSQL database instances?

Transaction IDs are unique identifiers that are given to each transaction. These unique transaction IDs allow concurrent Postgres transactions to run successfully. When all of the two billion available transaction IDs have been used, the transaction IDs start over again at one, which results in wraparound issues.

Transaction IDs that have wrapped around can cause severe data loss. To prevent data loss, use the autovacuum feature in PostgreSQL. Most workloads can use the default autovacuum settings, but some workloads and usage patterns require custom autovacuum tuning. For more information, see Best Practices for Working with PostgreSQL.

Before beginning an autovacuum operation, you might need to modify the following configuration parameters:

  • maintenance_work_mem is a dynamic parameter that specifies the maximum memory to be used for maintenance operations, such as VACUUM. For more information, see Maintenance Work Memory.
  • autovacuum_freeze_max_age is a static parameter that specifies the age at which to autovacuum a table to prevent transaction ID wraparound.
  • autovacuum_max_workers is a static parameter that specifies the maximum number of simultaneously running autovacuum worker processes. When there are many tables to vacuum, the default—3—might be insufficient, and you might need more autovacuum_max_workers. However, using too many autovacuum_max_workers can cause a large number of concurrent autovacuum operations that use a large number of I/O operations.
  • hot_standby_feedback is a dynamic parameter that determines whether a hot standby sends feedback messages to the primary or upstream standby. If you disable hot_standby_feedback, the read replicas can cancel long-running queries. However, enabling hot_standby_feedback can cause the master to accumulate bloat (dead rows), which can require frequent vacuuming.

To modify a configuration parameter, see Modifying Parameters in a DB Parameter Group.

When the Parameter Group status is in-sync, the parameter is modified successfully. You can also confirm that the modification is applied by running the following command:

SHOW autovacuum_freeze_max_age; 

To avoid transaction ID wraparound effects, it's a best practice to configure an alarm when the transaction ID age reaches one billion. For more information, see Implement an Early Warning System for Transaction ID Wraparound in Amazon RDS for PostgreSQL.

Did this page help you? Yes | No

Back to the AWS Support Knowledge Center

Need help? Visit the AWS Support Center

Published: 2018-08-09