Aurora Serverless V2 with PosgresSQL returning slow queries and high ACUs

Question

We have setup Aurora serverless V2 in our production environment. Workload is managed well, but when it comes to return larger datasets, it takes nearly 1 minute to return response of larger datasets, resulting in high ACUs. Pricing is also going very high.

I have used minimum 1 and max 4 ACUs for beginning. Going with more ACUs may increase a cost, which may be out of our pocket as we are just a startup. Not sure if Aurora is the right choice for us.

Here is the one the sample query that is resulting in high ACUs and slow response,

SELECT COUNT(DISTINCT c.id)
FROM customer c
JOIN customer_event ce ON c.id = ce.customer_id
        LEFT JOIN 
            customer_event_property cep_1 ON ce.id = cep_1.customer_event_id
        LEFT JOIN 
            event_property ep_1 ON ce.event_id = ep_1.event_id AND ep_1.id = cep_1.event_property_id
        LEFT JOIN 
            customer_event_property cep_2 ON ce.id = cep_2.customer_event_id
        LEFT JOIN 
            event_property ep_2 ON ce.event_id = ep_2.event_id AND ep_2.id = cep_2.event_property_id
        WHERE 
            (
                ce.customer_id = c.id
                and
                (
                    (ce.event_id = x AND cep_1.event_property_id = 89 AND cep_1.value_jsonb ->> 'value' = 'New Arrival' AND cep_2.event_property_id = y AND cep_2.value_jsonb ->> 'value' = 'xyz')
                    OR 
                    (ce.event_id = a AND cep_1.event_property_id = b AND (cep_1.value_jsonb ->> 'value')::numeric >= 100) 
                )
            )
    
    AND c.brand_id = xxyyzz;

Answer

Hello Zain,

Slow queries and high ACUs in Aurora Svless with PostgreSQL, especially with complex queries like yours, can be addressed by optimizing your database and query design. Consider the following strategies:

* **Indexing**: Ensure appropriate indexes are in place for the columns involved in joins and where conditions. Indexes on **customer_id**, **event_id**, and **event_property_id **might improve performance.
* **Query Optimization**: Simplify the query if possible. Break down complex queries into smaller parts or use common table expressions (CTEs) to make them more efficient.
* **Partitioning**: If your tables are large, consider partitioning them based on frequently queried columns or ranges to reduce the amount of data scanned.
* **Monitoring and Analyzing**: Use Amazon Aurora's performance insights to identify slow queries and the reasons behind them. Look for full table scans and inefficient joins.
* **Adjusting Workload**: Review and adjust your serverless V2 configuration to ensure it aligns with your workload requirements. Sometimes, fine-tuning the scaling policy can help manage costs better.

Implementing these strategies can help reduce query times and control ACUs, potentially leading to cost savings.

Aurora Serverless V2 with PosgresSQL returning slow queries and high ACUs

Relevant content