Чому ALTER TABLE може «повісити» базу, хоча сам виконується за мілісекунди?

У PostgreSQL кожна команда бере на таблицю блокування певного режиму. Режими конфліктують між собою за таблицею сумісності:

• SELECT - ACCESS SHARE (найслабший);
• INSERT/UPDATE/DELETE - ROW EXCLUSIVE;
• CREATE INDEX - SHARE (блокує запис);
• більшість ALTER TABLE, DROP, TRUNCATE, VACUUM FULL - ACCESS EXCLUSIVE (конфліктує з усім, навіть із SELECT).

Ланцюжок, що кладе прод:

1. Довгий запит (звіт, забута транзакція в idle in transaction) тримає ACCESS SHARE на таблиці.
2. Міграція виконує ALTER TABLE orders ADD COLUMN note text - їй потрібен ACCESS EXCLUSIVE, і вона стає в чергу за звітом.
3. Усі нові запити до orders, навіть прості SELECT, стають у чергу за ALTER TABLE, бо черга блокувань упорядкована.
4. Таблиця фактично недоступна, доки не завершиться звіт - хвилини чи години. Пул з'єднань застосунку вичерпується, сайт лягає.

Сама зміна після отримання блокування займає мілісекунди - проблема саме в очікуванні.

Захист:

SET lock_timeout = '3s';
ALTER TABLE orders ADD COLUMN note text;

Якщо блокування не отримано за 3 секунди, команда падає й звільняє чергу. Міграцію повторюють (автоматично з паузами чи вручну).

Ще правила:

• Перед міграцією перевіряти pg_stat_activity на довгі запити й idle in transaction.
• Налаштувати idle_in_transaction_session_timeout, щоб забуті транзакції не жили вічно.
• Робити кожну міграцію короткою: одна зміна схеми - одна транзакція, без масового оновлення даних в тій самій транзакції.

Докладніше в документації: Блокування таблиць