Aurora Postgres 10 to 14 Migration: Postmortem Lessons
A major-version database cutover can pass every consistency test and still fail under live traffic. This postmortem examines a November 23, 2022 Aurora Postgres 10.x to 14.x migration where the new cluster was not fully analyzed, the query planner chose poor plans, and failures cascaded across a subscription platform. The goal is a reusable pre-cutover checklist and a clearer separation between root cause and amplifier.
What happened
The team migrated from an AWS RDS cluster on Aurora Postgres 10.x to a new cluster on Aurora Postgres 14.x. The approach restored a snapshot, upgraded it, and replicated changes from production. Once replication caught up, the old database was set read-only and traffic shifted to the new cluster. Performance degraded almost immediately. The new cluster had not run ANALYZE on the largest tables, so the query planner produced inefficient plans for high-throughput queries. The outage began at 9:39 UTC and the dashboard and SDK endpoints were affected until about 13:07 UTC; around 11:30 UTC roughly half of requests were succeeding, ramping to full recovery at 13:07 UTC.
Root cause vs. amplifiers
The primary cause was missing statistics: ANALYZE had not completed on one of the largest tables before cutover. During earlier performance comparisons the 14.x test cluster had been up long enough to vacuum and gather statistics, so the problem did not appear. Several secondary issues made recovery harder:
- A third-party APM agent ran EXPLAIN on slow queries, adding database load once many queries became slow. Disabling that agent in a release helped.
- Primary key sequences were not replicated, so some inserts collided with existing rows. A prepared script raised the affected sequence values.
- Incoming Apple webhooks carried a particularly slow query and re-degraded recovery until they were temporarily paused.
- Integration event dispatching continued briefly during the read-only window and failed to record statuses; webhooks were replayed, other integrations were not.
Treating these as separate categories matters: fixing the statistics addressed the planner problem, while the other items were load and state-management failures that could have masked or prolonged it.
Customer impact
For apps using the SDK, existing entitlements generally remained unlocked because entitlement data is cached in the app and the cache is used when requests fail. Customers whose entitlement had already expired could lose access during the outage. New purchases completed at the store but entitlement unlocking was unreliable for about two hours; the SDK persisted receipts and retried when the app was foregrounded, so reopening the app reconciled the purchase without a special user action. The dashboard was inaccessible for the same window. Integration events were dispatched but not all were recorded; webhooks were replayed, while other integrations were left alone because replay risked duplicate side effects such as repeated Slack messages. Whether specific integration events can be replayed on request is a policy question for the current operator to define and document before making any such offer.
Timeline reference
The detailed timeline below is a dated record of the November 23, 2022 incident in UTC. Times marked with ~ are estimates. Use it to compare detection and mitigation order, not as a template for current infrastructure.
Pre-cutover checklist
The following checks are derived from the failure modes in this incident. They are not a guarantee of zero downtime.
- Confirm ANALYZE has completed on every large table on the target cluster, and verify statistics freshness independently of vacuum timing.
- Replay representative write traffic during dry runs, not only reads and consistency checks. The team had focused on data consistency and query performance and did not replay writes.
- Verify sequence state after replication. Logical replication copies row values but not sequence state, so sequences must be advanced or reset before writes resume.
- Decide in advance whether APM EXPLAIN collection should be disabled during cutover, and confirm which monitoring remains available.
- Pause non-web jobs and integration dispatchers before the read-only window, and confirm they are stopped.
- Model the load of inbound webhooks and other high-volume endpoints separately, and have a pause switch ready.
- Agree on a rollback trigger and time limit before the cutover. In this incident, rollback was considered but judged complex after the critical transition; the retrospective concluded that an earlier rollback would have been better.
- Avoid scheduling the cutover immediately before a high-traffic commercial period when support staff may be unavailable.
Worked example: how one endpoint re-degraded recovery
At about 10:15 to 10:30 UTC, index changes allowed the team to accept roughly 50% of traffic with elevated response times. Recovery continued until incoming Apple webhook requests arrived. Those requests included a query that was especially slow under the missing statistics, and overall recovery suffered again. Pausing Apple webhooks allowed faster recovery; offline receipt refreshers would eventually process those receipts. The lesson is that recovery is not only about the database plan: a single high-volume endpoint can reintroduce load that the partially recovered system cannot absorb. A pause switch and per-endpoint load visibility would have shortened this phase.
Action items and learnings
The incident review produced several follow-ups. Timing and communication: avoid critical commercial periods, improve how SDK reliability during backend outages is explained, and make maintenance and outage notifications easier to discover. Prevention: replay write traffic in dry runs, move costly processes to asynchronous paths, and re-evaluate unused indexes that can mislead the planner. Handling and escalation: surface per-endpoint database load in dashboards and involve support and customer communications during outages. The retrospective also noted that rollback at the beginning would have been a better choice. A closing acknowledgement to affected users, and thanks to the responders, is the kind of statement each operator should write in its own voice rather than inherit from another publisher's postmortem.
| Time (UTC) | Description |
|---|---|
| 2:51 UTC | New cluster is created via snapshot, replication of new changes from old cluster to new cluster begins. |
| 8:51 UTC | Replication has caught up and all prep work is done. |
| 9:34 UTC | Old cluster is switched to read only. Read-only requests still work, requests with writes are expected to fail for less than 2 minutes. |
| 9:34 UTC | Replication is fully caught up changes since switching to read-only within 10s. We are now ready to switch traffic to use the new cluster. Additionally, integration event dispatching, ETLs, and other non-web server jobs will be stopped until we switch over. |
| 9:34 UTC | Start of outage. Traffic begins to switch to the new cluster, writes begin to flow in the new cluster. |
| 9:39 UTC | 100% of traffic is going to the new cluster. Latency is not looking good. Infrastructure team begins to investigate. |
| 9:54 UTC | We are seeing statements take much longer than normal and using all db connections. We add statement timeout to the db user to ensure queries do not use a db connection for too long. |
| 9:58 UTC | Things are still not looking good. We begin to shed the majority of traffic via sending 503s from the load balancer to 90% of traffic, to leave the system enough headroom to recover. This also helps all systems recover and we can see db slowness as the source of issues. |
| ~10:00 to 10:30 UTC | After running EXPLAIN on some of the long-running queries, we realize the query plans are incredibly inefficient. Some indexes being used are doing more harm than good and aren’t needed. We begin to drop these indexes to hopefully alleviate issues. |
| ~10:15 to 10:30 UTC | Index tweaks are showing promise. We’re able to accept 50% of traffic with elevated response times. |
| ~10:30 to 13:07 UTC | End of outage (except for integration events). We’re able to gradually get back to 100% of traffic (with elevated latency). To do this, we have to temporarily stop accepting webhooks from Apple (our offline receipt refreshers will eventually get to these receipts). |
| ~12:00 to 13:07 UTC | After running through many query plans, it finally becomes apparent that ANALYZE did not run on the new cluster. ANALYZE is run on our biggest tables manually and database performance drastically improves. We’re now confident that we can bring everything back online. |
| 12:11 UTC | Separate Slack channel to field out support + social comms created. |
| 13:33 UTC | Everything else is restored (integration event dispatching, ETLs, and other non-web server jobs). |
| 14:51 UTC | Email sent out to all customers notifying them of the outage. |
Times are UTC on November 23, 2022; ~ marks estimates. This is a historical record, not current infrastructure guidance.
Frequently asked questions
Why did the Aurora Postgres 14 cluster perform worse than the 10.x cluster?
The new cluster had not run ANALYZE on its largest tables before cutover. Without up-to-date statistics, the query planner chose inefficient plans for high-throughput queries. The earlier test cluster had been running long enough to gather statistics, so the difference did not appear during preparation.
Did existing subscribers lose access during the outage?
Most did not, because entitlements are cached in the app and the cache is used when a request fails. Customers whose entitlement had already expired, for example because a billing cycle ended before the outage and they had not opened the app since, could lose access during the outage.
What happened to purchases made during the outage?
The store purchase succeeded, but entitlement unlocking was unreliable for about two hours. The SDK persisted the receipt and retried when the app was foregrounded. Reopening the app after the disruption reconciled the purchase without a special user action.
Why were some integration events replayed and others not?
Webhook events that were dispatched but not recorded were replayed out of caution. Other integrations were not replayed because doing so could duplicate side effects, such as sending a second Slack message for an old purchase. The source publisher offered case-by-case replay for specific events; whether the current publisher does is pending owner confirmation.
What would have caught the sequence-state problem earlier?
Replaying write traffic during dry runs and explicitly verifying sequence state after replication. Logical replication copies row values but not sequence state, so sequences must be advanced before writes resume. The team used a prepared script to raise the affected sequences once the problem appeared.