Почему ALTER TABLE ломает прод и как избежать блокировок

Введение: проблема ALTER TABLE в проде

Представь: ты добавляешь колонку в таблицу с миллионами строк, а через минуту продакшен встаёт. Запросы висят, клиенты сыплют ошибки, дежурный лезет в логи. Виновник - рядовой ALTER TABLE.

На тестовой базе всё проходило за долю секунды. Почему в бою иначе? Потому что на большой, активно используемой таблице изменение схемы превращается в тяжёлую операцию с эксклюзивной блокировкой. СУБД вынуждена либо перестроить всю таблицу, либо дождаться завершения чужих транзакций - и всё это под замком, который не пускает даже чтение.

Дальше разберём, как именно ALTER TABLE вызывает простой, и главное - как менять структуру без остановки сервиса.

Как ALTER TABLE вызывает блокировки в реляционных СУБД

Блокировки в PostgreSQL: ACCESS EXCLUSIVE и его последствия

PostgreSQL по умолчанию накладывает на таблицу блокировку ACCESS EXCLUSIVE, когда выполняется ALTER TABLE. Это самый жёсткий режим: он конфликтует со всеми остальными блокировками, включая те, что нужны для обычных SELECT и INSERT. Пока длится операция, никто не может ни читать, ни писать в таблицу.

При этом сама команда встаёт в очередь за уже активными транзакциями. Предположим, прямо перед ALTER кто‑то начал долгий отчёт. Тогда ALTER TABLE будет ждать его завершения, удерживая ACCESS EXCLUSIVE. Все новые запросы на эту же таблицу выстраиваются следом - и в итоге простой нарастает как снежный ком.

На небольших таблицах проблема не заметна: блокировка снимается за миллисекунды. На больших объёмах перестроение строк или индексов растягивается на минуты, а то и часы. Весь этот период база остаётся фактически недоступной.

Metadata locks в MySQL/InnoDB: почему финальная эксклюзивная блокировка неизбежна

MySQL (InnoDB) использует механизм metadata lock - блокировок на определение таблицы, а не на сами строки. Операция ALTER TABLE проходит в несколько фаз:

Инициализация: берётся разделяемый (shared) metadata lock, движок выбирает алгоритм.
Исполнение: основная работа (копирование таблицы, перестройка индексов). Здесь блокировка может временно становиться эксклюзивной, а потом возвращаться к разделяемой.
Фиксация (commit): для подмены старого определения таблицы новым обязательно берётся эксклюзивный metadata lock.

Проблема в том, что эксклюзивный lock в конце сталкивается с любыми незавершёнными транзакциями, удерживающими хотя бы shared-блокировку - например, долгим SELECT или забытым открытым соединением. ALTER встаёт в ожидание, но сам уже никого не пускает. В итоге сервис висит на ровном месте.

В документации MySQL предусмотрены подсказки LOCK: LOCK=NONE допускает конкурентные чтения и запись, LOCK=SHARED разрешает только чтение, LOCK=EXCLUSIVE блокирует всё. Но даже с NONE финальная фаза всё равно требует кратковременного эксклюзивного захвата - и если повезёт, ты его даже не заметишь. Если нет - прод встаёт.

Почему блокировка - это остановка прода

Механика очереди: как долгие транзакции умножают время простоя

Сама блокировка не так страшна, как ожидание её снятия. Когда ALTER TABLE захватывает ACCESS EXCLUSIVE (или ждёт эксклюзивный metadata lock), он блокирует всех, кто пришёл позже. При этом он обязан дождаться завершения всех, кто начал работу раньше.

Если в момент старта миграции висит десятиминутный аналитический запрос, то ALTER простоит минимум десять минут, удерживая эксклюзивную блокировку. За это время очередь из новых запросов вырастет на сотни или тысячи соединений. После окончания долгого чтения ALTER сам начнёт свою основную работу - перестройку. И всё это время доступ к таблице закрыт.

На практике простой может оказаться в несколько раз больше, чем длительность самой DDL-операции.

Особо опасные сценарии: столбец с DEFAULT, перестройка индексов

Не все ALTER TABLE одинаково опасны. Самые тяжёлые случаи - когда меняется физическая структура строк или индексов:

