Как добавить NOT NULL без долгой блокировки

— Макс, можешь помочь? — в дверь кабинета заглянул встревоженный разработчик Кирилл. — После прошлого обновления продакшн лег на полчаса, а сейчас нужно добавить ещё одно NOT NULL поле в таблицу с 300 млн записей. Начальник сказал сначала проконсультироваться с тобой.

Макс отложил кружку с кофе и тяжело вздохнул:

— Кирилл, кто-то же тебе говорил, что ALTER TABLE на 300 млн строк — это как прыгать с парашютом без инструктажа?

Кирилл покраснел:

— Ну… мы думали, если ночью запустить, то никто и не заметит…

— Лады, давай задачу.

Кирилл отправил сообщение. Макс на секунду задумался, развернул монитор и начал рисовать схему:

1. Добавляем колонку без NOT NULL

ALTER TABLE transactions ADD COLUMN external_id text;

— Такая команда меняет только метаданные и не переписывает все 300 миллионов строк, — пояснил Макс. — Но короткая блокировка на изменение схемы всё равно нужна, поэтому сначала проверь lock_timeout и активность на таблице.

2. Заполняем данные ПАКЕТАМИ

Макс написал один шаг пакетной обработки:

WITH batch AS (
  SELECT id
  FROM transactions
  WHERE external_id IS NULL
  ORDER BY id
  LIMIT 50000
  FOR UPDATE SKIP LOCKED
)
UPDATE transactions AS t
SET external_id = generate_external_id(t.id)
FROM batch
WHERE t.id = batch.id
RETURNING t.id;

— Запускай этот запрос из клиента или джобы отдельными транзакциями, пока он не вернёт ноль строк. Размер пакета и паузу подберите по WAL, задержке реплики и нагрузке. Сам цикл лучше держать вне DO: тогда каждый пакет действительно фиксируется отдельно, а повторный запуск остаётся безопасным.

— И не забудь сначала изменить приложение: новые строки должны сразу получать external_id. Иначе ты будешь бесконечно догонять движущуюся границу.

3. Проверяем ограничение без долгого удержания блокировки

ALTER TABLE transactions
  ADD CONSTRAINT transactions_external_id_nn
  CHECK (external_id IS NOT NULL) NOT VALID;

ALTER TABLE transactions
  VALIDATE CONSTRAINT transactions_external_id_nn;

— NOT VALID быстро добавляет ограничение для новых изменений, а VALIDATE CONSTRAINT отдельно проверяет существующие строки с более мягкой блокировкой. После успешной проверки можно коротко зафиксировать настоящий NOT NULL:

SET lock_timeout = '5s';

ALTER TABLE transactions
  ALTER COLUMN external_id SET NOT NULL;

ALTER TABLE transactions
  DROP CONSTRAINT transactions_external_id_nn;

— В актуальных версиях PostgreSQL валидное CHECK позволяет не сканировать таблицу повторно при SET NOT NULL. Короткая ACCESS EXCLUSIVE-блокировка всё равно потребуется, поэтому финальный шаг лучше выполнить в контролируемое окно. Если generate_external_id() тяжёлая функция, значения можно заранее подготовить в отдельной таблице и обновлять через JOIN.

— Почему так заморочено?

Макс терпеливо объяснил Кириллу:

— Загибай пальцы. Нет одной гигантской транзакции, пакеты можно замедлить или остановить, прогресс сохраняется, а нагрузку на WAL и репликацию проще контролировать. И главное: каждый этап можно проверить до следующего изменения схемы.

Через два дня Кирилл зашел со стаканом кофе:

— Макс, всё прошло идеально! Спасибо!

Макс с улыбкой взял кофе:

— Поздравляю! Кофе действительно проще и дешевле, чем даунтайм :)