RDS PostgreSQL: A Nightly Job That Everyone Has Learned to Ignore

The ceiling is the EBS volume, not the CPU. A 400 GB gp2 volume provides a baseline of 3 IOPS/GB = 1,200 IOPS, bursting to a hard maximum of 3,000 IOPS for volumes under 1 TB. Read IOPS sitting at exactly 3,000 for 90 minutes is a volume cap, not a coincidence.

Confirm with:

• BurstBalance - on gp2 it will fall to zero, after which the volume is throttled to its 1,200 IOPS baseline. This is the single most informative metric and it is not on the default dashboard.
• DiskQueueDepth climbing and ReadLatency rising while IOPS stays flat at the cap. Queue growth with flat throughput equals a saturated device.
• Performance Insights DB load broken down by SQL and by wait event: confirm IO:DataFileRead is driven by the one statement and see whether other sessions are queueing behind it.
• Working-set fit: a db.m6i.xlarge has 16 GiB of RAM against a 300 GB table, so the scan is a genuine cold read from EBS every night. No amount of buffer tuning fixes it.

The red herring. The CPU alarm is useless here: CPU hits 98% as a consequence of concurrent page reads and buffer contention, it returns to normal on its own, and it fires every night until people stop reading it. The alarm that would have been actionable is BurstBalance and read latency.

Recommended order (cheapest effective first):

1. Change the storage to gp3. gp3 delivers a 3,000 IOPS / 125 MiB/s baseline at any volume size, independent of capacity, and costs the same or less per GB than gp2 - so you get the burst ceiling as a floor. Provision 6,000 IOPS if the scan needs it; that is a modest monthly delta. Storage modification is online and does not require downtime. This is the highest ratio of relief to effort and should be done first.
2. Fix the query. A single statement at 92% of DB load is an invitation. Options: add the index or covering index that turns the scan into an index-only scan; pre-aggregate into a summary table populated incrementally during the day, so the 02:00 job reads thousands of rows instead of 300 GB; or materialize nightly aggregates from a replica. This removes the load rather than accommodating it, and it is the only fix that keeps working as data grows.
3. Move reporting to a read replica. Same instance class, doubles the DB bill, but isolates the API from the scan completely. Do this if steps 1 and 2 are not enough - not before, because a replica running the same scan against the same gp2-class volume just relocates the problem.
4. Scale the instance or move to io2. Most expensive, and it hides the query problem for a year or two at best.

Week one: confirm BurstBalance is zero, modify storage to gp3 in place, and simultaneously review the query plan with EXPLAIN (ANALYZE, BUFFERS) to size the index change. Re-evaluate after one nightly run before committing to a replica.

Principle: alarm on the resource you are actually consuming, and on user-visible symptoms - not on CPU, which is ambiguous in a database.

Storage and I/O

• BurstBalance < 20% for 5 minutes (gp2 volumes only - delete this alarm when you move to gp3, or it becomes noise).
• ReadIOPS / WriteIOPS sustained above 80% of the provisioned or volume-limited ceiling.
• DiskQueueDepth above a threshold derived from your normal baseline.
• ReadLatency p99 above target.

Database and query load

• Performance Insights DB load (average active sessions) versus vCPU count, alarmed on sustained DB load above the instance's core count.
• A CloudWatch custom metric emitted by a scheduled query from pg_stat_statements or pg_stat_activity for any single statement exceeding a share of total DB time. This is what would have caught the reporting query on night one.
• FreeableMemory trend, TransactionIDUtilization and OldestReplicationSlotLag for the slow-burn failures.

User-visible

• Application p95/p99 latency from the load balancer, alarmed against the SLO. The API degradation was visible for 90 minutes; an SLO-based alarm is the one the business will actually act on.

Process

• Route alarms to a named owner with an on-call action, and review monthly for alarms that fire repeatedly without action - the nightly email was the real defect. Any alarm nobody has acted on in 30 days is either misconfigured or must be deleted.