Колонка с DEFAULT на живой базе: почему это опаснее, чем кажется

MAATRIX

Задача выглядит на одну строчку: добавить в таблицу колонку status, is_active или created_at со значением по умолчанию, чтобы у старых записей сразу было что-то осмысленное вместо NULL. Миграция размером в один ALTER TABLE, ревью проходит за минуту, тикет закрывается за вечер. А потом эта миграция на проде подвешивает таблицу на несколько минут, приложение сыплет таймаутами, и разбор инцидента начинается с вопроса «а что вообще могло пойти не так в такой простой команде».

Почему добавление колонки — это не только про метаданные

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

Как только вы добавляете DEFAULT 0, DEFAULT false или DEFAULT now(), требование меняется принципиально: у каждой существующей строки таблицы должно появиться реальное, физически записанное значение в этой колонке. Исторически многие СУБД решали это буквально — проходили по таблице целиком и переписывали каждую строку, вставляя туда значение по умолчанию. Для таблицы на пару тысяч строк это доли секунды. Для таблицы на десятки и сотни миллионов строк это уже полноценная фоновая операция, сравнимая по цене с полным rewrite таблицы при обычном ALTER TABLE (смене типа колонки, например).

Проблема не только во времени самой перезаписи. Проблема в том, какую блокировку СУБД держит, пока эта перезапись идёт. В PostgreSQL и MySQL операция такого рода долго требовала эксклюзивной блокировки на уровне таблицы — то есть пока идёт переписывание строк, никто не может ни читать, ни писать в эту таблицу без ожидания. Если таблица активно используется (а если вы вообще думаете о риске для «живой базы» — она используется), то на это время встаёт весь код, который к ней обращается: не только сама эта транзакция, но и все остальные запросы, вставшие в очередь позади неё.

Что изменилось в современных версиях СУБД — и где это всё ещё не спасает

За последние годы разработчики популярных СУБД действительно закрыли часть этой проблемы. Общая идея оптимизации простая: если значение по умолчанию — это константа (число, булево значение, фиксированная строка), СУБД может не переписывать существующие строки физически, а просто запомнить в метаданных таблицы: «для строк, у которых физически нет значения в этой колонке, считать значение равным вот этому». Дальше это работает прозрачно при чтении, а реальная перезапись откладывается на потом (например, на следующий обычный VACUUM FULL, OPTIMIZE TABLE или естественную перезапись строки при обновлении).

Это меняет всё для типового кейса: ADD COLUMN is_active boolean DEFAULT true на таблицу в сто миллионов строк с такой оптимизацией может отработать за миллисекунды вместо минут. Но у оптимизации есть строгие границы, и именно из-за них колонка с DEFAULT продолжает быть опасной операцией, а не безопасной по умолчанию:

• Оптимизация обычно работает только для constant-выражений. Если значение по умолчанию вычисляется функцией — DEFAULT now(), DEFAULT random(), DEFAULT gen_random_uuid(), — оно не одинаковое для всех строк по определению, и значит физическую перезапись каждой строки всё равно нужно делать. Такие DEFAULT называют volatile (изменчивыми), и именно они чаще всего проскакивают в реальных миграциях: разработчику нужна не просто заглушка, а осмысленная метка времени или уникальный идентификатор для старых записей.
• Есть зависимость от типа данных и способа хранения. Некоторые типы (например, требующие отдельного TOAST-хранения больших значений в PostgreSQL, или определённые варианты ENUM/генерируемых колонок) исторически не попадали под быстрый путь и требовали полной перезаписи независимо от того, константа там или нет.
• NOT NULL добавляет отдельную проверку. Если вместе с DEFAULT вы сразу ставите NOT NULL, СУБД в некоторых случаях всё равно должна пройти по таблице, чтобы убедиться, что ограничение соблюдается для всех строк — это отдельная операция от простановки самого значения, и она тоже может требовать блокирующего прохода.
• Оптимизация появлялась не одновременно и не одинаково во всех СУБД и движках. PostgreSQL и MySQL/InnoDB подошли к этому по-разному и в разное время, у MariaDB — свои нюансы, у managed-версий баз (RDS, Cloud SQL и подобных) иногда свои ограничения поверх стандартного движка. Что оптимизировано в одной мажорной версии, может быть не оптимизировано в предыдущей — а на проде люди годами сидят не на последней версии.

