I'm currently working with an Amazon Aurora PostgreSQL R6g.Large instance (version 17), and I'm encountering some performance issues with a specific SELECT query. This query pulls data from several large tables, each with about 2 million rows, and it's expected that their size will grow significantly to 50-100 million rows in the future. We're currently observing that while this query runs fine on its own (taking around 2-3 seconds), the CPU usage spikes to 50-60% when we execute it across 10-15 sessions concurrently, and it can hit 90% when the load increases to 40-50 concurrent requests. Since this query is critical for displaying data on the first page of a UI for potentially thousands of users, I'm seeking advice on how to optimize its performance. Specifically, I have the following questions:
1. What might be causing the high CPU usage with this query, and how can I identify which specific part of the query is contributing to this issue?
2. What strategies can I employ to tune this query for better performance and reduced CPU consumption? Would adding indexes help?
3. Is there specific guidance on crafting queries or designing database layouts for scenarios where performance is a key factor?
4. Given the instance's CPU and memory limitations, how can I estimate the maximum number of concurrent queries this machine can handle before needing to scale up?
2 Answers
From what I see, it seems you're joining on a varchar field, which can be slow. Definitely look into indexing the txn_tbl by date to improve performance. If possible, consider caching results to lower the load on your database.
It sounds like your query might be suffering from inefficient joins, especially if there's a nested loop involved. You might need to invest in a database expert to help with optimization. They can analyze the query plan in detail.
Thanks for the suggestions! I’ll implement the indexing and check into caching options for query results. Do you know if PostgreSQL Aurora has native caching features?