SAP-C02 Practice Question 47: Reporting Load on Writer
AWS Certified Solutions Architect – Professional (SAP-C02) · Test 5 · question 7 of 10
An order platform runs on an Amazon Aurora PostgreSQL-Compatible Edition cluster with one writer and one reader. Every component of the application (the transactional order service and a reporting module that runs long analytical SELECT statements) connects to the cluster endpoint.
Amazon CloudWatch shows the writer instance pinned above 95 percent CPU from 09:00 to 18:00 on weekdays while the single reader idles at around 10 percent. Order submissions time out during the worst periods. Reporting load is spiky and unpredictable: a regional manager can kick off a dozen heavy reports at once, and overnight there is almost no activity at all.
The reporting module's SQL will not be rewritten, but its connection string can be changed. Finance has said that paying for peak reporting capacity around the clock is not acceptable. What should the solutions architect do?
Show answer and explanation
Answer:
There are two defects, and this option fixes both. The first is that read-only reporting queries are being sent to the cluster endpoint, which always resolves to the writer; that is why the writer is saturated while the reader idles. Repointing the reporting module at the reader endpoint moves that load onto the replicas, and a connection string change is explicitly allowed. The second is that reporting demand is spiky. Aurora Auto Scaling dynamically adjusts the number of Aurora Replicas using a target-tracking policy on a CloudWatch metric such as average reader CPU, adding replicas when a burst of reports arrives and removing them when the load falls so you don't pay for unused instances overnight. AWS also notes two things that make this the right pairing: Aurora Auto Scaling helps only with the workload on reader instances, and applications should connect through the reader endpoint so that newly added replicas actually receive traffic.
Option A scales the one instance that is already the bottleneck and keeps analytical queries competing with order writes on the same node. It is the most expensive option and it still leaves the reader idle.
Option B routes the reports correctly but fixes the capacity at peak. Four replicas run all night for no reason, which is the cost outcome Finance rejected, and a twelve-report burst can still exceed a fixed reader count.
Option C solves a different problem. RDS Proxy pools and shares database connections to survive connection storms and speed failover; it does not move read queries off the writer or add read capacity, so the writer stays at 95 percent CPU.