How do I optimize query performance in Aurora PostgreSQL-Compatible?

Short description

If you have missing indexes, outdated table statistics, or inadequate system configuration, then you might experience slow application response times or high CPU usage in Aurora PostgreSQL-Compatible.

Resolution

Use log_min_duration_statement to identify slow queries

To capture and analyze slow running queries, set the log_min_duration_statement parameter to a threshold for your application's performance requirements. Lower values such as 2 seconds provide more detailed logging but might impact performance in high-volume environments. Higher values such as 60 seconds reduce log volume but capture only the slowest queries. To deactivate logging entirely, set the value to -1.

To configure the log_min_duration_statement parameter, complete the following steps:

1. Create a database cluster parameter group.
   Note: It's a best practice to use a database cluster parameter group to change the cluster-wide settings for database instances in your cluster.
2. Associate the database cluster parameter group with your cluster.
3. Modify your database cluster parameter group.
4. To configure the log_min_duration_statement parameter at the user-level, run the following command:

   ALTER USER USERNAME SET log_min_duration_statement = '3s';

   Note: Replace USERNAME with your username.
   To configure the log_min_duration_statement at the database-level, run the following command:

   ALTER DATABASE DB_NAME SET log_min_duration_statement = '60s'; 

   Note: Replace DB_NAME with your database name.

Use pg_stat_statements to track query performance

Use the pg_stat_statements extension to track execution statistics for SQL statements and identify your slowest queries.

To activate the pg_stat_statements extension, run the following command:

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

It's a best practice to reset pg_stat_statements after major version upgrades and after significant workload changes for relevant statistics. Also, it's a best practice to reset before performance testing to get baseline data and when the statistics table is full.

To reset pg_stat_statements, run the following command:

SELECT pg_stat_statements_reset();

To find the top SQL statements by execution time, run the following query:

SELECT
     queryid,
     substring(query, 1, 50) AS short_query,
     round(total_exec_time::numeric, 2) AS total_time,
     calls,
     round(mean_exec_time::numeric, 2) AS mean,
     shared_blks_read+shared_blks_hit as block_gets,
     round((100 * (total_exec_time) / sum(total_exec_time::numeric) OVER ())::numeric, 2) AS percentage_time
FROM pg_stat_statements
ORDER BY percentage_time DESC
LIMIT 20;

Analyze query execution plans

To view the query execution plan for the slow queries that you identify, run the following EXPLAIN query:

EXPLAIN (ANALYZE, BUFFERS, VERBOSE) EXAMPLE_QUERY;

Note: Replace EXAMPLE_QUERY with your query. For example, SELECT * FROM your_table WHERE condition. For more information about EXPLAIN statements, see EXPLAIN on the PostgreSQL website.

Review the output data for key indicators that have optimization opportunities, such as missing index usage, excessive buffer reads, and sequential scans on large tables.

Perform database maintenance

Update table statistics

It's a best practice to keep your table statistics updated because outdated statistics might lead to poor query plans.

To check when autovaccum analyzed the tables, run the following SELECT query:

SELECT schemaname, relname, last_analyze, last_autoanalyze FROM pg_stat_user_tables;

To perform a database-wide analysis, run the following ANALYZE query:

ANALYZE VERBOSE;

To analyze specific tables, run the following ANALYZE query:

ANALYZE VERBOSE table_name;

Note: Replace table_name with your table name.

Optimize autovacuum

To monitor autovacuum activity and maintain good table health, run the following query:

SELECT relname, last_vacuum, last_autovacuum, n_dead_tup, autovacuum_count FROM pg_stat_user_tables;

To view vacuum settings for a specific table, run the following query:

SELECT relname, reloptions FROM pg_class WHERE relname='tablename';

Note: Replace tablename with your table name.

To obtain a more frequent analysis for critical tables, run the following query:

ALTER TABLE critical_table SET (autovacuum_analyze_scale_factor = 0.05);

Note: Default value is 0.1 (10%), set this value to 0.05 (5%) for more frequent updates.