Добавление колонки с ненулевым значением по умолчанию. В старых версиях PostgreSQL это вызывает полную перезапись всех строк: каждой надо проставить новое значение. Таблица переписывается целиком, и эксклюзивная блокировка держится всё это время. В современных версиях проблему частично решили, но риск остаётся.
Изменение типа столбца (например, с INT на BIGINT) - почти всегда перестройка всей таблицы.
Создание или перестроение индекса без указания CONCURRENTLY (в PostgreSQL) - блокирует запись до завершения.
Любая операция, заставляющая MySQL перестраивать кластерный индекс InnoDB, практически равносильна полному копированию таблицы под metadata lock.

Именно такие изменения чаще всего приводят к авариям. Они требуют не доли секунды, а минут - и всё это время продакшен недоступен.

Как избежать простоя: безопасные альтернативы

Использование онлайн DDL: что могут современные версии

И PostgreSQL, и MySQL научились проводить часть изменений без длительной эксклюзивной блокировки. Такие операции называют онлайн DDL.

В PostgreSQL многие виды ALTER TABLE можно выполнить без ACCESS EXCLUSIVE - но только если движок способен обойтись без полной перезаписи данных. Например, добавление колонки с NULL-значением по умолчанию часто отрабатывает почти мгновенно. А вот изменение типа столбца всё равно потребует жёсткого захвата.

MySQL даёт больше контроля через явное указание алгоритма и уровня блокировки: ALGORITHM=INPLACE, LOCK=NONE. Если операция допускает INPLACE и NONE, изменения применяются на лету, без копирования таблицы, а финальный эксклюзивный захват длится миллисекунды. Но список поддерживаемых операций ограничен: для серьёзной перестройки (например, изменение типа первичного ключа) всё равно потребуется эксклюзивный lock или полное копирование.

Проверить, пойдёт ли конкретный ALTER по онлайн-пути, всегда необходимо на тестовой копии, максимально приближенной к продакшену.

Инструменты с минимальным временем блокировки: pt-online-schema-change, gh-ost, pg_repack

Когда встроенных возможностей не хватает, на помощь приходят внешние утилиты. Они выполняют миграцию незаметно для клиентов, сводя время эксклюзивной блокировки к минимуму - буквально к моменту переключения на новую структуру.

pt-online-schema-change (Percona Toolkit для MySQL) создаёт пустую копию таблицы с нужной структурой. Затем копирует данные и вешает триггеры, чтобы фиксировать изменения в оригинале. В финале на долю секунды блокирует оригинал и атомарно подменяет таблицы. Клиенты почти не замечают переключения.
gh-ost (GitHub) действует похоже, но вместо триггеров читает бинарный лог. Это снижает нагрузку на исходную таблицу и позволяет в любой момент поставить миграцию на паузу или откатить.
pg_repack (PostgreSQL) умеет перестраивать таблицы и индексы без эксклюзивной блокировки на основное время работы. Он также создаёт новую структуру, заполняет данными и на короткий момент применяет финальную блокировку для синхронизации.

Главное преимущество этих инструментов - предсказуемое, очень короткое время захвата, которое можно заранее обкатать на проде.

Стратегия «ручного» обхода: фоновое копирование через триггеры или промежуточную таблицу

Если нет возможности поставить внешние утилиты, работает классический подход с ручным копированием.

Создаёшь новую таблицу с целевой структурой.
Начинаешь фоновое копирование существующих данных порциями (например, скриптом, который переносит по 10 000 строк с паузами, чтобы не перегружать БД).
На исходную таблицу вешаешь триггеры, которые дублируют вставки, обновления и удаления в новую таблицу.
После переноса всех старых данных дожидаешься, пока триггеры нагонят оставшиеся изменения.
Короткая блокировка: удаляешь старую таблицу и переименовываешь новую.

Метод трудоёмкий, но даёт полный контроль над нагрузкой и временем блокировки. Используется, когда онлайн DDL не поддерживается для конкретной операции, а внешние инструменты неприменимы из‑за особенностей окружения.

Превентивные меры: мониторинг долгих транзакций, таймауты и лимиты блокировок

Любой безопасный ALTER сводит к нулю риск только при здоровой дисциплине эксплуатации.

Мониторинг транзакций-долгожителей. В PostgreSQL запросы, висящие дольше разумного порога, видны через pg_stat_activity. В MySQL - SHOW PROCESSLIST или information_schema.innodb_trx. Спящие коннекты, забытые открытые транзакции - главные враги любой миграции.
Агрессивные таймауты. Выставление idle_in_transaction_session_timeout (PostgreSQL) или wait_timeout (MySQL) убивает соединения, которые удерживают блокировки без дела.
Лимит времени на блокировку. В PostgreSQL можно задать lock_timeout, чтобы ALTER TABLE не стоял вечно в очереди за другими транзакциями, а падал с ошибкой.
Предварительная проверка. Перед любым ALTER стоит убедиться, что на таблице нет длительных несовершённых операций, и принудительно их завершить, если это безопасно.

