A 30 ms migration took the orders table down for 17 minutes

01Symptom

You run schema migrations automatically as the first step of every deploy. Today's migration adds one nullable column to orders with no default. On staging it finishes in 30 ms. In production, starting at 14:02:11, every endpoint that touches orders times out, while endpoints on users or catalog stay healthy. By 14:02:40 the app's connection pool is saturated. CPU is around 4%, IOPS flat, replication lag zero. On-call rolls back the deploy at 14:06, and nothing changes.

02Constraints

• One PostgreSQL primary, about 2,400 requests per second, most of them touching orders
• Migrations run unattended as part of the deploy, with no lock_timeout or statement_timeout on the migration role
• The app pool has 200 connections with a 5-second wait before a request fails
• A read-only BI tool connects to the same primary and keeps sessions open
• Staging has no concurrent traffic

03Evidence

• →In pg_stat_activity, 192 of the 200 app connections are waiting on a lock on orders; the other 8 serve queries on other tables
• →One session, the migration's ALTER TABLE orders, is itself waiting on a lock — it is not running
• →One BI-role session is idle in transaction, idle for 52 minutes, its last statement a SELECT on orders
• →Orders endpoints error within a second of the migration starting, with pool-timeout errors, not SQL errors
• →Rolling back the application code had no effect; the migration session stayed where it was
• →The same migration ran in 30 ms against staging

→The question

Why does a metadata-only change take every query on the table with it, which session is the real problem, and what do you change so deploys can't do this?

04Your prediction