To increase the threshold for large tables, run the following command:

ALTER TABLE large_table SET (autovacuum_analyze_threshold = 10000);

Note: The default value is 50. You can increase the autovacuum_analyze_threshold value for tables with millions of rows.

Stabilize query performance with query plan management

Aurora PostgreSQL query plan management captures and enforces optimal query execution plans to prevent performance regressions from statistic changes or upgrades. For more information, see Introduction to Aurora PostgreSQL Query Plan Management.

To use the parameters to collect and enforce plans, complete the following steps:

1. Set your rds.enable_plan_management parameter to 1 in your cluster parameter group.
2. To capture plan baseline mode, in your database instance-level parameter group, set the apg_plan_mgmt.capture_plan_baselines parameter to automatic.
3. To use only approved or fixed plans, in your database instance-level parameter group, set the apg_plan_mgmt.use_plan_baselines parameter to On.
4. Reboot your instance, and then run the following command:

   CREATE EXTENSION apg_plan_mgmt;

To view captured plans, run the following SELECT query:

SELECT sql_hash, plan_hash, status, enabled, first_used FROM apg_plan_mgmt.dba_plans ORDER BY first_used DESC;

To approve good performing plans, run the following SELECT query:

SELECT apg_plan_mgmt.set_plan_status('your_sql_hash', 'your_plan_hash', 'Approved');

Note: Replace your_sql_hash with your SQL hash and your_plan_hash with your plan hash.

To compare before you approve, run the following SELECT query:

SELECT apg_plan_mgmt.evolve_plan_baselines('example_sql_hash', 'example_plan_hash', 1.1);

Note: Replace example_sql_hash with your SQL hash and example_plan_hash with your plan hash.

Optimize index and memory performance

Optimize index

To identify missing indexes, run the following query:

SELECT relname,
       seq_scan - idx_scan AS too_much_seq,
       CASE WHEN seq_scan - idx_scan > 0
           THEN 'Missing Index?'
           ELSE 'OK'
       END,
       pg_size_pretty(pg_relation_size(relname::regclass)) AS rel_size,
       seq_scan, idx_scan
FROM pg_stat_user_tables
WHERE schemaname='public'
       AND pg_relation_size(relname::regclass) > 80000
ORDER BY too_much_seq DESC;

To create indexes for frequently used columns in WHERE, JOIN, and ORDER BY clauses, run the following command:

CREATE INDEX CONCURRENTLY idx_table_column ON table_name(column_name);

Note: Replace idx_table_column with the name of the new index, table_name with the name of table you want to create the index for, and column_name with the name of the column that you want to index.

To review partial indexes for frequently filtered data, run the following command:

CREATE INDEX CONCURRENTLY idx_name ON table_name(column_name) WHERE NOT (column_name= 35);

Note: Replace idx_name with your index name, table_name with your table name, and column_name with your column name.

Configure memory

Larger values help complex sorts and hash joins but consumes more memory per connection. Make sure that you adjust default values and gradually increase based on query complexity. Monitor for out-of-memory errors.

To adjust work_mem for session-level settings that affect only the current session, run the following command:

SET work_mem = '100MB';

To adjust work_mem for system-level setting that affects new connections, use database cluster parameter groups.

To check your current work_mem configuration, run the following command:

SELECT name, setting, unit, context FROM pg_settings WHERE name = 'work_mem';

To determine appropriate work_mem values, see Tune sorting operations in PostgreSQL with work_mem.

To identify queries that require external sorting, run the following query:

EXPLAIN (ANALYZE, BUFFERS) SELECT  FROM large_table ORDER BY column_name;

Note: Replace large_table with your large table name and both column_name with your column name.

For Aurora PostgreSQL-Compatible, you must keep the default value for the shared_buffers parameter. For more information, see Determining the optimal value for shared_buffers using the pg_buffercache extension in PostgreSQL.

Related information

Tuning memory parameters for Aurora PostgreSQL

Best practices with Amazon Aurora PostgreSQL