Why does my Aurora PostgreSQL-Compatible instance have high CPU utilization after a major version upgrade?

Short description

First, follow the Aurora PostgreSQL-Compatible major upgrade process to make sure that your upgrade was successful. There are a number of steps to take before and after your major version upgrade, so check that you've followed the process step-by-step. If you skip any of the steps in the process, your upgrade might fail. Or, you might experience performance impacts, like high CPU, even if the upgrade itself is successful.

Resolution

1. Before you upgrade a production Aurora PostgreSQL-Compatible cluster to a new major version, test the upgrade on a duplicate of your production database. To create a duplicate test instance, you can either restore your database from a recent snapshot, or clone your database.
2. Every major version includes enhancements to the query optimizer that are designed to improve the database performance. But the DB planner/optimizer might not choose the most optimal plan for running your queries. As a result, you might see performance degradation when running the same queries in a new major version. So, it's a best practice to always test and review the performance of your queries before performing a major version upgrade. In Aurora PostgreSQL-Compatible, you can use the Query Plan Management (QPM) feature to manage query plan stability across different major versions.
3. One main reason for high CPU utilization after a major version upgrade is that statistics aren't up to date. This happens because the statistics data that is stored in the catalog pg_statistic isn't transferred during the major version upgrade. This can cause the DB optimizer to not choose the most optimal plan for running your queries. To avoid this issue, regenerate all statistics running the ANALYZE operation to refresh the pg_statistic table. Do this for every database on your Aurora PostgreSQL-Compatible DB instances.

Note: Make sure to run the ANALYZE command without any parameters to generate statistics for all regular tables in the current database.

ANALYZE VERBOSE

If you experience performance issues after following these steps, see How can I troubleshoot high CPU utilization for Amazon RDS or Amazon Aurora PostgreSQL?

Related information

Ensuring plan stability after a major version upgrade