A migration locked my table and the deploy is waitingSolved

Two questions decide it: is the migration making progress, and is it going to finish before the outage does?

Look at it. pg_stat_activity shows what every session is doing, and for the running migration you can see the query, the state and how long it has been running. pg_locks shows who is holding what and who is waiting.

If it is progressing, let it finish. Killing a migration that is halfway through a table rewrite leaves the table exactly as it was, plus the time already spent, plus a rollback to plan. Interrupting is the expensive option.

If it is not progressing, it is waiting for something, and the thing it is waiting for is almost always a long-running query that started before the migration and is holding a row lock. Find it in pg_stat_activity — look for idle in transaction and for long-running SELECTs — and deal with that one. Killing the migration while a blocking session holds the lock just moves the queue.

The immediate mitigation is to stop the application from queueing writes: put it in read-only mode if it has that, or scale the writers down. A queue that is not draining is a queue that will still be there after the migration finishes, and the timeouts you get in the meantime are the outage.

For next time, the three things that would have prevented this.

1. Set a lock_timeout on the migration, so it gives up rather than queueing behind a long transaction. A migration that fails fast and can be retried is much better than one that waits silently.
2. Do not add a column with a volatile default on a table that big. Postgres 11 and later can add a column with a non-volatile default without rewriting the table; a volatile default rewrites it, and the rewrite takes an exclusive lock for the whole duration. This is the single most common cause of exactly this situation.
3. Add the column without a default, then backfill it in batches — a few thousand rows at a time, each batch in its own transaction, with a sleep between them. No long lock, and the backfill is restartable.

That last pattern is the one to have in the toolbox. It is a few more lines than ALTER TABLE ADD COLUMN and it is the difference between a five-minute deploy and an outage.