Таблица 500 ГБ, нужно добавить колонку с дефолтом. Как сделать без блокировки на часы?

Почему ADD COLUMN с DEFAULT опасен на большой таблице

До PostgreSQL 11: любой ADD COLUMN ... DEFAULT ... переписывал всю таблицу (table rewrite) с AccessExclusiveLock на всё время операции. На 500 ГБ это часы простоя.

С PostgreSQL 11+: если DEFAULT — константное неизменяемое значение (не NOW(), не gen_random_uuid()), PostgreSQL использует «fast default» — значение хранится в системном каталоге, а не записывается в каждую строку. AccessExclusiveLock нужен только на обновление метаданных (миллисекунды). Но volatile default всё ещё вызывает full table rewrite.

Сценарий 1 — константный DEFAULT (PostgreSQL 11+)

-- Безопасно: fast default, без rewrite таблицы
-- Всё равно используем lock_timeout как страховку
SET lock_timeout = '2s';
ALTER TABLE orders ADD COLUMN retry_count integer DEFAULT 0 NOT NULL;
-- Если таймаут истёк — retry через несколько секунд

Сценарий 2 — volatile DEFAULT или сложная логика (staged migration)

-- ШАГ 1: добавляем nullable колонку без default — мгновенно
SET lock_timeout = '2s';
ALTER TABLE orders ADD COLUMN external_id uuid;

-- ШАГ 2: batched backfill — коммитим маленькими порциями
-- Запускать как отдельный скрипт, не в одной транзакции!
DO $$
DECLARE
  batch_size  int := 10000;
  last_id     bigint := 0;
  max_id      bigint;
BEGIN
  SELECT max(id) INTO max_id FROM orders;
  WHILE last_id < max_id LOOP
    UPDATE orders
    SET external_id = gen_random_uuid()
    WHERE id > last_id AND id <= last_id + batch_size
      AND external_id IS NULL;
    last_id := last_id + batch_size;
    COMMIT;  -- освобождаем lock после каждого батча
    PERFORM pg_sleep(0.05);  -- throttle, чтобы не давить реплику
  END LOOP;
END;
$$;

-- ШАГ 3: выставить DEFAULT для новых строк
SET lock_timeout = '2s';
ALTER TABLE orders ALTER COLUMN external_id SET DEFAULT gen_random_uuid();

-- ШАГ 4: NOT NULL constraint через NOT VALID (не сканирует старые строки)
SET lock_timeout = '2s';
ALTER TABLE orders ADD CONSTRAINT orders_external_id_not_null
  CHECK (external_id IS NOT NULL) NOT VALID;

-- ШАГ 5: VALIDATE (ShareUpdateExclusiveLock — не блокирует DML)
ALTER TABLE orders VALIDATE CONSTRAINT orders_external_id_not_null;

-- ШАГ 6: после успешного validate можно заменить на настоящий NOT NULL
-- (только если уверены, что все строки заполнены)
SET lock_timeout = '2s';
ALTER TABLE orders ALTER COLUMN external_id SET NOT NULL;
ALTER TABLE orders DROP CONSTRAINT orders_external_id_not_null;

Rolling deploy совместимость

Старый код должен пережить наличие нового nullable столбца (просто игнорирует). Новый код должен корректно обрабатывать NULL в external_id до завершения backfill. Деплой нового кода — только после завершения всех шагов миграции.

Подводные камни

• Один огромный UPDATE вместо батчей — WAL storm. UPDATE 500 ГБ таблицы в одной транзакции создаст огромный WAL, реплики отстанут на часы, autovacuum не успеет за bloat. Всегда бейте на батчи с COMMIT между ними.
• Volatile DEFAULT всё ещё вызывает table rewrite. DEFAULT NOW(), DEFAULT gen_random_uuid(), DEFAULT nextval(...) — всё это volatile в PostgreSQL 11+ и вызывает полный rewrite. Только literal-константы безопасны.
• NOT NULL без NOT VALID берёт долгую блокировку. ALTER TABLE ... SET NOT NULL сканирует всю таблицу под AccessExclusiveLock. Используйте CHECK ... NOT VALID + VALIDATE CONSTRAINT.
• lock_timeout без retry ломает деплой. Если lock_timeout истёк, миграционный инструмент (Alembic, Flyway) может упасть. Добавьте retry-логику или выставляйте lock_timeout в начале скрипта миграции.
• Autovacuum не справляется с bloat от backfill. После батчевого UPDATE dead tuples накапливаются быстрее, чем autovacuum успевает их убирать. Запустите VACUUM ANALYZE orders после backfill.
• Replica lag и replikation slots. Батчевый backfill даже с pg_sleep может создать задержку на репликах. Если есть replication slot, WAL не будет удалён до обработки репликой — следите за pg_replication_slots.
• Несовместимость кода с NULL в период backfill. Если приложение ожидает NOT NULL но колонка ещё null — runtime errors. Проектируйте staged rollout: сначала код, понимающий NULL, затем миграция, затем код, требующий NOT NULL.
• Проверить тип default перед prod. Протестируйте на dev/staging с EXPLAIN или \d+ tablename — убедитесь, что pg_attribute.atthasdef и attgenerated выглядят как ожидается.

• Не учитывать оптимизацию PG 11+.
• Делать один большой backfill UPDATE.
• Добавлять NOT NULL без staged validation.

• Просит версионную оговорку.
• Проверяет lock_timeout и batching.
• Уточняет rolling deploy compatibility.