Cases3 cases

1 to 3 of 3

1. IL-0308 Most RevenueCat backend systems failed after cutover to a new Aurora PostgreSQL 14 cluster as queries became very slow

   RevenueCat2022

   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.

   Database overloadHigh latencyService outageConnection pool exhaustion

   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.

   Root cause

   Breaking change in an upgrade

   Component

   DatabasePostgreSQL vacuumDatabase replicationDatabase proxy and connection poolerQuery planner and table statistics

   Contributing factor

   Unrepresentative stagingUnverified assumptionAutomation amplificationMonitoring blind spot

   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.

   View case
2. IL-0456 Inflearn went down six times during a January 2022 free-course event as slow queries piled up on Aurora PostgreSQL

   Inflab2022

   Symptoms

   During a 100% discount event that made 125 courses free, Inflearn received 5 to 20 times its normal traffic, and on four days, 3, 4, 7 and 17 January 2022, the service became unusable around 9 a.m. and 10 p.m. There were six outages in all.

   Service outageDatabase overloadHigh latency

   Trigger

   A 100% discount event on 125 courses, from 3 to 17 January and 18 to 24 January 2022, went viral and brought 5 to 20 times the usual traffic.

   Root cause

   Capacity exhaustion

   Component

   DatabasePostgreSQL vacuum

   Contributing factor

   Inefficient queryMonitoring blind spotExcessive blast radius

   Detection

   The team followed the outages through hourly transaction counts, RDS metrics, monitoring and slow query alerts.

   Remediation

   • Added an index to the interested-companies query on the course page
   • Changed how often the cache data stored in the database was reset and regenerated
   • Moved the read queries that loaded the writer DB to the reader DBs
   • Scaled the database to 8 r5.8xlarge instances, which bought only a few tens of minutes
   • Rewrote slow course queries or added indexes so they use indexes, and switched off features whose queries could not be fixed
   • Removed the large course description column from the select * course queries
   • Split the IDs of the IN clause into batches of 20 run with Promise.all
   • Upgraded PostgreSQL from 10.16 to 11.13
   • Set a 5-second query timeout
   • Raised max connections
   • Ran VACUUM by hand on the course table
   • Moved the large course description column into a separate table

   Action items

   • Change the structure in which one table's failure spreads to the whole service
   • Improve the table and cache structures that are hard to tune
   • Make the SQL code easier to follow
   • Improve code that gives no confidence to change, such as code without types or test code
   • Build a stable batch environment
   • Keep a monolith of reasonable size and separate the domains through which failures spread most

   Lessons

   Because every database bottleneck appeared as slow queries on the reader DBs, watching only the readers hid the root cause on the writer. In PostgreSQL, frequent updates to a table with large columns produce large dead tuples in bulk and slow every query on that table. Before the second event the team could not run a meaningful performance test because the first week showed that almost any page could fail under load. The event exposed high sensitivity to failure in every area, one table's failure spreading to the whole service, hard-to-tune table and cache structures, unintuitive SQL code, code without types or tests, no stable batch environment, and many hidden failure points not yet found.

   View case
3. IL-0486 Figma's PostgreSQL 9 database on RDS slowed under high write IOPS and lock contention until the site was unavailable

   Figma2020

   Symptoms

   On January 22, 2020 at 9:45 AM PST, Figma's database metrics showed rising write IOPS and lock contention, while database CPU was lower than normal. An automated alert followed at 10:00 AM, and by 11:42 AM enough API requests had queued up behind the database that Figma became unavailable for some users.

   Lock contention or deadlockDatabase overloadService outage

   Trigger

   An automatic ANALYZE on one of the tables in the query finished at exactly the moment the incident began. The new statistics pushed the planner's row estimate for an inner subquery past 200 rows, and the plan for the whole query switched to an estimate of over 20 million rows.

   Root cause

   Software defect

   Component

   DatabasePostgreSQL vacuum

   Contributing factor

   Overlapping failuresHarmful manual action

   Detection

   Database metrics showed higher write IOPS and lock contention at 9:45 AM PST, and an automated alert at 10:00 AM started the investigation.

   Remediation

   • Cancelled several inessential queries and increased the allocated IOPS
   • Cancelled autovacuum statements running on the largest tables
   • Changed autovacuum_freeze_max_age and restarted the database to stop aggressive autovacuuming
   • Took the service down for emergency maintenance and upgraded the database to PostgreSQL 11

   Action items

   • Revisit transaction timeouts and limits so long-running and expensive queries are terminated
   • Improve metrics, monitoring and database settings for autovacuuming, locking and query performance
   • Audit queries for unnecessary or excessive joins and subqueries that could trip up the query planner
   • Bring the performance tools of the upgraded PostgreSQL into the incident response plans
   • Audit the structure and access patterns of data stored in PostgreSQL and consider moving some of it to datastores suited to those patterns
   • Improve in-app messaging about unexpected maintenance windows
   • Move towards online database upgrades that do not need downtime
   • Let users keep working on already open files safely while offline, even after closing the browser

   Lessons

   Having upgraded staging to PostgreSQL 11, tested it for months and written a production upgrade plan let the team upgrade under poor conditions with confidence. A routine statistics refresh can flip a query to a catastrophic plan, and the same query's plan can differ widely over time, so comparing plans from snapshots taken at different dates exposed the cause.

   View case

By root cause3