Практический чеклист перед ALTER на большой таблице

Вот минимальный список шагов, который убережёт от ночных дежурств и гневных сообщений:

Оцени размер таблицы и время на тестовой среде, максимально похожей на прод - и умножь на коэффициент 2.
Убедись, что операция поддерживается онлайн в твоей версии СУБД. Если нет - бери pt-osc, gh-ost или pg_repack.
Проверь отсутствие долгих транзакций и спящих коннектов; прибей их, если они не критичны.
Выстави lock_timeout, чтобы ALTER не висел вечно, а падал с ошибкой.
Проведи тестовый прогон на staging-окружении с реальным трафиком или хотя бы с эмуляцией конкурентных запросов.
Согласуй окно минимальной нагрузки - даже с онлайн DDL лучше не экспериментировать в пиковые часы.
Подготовь план отката: если миграция идёт через внешний инструмент, убедись, что умеешь прервать операцию без потери данных.

Заключение: когда без простоя всё равно не обойтись

Некоторые изменения схемы принципиально нельзя сделать полностью онлайн. Смена типа данных колонки, добавление внешнего ключа громадной таблице, радикальная перестройка кластерного индекса - всё это рано или поздно потребует эксклюзивной блокировки, как ни выкручивайся. В таких случаях речь идёт не о том, чтобы избежать простоя, а о том, чтобы он был предсказуемо коротким и произошёл в наименее критичное время.

Главный враг не блокировка как таковая, а неизвестность. Когда ты понимаешь, сколько именно продлится захват, и заранее готовишь окружение, ALTER TABLE превращается из аварии в рутинную операцию.

FAQ

Почему ALTER TABLE на большой таблице может занять часы?
Если операция требует полной перезаписи строк (например, изменение типа столбца или добавление колонки со значением по умолчанию), СУБД физически перестраивает все данные. На больших объёмах это длительная задача, которая к тому же удерживает жёсткую блокировку и вынуждена ждать завершения всех предыдущих транзакций.

Какие операции ALTER TABLE самые опасные для продакшена?
Изменение типа колонки, добавление ненулевого DEFAULT, перестроение индексов без CONCURRENTLY, изменение первичного ключа - любое действие, вынуждающее СУБД заново записать всю таблицу или надолго захватить эксклюзивную блокировку.

Можно ли изменить структуру таблицы, не останавливая запись?
Да. Современные версии MySQL и PostgreSQL поддерживают онлайн DDL для многих операций. Когда встроенных возможностей не хватает, используют pt-online-schema-change, gh-ost или pg_repack. Они фактически не блокируют запись на время основной работы, а финальный захват длится доли секунды.

Как понять, что ALTER TABLE вот-вот положит прод, и остановить его?
Если запрос уже запущен и держит эксклюзивную блокировку, а очередь растёт - можно принудительно прервать сам ALTER (CANCEL в PostgreSQL, KILL в MySQL). Чтобы не доводить до этого, заранее ставьте lock_timeout; тогда команда упадёт сама при превышении лимита ожидания.

Зачем нужны pt-online-schema-change и gh-ost, если есть встроенный онлайн DDL?
Встроенный онлайн DDL покрывает не все сценарии. Для операций, требующих перестройки таблицы или индексов с долгой эксклюзивной блокировкой, внешние инструменты умеют выполнить миграцию практически без простоя, с контролируемой нагрузкой и возможностью паузы.

Что делать с таблицами-гигантами, если даже онлайн DDL требует много ресурсов?
Вместо одного долгого ALTER можно разбить операцию: сначала скопировать данные порциями в новую таблицу с триггерами, затем переключиться. Инструменты вроде gh-ost позволяют регулировать нагрузку, ставить на паузу и проводить миграцию постепенно, даже если таблица весит сотни гигабайт.

Есть ли ситуации, когда простоя не избежать?
Да. Радикальные изменения схемы (смена типа данных в ключевом поле, добавление внешнего ключа с проверкой, изменение кластерного индекса) почти всегда требуют кратковременной эксклюзивной блокировки. В таких случаях задача - сделать её максимально короткой и спланированной, а не пытаться избежать полностью.

Почему ALTER TABLE на большой таблице ломает прод и как этого избежать