Posted On: Oct 25, 2023
Amazon Aurora PostgreSQL query plan management supported by the apg_plan_mgmt extension now includes the ability to capture query plans on read-only replica instances. Query plan management (QPM) includes other improvements in this version such as limiting the capture of query plans based on the estimated cost.
The PostgreSQL query optimizer creates a query execution plan for a SQL statement at a specific point in time. As conditions change, the optimizer might pick a different plan which changes performance. QPM provides you with capabilities such as: limiting the optimizer to a small number of known good plans, assess the impact of creating or dropping an index, and automatically detect lower cost plans. By using the new extension function create_replica_plan_capture, you can now capture query plans generated from all your replicas and store the captured plans in the apg_plan_mgmt schema tables using the postgres_fdw extension. QPM can then apply the approved plans that you identify across all your replicas including Amazon Aurora global databases.
The apg_plan_mgmt extension version 2.5 is available on Aurora PostgreSQL 15.4, 14.9, 13.12, 12.16 and higher in all AWS Regions, including the AWS GovCloud (US) Regions.
Amazon Aurora is designed for unparalleled high performance and availability at a global scale with full MySQL and PostgreSQL compatibility. It provides built-in security, continuous backups, serverless compute, up to 15 read replicas, automated multi-Region replication, and integrations with other AWS services. To learn more about query plan management, see Best practices for Aurora PostgreSQL query plan management.