How would you optimize this scenario to solve performance issues and automate the process as much as possible?

By:
In: SAP-C01
Tagged:
With: 1 Comment
A company is running a batch analysis every hour on their main transactional DB, running on an RDS MySQL instance, to populate their central Data Warehouse running on Redshift.During the execution of the batch, their transactional applications are very slow.When the batch completes they need to update the top management dashboard with the new data.The dashboard is produced by another system running on-premises that is currently started when a manually-sent email notifies that an update is required.The on-premises system cannot be modified because is managed by another team.

How would you optimize this scenario to solve performance issues and automate the process as much as possible?

Replace RDS with Redshift for the batch analysis and SNS to notify the on-premises system to update the dashboard

Replace RDS with Redshift for the oaten analysis and SQS to send a message to the on-premises system to update the dashboard

Create an RDS Read Replica for the batch analysis and SNS to notify me on-premises system to update the dashboard

Create an RDS Read Replica for the batch analysis and SQS to send a message to the on-premises system to update the dashboard.

Explanations:

Replacing RDS with Redshift for batch analysis may not address the performance issues with the transactional database since Redshift is not designed for OLTP operations. Additionally, SNS for notification lacks the reliability and decoupling benefits of SQS in message processing.

Similar to Option A, replacing RDS with Redshift for batch analysis can lead to performance challenges since Redshift is not suitable for transactional workloads. SQS for notification is better than SNS but does not fully resolve the issues with the batch processing affecting the main database.

Creating an RDS Read Replica allows for offloading the batch analysis workload from the primary database, reducing the performance impact on transactional applications. Using SNS to notify the on-premises system allows for direct notification of updates, although it does not guarantee message delivery.

While creating an RDS Read Replica is a good strategy to improve performance, using SQS instead of SNS would have been a better choice for reliable messaging. However, the requirement for an on-premises system to trigger updates suggests that SNS is adequate despite its limitations.

2025-10-16

1 Comment

  1. Juan
    Author

    As far as I’m aware, the answer is:
    Create an RDS Read Replica for the batch analysis and SNS to notify me on-premises system to update the dashboard

Leave a Reply

Your email address will not be published. Required fields are marked *

thirteen − 11 =