Shipment tracking: reporting reads crowding the primary

Challenge brief

A logistics company runs shipment tracking on one Amazon RDS for PostgreSQL DB instance that also answers a fast-growing analyst reporting workload. Reporting queries now hold the instance above 90 percent CPU for most of the afternoon, tracking updates queue behind them, and the instance already runs on the largest class in its family. Reports may read data a few seconds behind the primary, but every insert and update has to keep landing on the primary, and the team cannot change the relational schema or the SQL the reports already run. A two-person platform team owns the database, so the change must ship in one sprint and must not add application code they then have to maintain. Which change scales the reporting reads with the least new code to maintain?

Success criteria

• 1.Add the RDS primary DB instance that keeps taking every insert and update.
• 2.Add an RDS read replica so read-heavy reporting scales out beyond the capacity of the primary.
• 3.Add the reporting readers that will use the replica endpoint.
• 4.Connect the primary to the read replica and the read replica to the reporting readers.
• 5.Leave DynamoDB out; the reports keep their existing relational schema and SQL.
• 6.Leave the Multi-AZ standby out of the read path; a standby replica cannot serve read traffic.

Service palette