Amazon Aurora with PostgreSQL Compatibility Adds Query Plan Management
Amazon Aurora with PostgreSQL Compatibility now supports Query Plan Management in version 10.5, and is available in the US East (N. Virginia, Ohio), US West (Oregon), Europe (Ireland), Asia Pacific (Tokyo), and Asia Pacific (Seoul) Regions.
Amazon Aurora with PostgreSQL Compatibility version 10.5 adds support for Query Plan Management, a new feature that allows you to control how and when query execution plans change. Query plan management allows you to prevent plan regressions when the database system or environment changes, and allows you to control when and whether the optimizer can use new plans.
The quality and consistency of query optimization have a major impact on the performance and stability of any relational database management system (RDBMS). Query optimizers create a query execution plan for a SQL statement at a specific point in time. As conditions change, the optimizer might pick a different plan that makes performance worse. For example, changes in statistics, constraints, environment settings, query parameter bindings, and software upgrades can all cause the query optimizer to choose a different plan and lead to performance regression. Regression is a major concern for high-performance applications.
With query plan management, you can control execution plans for a set of statements that you want to manage. You can do the following:
- Improve plan stability by forcing the optimizer to choose from a small number of known, good plans.
- Optimize plans centrally and then distribute the best plans globally.
- Identify indexes that aren't used and assess the impact of creating or dropping an index.
- Automatically detect a new minimum-cost plan discovered by the optimizer.
- Try new optimizer features with less risk, because you can choose to approve only the plan changes that improve performance.
To use the new version, you can create an Amazon Aurora with PostgreSQL Compatibility database instance with just a few clicks in the AWS Management Console. You can also create an Amazon Aurora with PostgreSQL Compatibility 10.5 read replica using an RDS for PostgreSQL 10.5 master instance, and then fail over your connections and applications to Aurora when ready. Learn more about migrating to Aurora PostgreSQL 10.5 in the Amazon RDS User Guide.
Amazon Aurora combines the performance and availability of high-end commercial databases with the simplicity and cost-effectiveness of open source databases. It provides up to three times better performance than the typical PostgreSQL database, together with increased scalability, durability, and security.