AWS re:Invent 2025 - PostgreSQL performance: Real-world workload tuning (DAT410)

Optimizing PostgreSQL Performance: Real-World Workload Tuning

Common Performance Challenges

• Database administrators often receive alerts at 3 AM due to degraded database performance, such as high CPU utilization, high query execution times, and changing query plans.
• Key areas to focus on for performance tuning include:
  • CPU utilization: Suboptimal queries, application workload undersized for the instance, and parallel queries consuming CPU
  • Memory: Suboptimal queries, memory-intensive queries, and high number of connections consuming memory
  • Storage and IOPS: Bloat from multi-version concurrency control, unused/duplicate indexes, and insufficient work memory leading to temporary files
  • Application patterns: Blocking queries, long-running transactions, and idle connections

Query Tuning Methodology

• Start by analyzing the active session summary in database insights or the pg_stat_activity view to identify top SQL and wait events consuming resources.
• Generate EXPLAIN ANALYZE BUFFERS plans to investigate plan node details, including:
  • Estimated vs. actual costs, rows, and widths
  • Actual startup time, total time, and rows returned
  • Shared buffer hits and sequential/index scans

Addressing Performance Issues

1. Rewriting Queries: A function call in a predicate can lead the optimizer to perform a sequential full table scan instead of using an index. Rewriting the query to include a SELECT call for the function provides the optimizer with the necessary information to use the index.

2. Leveraging Partial Indexes: When a table has a high percentage of inactive/deleted data, creating a partial index on the active data can significantly improve performance by reducing the number of rows the optimizer needs to consider.

3. Switching Query Plans: When the optimizer chooses a suboptimal index plan, the PostgreSQL Plan Management extension can be used to capture, approve, and switch between different query plans without modifying the application code.

4. Heap-Only Tuple Updates: For updates to non-indexed columns, the "heap-only tuple" feature can be leveraged by reducing the table's fill factor, allowing PostgreSQL to update the data in-place without modifying indexes.

5. Partition Pruning and Locking: For partitioned tables, ensuring the partition key is used in the query predicate can enable partition pruning and reduce the number of locks required, improving performance.

6. Identifying and Removing Unused/Duplicate Indexes: Unused and duplicate indexes can consume unnecessary storage and impact write performance. Regularly monitoring and removing these indexes can provide a performance boost.

Business Impact and Results

• By addressing the various performance issues, the overall query throughput increased from 15,000 to 20,000 queries per 30-second interval, a 33% improvement.
• Specific examples showed:
  • Rewriting a function call query reduced execution time from 1 minute to 800 milliseconds.
  • Creating a partial index on the active products reduced the number of rows removed by the filter from 30,000 to 900, and execution time from 23 seconds to 360 milliseconds.
  • Switching to a more optimal index plan increased the number of queries from 1,200 to 2,000, a 67% improvement.
  • Enabling heap-only tuple updates increased the number of hot updates from 2,300 to 4,000, improving write performance.
  • Removing unused and duplicate indexes reduced unnecessary storage consumption by 5-6 GB.

Key Takeaways

• Proactively monitoring database performance metrics and identifying the root causes of issues is crucial for maintaining optimal database performance.
• Leveraging PostgreSQL features like partial indexes, query plan management, and heap-only tuple updates can provide significant performance improvements without modifying application code.
• Regularly reviewing and optimizing indexes, including removing unused and duplicate ones, can have a meaningful impact on both read and write performance.
• Partitioning tables and using the partition key in queries can enable partition pruning and reduce locking overhead, boosting performance.
• A methodical, iterative approach to query tuning, including analyzing explain plans and addressing specific performance bottlenecks, is essential for optimizing complex, real-world workloads.