docs.aws.amazon.com
Open in
urlscan Pro
108.138.36.90
Public Scan
URL:
https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/AuroraPostgreSQL.Optimize.overview.html
Submission: On February 26 via api from DE — Scanned from DE
Submission: On February 26 via api from DE — Scanned from DE
Form analysis 0 forms found in the DOM
Text Content
SELECT YOUR COOKIE PREFERENCES
We use essential cookies and similar tools that are necessary to provide our
site and services. We use performance cookies to collect anonymous statistics so
we can understand how customers use our site and make improvements. Essential
cookies cannot be deactivated, but you can click “Customize cookies” to decline
performance cookies.
If you agree, AWS and approved third parties will also use cookies to provide
useful site features, remember your preferences, and display relevant content,
including relevant advertising. To continue without accepting these cookies,
click “Continue without accepting.” To make more detailed choices or learn more,
click “Customize cookies.”
Accept all cookiesContinue without acceptingCustomize cookies
CUSTOMIZE COOKIE PREFERENCES
We use cookies and similar tools (collectively, "cookies") for the following
purposes.
ESSENTIAL
Essential cookies are necessary to provide our site and services and cannot be
deactivated. They are usually set in response to your actions on the site, such
as setting your privacy preferences, signing in, or filling in forms.
PERFORMANCE
Performance cookies provide anonymous statistics about how customers navigate
our site so we can improve site experience and performance. Approved third
parties may perform analytics on our behalf, but they cannot use the data for
their own purposes.
Allow performance category
Allowed
FUNCTIONAL
Functional cookies help us provide useful site features, remember your
preferences, and display relevant content. Approved third parties may set these
cookies to provide certain site features. If you do not allow these cookies,
then some or all of these services may not function properly.
Allow functional category
Allowed
ADVERTISING
Advertising cookies may be set through our site by us or our advertising
partners and help us deliver relevant marketing content. If you do not allow
these cookies, you will experience less relevant advertising.
Allow advertising category
Allowed
Blocking some types of cookies may impact your experience of our sites. You may
review and change your choices at any time by clicking Cookie preferences in the
footer of this site. We and selected third-parties use cookies or similar
technologies as specified in the AWS Cookie Notice.
CancelSave preferences
UNABLE TO SAVE COOKIE PREFERENCES
We will only store essential cookies at this time, because we were unable to
save your cookie preferences.
If you want to change your cookie preferences, try again later using the link in
the AWS console footer, or contact support if the problem persists.
Dismiss
Contact Us
English
Create an AWS Account
1. AWS
2. ...
3. Documentation
4. Amazon RDS
5. User Guide for Aurora
Feedback
Preferences
AMAZON AURORA
USER GUIDE FOR AURORA
* What is Aurora?
* Aurora DB clusters
* Aurora versions
* Regions and Availability Zones
* Supported Aurora features by Region and engine
* Blue/Green Deployments
* Aurora cluster configurations
* Database activity streams in Aurora
* Exporting cluster data to Amazon S3
* Exporting snapshot data to Amazon S3
* Aurora global databases
* IAM database authentication in Aurora
* Kerberos authentication with Aurora
* Aurora machine learning
* Performance Insights with Aurora
* Zero-ETL integrations
* Amazon RDS Proxy
* Secrets Manager integration
* Aurora Serverless v2
* Aurora Serverless v1
* RDS Data API
* Zero-downtime patching (ZDP)
* Engine-native features
* Aurora connection management
* DB instance classes
* Aurora storage and reliability
* Aurora security
* High availability for Amazon Aurora
* Replication with Aurora
* DB instance billing for Aurora
* On-Demand DB instances
* Reserved DB instances
* Setting up your environment
* Getting started
* Creating and connecting to an Aurora MySQL DB cluster
* Creating and connecting to an Aurora PostgreSQL DB cluster
* Tutorial: Create a web server and an Amazon Aurora DB cluster
* Launch an EC2 instance
* Create a DB cluster
* Install a web server
* Tutorials and sample code
* Configuring your Aurora DB cluster
* Creating a DB cluster
* Creating resources with AWS CloudFormation
* Connecting to a DB cluster
* Working with parameter groups
* Overview of parameter groups
* Working with DB cluster parameter groups
* Working with DB parameter groups
* Comparing DB parameter groups
* Specifying DB parameters
* Migrating data to a DB cluster
* Creating an ElastiCache cache from Amazon RDS
* Managing an Aurora DB cluster
* Stopping and starting a cluster
* Connecting an AWS compute resource
* Connecting an EC2 instance
* Connecting a Lambda function
* Modifying an Aurora DB cluster
* Adding Aurora Replicas
* Managing performance and scaling
* Cloning a volume for an Aurora DB cluster
* Integrating with AWS services
* Using Auto Scaling with Aurora Replicas
* Maintaining an Aurora DB cluster
* Rebooting an Aurora DB cluster or instance
* Deleting Aurora clusters and instances
* Tagging RDS resources
* Working with ARNs
* Aurora updates
* Using RDS Extended Support
* Using Blue/Green Deployments for database updates
* Overview of Amazon RDS Blue/Green Deployments
* Creating a blue/green deployment
* Viewing a blue/green deployment
* Switching a blue/green deployment
* Deleting a blue/green deployment
* Backing up and restoring an Aurora DB cluster
* Overview of backing up and restoring
* Backup storage
* Creating a DB cluster snapshot
* Restoring from a DB cluster snapshot
* Copying a DB cluster snapshot
* Sharing a DB cluster snapshot
* Exporting DB cluster data to Amazon S3
* Exporting DB cluster snapshot data to Amazon S3
* Point-in-time recovery
* Deleting a DB cluster snapshot
* Tutorial: Restore a DB cluster from a snapshot
* Monitoring metrics in an Aurora DB cluster
* Overview of monitoring
* Viewing cluster status
* Viewing and responding to Amazon Aurora recommendations
* Viewing Amazon Aurora recommendations
* Responding to Amazon Aurora recommendations
* Viewing metrics in the Amazon RDS console
* Viewing combined metrics in the Amazon RDS console
* Monitoring Aurora with CloudWatch
* Overview of Amazon Aurora and Amazon CloudWatch
* Viewing CloudWatch metrics
* Exporting Performance Insights metrics to CloudWatch
* Creating CloudWatch alarms
* Monitoring DB load with Performance Insights
* Overview of Performance Insights
* Database load
* Maximum CPU
* Amazon Aurora DB engine, Region, and instance class support for
Performance Insights
* Pricing and data retention for Performance Insights
* Turning Performance Insights on and off
* Turning on the Performance Schema for Aurora MySQL
* Performance Insights policies
* Analyzing metrics with the Performance Insights dashboard
* Overview of the dashboard
* Accessing the dashboard
* Analyzing DB load
* Analyzing database performance for a period of time
* Analyzing queries
* Overview of the Top SQL tab
* Accessing more SQL text
* Viewing SQL statistics
* Viewing Performance Insights proactive recommendations
* Retrieving metrics with the Performance Insights API
* Logging Performance Insights calls using AWS CloudTrail
* Analyzing performance with DevOps Guru for RDS
* Monitoring threats with GuardDuty RDS Protection
* Monitoring the OS with Enhanced Monitoring
* Overview of Enhanced Monitoring
* Setting up and enabling Enhanced Monitoring
* Viewing OS metrics in the RDS console
* Viewing OS metrics using CloudWatch Logs
* Aurora metrics reference
* CloudWatch metrics for Aurora
* CloudWatch dimensions for Aurora
* Availability of Aurora metrics in the Amazon RDS console
* CloudWatch metrics for Performance Insights
* Counter metrics for Performance Insights
* SQL statistics for Performance Insights
* SQL statistics for Aurora MySQL
* SQL statistics for Aurora PostgreSQL
* OS metrics in Enhanced Monitoring
* Monitoring events, logs, and database activity streams
* Viewing logs, events, and streams in the Amazon RDS console
* Monitoring Aurora events
* Overview of events for Aurora
* Viewing Amazon RDS events
* Working with Amazon RDS event notification
* Overview of Amazon RDS event notification
* Granting permissions
* Subscribing to Amazon RDS event notification
* Amazon RDS event notification tags and attributes
* Listing Amazon RDS event notification subscriptions
* Modifying an Amazon RDS event notification subscription
* Adding a source identifier to an Amazon RDS event notification
subscription
* Removing a source identifier from an Amazon RDS event notification
subscription
* Listing the Amazon RDS event notification categories
* Deleting an Amazon RDS event notification subscription
* Creating a rule that triggers on an Amazon Aurora event
* Amazon RDS event categories and event messages
* Monitoring Aurora logs
* Viewing and listing database log files
* Downloading a database log file
* Watching a database log file
* Publishing to CloudWatch Logs
* Reading log file contents using REST
* MySQL database log files
* Overview of Aurora MySQL database logs
* Publishing Aurora MySQL logs to Amazon CloudWatch Logs
* Managing table-based Aurora MySQL logs
* Configuring Aurora MySQL binary logging
* Accessing MySQL binary logs
* PostgreSQL database log files
* Monitoring Aurora API calls in CloudTrail
* Monitoring Aurora with Database Activity Streams
* Overview
* Aurora MySQL network prerequisites
* Starting a database activity stream
* Getting the activity stream status
* Stopping a database activity stream
* Monitoring activity streams
* Managing access to activity streams
* Working with Aurora MySQL
* Overview of Aurora MySQL
* Aurora MySQL version 3 compatible with MySQL 8.0
* New temporary table behavior in Aurora MySQL version 3
* Comparison of Aurora MySQL version 2 and Aurora MySQL version 3
* Comparison of Aurora MySQL version 3 and MySQL 8.0 Community Edition
* Upgrading to Aurora MySQL version 3
* Aurora MySQL version 2 compatible with MySQL 5.7
* Security with Aurora MySQL
* Updating applications for new TLS certificates
* Using Kerberos authentication for Aurora MySQL
* Setting up Kerberos authentication for Aurora MySQL
* Connecting to Aurora MySQL with Kerberos authentication
* Managing a DB cluster in a domain
* Migrating data to Aurora MySQL
* Migrating from an external MySQL database to Aurora MySQL
* Physical migration using Percona XtraBackup and Amazon S3
* Reducing the physical migration time
* Logical migration using mysqldump
* Migrating from a MySQL DB instance to Aurora MySQL
* Migrating an RDS for MySQL snapshot to Aurora
* Migrating from RDS for MySQL to Aurora MySQL using a read replica
* Managing Aurora MySQL
* Managing performance and scaling for Amazon Aurora MySQL
* Backtracking a DB cluster
* Testing Amazon Aurora MySQL using fault injection queries
* Altering tables in Amazon Aurora using Fast DDL
* Displaying volume status for an Aurora DB cluster
* Tuning Aurora MySQL
* Essential concepts for Aurora MySQL tuning
* Tuning Aurora MySQL with wait events
* cpu
* io/aurora_redo_log_flush
* io/aurora_respond_to_client
* io/redo_log_flush
* io/socket/sql/client_connection
* io/table/sql/handler
* synch/cond/innodb/row_lock_wait
* synch/cond/innodb/row_lock_wait_cond
* synch/cond/sql/MDL_context::COND_wait_status
* synch/mutex/innodb/aurora_lock_thread_slot_futex
* synch/mutex/innodb/buf_pool_mutex
* synch/mutex/innodb/fil_system_mutex
* synch/mutex/innodb/trx_sys_mutex
* synch/sxlock/innodb/hash_table_locks
* Tuning Aurora MySQL with thread states
* creating sort index
* sending data
* Tuning Aurora MySQL with Amazon DevOps Guru proactive insights
* The InnoDB history list length increased significantly
* Database is creating temporary tables on disk
* Parallel query for Aurora MySQL
* Advanced Auditing with Aurora MySQL
* Replication with Aurora MySQL
* Using local write forwarding
* Cross-Region replication
* Using binary log (binlog) replication
* Using GTID-based replication
* Integrating Aurora MySQL with AWS services
* Authorizing Aurora MySQL to access AWS services
* Setting up IAM roles to access AWS services
* Creating an IAM policy to access Amazon S3
* Creating an IAM policy to access Lambda
* Creating an IAM policy to access CloudWatch Logs
* Creating an IAM policy to access AWS KMS
* Creating an IAM role to access AWS services
* Associating an IAM role with a DB cluster
* Enabling network communication to AWS services
* Loading data from text files in Amazon S3
* Saving data into text files in Amazon S3
* Invoking a Lambda function from Aurora MySQL
* Publishing Aurora MySQL logs to CloudWatch Logs
* Aurora MySQL lab mode
* Best practices with Aurora MySQL
* Aurora MySQL reference
* Configuration parameters
* Wait events
* Thread states
* Isolation levels
* Hints
* Stored procedures
* Configuring
* Ending a session or query
* Logging
* Managing the Global Status History
* Replicating
* information_schema tables
* Aurora MySQL updates
* Version Numbers and Special Versions
* Preparing for Aurora MySQL version 2 end of life
* Preparing for Aurora MySQL version 1 end of life
* Upgrading Amazon Aurora MySQL DB clusters
* Upgrading the minor version or patch level of an Aurora MySQL DB
cluster
* Upgrading the Aurora MySQL major version of a DB cluster
* Database engine updates for Amazon Aurora MySQL version 3
* Database engine updates for Amazon Aurora MySQL version 2
* Database engine updates for Amazon Aurora MySQL version 1
* Database engine updates for Aurora MySQL Serverless clusters
* MySQL bugs fixed by Aurora MySQL database engine updates
* Security vulnerabilities fixed in Amazon Aurora MySQL
* Working with Aurora PostgreSQL
* The database preview environment
* Security with Aurora PostgreSQL
* Understanding PostgreSQL roles and permissions
* Updating applications for new SSL/TLS certificates
* Using Kerberos authentication
* Setting up
* Managing a DB cluster in a Domain
* Connecting with Kerberos authentication
* Using AD security groups for Aurora PostgreSQL access control
* Migrating data to Aurora PostgreSQL
* Improving query performance with Aurora Optimized Reads
* Using Babelfish for Aurora PostgreSQL
* Babelfish limitations
* Understanding Babelfish architecture and configuration
* Babelfish architecture
* DB cluster parameter group settings for Babelfish
* Collations supported by Babelfish
* Managing collations
* Collation limitations and differences
* Managing Babelfish error handling
* Transaction Isolation Levels in Babelfish
* Creating a Babelfish for Aurora PostgreSQL DB cluster
* Migrating a SQL Server database to Babelfish
* Database authentication with Babelfish for Aurora PostgreSQL
* Connecting to a Babelfish DB cluster
* Creating C# or JDBC client connections to Babelfish
* Using a SQL Server client to connect to your DB cluster
* Using a PostgreSQL client to connect to your DB cluster
* Working with Babelfish
* Getting information from the Babelfish system catalog
* Differences between Babelfish for Aurora PostgreSQL and SQL Server
* T-SQL differences in Babelfish
* Using Babelfish features with limited implementation
* Improving Babelfish query performance
* Using explain plan to improve query performance
* Using T-SQL query hints to improve Babelfish query performance
* Using Aurora PostgreSQL extensions with Babelfish
* Babelfish supports linked servers
* Full Text Search in Babelfish
* Troubleshooting Babelfish
* Turning off Babelfish
* Babelfish versions
* Identifying your version of Babelfish
* Upgrading Babelfish to a new version
* Using Babelfish product version parameter
* Babelfish reference
* Unsupported functionality
* Supported functionality by Babelfish version
* Babelfish procedure reference
* Managing Aurora PostgreSQL
* Testing Amazon Aurora PostgreSQL by using fault injection queries
* Displaying volume status for an Aurora DB cluster
* Specifying the RAM disk for the stats_temp_directory
* Managing temporary files with PostgreSQL
* Tuning with wait events for Aurora PostgreSQL
* Essential concepts for Aurora PostgreSQL tuning
* Aurora PostgreSQL wait events
* Client:ClientRead
* Client:ClientWrite
* CPU
* IO:BufFileRead and IO:BufFileWrite
* IO:DataFileRead
* IO:XactSync
* IPC:DamRecordTxAck
* Lock:advisory
* Lock:extend
* Lock:Relation
* Lock:transactionid
* Lock:tuple
* LWLock:buffer_content (BufferContent)
* LWLock:buffer_mapping
* LWLock:BufferIO (IPC:BufferIO)
* LWLock:lock_manager
* LWLock:MultiXact
* Timeout:PgSleep
* Tuning Aurora PostgreSQL with Amazon DevOps Guru proactive insights
* Database has long running idle in transaction connection
* Best practices with Aurora PostgreSQL
* Diagnosing table and index bloat
* Improved memory management in Aurora PostgreSQL
* Fast failover
* Fast recovery after failover
* Managing connection churn
* Tuning memory parameters for Aurora PostgreSQL
* Analyze resource usage with CloudWatch metrics
* Using logical replication for a major version upgrade
* Troubleshooting storage issues
* Replication with Aurora PostgreSQL
* Using logical replication
* Using Aurora PostgreSQL as a Knowledge Base for Amazon Bedrock
* Integrating Aurora PostgreSQL with AWS services
* Importing data from Amazon S3 into Aurora PostgreSQL
* Exporting PostgreSQL data to Amazon S3
* Invoking a Lambda function from Aurora PostgreSQL
* Lambda function and parameter reference
* Publishing Aurora PostgreSQL logs to CloudWatch Logs
* Monitoring query execution plans for Aurora PostgreSQL
* Managing query execution plans for Aurora PostgreSQL
* Overview of Aurora PostgreSQL query plan management
* Best practices for Aurora PostgreSQL query plan management
* Understanding query plan management
* Capturing Aurora PostgreSQL execution plans
* Using Aurora PostgreSQL managed plans
* Examining Aurora PostgreSQL query plans in the dba_plans view
* Maintaining Aurora PostgreSQL execution plans
* Reference
* Parameter reference for Aurora PostgreSQL query plan management
* Function reference for Aurora PostgreSQL query plan management
* Reference for the apg_plan_mgmt.dba_plans view
* Advanced features in Query Plan Management
* Capturing Aurora PostgreSQL execution plans in Replicas
* Supporting table partition by Query Plan Management
* Working with extensions and foreign data wrappers
* Using Amazon Aurora delegated extension support for PostgreSQL
* Managing large objects more efficiently with the lo module
* Managing spatial data with PostGIS
* Managing partitions with the pg_partman extension
* Scheduling maintenance with the pg_cron extension
* Using pgAudit to log database activity
* Using pglogical to synchronize data
* Supported foreign data wrappers
* Working with Trusted Language Extensions for PostgreSQL
* Functions reference for Trusted Language Extensions
* pgtle.available_extensions
* pgtle.available_extension_versions
* pgtle.extension_update_paths
* pgtle.install_extension
* pgtle.install_update_path
* pgtle.register_feature
* pgtle.register_feature_if_not_exists
* pgtle.set_default_version
* pgtle.uninstall_extension
* pgtle.uninstall_extension
* pgtle.uninstall_extension_if_exists
* pgtle.uninstall_update_path
* pgtle.uninstall_update_path_if_exists
* pgtle.unregister_feature
* pgtle.unregister_feature_if_exists
* Hooks reference for Trusted Language Extensions
* Password check hook (passcheck)
* Aurora PostgreSQL reference
* Collations supported in Aurora PostgreSQL
* Aurora PostgreSQL functions reference
* aurora_db_instance_identifier
* aurora_ccm_status
* aurora_global_db_instance_status
* aurora_global_db_status
* aurora_list_builtins
* aurora_replica_status
* aurora_stat_activity
* aurora_stat_backend_waits
* aurora_stat_bgwriter
* aurora_stat_database
* aurora_stat_dml_activity
* aurora_stat_get_db_commit_latency
* aurora_stat_logical_wal_cache
* aurora_stat_memctx_usage
* aurora_stat_optimized_reads_cache
* aurora_stat_plans
* aurora_stat_reset_wal_cache
* aurora_stat_statements
* aurora_stat_system_waits
* aurora_stat_wait_event
* aurora_stat_wait_type
* aurora_version
* aurora_volume_logical_start_lsn
* aurora_wait_report
* Aurora PostgreSQL parameters
* Aurora PostgreSQL wait events
* Aurora PostgreSQL updates
* Identifying versions of Amazon Aurora PostgreSQL
* Aurora PostgreSQL releases
* Extension versions for Aurora PostgreSQL
* Upgrading Amazon Aurora PostgreSQL DB clusters
* Using a long-term support (LTS) release
* Using Aurora global databases
* Getting started with Aurora global databases
* Managing an Aurora global database
* Connecting to an Aurora global database
* Using write forwarding in an Aurora global database
* Using write forwarding in Aurora MySQL
* Using write forwarding in Aurora PostgreSQL
* Using switchover or failover in an Aurora global database
* Monitoring an Aurora global database
* Using Aurora global databases with other AWS services
* Upgrading an Amazon Aurora global database
* Using RDS Proxy
* Planning where to use RDS Proxy
* RDS Proxy concepts and terminology
* Getting started with RDS Proxy
* Managing an RDS Proxy
* Working with RDS Proxy endpoints
* Monitoring RDS Proxy with CloudWatch
* Working with RDS Proxy events
* RDS Proxy examples
* Troubleshooting RDS Proxy
* Using RDS Proxy with AWS CloudFormation
* Using RDS Proxy with Aurora global databases
* Working with zero-ETL integrations
* Getting started with zero-ETL integrations
* Creating zero-ETL integrations
* Adding and querying data
* Viewing and monitoring zero-ETL integrations
* Deleting zero-ETL integrations
* Troubleshooting zero-ETL integrations
* Using Aurora Serverless v2
* How Aurora Serverless v2 works
* Requirements and limitations for Aurora Serverless v2
* Creating an Aurora Serverless v2 DB cluster
* Managing Aurora Serverless v2
* Performance and scaling for Aurora Serverless v2
* Migrating to Aurora Serverless v2
* Using Aurora Serverless v1
* How Aurora Serverless v1 works
* Creating an Aurora Serverless v1 DB cluster
* Restoring an Aurora Serverless v1 DB cluster
* Modifying an Aurora Serverless v1 DB cluster
* Scaling Aurora Serverless v1 DB cluster capacity manually
* Viewing Aurora Serverless v1 DB clusters
* Deleting an Aurora Serverless v1 DB cluster
* Aurora Serverless v1 and Aurora database engine versions
* Using RDS Data API
* Logging RDS Data API calls with AWS CloudTrail
* Using the query editor
* DBQMS API reference
* Using Aurora machine learning
* Using Aurora machine learning with Aurora MySQL
* Using Aurora machine learning with Aurora PostgreSQL
* Code examples
* Actions
* Create a DB cluster
* Create a DB cluster parameter group
* Create a DB cluster snapshot
* Create a DB instance in a DB cluster
* Delete a DB cluster
* Delete a DB cluster parameter group
* Delete a DB instance
* Describe DB cluster parameter groups
* Describe DB cluster snapshots
* Describe DB clusters
* Describe DB instances
* Describe database engine versions
* Describe options for DB instances
* Describe parameters from a DB cluster parameter group
* Update parameters in a DB cluster parameter group
* Scenarios
* Get started with DB clusters
* Cross-service examples
* Create a lending library REST API
* Create an Aurora Serverless work item tracker
* Best practices with Aurora
* Performing an Aurora proof of concept
* Security
* Database authentication
* Password management with Aurora and Secrets Manager
* Data protection
* Data encryption
* Encrypting Amazon Aurora resources
* AWS KMS key management
* Using SSL/TLS to encrypt a connection
* Rotating your SSL/TLS certificate
* Internetwork traffic privacy
* Identity and access management
* How Amazon Aurora works with IAM
* Identity-based policy examples
* AWS managed policies
* Policy updates
* Cross-service confused deputy prevention
* IAM database authentication
* Enabling and disabling
* Creating and using an IAM policy for IAM database access
* Creating a database account using IAM authentication
* Connecting to your DB cluster using IAM authentication
* Connecting using IAM: AWS CLI and mysql client
* Connecting using IAM authentication from the command line: AWS
CLI and psql client
* Connecting using IAM authentication and the AWS SDK for .NET
* Connecting using IAM authentication and the AWS SDK for Go
* Connecting using IAM authentication and the AWS SDK for Java
* Connecting using IAM authentication and the AWS SDK for Python
(Boto3)
* Troubleshooting
* Logging and monitoring
* Compliance validation
* Resilience
* Infrastructure security
* VPC endpoints (AWS PrivateLink)
* Security best practices
* Controlling access with security groups
* Master user account privileges
* Service-linked roles
* Using Amazon Aurora with Amazon VPC
* Working with a DB cluster in a VPC
* Scenarios for accessing a DB cluster in a VPC
* Tutorial: Create a VPC for use with a DB cluster (IPv4 only)
* Tutorial: Create a VPC for use with a DB cluster (dual-stack mode)
* Quotas and constraints
* Troubleshooting
* Amazon RDS API reference
* Using the Query API
* Troubleshooting applications
* Document history
* AWS Glossary
Overview of Aurora PostgreSQL query plan management - Amazon Aurora
AWSDocumentationAmazon RDSUser Guide for Aurora
Supported SQL statementsLimitationsTerminologyAvailable versionsTurning on query
plan managementUpgrading query plan managementTurning off Aurora PostgreSQL
query plan management
OVERVIEW OF AURORA POSTGRESQL QUERY PLAN MANAGEMENT
PDFRSS
Aurora PostgreSQL query plan management is designed to ensure plan stability
regardless of changes to the database that might cause query plan regression.
Query plan regression occurs when the optimizer chooses a sub-optimal plan for a
given SQL statement after system or database changes. Changes to statistics,
constraints, environment settings, query parameter bindings, and upgrades to the
PostgreSQL database engine can all cause plan regression.
With Aurora PostgreSQL query plan management, you can control how and when query
execution plans change. The benefits of Aurora PostgreSQL query plan management
include 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.
You can use the tools provided by query plan management proactively, to specify
the best plan for certain queries. Or you can use query plan management to react
to changing circumstances and avoid plan regressions. For more information, see
Best practices for Aurora PostgreSQL query plan management.
TOPICS
* Supported SQL statements
* Query plan management limitations
* Query plan management terminology
* Aurora PostgreSQL query plan management versions
* Turning on Aurora PostgreSQL query plan management
* Upgrading Aurora PostgreSQL query plan management
* Turning off Aurora PostgreSQL query plan management
SUPPORTED SQL STATEMENTS
Query plan management supports the following types of SQL statements.
* Any SELECT, INSERT, UPDATE, or DELETE statement, regardless of complexity.
* Prepared statements. For more information, see PREPARE in the PostgreSQL
documentation.
* Dynamic statements, including those run in immediate-mode. For more
information, see Dynamic SQL and EXECUTE IMMEDIATE in PostgreSQL
documentation.
* Embedded SQL commands and statements. For more information, see Embedded SQL
Commands in the PostgreSQL documentation.
* Statements inside named functions. For more information, see CREATE FUNCTION
in the PostgreSQL documentation.
* Statements containing temp tables.
* Statements inside procedures and DO-blocks.
You can use query plan management with EXPLAIN in manual mode to capture a plan
without actually running it. For more information, see Analyzing the optimizer's
chosen plan. To learn more about query plan management's modes (manual,
automatic), see Capturing Aurora PostgreSQL execution plans.
Aurora PostgreSQL query plan management supports all PostgreSQL language
features, including partitioned tables, inheritance, row-level security, and
recursive common table expressions (CTEs). To learn more about these PostgreSQL
language features, see Table Partitioning, Row Security Policies, and WITH
Queries (Common Table Expressions) and other topics in the PostgreSQL
documentation.
For information about different versions of the Aurora PostgreSQL query plan
management feature, see Aurora PostgreSQL apg_plan_mgmt extension versions in
the Release Notes for Aurora PostgreSQL.
QUERY PLAN MANAGEMENT LIMITATIONS
The current release of Aurora PostgreSQL query plan management has the following
limitations.
* Plans aren't captured for statements that reference system relations –
Statements that reference system relations, such as pg_class, aren't
captured. This is by design, to prevent a large number of system-generated
plans that are used internally from being captured. This also applies to
system tables inside views.
* Larger DB instance class might be needed for your Aurora PostgreSQL DB
cluster – Depending on the workload, query plan management might need a DB
instance class that has more than 2 vCPUs. The number of max_worker_processes
is limited by the DB instance class size. The number of max_worker_processes
provided by a 2-vCPU DB instance class (db.t3.medium, for example) might not
be sufficient for a given workload. We recommend that you choose a DB
instance class with more than 2 vCPUs for your Aurora PostgreSQL DB cluster
if you use query plan managment.
When the DB instance class can't support the workload, query plan management
raises an error message such as the following.
WARNING: could not register plan insert background process
HINT: You may need to increase max_worker_processes.
In this case, you should scale up your Aurora PostgreSQL DB cluster to a DB
instance class size with more memory. For more information, see Supported DB
engines for DB instance classes.
* Plans already stored in sessions aren't affected – Query plan management
provides a way to influence query plans without changing the application
code. However, when a generic plan is already stored in an existing session
and if you want to change its query plan, then you must first
setplan_cache_mode to force_custom_plan in the DB cluster parameter group.
* queryid in apg_plan_mgmt.dba_plans and pg_stat_statements can diverge when:
* Objects are dropped and recreated after storing in
apg_plan_mgmt.dba_plans.
* apg_plan_mgmt.plans table is imported from another cluster.
For information about different versions of the Aurora PostgreSQL query plan
management feature, see Aurora PostgreSQL apg_plan_mgmt extension versions in
the Release Notes for Aurora PostgreSQL.
QUERY PLAN MANAGEMENT TERMINOLOGY
The following terms are used throughout this topic.
managed statement
A SQL statement captured by the optimizer under query plan management. A managed
statement has one or more query execution plans stored in the
apg_plan_mgmt.dba_plans view.
plan baseline
The set of approved plans for a given managed statement. That is, all the plans
for the managed statement that have "Approved" for their status column in the
dba_plan view.
plan history
The set of all captured plans for a given managed statement. The plan history
contains all plans captured for the statement, regardless of status.
query plan regression
The case when the optimizer chooses a less optimal plan than it did before a
given change to the database environment, such as a new PostgreSQL version or
changes to statistics.
AURORA POSTGRESQL QUERY PLAN MANAGEMENT VERSIONS
Query plan management is supported by all currently available Aurora PostgreSQL
releases. For more information, see the list of Amazon Aurora PostgreSQL updates
in the Release Notes for Aurora PostgreSQL.
Query plan management functionality is added to your Aurora PostgreSQL DB
cluster when you install the apg_plan_mgmt extension. Different versions of
Aurora PostgreSQL support different versions of the apg_plan_mgmt extension. We
recommend that you upgrade the query plan management extension to the latest
release for your version of Aurora PostgreSQL.
NOTE
For release notes for each apg_plan_mgmt extension versions, see Aurora
PostgreSQL apg_plan_mgmt extension versions in the Release Notes for Aurora
PostgreSQL.
You can identify the version running on your cluster by connecting to an
instance using psql and using the metacommand \dx to list extensions as shown
following.
labdb=> \dx
List of installed extensions
Name | Version | Schema | Description
---------------+---------+---------------+-------------------------------------------------------------------
apg_plan_mgmt | 1.0 | apg_plan_mgmt | Amazon Aurora with PostgreSQL compatibility Query Plan Management
plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language
(2 rows)
The output shows that this cluster is using 1.0 version of the extension. Only
certain apg_plan_mgmt versions are available for a given Aurora PostgreSQL
version. In some cases, you might need to upgrade the Aurora PostgreSQL DB
cluster to a new minor release or apply a patch so that you can upgrade to the
most recent version of query plan management. The apg_plan_mgmt version 1.0
shown in the output is from an Aurora PostgreSQL version 10.17 DB cluster, which
doesn't have a newer version of apg_plan_mgmt available. In this case, the
Aurora PostgreSQL DB cluster should be upgraded to a more recent version of
PostgreSQL.
For more information about upgrading your Aurora PostgreSQL DB cluster to a new
version of PostgreSQL, see Amazon Aurora PostgreSQL updates.
To learn how to upgrade the apg_plan_mgmt extension, see Upgrading Aurora
PostgreSQL query plan management.
TURNING ON AURORA POSTGRESQL QUERY PLAN MANAGEMENT
Setting up query plan management for your Aurora PostgreSQL DB cluster involves
installing an extension and changing several DB cluster parameter settings. You
need rds_superuser permissions to install the apg_plan_mgmt extension and to
turn on the feature for the Aurora PostgreSQL DB cluster.
Installing the extension creates a new role, apg_plan_mgmt. This role allows
database users to view, manage, and maintain query plans. As an administrator
with rds_superuser privileges, be sure to grant the apg_plan_mgmt role to
database users as needed.
Only users with the rds_superuser role can complete the following procedure. The
rds_superuser is required for creating the apg_plan_mgmt extension and its
apg_plan_mgmt role. Users must be granted the apg_plan_mgmt role to administer
the apg_plan_mgmt extension.
TO TURN ON QUERY PLAN MANAGEMENT FOR YOUR AURORA POSTGRESQL DB CLUSTER
The following steps turn on query plan management for all SQL statements that
get submitted to the Aurora PostgreSQL DB cluster. This is known as automatic
mode. To learn more about the difference between modes, see Capturing Aurora
PostgreSQL execution plans.
1. Open the Amazon RDS console at https://console.aws.amazon.com/rds/.
2. Create a custom DB cluster parameter group for your Aurora PostgreSQL DB
cluster. You need to change certain parameters to activate query plan
management and to set its behavior. For more information, see Creating a DB
parameter group.
3. Open the custom DB cluster parameter group and set the
rds.enable_plan_management parameter to 1, as shown in the following image.
For more information, see Modifying parameters in a DB cluster parameter
group.
4. Create a custom DB parameter group that you can use to set query plan
parameters at the instance level. For more information, see Creating a DB
cluster parameter group.
5. Modify the writer instance of the Aurora PostgreSQL DB cluster to use the
custom DB parameter group. For more information, see Modifying a DB
instance in a DB cluster.
6. Modify the Aurora PostgreSQL DB cluster to use the custom DB cluster
parameter group. For more information, see Modifying the DB cluster by
using the console, CLI, and API.
7. Reboot your DB instance to enable the custom parameter group settings.
8. Connect to your Aurora PostgreSQL DB cluster's DB instance endpoint using
psql or pgAdmin. The following example uses the default postgres account
for the rds_superuser role.
psql --host=cluster-instance-1.111122223333.aws-region.rds.amazonaws.com --port=5432 --username=postgres --password --dbname=my-db
9. Create the apg_plan_mgmt extension for your DB instance, as shown
following.
labdb=> CREATE EXTENSION apg_plan_mgmt;
CREATE EXTENSION
TIP
Install the apg_plan_mgmt extension in the template database for your
application. The default template database is named template1. To learn
more, see Template Databases in the PostgreSQL documentation.
10. Change the apg_plan_mgmt.capture_plan_baselines parameter to automatic.
This setting causes the optimizer to generate plans for every SQL statement
that is either planned or executed two or more times.
NOTE
Query plan management also has a manual mode that you can use for specific
SQL statements. To learn more, see Capturing Aurora PostgreSQL execution
plans.
11. Change the value of apg_plan_mgmt.use_plan_baselines parameter to "on."
This parameter causes the optimizer to choose a plan for the statement from
its plan baseline. To learn more, see Using Aurora PostgreSQL managed
plans.
NOTE
You can modify the value of either of these dynamic parameters for the
session without needing to reboot the instance.
When your query plan management set up is complete, be sure to grant the
apg_plan_mgmt role to any database users that need to view, manage, or maintain
query plans.
UPGRADING AURORA POSTGRESQL QUERY PLAN MANAGEMENT
We recommend that you upgrade the query plan management extension to the latest
release for your version of Aurora PostgreSQL.
1. Connect to the writer instance of your Aurora PostgreSQL DB cluster as a
user that has rds_superuser privileges. If you kept the default name when
you set up your instance, you connect as postgres This example shows how to
use psql, but you can also use pgAdmin if you prefer.
psql --host=111122223333.aws-region.rds.amazonaws.com --port=5432 --username=postgres --password
2. Run the following query to upgrade the extension.
ALTER EXTENSION apg_plan_mgmt UPDATE TO '2.1';
3. Use the apg_plan_mgmt.validate_plans function to update the hashes of all
plans. The optimizer validates all Approved, Unapproved, and Rejected plans
to ensure that they's still viable plans for new version of the extension.
SELECT apg_plan_mgmt.validate_plans('update_plan_hash');
To learn more about using this function, see Validating plans.
4. Use the apg_plan_mgmt.reload function to refresh any plans in the shared
memory with the validated plans from the dba_plans view.
SELECT apg_plan_mgmt.reload();
To learn more about all functions available for query plan management, see
Function reference for Aurora PostgreSQL query plan management.
TURNING OFF AURORA POSTGRESQL QUERY PLAN MANAGEMENT
You can disable query plan management at any time by turning off the
apg_plan_mgmt.use_plan_baselines and apg_plan_mgmt.capture_plan_baselines.
labdb=> SET apg_plan_mgmt.use_plan_baselines = off;
labdb=> SET apg_plan_mgmt.capture_plan_baselines = off;
Javascript is disabled or is unavailable in your browser.
To use the Amazon Web Services Documentation, Javascript must be enabled. Please
refer to your browser's Help pages for instructions.
Document Conventions
Managing query execution plans for Aurora PostgreSQL
Best practices for Aurora PostgreSQL query plan management
Did this page help you? - Yes
Thanks for letting us know we're doing a good job!
If you've got a moment, please tell us what we did right so we can do more of
it.
Did this page help you? - No
Thanks for letting us know this page needs work. We're sorry we let you down.
If you've got a moment, please tell us how we can make the documentation better.
DID THIS PAGE HELP YOU?
Yes
No
Provide feedback
NEXT TOPIC:
Best practices for Aurora PostgreSQL query plan management
PREVIOUS TOPIC:
Managing query execution plans for Aurora PostgreSQL
NEED HELP?
* Try AWS re:Post
* Connect with an AWS IQ expert
PrivacySite termsCookie preferences
© 2024, Amazon Web Services, Inc. or its affiliates. All rights reserved.
ON THIS PAGE
* Supported SQL statements
* Limitations
* Terminology
* Available versions
* Turning on query plan management
* Upgrading query plan management
* Turning off Aurora PostgreSQL query plan management
DID THIS PAGE HELP YOU? - NO
Thanks for letting us know this page needs work. We're sorry we let you down.
If you've got a moment, please tell us how we can make the documentation better.
Feedback