I've seen a two-second migration freeze a checkout page because a plain CREATE INDEX held a lock on a table with a few million rows. Staging never showed it, because staging had a few hundred rows.
What was yours? Curious which statements bit people most: CREATE INDEX, SET NOT NULL, adding a column with a volatile default, something with foreign keys?
I wrote up the three I see most often with the safe versions, if useful: https://zelsis.com/guides/postgres-migration-locks
Top comments (1)
The CREATE INDEX without CONCURRENTLY is the classic trap. Staging with a few thousand rows takes 50ms, so everyone thinks it's fine, and then it takes 20 minutes of table-locking on prod.
The one that bit me the hardest was ALTER TABLE ... ADD COLUMN ... DEFAULT now() before Postgres 11. It rewrote the entire multi-million row table, locking it for what felt like an eternity. (Thankfully, since PG 11, non-volatile defaults are metadata-only and instant).
Also, SET NOT NULL is sneaky. It does a full table scan to verify the constraint, which blocks writes if you just run it directly. Using the NOT VALID constraint first, then VALIDATE CONSTRAINT in a separate transaction, is the only safe way to do it without downtime.