The correct answer is: Provision an Amazon RDS read replica and direct the reporting queries to the read replica.

Amazon RDS read replicas are designed to offload read-heavy workloads from the primary database. By routing long-running or complex reporting queries to the read replica, you reduce contention on the primary database, ensuring that order processing and transactional performance remain unaffected. Read replicas can be created with minimal configuration and remain in sync with the primary instance through asynchronous replication.

Why the other options are incorrect:

Distributing both the reporting and transactional workloads across the primary and read replica defeats the purpose of workload isolation. Read replicas are not suitable for handling writes or transactional updates, and using them this way can lead to data inconsistencies and operational challenges.

Migrating the application to Amazon DynamoDB is not a practical solution in this scenario. DynamoDB is a NoSQL database and would require a full application redesign and data model change, which is unnecessary just to solve read contention.

Scheduling reports outside of business hours may temporarily mitigate the issue but doesnt meet the real-time needs of staff who need access to reports during working hours. It also does not scale well and relies on manual or time-based restrictions.

Why this question matters: This question tests your understanding of how to scale relational databases and how to offload read-intensive operations using RDS features. It also reinforces the concept of workload separation and fault tolerance in high-availability environments.

Tip: When a question mentions read performance issues and reporting or analytical queries, look for options involving read replicas or caching layers. Be cautious of options that involve disruptive migrations or mixing read and write traffic on replicas.

Full AWS Practitioner Certification Question