1. Introduction

This document outlines a structured framework for identifying, diagnosing, and resolving performance issues in AWS Aurora PostgreSQL. It covers methods to spot performance issues, tools used for analysis, and step-by-step processes for tuning queries, indexes, and parameters.

2. Spotting Performance Issues

2.1 Key Indicators

• Long query runtimes
• High CPU/IO usage
• Deadlocks or Lock Waits
• Frequent connection timeouts
• Poor concurrency handling

2.2 Monitoring Tools

AWS Performance Insights

• Access via AWS Console > RDS > [DB Instance] > Performance Insights.
• Look For:
  • Top wait events (e.g., I/O:DataFileRead, CPU, LWLock)
  • SQL queries consuming highest database load

pg_stat_statements Extension

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
SELECT * FROM pg_stat_statements ORDER BY total_time DESC LIMIT 10;

• Enables tracking execution statistics of all SQL statements

Datadog PostgreSQL Integration

• Custom dashboards to view query latency, slow query logs, CPU usage, connections
• Set alerts for unusual patterns or threshold breaches

3. Identifying Slow Queries

3.1 From Performance Insights

• Navigate to SQL tab and select the top query by average or total load
• Extract the full SQL text

3.2 From pg_stat_statements

• Run a query ordered by mean_time or total_time

3.3 From PostgreSQL Logs

Ensure logging is enabled:

ALTER SYSTEM SET log_min_duration_statement = 500; -- in milliseconds
SELECT pg_reload_conf();

Then parse logs or use log analysis tools like pgBadger

4. Analyzing Execution Plans

4.1 Running EXPLAIN

EXPLAIN ANALYZE SELECT * FROM table WHERE col = 'value';

4.2 Key Terms to Know

• Seq Scan: Full table scan, expensive for large tables
• Index Scan/Index Only Scan: Indicates use of indexes
• Hash Join / Nested Loop / Merge Join: Type of join algorithm
• Rows Removed by Filter: Indicates excessive post-processing
• Actual vs Estimated Rows: Large discrepancies may suggest stats are stale

4.3 Common Fixes

• Missing index
• Bad join order
• Redundant data processing

5. Index Optimization

5.1 Determine Candidate Indexes

EXPLAIN SELECT ...;
SELECT * FROM pg_stat_user_indexes WHERE idx_scan = 0;

• Use hypopg extension to test hypothetical indexes

CREATE EXTENSION hypopg;
SELECT * FROM hypopg_create_index('CREATE INDEX ON my_table(col1, col2)');
EXPLAIN SELECT * FROM my_table WHERE col1 = 'abc' AND col2 = 'xyz';

5.2 Best Practices

• Avoid indexes on low-cardinality columns
• Multicolumn indexes must match filter order
• Use covering indexes if possible (include columns in SELECT)

6. Materialized Views

6.1 When to Use

• Costly aggregation or join logic
• Rarely changing data
• Queries with multiple joins and filters over large datasets

6.2 Example

CREATE MATERIALIZED VIEW daily_summary AS
SELECT facility_id, date_trunc('day', created_at) AS day, COUNT(*)
FROM events
GROUP BY facility_id, day;

-- To refresh:
REFRESH MATERIALIZED VIEW daily_summary;

7. Parameter Tuning

7.1 Key Parameters

• work_mem: Memory per operation for sorting, hashing
• shared_buffers: RAM used by PostgreSQL to cache data
• effective_cache_size: Estimate of how much memory is available for disk caching
• max_parallel_workers_per_gather: Controls degree of parallelism

7.2 Determine Current Settings

SHOW work_mem;
SELECT * FROM pg_settings WHERE name IN ('work_mem', 'shared_buffers', 'effective_cache_size');

7.3 Adjust Parameters

ALTER SYSTEM SET work_mem = '64MB';
SELECT pg_reload_conf();

• Test effect using query performance and explain plans

8. Plan Stabilization

8.1 When Needed

• Queries using prepared statements behave inconsistently due to changing parameters

8.2 Solutions

• Use pg_hint_plan extension to guide planner
• Avoid generic plans by forcing constant values (e.g., inline SQL instead of prepared statements for high-variance queries)

9. Testing and Validation

9.1 Testing Queries

• Use EXPLAIN (ANALYZE, BUFFERS)
• Run queries with and without optimization to compare

9.2 A/B Test Indexes

SET enable_seqscan = OFF; -- Force index usage for testing

9.3 Regression Testing

• Ensure optimized queries return correct data
• Use query result diffing in CI/CD pipelines

10. Change Management

• Always test changes in Dev > Stage > Prod
• Keep a rollback plan
• Track changes using version control and deployment scripts

11. Automation Scripts (Examples)

11.1 Find Unused Indexes

SELECT * FROM pg_stat_user_indexes WHERE idx_scan = 0 AND indexrelname NOT LIKE '%pkey%';

11.2 Monitor Query Latency

SELECT query, calls, total_time, mean_time
FROM pg_stat_statements
ORDER BY mean_time DESC LIMIT 10;

11.3 Refresh Materialized Views in Batch

#!/bin/bash
for view in view1 view2 view3; do
  psql -c "REFRESH MATERIALIZED VIEW CONCURRENTLY $view;"
done

12. Conclusion

Tuning AWS Aurora PostgreSQL is a structured, ongoing process involving monitoring, diagnosis, experimentation, and validation. A combination of query optimizations, indexing, view management, parameter tuning, and careful change control ensures sustained performance improvements.