Order Intake at Northwind: Retry Storms, Session Loss, and a 15-Minute RTO

Root cause chain, not three separate bugs

1. The single-node Redis lives in one AZ. When that AZ degraded, sessions vanished and users re-submitted carts, so request volume rose exactly when capacity fell.
2. Aurora failed over at the same moment, so the writer was unavailable or cold-cache for roughly 30-60 s.
3. The client retried POST /orders immediately on 5xx with no backoff and no idempotency key. Retries multiplied load on a database that was already in failover, which extended the 5xx window, which produced more retries. That positive feedback loop is the eleven-minute outage.

Evaluate the proposals

• Cross-zone load balancing: ALB already distributes across AZs by default and does not bill for cross-zone traffic. This change does nothing - the symptom is real but the diagnosis is wrong.
• Pool 20 to 50: actively harmful. 48 tasks x 50 = 2,400 potential connections against a writer sized for far less, and a larger pool makes the failover handshake slower and more expensive. Connection count here is not a throughput constraint, it is an amplification channel.
• Third reader: useful for read scaling, irrelevant to writer CPU caused by connection churn.

Changes I would make

• Put Amazon RDS Proxy in front of Aurora. It pools and multiplexes from the app side, holds connections through a failover, and caps total connections to the writer. Validate pinning behaviour under your transaction patterns.
• ElastiCache for Redis with one primary plus replicas across AZs and Multi-AZ automatic failover enabled. Client must be configured to re-resolve and retry - verify it in a fault injection test.
• Add exponential backoff with jitter, a capped retry budget, per-dependency circuit breakers, and a server-side retry-after. Lower the ALB deregistration delay from the 300 s default to about 30 s and set the ECS stop timeout so draining tasks stop accepting work quickly.
• Size each AZ for N-1: each AZ should carry about 1.5x its steady share so losing one leaves headroom without a scramble.

Why this ordering matters The proposed fix addresses symptoms (connections, read capacity, load balancing). The failure is a retry-amplification problem gated by a stateful dependency in a single AZ. Fix the feedback loop and the AZ-dependency, then tune capacity.

Requirement At-least-once HTTP delivery plus a third-party payment gateway means exactly-once is not achievable end to end. The achievable and correct target is: at-least-once delivery with an idempotent consumer, which is effectively-once from the customer's perspective.

Design

1. The mobile client generates a UUID idempotency key per checkout attempt and reuses the same key for every retry of that attempt. A new purchase gets a new key.
2. POST /orders writes the order row and an idempotency record in the same Aurora transaction, with a unique index on the key. A duplicate insert raises a constraint violation, which the handler catches.
3. On violation, return the stored original response (same status, same body) instead of an error. A 409 here would cause the client to retry again - the exact bug being fixed.
4. Payment capture is a separate, non-transactional call to an external gateway. Use a transactional outbox: the same Aurora transaction writes an outbox row, a relay publishes it, and the payment worker consumes it with a conditional DynamoDB write on the idempotency key before calling the gateway. Pass the key to the gateway if it supports idempotent capture.
5. Store the record with a TTL longer than any plausible client retry horizon.

Why not SQS FIFO alone SQS FIFO deduplication has a five-minute window, so it does not cover a retried order hours later. It reduces duplicate delivery but cannot replace the durable idempotency record. Use FIFO for ordering, the durable record for correctness.

Validation Chaos test: kill the writer mid-transaction, replay 10,000 requests with duplicate keys, and assert exactly one charge and one order per key.

Rule out the cheap options first

• Backup and restore: Aurora continuous backups give point-in-time recovery to roughly a five-minute granularity, which technically approaches the RPO, but restore plus stack rebuild is hours. Fails RTO.
• Pilot light with a restore-based database: same RTO problem.

What the targets actually force A 1-minute RPO means the data must be continuously replicated, not snapshotted. A 15-minute RTO means the application stack must already exist and be startable, not buildable.

Recommended shape

• Database: Aurora Global Database with the secondary cluster in us-west-2. Replication lag is typically well under one second in steady state, and managed planned failover typically completes in under a minute. Unplanned failover requires detaching and promoting the secondary; test it quarterly and measure the real number rather than trusting the marketing figure. Point the app at the global writer endpoint so promotion does not require a config change.
• Sessions: ElastiCache Global Datastore for Redis (cross-region replication), or move sessions to DynamoDB global tables if active-active writes are acceptable. Losing sessions on regional failover is an order-loss event during a checkout, so replicating them is part of the RPO budget, not an afterthought.
• Compute: warm standby - identical ECS service definitions, ALBs, target groups, ACM certificates and secrets pre-created in us-west-2, running at about 10% of primary task count with autoscaling policies ready to scale. This is the cheapest shape that still meets 15 minutes, because capacity exists and only needs to grow.
• Traffic: Route 53 health checks with failover records, and pre-staged DNS TTLs lowered to 30-60 s. Avoid a manual runbook step in the failover path; automate promotion behind a runbook automation document with a one-click and a one-command path.

The judgement call The pilot light versus warm standby delta is roughly the cost of 10% of the Fargate fleet, which is small relative to the revenue impact of missing 15 minutes. Do not economize on the standby compute; economize on the primary-region over-provisioning instead.