Capturing Aurora PostgreSQL execution plans in Replicas - Amazon Aurora
PrerequisitesManaging plan capture for Aurora ReplicasTroubleshooting
Services or capabilities described in Amazon Web Services documentation might vary by Region. To see the differences applicable to the China Regions, see Getting Started with Amazon Web Services in China (PDF).

Capturing Aurora PostgreSQL execution plans in Replicas

QPM (Query Plan Management) allows you to capture the query plans generated by Aurora Replicas and stores them on the primary DB instance of the Aurora DB cluster. You can collect the query plans from all the Aurora Replicas, and maintain a set of optimal plans in a central persistent table on the primary instance. You can then apply these plans on other Replicas when needed. This helps you to maintain the stability of execution plans and improve performance of the queries across the DB clusters and engine versions.

Prerequisites

Turn on capture_plan_baselines parameter in Aurora Replica - Set capture_plan_baselines parameter to automatic or manual to capture plans in Aurora Replicas. For more information, see apg_plan_mgmt.capture_plan_baselines.

Install postgres_fdw extension - You must install postgres_fdw foreign data wrapper extension to capture plans in Aurora Replicas. Run the following command in each database, to install the extension. 

postgres=> CREATE EXTENSION IF NOT EXISTS postgres_fdw;

Managing plan capture for Aurora Replicas

Turn on plan capture for Aurora Replicas

You must have rds_superuser privileges to create or remove Plan Capture in Aurora Replicas. For more information on user roles and permissions, see Understanding PostgreSQL roles and permissions.

To capture plans, call the function apg_plan_mgmt.create_replica_plan_capture in the writer DB instance, as shown in the following:

postgres=> CALL apg_plan_mgmt.create_replica_plan_capture('cluster_endpoint', 'password');

  • cluster_endpoint - cluster_endpoint (writer endpoint) provides failover support for Plan Capture in Aurora Replicas.

  • password - We recommend you to follow the below guidelines while creating the password to enhance the security:

    • It must contain at least 8 characters.

    • It must contain at least one uppercase, one lowercase letter, and one number.

    • It must have at least one special character (?, !, #, <, >, *, and so on).

Note

If you change the cluster endpoint, password, or port number, you must run apg_plan_mgmt.create_replica_plan_capture() again with the cluster endpoint and password to re-initialize the plan capture. If not, capturing plans from Aurora Replicas will fail.

Turn off plan capture for Aurora Replicas

You can turn off capture_plan_baselines parameter in Aurora Replica by setting its value to off in the Parameter group.

Remove plan capture for Aurora Replicas

You can completely remove Plan Capture in Aurora Replicas but make sure before you do. To remove plan capture, call apg_plan_mgmt.remove_replica_plan_capture as shown:

postgres=> CALL apg_plan_mgmt.remove_replica_plan_capture();

You must call apg_plan_mgmt.create_replica_plan_capture() again to turn on plan capture in Aurora Replicas with the cluster endpoint and password.

Troubleshooting

Following, you can find troubleshooting ideas and workarounds if the plan is not captured in Aurora Replicas as expected.

  • Parameter settings - Check if the capture_plan_baselines parameter is set to proper value to turn on plan capture.

  • postgres_fdw extension is installed - Use the following query to check if postgres_fdw is installed.

    postgres=> SELECT * FROM pg_extension WHERE extname = 'postgres_fdw'

  • create_replica_plan_capture() is called - Use the following command to check if the user mapping exits. Otherwise, call create_replica_plan_capture() to initialize the feature.

    postgres=> SELECT * FROM pg_foreign_server WHERE srvname = 'apg_plan_mgmt_writer_foreign_server';

  • Cluster endpoint and port number - Check if the cluster endpoint and port number if appropriate. There won't be any error message displayed if these values are incorrect.

    Use the following command to verify if the endpoint used in the create() and to check which database it resides in:

    postgres=> SELECT srvoptions FROM pg_foreign_server WHERE srvname = 'apg_plan_mgmt_writer_foreign_server';

  • reload() - You must call apg_plan_mgmt.reload() after calling apg_plan_mgmt.delete_plan() in Aurora Replicas to make the delete function effective. This ensures that the change has been successfully implemented.

  • Password - You must enter password in create_replica_plan_capture() as per the guidelines mentioned. Otherwise, you will receive an error message. For more information,see Managing plan capture for Aurora Replicas. Use another password that aligns with the requirements.

  • Cross-Region connection - Plan capture in Aurora Replicas is also supported in Aurora global database, where writer instance and Aurora Replicas can be in different regions. The writer instance and cross-Region Replica must be able to communicate, using VPC Peering. For more information, see VPC peering. If a cross-Region failover happens, you must reconfigure the endpoint to new primary DB cluster endpoint.