I'm working with an Aurora PostgreSQL database (R6g.Large, version 17) that includes several transaction tables (like txn_tbl, txn_status, txn_decision, txn_sale, and ath), each containing around 2 million rows, which will soon increase to 50-100 million. I have a Select query that runs fine on its own, taking about 2-3 seconds, but when multiple sessions (10-15) hit it at the same time, the CPU usage spikes to 50-60%. It can even reach up to 90% with 40-50 concurrent hits. Since this query is meant to display the latest 1000 rows on the first page of a user interface, it's critical for thousands of users to run it simultaneously. The DB instance has 2 vCPUs and 16 GB of RAM.
I have a few questions:
1) What could be causing the high CPU spikes, and how can I pinpoint the parts of the query that might be contributing to this?
2) What steps can I take to optimize the query for better performance, especially in terms of reducing CPU usage? Are there indexing strategies that could help?
3) Is there any expert advice on designing queries for high-performance scenarios like this?
4) Can we calculate how many concurrent queries this instance can handle based on its CPU and memory, and when should we consider scaling up?
Here's the query I'm currently using and its execution plan: [Link to query plan](https://gist.github.com/databasetech0073/6688701431dc4bf4eaab8d345c1dc65f)
1 Answer
It sounds like you're dealing with a pretty complex query! From what I know, nested loops can really drive CPU usage up. If it's not already indexed, consider indexing the txn_tbl by date. That could help a lot with performance. Also, you mentioned potentially materializing the filtered_entity CTE—definitely worth trying, as it could streamline your data retrieval.
Thanks for the tips! I’ll look into indexing and materialization. Do you think caching results would be beneficial here?