Безопасные DDL-паттерны: NOT VALID, CONCURRENTLY и исправления небезопасных операций

Команда запускает CREATE INDEX ON orders(user_id) в 10:00. На таблице с 200 млн строк это удерживает ACCESS EXCLUSIVE 20 минут — чтения и записи заблокированы. Тот же индекс, построенный через CREATE INDEX CONCURRENTLY, занимает 45 минут, но ничего не блокирует. Один и тот же индекс, одни данные, кардинально разный эффект на продакшн.

CREATE INDEX CONCURRENTLY

Обычный CREATE INDEX удерживает ACCESS EXCLUSIVE всё время построения. На больших таблицах это блокирует чтения и записи на минуты.

CREATE INDEX CONCURRENTLY строит индекс в два этапа, оба под SHARE UPDATE EXCLUSIVE (не блокирует DML):

1. Этап 1: сканирует таблицу и строит черновой индекс, пока продолжается обычный трафик.
2. Этап 2: нагоняет записи, произошедшие во время этапа 1; помечает индекс валидным.

Компромиссы:

• Занимает в 2–3 раза дольше, чем не-конкурентный.
• Не может выполняться внутри блока транзакции — если инструмент миграции оборачивает миграции в BEGIN..COMMIT, нужно пометить эту миграцию как нетранзакционную.
• Может дать сбой в середине построения и оставить INVALID-индекс (виден в pg_indexes где indisvalid = false).

Продакшн-инструкция для конкурентного индекса:

1. Всегда используйте CREATE INDEX CONCURRENTLY в продакшне.
2. После миграции проверьте: SELECT indexname, indisvalid FROM pg_indexes JOIN pg_class ... WHERE indisvalid = false — должно вернуть ничего.
3. Если найден INVALID-индекс: DROP INDEX CONCURRENTLY (тоже неблокирующий), затем повторите.

ADD CONSTRAINT NOT VALID + VALIDATE CONSTRAINT

Обычное добавление CHECK или FOREIGN KEY constraint получает ACCESS EXCLUSIVE и сканирует всю таблицу.

Двухшаговый паттерн:

-- Шаг 1: добавить constraint, применять только для НОВЫХ строк (мгновенно, краткий ACCESS EXCLUSIVE)
ALTER TABLE orders
  ADD CONSTRAINT orders_status_valid
  CHECK (status IN ('pending', 'active', 'closed')) NOT VALID;

-- Шаг 2: проверить существующие строки (SHARE UPDATE EXCLUSIVE — не блокирует DML)
ALTER TABLE orders VALIDATE CONSTRAINT orders_status_valid;

NOT VALID означает: применять для новых строк с этого момента; существующие строки не проверяются. VALIDATE CONSTRAINT затем сканирует все строки под SHARE UPDATE EXCLUSIVE, что не блокирует INSERT, UPDATE или DELETE. Сканирование может выполняться при живом трафике.

Этот паттерн применяется к:

• CHECK-ограничениям
• FOREIGN KEY-ограничениям: ADD CONSTRAINT ... FOREIGN KEY ... NOT VALID + VALIDATE CONSTRAINT
• NOT NULL через CHECK (col IS NOT NULL) NOT VALID (см. урок 02)

Небезопасные операции и их безопасные эквиваленты

ALTER COLUMN TYPE: исключение для бинарно-совместимых типов

Большинство изменений типа перезаписывают таблицу. Исключения — бинарно-совместимые типы с одинаковым физическим представлением:

• varchar(50) → varchar(100) (шире, те же байты)
• varchar → text (эквивалентное хранение)
• text → varchar (то же)

Postgres обнаруживает их и пропускает перезапись — краткий ACCESS EXCLUSIVE, строки не пишутся. Не-совместимые изменения (int → bigint, text → int, json → jsonb) требуют либо полной перезаписи, либо паттерна expand-contract через теневую колонку (урок 05).