How do I maintain consistent application behavior after a major Amazon RDS for PostgreSQL version upgrade?

Resolution

Set up a test environment for your Amazon RDS for PostgreSQL production instance

To build a test environment, you must restore your database from a recent snapshot. Or, conduct a point-in-time restore of your database to the latest restorable time, and then perform an upgrade to the newly restored snapshot.

Complete the following steps:

1. Restore your database from a recent snapshot or perform a point-in-time restore.
2. Perform the version upgrade on the restored database.

Conduct functional testing

Note: For more information, see Amazon RDS for PostgreSQL updates and Migration to Version 17.2 on the PostgreSQL website.

When you perform a major version upgrade, any version changes might affect your application compatibility. For example, a connection management change in RDS for PostgreSQL might cause an application interruption if the Max_connection parameter is not properly adjusted.

To identify any version compatibility issues, you must test your application functionality with production-like workloads against the test database. Then, you can make necessary application code changes.

Conduct performance testing

Establish current performance baseline

Complete the following steps:

1. Enable auto_explain for query plan logging. For more information, see auto_explain on the PostgreSQL website.
   Note: For more information, see How can I log execution plans of queries for Amazon RDS PostgreSQL or Aurora PostgreSQL to tune query performance?
2. Enable query logging with duration. For more information, see How do I use Amazon RDS to turn on query logging for PostgreSQL?
3. Activate Enhanced Monitoring and Performance Insights.
4. To get a historical comparison of the database performance and workload, export database logs to PostgreSQL logs to Amazon CloudWatch.

Identify performance issues

To compare execution strategies and costs between database versions, use EXPLAIN query plans. To monitor and compare actual query response times and resource utilization patterns from older versions, use query logging and Performance Insights.

Complete the following steps:

1. Compare EXPLAIN plans between versions. For more information, see EXPLAIN on the PostgreSQL website.
2. To update table statistics, use ANALYZE with increased default_statistics_target. For more information, see ANALYZE on the PostgreSQL website.
3. To remove index bloat, use REINDEX. For more information, see REINDEX on the PostgreSQL website.
4. (Optional) To create extended statistic for correlated columns, use the CREATE STATISTICS command. For more information, see CREATE STATISTICS on the PostgreSQL website.
   Note: Use the pg_hint_plan extension if needed.

Conduct stress testing

Complete the following steps:

1. Use pgbench with custom scripts based on your application workload. For more information, see pgbench on the PostgreSQL website.
2. To test database behavior and monitor performance metrics under a high load, you must apply a production-like workload. For more information, see PostgreSQL benchmark observations and considerations and Benchmark Amazon RDS for PostgreSQL with Dedicated Log Volumes.

Related Information

Planner Method Configuration on the PostgreSQL website

Understanding statistics in PostgreSQL

How to perform a major version upgrade for RDS for PostgreSQL