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

Short description

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 make sure that you follow the process step by step. If you skip a step in the process, then your upgrade might fail. Or, you might experience performance affects such as high CPU usage, even when the upgrade is successful.

Resolution

Complete the following steps:

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 always choose the most optimal plan to run your queries. It's a best practice that before you perform a major version upgrade, you test and review the performance of your queries. Use the Query Plan Management (QPM) feature to manage query plan stability across different major versions.
3. A common 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's stored in the catalog pg_statistic isn't transferred during the major version upgrade. To avoid this issue, regenerate statistics that run the ANALYZE operation to refresh the pg_statistic table for every database on your Aurora PostgreSQL-Compatible DB instances. For more information, see ANALYZE and pg_statistic on the PostgreSQL website.

Note: Run the ANALYZE operation without parameters to generate statistics for all regular tables in the current database.

ANALYZE VERBOSE

If you experience performance issues after you follow these steps, then 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