IL-0308 RevenueCat · 2022-11-23databaseaws
Most RevenueCat backend systems failed after cutover to a new Aurora PostgreSQL 14 cluster as queries became very slow
AWS planned to end support for Aurora PostgreSQL 10.x on January 31st, 2023, so RevenueCat had to upgrade its main database cluster. This time the team restored a snapshot on a new cluster, upgraded it to Aurora PostgreSQL 14.x, replicated changes from the production cluster into it, and planned to switch the old cluster to read-only and fail over once the last changes had replicated. The 44-step procedure was prepared over more than two months of tests and dry runs, which predicted less than 10 seconds of full and 1 to 2 minutes of partial downtime. On November 23, 2022 the new cluster was created from the snapshot at 2:51 UTC, replication had caught up by 8:51 UTC, the old cluster was switched to read-only at 9:34 UTC, and traffic moved to the new cluster between 9:34 and 9:39 UTC.
On the new cluster, high-throughput queries became extremely inefficient because ANALYZE had not run on the largest tables. In testing, the 14.x cluster had been up long enough for vacuum to finish and gather the statistics the query planner needs, but on migration day the vacuum of one of the largest tables had not finished before the cutover. Slow queries used all database connections and caused contention on the pg bouncers, and the failures spread through all systems. The team added a statement timeout to the database user at 9:54 UTC, shed 90% of traffic with 503s from the load balancer at 9:58 UTC, dropped indexes that were being used wrongly and were not needed, stopped accepting Apple webhooks, and rolled out a release without New Relic, whose agent was running EXPLAIN on the many slow queries. Between about 12:00 and 13:07 UTC the team found that ANALYZE had not run on the new cluster and ran it by hand on the biggest tables, and database performance improved drastically. Traffic was back to 100% at 13:07 UTC, and the remaining jobs were restored at 13:33 UTC.
Two other problems came with the switch. Replication had copied the table data but not the state of sequences, so primary key sequences on a handful of large tables produced values that already existed; a script prepared in advance raised the affected sequences. Integration event dispatching had not been stopped at the switch, so for a few minutes while the old cluster was read-only, events were dispatched but their status could not be written, until the dispatcher was stopped by hand; RevenueCat replayed the webhooks among them. Rollback was considered, but it meant reversing replication and moving traffic back while accepting no writes, and RevenueCat later judged that rolling back at the start would have been the better choice. For new purchases, entitlements did not unlock or needed several attempts during a 2-hour period.
- Severity
- –
- Scope
- The RevenueCat dashboard and the SDK endpoints failed or partly failed from 9:39 to about 13:07 UTC; entitlements for new purchases did not unlock or needed several attempts for about 2 hours; some integration events were dispatched without being recorded
- Users affected
- RevenueCat customers, and users of their apps who made new purchases or had expired entitlements
- Region
- –
- Time to detect
- 5m
- Time to resolve
- 3h 33m
Symptoms
At 9:34 UTC on November 23, 2022, RevenueCat started moving traffic from its Aurora PostgreSQL 10.x cluster to a new Aurora PostgreSQL 14.x cluster. By 9:39 UTC all traffic was on the new cluster, latency was bad, and the RevenueCat dashboard and the endpoints used by the RevenueCat SDK stopped working.
Statements took much longer than normal and used up all database connections, and the pg pool cluster looked totally saturated, with the slow queries causing contention on the pg bouncers waiting for a free connection. Running EXPLAIN on long-running queries showed very inefficient query plans: one query used the wrong index, and Postgres did not choose the obvious index even in very simple cases. A third-party APM agent runs EXPLAIN on slow queries, so nearly every query had a long-running EXPLAIN beside it. On a handful of large tables, primary key sequences produced values that violated unique constraints for rows already in the tables.
The service came back in steps. With 90% of traffic shed, the systems recovered enough to show that database slowness was the source. After index changes, about 50% of traffic could be served by 10:15 to 10:30 UTC, with high response times. Incoming Apple webhook requests ran a particularly slow query and set recovery back until RevenueCat stopped accepting them for a time. Traffic was back to 100% at 13:07 UTC, still with higher latency.
The team first blamed a cold cache on the new cluster, although it had warmed the critical tables by reading all records added in the last 24 hours. As the database warmed and spent less time on IO, attention moved to the saturated pg pool cluster, and it was not clear whether pg bouncer or the database was slow; telling cause from effect took about an hour. The wrong index could also have been a different choice by the PostgreSQL 14 query planner. Only between about 12:00 and 13:07 UTC did it become clear that ANALYZE had not run on the new cluster.
Symptom Database overloadHigh latencyService outageConnection pool exhaustion
Root cause
Root cause Breaking change in an upgrade
- Trigger
- At 9:34 UTC production traffic was switched from the Aurora PostgreSQL 10.x cluster to the new Aurora PostgreSQL 14.x cluster, before ANALYZE had run on its largest tables.
Detection
At 9:39 UTC, with all traffic on the new cluster, latency looked bad and the infrastructure team began to investigate.
Remediation
- Added a statement timeout to the database user so queries could not hold a connection too long
- Shed 90% of traffic by returning 503s from the load balancer
- Dropped indexes that the planner was using wrongly and that were not needed
- Stopped accepting Apple webhooks for a time
- Rolled out a release without New Relic to stop its EXPLAIN queries
- Ran ANALYZE by hand on the biggest tables
- Raised the values of the affected primary key sequences with a prepared script
- Stopped the integration event dispatcher by hand
- Replayed the webhooks that were dispatched but not recorded
- Restored integration event dispatching, ETLs and other non-web server jobs
Action items
- Consider change freezes during business-critical periods
- Explain better how the SDK behaves during backend outages
- Tell customers about maintenance and outages in the developer dashboard and by direct email to technical contacts
- Replay a subset of write traffic in migration dry runs to mimic the cutover
- Move costly processes to asynchronous work to keep the API fast and stable
- Re-evaluate table indexes, including unused ones the database may choose for queries
- Show the load of each endpoint and system in the database dashboards
- Involve support and customer communications staff during outages
Lessons
The new cluster had not been analyzed, and the tests missed it because the test cluster had been up long enough to gather statistics. Dry runs replayed no write traffic, on the belief that writes are simpler than reads, and the team assumed sequence state was replicated with the data. Rolling back at the start would have been better than fixing forward. The migration, timed to finish before Thanksgiving and Black Friday, should have been delayed until the week after, because the US had entered a sensitive period and part of the US support team was already on holiday.
Sources
Related cases
IL-0569After an MHA master failover, one of three MySQL replicas failed with a malformed binlog error and could not catch upDeNA · 2026same root cause · shared Database, Database replication
IL-0304Intercom's US region was fully down for 71 minutes when its Vitess VTGates dropped all routes to the database shardsIntercom · 2026same root cause · shared Database, Database proxy and connection pooler
IL-0316Railway's control plane slowed and deployments stopped after a PgBouncer version jump used abnormal CPU and memoryRailway · 2025same root cause · shared Database, Database proxy and connection pooler
IL-0498Clerk's Postgres database had load spikes every 15 minutes and requests failed and slowed intermittently for four daysClerk · 2025same root cause · shared Database, Database proxy and connection pooler
IL-0572About 10 million Pub/Sub messages backed up and Merpay accounting data was not written to Cloud Spanner for over a dayMercari · 2026same root cause · shared Database
IL-0444Reader connections to Repro's Aurora MySQL cluster were cut with Lost connection errors every time ALTER TABLE ranRepro · 2024same root cause · shared Database
IL-0117A scheduled maintenance and an ingress upgrade on OpenAI's user-facing clusters caused a networking control plane regression that briefly broke the data plane, and the services that restarted then overwhelmed a backend persistence store with a heavy first-start queryOpenAI · 2024same root cause · shared Database
IL-0314Daily's newly upgraded Aurora MySQL database deadlocked on meeting-event queries and its call servers kept restartingDaily · 2023same root cause · shared Database
IL-0294CircleCI Docker and machine jobs stalled for nearly 12 hours after a VM cleanup queue lost all its RabbitMQ consumersCircleCI · 2021same root cause · shared Database
IL-0372Heroku apps could not connect to about 1% of hobby-tier Postgres databases after the databases' host names changedHeroku · 2021same root cause · shared Database
Check your cluster for this
For AI tools and scripts
- This case as JSON: https://incidentlib-api.benchlet.com/cases/IL-0308
- The cases most like it: https://incidentlib-api.benchlet.com/cases/IL-0308/similar
- MCP server (Streamable HTTP, no key):
https://incidentlib-mcp.benchlet.com/mcp
The API needs no key. The MCP server offers search_incidents, get_incident, list_incidents, get_topic and vocab; both are explained on API and MCP.