Incident Library

IL-0486 Figma · 2020-01-22databaseaws

Figma's PostgreSQL 9 database on RDS slowed under high write IOPS and lock contention until the site was unavailable

Figma runs PostgreSQL on Amazon RDS. On January 21 a long-running, expensive query raised database CPU until it was cancelled. On January 22 a routine automatic ANALYZE changed the statistics of a table used by a complex ORM-generated query. ANALYZE samples rows, and it had increasingly underestimated the selectivity of the innermost subquery; once the estimate for an inner HashAggregate crossed 200 rows, a deficiency in the PostgreSQL 9 query planner made the total estimate jump from 199 rows to over 20 million. The resulting plan used full table scans and wrote large amounts of data to temporary buffers, consistent with the high write IOPS and temp bytes metrics seen during the incident. At the same time, the backlog left by the query cancelled on January 21 had started aggressive anti-wraparound autovacuum, which added to the locking and writes.

The team cancelled inessential queries, raised IOPS, cancelled autovacuum statements, and at 3:00 PM restarted the database to apply a change to autovacuum_freeze_max_age that stopped aggressive autovacuum; that reboot caused part of the downtime between 2 PM and 3 PM. Because the database stayed unhealthy, the team took the service down at 7:00 PM for emergency maintenance and upgraded to PostgreSQL 11, which had been tested in staging for several months with a written upgrade plan. The site was back at 8:15 PM and all site and database metrics returned to normal.

Afterwards the team restored a snapshot from before the upgrade, reduced the query to nested sub-selects on the roles table, reproduced the bad estimate by running ANALYZE manually, and bisected PostgreSQL versions with the official Docker images: the test case from a PostgreSQL mailing list thread reproduced in 9.6.16 but not in 10.0. The 10.0 change "Use uniqueness guarantees to optimize certain join types" lets the planner use the primary key's uniqueness, and on the upgraded database EXPLAIN VERBOSE shows "Inner Unique: true" for the query. PostgreSQL 11 also handles autovacuum much better than version 9.

Severity
–
Scope
Figma web service: intermittent issues and failures to load Figma at several points during January 22, plus an emergency maintenance downtime from 7:00 PM to 8:15 PM PST
Users affected
some users
Region
–
Time to detect
15m
Time to resolve
10h 30m

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.

The pressure showed in write IOPS, write latency, lock contention and the temp bytes metric. One statement with complex subqueries, generated by Figma's ORM layer, kept appearing in the lock contention. Its plan estimated over 20 million rows when the real result was 3 rows, did full table scans instead of using an index and wrote large amounts of data to temporary buffers, and its execution time stretched into minutes.

The disruption came in waves. Cancelling several inessential queries and raising the allocated IOPS stabilised performance for a while, but at 2:00 PM the database degraded again and the site went down a second time. A restart at 3:00 PM brought relative stability, but write IOPS, write latency and lock contention stayed higher than normal until the evening upgrade.

The first theory was aggressive autovacuum. A long-running query cancelled the previous day had left a vacuuming backlog that crossed the threshold for the autovacuum mode that prevents transaction ID wraparound, which weighs more on locking and writes in the PostgreSQL version Figma ran. Cancelling autovacuum statements on the largest tables helped only briefly, because autovacuum resumed, and even after it was disabled the performance problems continued.

Symptom Lock contention or deadlockDatabase overloadService outage

Root cause

Root cause Software defect

Component DatabasePostgreSQL vacuum
Contributing factor Overlapping failuresHarmful manual action
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.

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

Action items

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.

Sources

Related cases

Check your cluster for this

kubegap audits a Kubernetes cluster for conditions like the ones in this case, Database, PostgreSQL vacuum among them, read-only, from Claude Code or Codex. Its checks cite public reports like this one.

Open kubegap

For AI tools and scripts

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.