An index migration locked Open Collective's whole Transactions table and degraded the service for nearly an hour

Symptoms

At 09:20 UTC on 4 April 2023, seven minutes after a deployment was triggered, Open Collective's service degraded: a migration in the deployment had locked the entire Transactions table in the production database.

The migration re-created indexes on the Transactions table so that soft-deleted rows were excluded, to improve performance. It dropped the old index and then created the new one, with CONCURRENTLY. The numerous queries constantly hitting the Transactions table seem to have conflicted with the migration and the table locked, and every query that arrived between the drop and the creation ran without an index, which added heavily to the slowdown.

Killing the migration did not end the problem, because the table remained locked. The migration had been tested locally and had run successfully on staging.

Symptom Lock contention or deadlockDatabase overload

Root cause

Trigger

A deployment ran a migration that dropped indexes on the Transactions table and re-created them with CONCURRENTLY, excluding soft-deleted rows.

Detection

The degradation of service was noticed at 09:20 UTC, seven minutes after the deployment was triggered.

Remediation

• Killed the migration, though the table remained locked
• Put the frontend into maintenance mode to limit queries
• Closed the other connections to the database
• Cancelled the new index creation and restored the old index
• Restored the frontend and confirmed the issue was resolved

Action items

• Add a script that simulates production load locally, using a tool like vegeta
• Run this kind of migration during specific maintenance windows
• Create the new index first and drop the old one afterwards in future migrations
• Insist on a review from another engineer before merging such a pull request

Lessons

Dropping the old index before creating the new one was too optimistic under constant production queries, even with CONCURRENTLY, and an unreviewed pull request let it through. Tests locally and on staging did not show the problem because there were no tools to run migrations in stressed environments. Putting the frontend in maintenance mode limited the impact while showing users a clear message.

Sources

• https://github.com/opencollective/opencollective/blob/main/postmortem/2023-04-04-transactions-null-indexes.md · github

References

• https://github.com/opencollective/opencollective-api/pull/8774
• https://github.com/tsenart/vegeta

Related cases

• IL-0570 Dropping an unused MySQL table stalled queries on its busy parent table and crowdworks.jp was hard to reach for a minuteCrowdWorks · 2026

  same root cause · shared Database, Schema migration

• IL-0369 Railway's backend API went down when a column migration waited on locks and replicas could not get database connectionsRailway · 2025

  same root cause · shared Database, Schema migration

• IL-0554 A database migration used up Sentry US's Postgres connections, and the site and event ingestion were down for 22 minutesSentry · 2024

  same root cause · shared Database, Schema migration

• IL-0523 TANF-app production deployments were blocked for two months when a search-index migration ran out of database disk spaceU.S. Department of Health and Human Services · 2023

  same root cause · shared Database, Schema migration

• IL-0391 GoCardless API requests timed out for about 15 seconds while a Postgres migration waited for an AccessExclusive lockGoCardless · 2015

  same root cause · shared Database, Schema migration

• IL-0356 Every domain hosted on Railway returned HTTP 404 to new connections for five minutes because route lookups failedRailway · 2026

  same root cause · shared Database

• IL-0136 A deployment that cleared an internal service's caches at peak traffic sent 20 times the normal load to the Azure OpenAI metadata backend in Sweden Central, and health checks that depended on its database restarted instances in a loop for almost six hoursMicrosoft Azure · 2026

  same root cause · shared Database

• IL-0510 MIT Learn's RDS PostgreSQL ran out of storage after a release and learn.mit.edu returned 503s and 504s for hoursMIT Open Learning · 2026

  same root cause · shared Database

• IL-0380 A compaction change in InfluxDB Cloud us-east-1 dropped data from compacted files, and queries missed data for weeksInfluxData · 2026

  same root cause · shared Database

• IL-0547 Buildkite annotation queries timed out and drove database CPU up across shards after a migration removed an indexBuildkite · 2025

  same root cause · shared Database

• IL-0315 Railway's backend API went down when an index build locked a billion-row Postgres table and used up every connectionRailway · 2025

  same root cause · shared Database

• IL-0327 After a blue/green upgrade of CircleCI's workflows database every query hit disk, and workflow starts lagged or failedCircleCI · 2025

  same root cause · shared Database

• IL-0324 Three MediaWiki deployments lost Memcached after a PHP 8.1 rollout and overloaded the External Storage database clusterWikimedia Foundation · 2025

  same root cause · shared Database

• IL-0307 Supabase's Postgres connection pooler Supavisor 2.0 dropped connections for about 80% of us-east-1 tenantsSupabase · 2025

  same root cause · shared Database

• IL-0552 CircleCI's archival database ran out of storage, slowing workflow and job starts and the display of workflows in the UICircleCI · 2025

  same root cause · shared Database

• IL-0571 STORES reservation returned bursts of 504 errors when a smart-list query held a database process for over 20 minutesSTORES · 2024

  same root cause · shared Database

• IL-0293 Cloud Bigtable in us-central1 had higher latency and connection errors for about 32 hours from low-rate packet lossGoogle Cloud · 2023

  same root cause · shared Database

• IL-0567 Aurora MySQL behind Cookie Run: OvenBreak hit 100% CPU after a sales-ranking feature launch, and players could not playDevsisters · 2023

  same root cause · shared Database

• IL-0483 Replicate's website and API returned slow responses and HTTP 500s for two hours as PostgreSQL connections ran outReplicate · 2023

  same root cause · shared Database

• IL-0371 Half of new connections to Render Postgres databases with point-in-time recovery failed while their backup jobs ranRender · 2023

  same root cause · shared Database

• IL-0328 A field type change in the database of CircleCI's job distribution service left all jobs stuck in "not running"CircleCI · 2021

  same root cause · shared Database

• IL-0566 Hot ranges after a Cookie Run: Kingdom guild update drove CockroachDB nodes to 100% CPU, forcing emergency maintenanceDevsisters · 2021

  same 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-0524
• The cases most like it: https://incidentlib-api.benchlet.com/cases/IL-0524/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.