Вывод из этого раздела простой и неприятный: сам факт «моя СУБД вроде бы умеет быстрый ADD COLUMN» ничего не гарантирует для конкретной миграции, пока вы не проверили, что ваш DEFAULT — действительно константа, тип колонки поддерживается, и NOT NULL не сводит оптимизацию на нет.

Какие условия решают, попадёте вы в блокировку или нет

Чтобы быстро прикинуть риск конкретной миграции, полезно пройтись по короткому чек-листу условий. Чем больше пунктов справа — тем ближе вы к полной перезаписи таблицы:

Отдельно про размер таблицы: даже там, где перезапись неизбежна, время блокировки не растёт линейно только от числа строк — на него влияет ширина строки, число индексов (каждый нужно поддерживать в консистентном состоянии при перезаписи), внешние ключи и то, насколько диск и буферный кэш успевают за операцией. «У нас таблица не такая уж большая, всего пара десятков миллионов строк» — плохой аргумент для пропуска проверки: где именно проходит граница «безопасно» и «уже больно», зависит от конкретного железа и конкретной таблицы, и без измерения на сравнимом объёме эту границу не угадать.

Что видно с той стороны, когда таблица встала

Если миграция всё же попала в блокирующий сценарий, картина обычно разворачивается так. Сама команда ALTER TABLE ... ADD COLUMN ... DEFAULT ... берёт блокировку, несовместимую с обычным чтением и записью в эту таблицу. Пока она держится:

• Новые запросы к таблице (включая простой SELECT по первичному ключу) встают в очередь ожидания блокировки — они физически не выполняются, пока миграция не отпустит захваченный ресурс.
• Если у вас есть пул соединений с ограниченным размером и таймаутами (что почти всегда так в проде), эти зависшие запросы начинают съедать все свободные соединения. Приложение быстро исчерпывает пул, и даже запросы к совершенно другим таблицам начинают падать по таймауту — не потому что они заблокированы напрямую, а потому что в пуле физически не осталось свободных соединений.
• Если сама DDL-транзакция стартовала не мгновенно (например, ждала своей очереди за длинной read-транзакцией, которая уже держит блокировку на этой таблице), то к моменту, когда она наконец возьмёт эксклюзивную блокировку, за ней уже выстроилась очередь из десятков обычных запросов — и все они будут ждать столько же, сколько длится перезапись.
• Мониторинг в этот момент обычно показывает рост числа активных подключений, рост latency у эндпоинтов, использующих эту таблицу, и характерный «провал» в графике успешных запросов — иногда без явной ошибки конкретно от СУБД, потому что снаружи это выглядит просто как всё резко замедлилось.

Разбор подобного инцидента — с конкретными цифрами по времени блокировки и вскрытием того, что именно пошло не так, — есть в статье «Миграция добавила одну колонку и заблокировала таблицу на 11 минут»: там на реальном кейсе видно, как быстрая на первый взгляд DDL встала в очередь блокировок и утащила за собой обычные запросы приложения.

Как проверить заранее, прежде чем катить миграцию на прод

Единственный надёжный способ не гадать — измерить время выполнения на данных сравнимого объёма до того, как команда попадёт на прод. Практический порядок действий:

1. Возьмите копию прод-таблицы (или хотя бы её объём) на staging. Не выборку в тысячу строк «для теста», а данные, сопоставимые по количеству строк, ширине строки и числу индексов с тем, что реально лежит в проде. Логическая структура без объёма ничего не скажет о времени блокировки.
2. Прогоните ровно ту же команду, которую собираетесь катить, с теми же опциями, в отдельной транзакции, замеряя время выполнения от начала DDL до коммита:

   \timing on
   BEGIN;
   ALTER TABLE orders ADD COLUMN priority integer DEFAULT 0;
   COMMIT;

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

