Scenario: a migration locks the orders table Intermediate

The migration is a single ADD COLUMN line and ran in 4 ms on staging, yet production’s API went down right after the deploy. You diagnose, clear the lock queue, rerun it safely and review the next migration PR.

• The lock behavior in the game was reproduced on PostgreSQL 17: SELECTs are blocked by the waiting migration, not by the transaction holding the lock.
• If you pick wrong, read the explanation and Choose again; only the first choice counts toward the score.

Scenario

14:05 deploy: one ALTER TABLE takes down the API

You are the on-call backend dev. The orders table has 80 million rows, there are 6 API pods, each with a 40-connection pool. Every choice immediately shows its consequences on the metrics.

API 5xx errors0 %

API p99120 ms

Queries waiting for a lock on orders0

Connection pool busy25 %

Step 1/4 · The API hangs right after the deploy

14:05, the pipeline deploys a new version. The container runs npm run migrate && node server.js, and the migration is a single line. Two minutes later, every endpoint that reads orders times out. Database CPU is 8%, and no heavy query is running.

pool

240/240 connections busy

app error

timeout exceeded when trying to connect

DB CPU

8%

-- migrations/20261024_add_discount_code.sql
ALTER TABLE orders ADD COLUMN discount_code text;
-- staging: 4 ms

What do you do?

AOpen pg_stat_statements and find the query whose mean_exec_time rose the most, to optimize itBRun pg_stat_activity with pg_blocking_pids(pid) to build the tree of who blocks whomSELECT pid, application_name, state, now() - xact_start, pg_blocking_pids(pid) FROM pg_stat_activity WHERE wait_event_type = 'Lock' OR state = 'idle in transaction'CScale the API from 6 to 12 pods to get enough connectionsDLook at pg_locks WHERE NOT granted to see whether anything waits on a lock

To understand why a DDL that has not even started can block SELECTs, read Lock queue.