Целостность схемы: deferral, версионирование и сбои в продакшне

Команда запускает пакетную миграцию — 3 миллиона строк перепривязываются к новым родителям. На строке 2 847 221 срабатывает нарушение foreign key. Транзакция откатывается. Все три миллиона обновлений отменяются. Миграция запускается повторно в 3 ночи. То же нарушение. Никто не проверил наличие сиротских строк перед стартом.

Применение переходов состояний

SQL CHECK constraints вычисляются при каждой записи и должны быть детерминированы. Они могут обращаться только к другим колонкам той же строки, но не к другим строкам. Это значит, что CHECK на уровне колонки может проверить «статус должен быть одним из этих значений», но не «завершённый заказ не может вернуться в pending».

Для инвариантов переходов состояний правильные инструменты:

Row-level triggers. BEFORE UPDATE триггер, бросающий исключение при недопустимом переходе:

CREATE OR REPLACE FUNCTION enforce_order_transitions()
RETURNS TRIGGER AS $$
BEGIN
  IF OLD.status = 'completed' AND NEW.status != 'completed' THEN
    RAISE EXCEPTION 'order % cannot leave completed state', OLD.id;
  END IF;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_order_status
  BEFORE UPDATE ON orders
  FOR EACH ROW EXECUTE FUNCTION enforce_order_transitions();

Сгенерированные колонки с CHECK. Колонка is_final типа GENERATED ALWAYS AS (status IN ('completed','cancelled')) STORED плюс триггер или проверка на уровне приложения, запрещающая обновление строк, где OLD.is_final = true. Сгенерированная колонка документирует правило финальности прямо в схеме.

Row-level security. RLS-политики Postgres могут кодировать правила доступа, зависящие от состояния строки — например, USING (status != 'archived') на UPDATE запрещает обновление архивных строк без проверок на уровне приложения.

Дисциплина: бизнес-правила, которые можно выразить декларативно (в CHECK или RLS), живут в схеме. Правила, требующие процедурной логики (переходы состояний, cross-row проверки), живут в триггерах. Применение только на уровне приложения — наиболее слабый вариант: каждый потребитель должен знать правила.

Отложенные constraints

По умолчанию Postgres проверяет constraints после каждого отдельного выражения. Для некоторых сложных многотабличных операций промежуточные состояния нарушали бы constraint, хотя конечное состояние корректно.

-- Circular FK: A ссылается на B, B ссылается на A
-- Вставить A требует существования B; вставить B требует существования A
-- Ни то, ни другое нельзя вставить первым при IMMEDIATE constraints

ALTER TABLE a ALTER CONSTRAINT fk_a_to_b DEFERRABLE INITIALLY DEFERRED;
ALTER TABLE b ALTER CONSTRAINT fk_b_to_a DEFERRABLE INITIALLY DEFERRED;

BEGIN;
  INSERT INTO a (id, b_id) VALUES (1, 1); -- b_id=1 ещё не существует; deferred: OK
  INSERT INTO b (id, a_id) VALUES (1, 1); -- a_id=1 уже существует; deferred check: consistent
COMMIT; -- Обе FK-проверки выполняются здесь; обе проходят

DEFERRABLE INITIALLY DEFERRED означает отложенную проверку до COMMIT. DEFERRABLE INITIALLY IMMEDIATE объявляет constraint отложенным, но немедленным по умолчанию — можно переключить в рамках транзакции через SET CONSTRAINTS ... DEFERRED.

Используйте deferral экономно. Отложенные constraints проверяются при COMMIT. Нарушение откатывает всю транзакцию без промежуточного указания — в отличие от немедленного constraint, который проваливает конкретное выражение. Это усложняет отладку. Продакшн-гигиена: используйте DEFERRABLE только для FK с реальными циклическими или многошаговыми зависимостями, и только в транзакциях, где временное нарушение хорошо понято.

Версионирование схемы: expand-then-contract

Реляционная схема — это контракт между базой и каждым потребителем. Добавление nullable-колонки не нарушает контракт. Добавление NOT NULL-колонки без дефолта ломает каждый INSERT. Удаление колонки ломает каждого читателя. Переименование ломает обоих.

Зрелые команды управляют схемой как кодом — файлы миграций в системе контроля версий (Flyway, Liquibase, golang-migrate, Prisma Migrate, sqlx) — и следуют паттерну expand-then-contract для ломающих изменений:

1. Expand: добавить новую форму рядом со старой. Новая колонка nullable, никаких изменений в существующем коде.
2. Задеплоить код, пишущий в обе. Приложение пишет и в старую, и в новую колонку. Старая всё ещё читается.
3. Backfill новой колонки для существующих строк (пакетами, чтобы не держать долгие блокировки).
4. Задеплоить код, читающий из новой. Приложение читает из новой, откатывается на старую в переходный период.
5. Проверить паритет. Запрос сравнивает старые и новые значения; ноль расхождений перед продолжением.
6. Contract: удалить старую форму в последующей миграции.

Паттерн применяется к переименованиям колонок, изменениям типов и разбиению таблиц. Добавляет один цикл деплоя на каждое ломающее изменение; альтернатива — даунтайм или тихая потеря данных.

Пять типичных сбоев в продакшне

Эти сбои повторяются вне зависимости от тщательности проектирования схемы.

1. Неявное преобразование типов убивает индекс. Запрос WHERE user_id = '42' (строковый литерал вместо целого числа) вызывает неявный cast на каждой строке. Postgres не может использовать целочисленный B-tree индекс, потому что сравнение происходит между индексированным integer и text-значением. EXPLAIN ANALYZE показывает последовательный скан. Исправление: типизированные построители запросов или ORM, которые привязывают правильный тип параметра.

2. NULL-семантика в джойнах приводит к тихой потере строк. LEFT JOIN, где джойн-колонка содержит NULL, молча теряет эти строки в последующих inner JOIN. NULL != NULL (NULL не равен ничему, включая другой NULL). Паттерн зрелого разработчика: использовать IS DISTINCT FROM для null-безопасного сравнения и явный COALESCE для null-толерантной логики в WHERE.

3. Каскад на каскаде — неограниченные транзакции. DELETE на сильно-каскадированной корневой строке создаёт транзакцию на несколько миллионов строк, держащую блокировки минутами. Все записи в каскадированные таблицы блокируются. Исправление: soft-delete корневой строки (deleted_at = now()), затем пакетная очистка дочерних с явными DELETE-циклами по 10 000 строк.

4. Дрейф схемы через ad-hoc ALTER. Разработчик запускает ALTER TABLE напрямую в продакшне, минуя систему миграций. Инструмент миграций больше не соответствует реальности; будущие миграции загадочно ломаются или создают несогласованное состояние. Исправление: каждое изменение схемы проходит через систему миграций. CI применяет это правило.

5. JSONB-колонки, выросшие до реляционной формы. Колонка «metadata» типа JSONB накопила 30 типизированных полей за два года. Каждая новая фича добавляет IF metadata->>'feature_x' = .... Колонка теперь представляет schema-on-read без применения. Правильный шаг — извлечь колонки, но миграция дорогая и постоянно откладывается. Исправление: правило «promote-to-column»: как только поле появляется в WHERE, запланировать миграцию с извлечением колонки.