1. Проверьте план и активность во время выполнения. В PostgreSQL полезно параллельно смотреть pg_stat_activity и pg_locks, чтобы увидеть тип блокировки и то, ждут ли её другие сессии. В MySQL — SHOW PROCESSLIST и performance_schema.data_locks (или INFORMATION_SCHEMA.INNODB_LOCKS в более старых версиях).
2. Явно уточните поведение именно вашей версии и именно вашего типа DEFAULT в документации СУБД, а не полагайтесь на общие статьи в интернете (включая эту) — оптимизация ADD COLUMN с DEFAULT менялась от версии к версии, и то, что верно для одной мажорной версии, не обязано быть верным для соседней.
3. Задайте lock_timeout (PostgreSQL) или аналогичный таймаут для DDL-сессии. Это не ускорит саму операцию, но не даст ей зависнуть намертво в ожидании блокировки на фоне конкурентной активности — вы получите управляемую ошибку вместо часового зависания.

   SET lock_timeout = '5s';
   ALTER TABLE orders ADD COLUMN priority integer DEFAULT 0;

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

Как снизить риск, если полной перезаписи не избежать

Если по итогам проверки видно, что операция всё-таки требует переписать таблицу (volatile default, неподходящий тип, обязательный NOT NULL сразу), обычно есть способ разбить одну рискованную DDL-команду на несколько безопасных шагов:

• Добавьте колонку без DEFAULT и без NOT NULL. Это почти всегда быстрая операция — только метаданные.

  ALTER TABLE orders ADD COLUMN priority integer;

• Заполните значения отдельными пакетами (batches), а не одним UPDATE на всю таблицу. Один большой UPDATE orders SET priority = 0 WHERE priority IS NULL держит блокировку не хуже прямого DDL с DEFAULT — вся идея разбиения теряется, если сделать это одной транзакцией. Разбивайте на пачки по первичному ключу с паузами между ними, чтобы дать другим запросам возможность выполниться:

  UPDATE orders SET priority = 0
  WHERE id BETWEEN 1 AND 100000 AND priority IS NULL;
  -- следующая пачка после паузы, и так далее

• Отдельным шагом установите DEFAULT для новых строк, если он вам нужен и дальше:

  ALTER TABLE orders ALTER COLUMN priority SET DEFAULT 0;

• NOT NULL добавляйте последним шагом, после того как убедились, что старых NULL не осталось — и там, где СУБД это умеет, используйте более лёгкую форму проверки ограничения вместо полного блокирующего сканирования (в части версий PostgreSQL это NOT NULL через предварительно добавленный CHECK constraint с NOT VALID, который затем валидируется без долгой эксклюзивной блокировки — уточняйте точный синтаксис и доступность для вашей версии).
• Для по-настоящему больших и горячих таблиц рассматривайте специализированные инструменты бесблокировочных миграций (pt-online-schema-change, gh-ost в мире MySQL) — они создают теневую копию таблицы, копируют данные пакетами и переключают её атомарно, но и у них есть свои ограничения и накладные расходы, которые стоит изучить отдельно, а не считать серебряной пулей.
• Планируйте окно с минимальной нагрузкой, даже если по расчётам операция должна быть быстрой — расчёт на staging не учитывает конкурентные блокировки от реального трафика в момент выката.

Дисциплину вокруг того, кто и как принимает решение катить рискованную DDL на боевую базу, стоит закрепить формально, а не полагаться на память отдельного разработчика — об этом статья «Регламент работы с боевой базой: кто, когда и с чьего разрешения». А если миграций в проекте много и они меняются от релиза к релизу, версионированный подход через инструменты вроде Flyway или Liquibase снимает часть риска ещё на этапе код-ревью — этому посвящена статья «Миграции базы данных: Flyway и Liquibase».

Частые вопросы