Внешний ключ в большой таблице заказов PostgreSQL: обычное добавление или NOT VALID

Допустим, в базе интернет-магазина есть таблица orders, а в ней колонка customer_id, которая по замыслу ссылается на customers. Замысел жил в головах разработчиков и в комментариях к коду, но не в схеме. Теперь связь нужно закрепить ограничением. Таблица за годы разрослась, а заказы продолжают поступать. Сама команда выглядит безобидно, одна строка. Трудность в том, что за этой строкой скрываются две разные работы: поставить на страже правило для будущих записей и доказать, что всё накопленное этому правилу соответствует. Их можно делать вместе, а можно по отдельности. Именно здесь решается, насколько заметной окажется операция для живой системы.

Что делает обычное добавление ключа на заполненной таблице

Классический вариант выглядит так:

ALTER TABLE orders
  ADD CONSTRAINT orders_customer_fk
  FOREIGN KEY (customer_id) REFERENCES customers (id);

Пока таблица маленькая, вы этого не заметите. На большой картина другая. Чтобы ограничение появилось в каталоге как действующее, PostgreSQL должен убедиться, что каждая существующая строка в orders находит себе родителя. Это сканирование всей таблицы с поиском соответствий в customers. И всё это время действует блокировка. По документации к ALTER TABLE, добавление внешнего ключа требует режима SHARE ROW EXCLUSIVE как на изменяемой таблице, так и на таблице, на которую ссылаются.

Что это значит practically? Режим SHARE ROW EXCLUSIVE конфликтует с режимами, которые берут обычные команды записи, то есть INSERT, UPDATE и DELETE. Пока идёт проверка, новые заказы в таблицу не запишутся: транзакции встанут в очередь за ALTER TABLE. Чтение обычным SELECT при этом не блокируется, но для магазина, где главное действие это создание заказа, радости от этого мало. И важна не только таблица заказов. Блокировка нужна и на customers, так что регистрация и изменение клиентов тоже окажутся в зоне риска.

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

Есть и неприятный сюжет с очередями. Если ALTER TABLE ждёт получения блокировки, потому что в таблице висит длинная транзакция, то за ним выстраиваются и все последующие запросы, даже те, что сами по себе с ним не конфликтовали бы. Команда, которая должна была работать минуту, приводит к тому, что приложение зависает уже на этапе ожидания. Об этом стоит помнить при любом варианте, мы вернёмся к этому в разделе про окно выполнения.

NOT VALID: ограничение есть, ретроспективной проверки нет

Второй путь разделяет две работы:

ALTER TABLE orders
  ADD CONSTRAINT orders_customer_fk
  FOREIGN KEY (customer_id) REFERENCES customers (id)
  NOT VALID;

Согласно документации, при таком добавлении исходное сканирование таблицы пропускается. Ограничение создаётся быстро, потому что PostgreSQL не обходит существующие строки.

Название вводит в заблуждение, и из него легко вывести опасное впечатление: «не валидно, значит, не работает». Это не так. Ограничение, созданное с NOT VALID, действует для новых данных: те же документация и описание говорят, что последующие INSERT и UPDATE уже обязаны его соблюдать. Попытка вставить заказ с несуществующим customer_id завершится ошибкой так же, как при обычном ключе. Правильнее читать пометку так: «ограничение не подтверждено для старых строк». Это честное описание состояния, а не отключённый предохранитель.

Обратная сторона такая же важная. Успешное выполнение команды с NOT VALID не означает, что старые данные в порядке. Возможно, в таблице давно лежат заказы с осиротевшими ссылками: клиента удалили руками, импорт прошёл мимо проверок, миграция десятилетней давности что-то потеряла. Команда об этом не скажет ни слова, она просто их не смотрела. Поэтому состояние после этого шага промежуточное: новые строки защищены, старые не доказаны.

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

Что при этом происходит с блокировками

Фраза «NOT VALID не блокирует» была бы красивой и неверной. Сама команда ADD CONSTRAINT всё равно берёт блокировку, требуемую для добавления внешнего ключа, то есть SHARE ROW EXCLUSIVE на обе таблицы. Выигрыш не в том, что блокировки нет, а в том, что она удерживается очень недолго: нет сканирования, значит, нет затяжной проверки под замком. Время удержания сжимается с «длительности прохода по всей таблице» до «времени на запись в каталог». Но получить эту блокировку всё равно нужно, а значит, вопрос ожидания за длинными транзакциями остаётся в силе.

Это принципиальное различие стоит держать в голове при планировании. Мы не убираем блокировку, мы укорачиваем её до момента и переносим дорогую часть в другое место.

VALIDATE CONSTRAINT: проверка старого отдельным шагом

Теперь можно доказать целостность накопленного:

ALTER TABLE orders
  VALIDATE CONSTRAINT orders_customer_fk;

По документации, эта команда проверяет существующие строки отдельно и берёт на изменяемую таблицу блокировку SHARE UPDATE EXCLUSIVE. Здесь и кроется практический смысл всей схемы. Режим SHARE UPDATE EXCLUSIVE не конфликтует с обычными командами записи, так что INSERT, UPDATE и DELETE в таблице продолжают проходить. Он конфликтует с другими командами, которые тоже берут такой режим или более сильные: например, с параллельным VACUUM, другим ALTER TABLE на той же таблице или созданием индексов обычным способом. Для типичного сервиса это существенно более мягкое условие, чем закрытая запись.

Если сложить картину, получается такая таблица.

Обратите внимание, что в правой колонке цена не исчезла. Полный проход по таблице всё равно происходит, он потребляет диск и процессор, и на нагруженной базе его чувствуют соседние запросы. Он просто перестал останавливать запись. Для виртуальной машины, на которой живёт и база, и приложение, это значит конкуренцию за ввод-вывод и память, а не за блокировки. Если вы выбираете ресурсы под такую базу, разумно смотреть на быстрый диск и запас процессорных ядер; подробности про настройку сервера мы разбирали в материале про VPS для PostgreSQL.

Сначала найти сирот, потом валидировать

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

SELECT o.id, o.customer_id
FROM orders o
LEFT JOIN customers c ON c.id = o.customer_id
WHERE o.customer_id IS NOT NULL
  AND c.id IS NULL
LIMIT 100;

Условие на NULL не случайно: по правилам внешнего ключа строка с пустым значением в ссылающейся колонке проверку проходит, если колонка одна. Сироты это только строки, где значение задано, а родителя нет.

Запрос с LEFT JOIN и отбором пустого совпадения опирается на то, что в customers.id есть индекс, а он есть, раз это первичный или уникальный ключ. Внешний ключ вообще требует уникальности на стороне родителя. А вот на стороне orders.customer_id индекс не создаётся автоматически, и это отдельная тема: он понадобится для быстрого каскадного удаления и проверок при изменении клиентов, но для самой валидации, которая идёт от дочерней таблицы к родительской, он не обязателен. Мы не будем превращать статью в учебник индексов, лишь отметим, что вопрос стоит задать до выката, а не после.

Что делать с найденными сиротами, решает бизнес, а не база. Варианты бывают такие:

• восстановить недостающего клиента, если запись удалили по ошибке и данные ещё живы;
• перенаправить заказы на служебного клиента вроде «неизвестный», если важно сохранить историю суммы;
• обнулить ссылку, если колонка допускает NULL и связь для этих заказов не имеет смысла;
• удалить или заархивировать заказы, если это заведомый мусор после тестов.

Каждое из решений меняет данные, поэтому его стоит провести отдельной понятной миграцией, а не прятать в одной транзакции с DDL. Пока вы чините сирот, ограничение с NOT VALID уже работает и не даёт появиться новым: число нарушителей может только уменьшаться. Это ещё один довод в пользу двухшагового пути: цель перестаёт двигаться. При обычном добавлении вы вынуждены сначала вычистить всё, а в промежутке между чисткой и ALTER приложение успеет создать новых нарушителей.

Репетиция на копии: что и как измерять

Теоретические рассуждения о блокировках надо подкрепить цифрой, а цифру можно получить только на данных, похожих на рабочие. Для этого поднимают копию базы на отдельной машине и прогоняют на ней оба сценария. Хорошая копия должна быть сопоставима по объёму таблиц, по индексам и по тому, как данные лежат на диске; на пустой базе или на урезанной выборке время проверки покажет красивые, но бесполезные величины.

Что именно записать по ходу репетиции:

1. Время выполнения ADD CONSTRAINT ... NOT VALID. Ожидаемо оно совсем небольшое, но его стоит увидеть своими глазами.
2. Время выполнения VALIDATE CONSTRAINT на холодном кэше и повторно на тёплом. Разница покажет, насколько результат зависит от того, что уже прочитано в память.
3. Время обычного ADD FOREIGN KEY на другой копии, если хотите честно сравнить подходы. Это и есть ваш потенциальный простой записи.
4. Поведение параллельной нагрузки: во время валидации запустите имитацию вставок в orders и посмотрите, растёт ли у них задержка.

Для наблюдения за блокировками во время репетиции удобно держать открытым второй сеанс и смотреть системное представление pg_locks вместе с pg_stat_activity:

SELECT a.pid, a.state, a.wait_event_type, a.wait_event,
       l.mode, l.granted, l.relation::regclass AS rel,
       left(a.query, 60) AS query
FROM pg_locks l
JOIN pg_stat_activity a ON a.pid = l.pid
WHERE l.relation IN ('orders'::regclass, 'customers'::regclass)
ORDER BY l.granted, a.pid;

Вы увидите, какой режим держит ваша команда и есть ли за ней очередь с granted = false. Этот взгляд ценнее любых рассуждений: он показывает, чему конкретно мешает ваша операция в вашей базе.

Условный пример для расчёта. Допустим, на копии полный проход при валидации занял двадцать минут, а обычное добавление ключа заняло примерно столько же, потому что объём работы тот же. Тогда при обычном пути запись в orders и customers была бы закрыта около двадцати минут, а при двухшаговом путь закрытие сократится до секунд на создание ограничения, и ещё двадцать минут ограничение будет проверять уже существующее, пока магазин продолжает принимать заказы. Цифры здесь иллюстрация, а не прогноз: ваши будут другими, и именно ради них нужна репетиция.

Есть и вторая величина, которую легко упустить: вклад валидации в общую нагрузку. Если в этот же период на сервере идёт ночной отчёт или резервное копирование, суммарное давление на диск может оказаться заметным. Репетиция, на которой вы повторили не только DDL, но и сопутствующие процессы, ближе к правде.

Окно выполнения: новые заказы и конкурирующие транзакции

Теперь про самое практическое: когда запускать. Для двухшагового пути окно нужно выбирать отдельно для каждого этапа, и у них разные критерии.

Создание ограничения

Этот шаг короткий, но требует блокировки SHARE ROW EXCLUSIVE, а значит, должен дождаться завершения всех транзакций, которые уже держат конфликтующие режимы на этих таблицах. Если где-то висит забытая транзакция, например отчёт, открывшийся много часов назад и записавший что-то в orders, ваша команда будет ждать, а за ней и всё остальное. Поэтому перед запуском имеет смысл посмотреть на длинные транзакции в pg_stat_activity, а саму команду выполнять с ограничением времени ожидания блокировки:

SET lock_timeout = '3s';
ALTER TABLE orders
  ADD CONSTRAINT orders_customer_fk
  FOREIGN KEY (customer_id) REFERENCES customers (id)
  NOT VALID;

Если блокировку получить не удалось, команда завершится ошибкой, а не будет держать очередь. Вы просто повторите попытку позже. Это дешёвая страховка, которая превращает потенциальный затор в безобидный отказ. Настройка lock_timeout это стандартный параметр сеанса PostgreSQL, и его значение подбирают под свою систему.

Валидация

Для второго шага важнее другое: он длинный, но не закрывает запись. Тут нужно окно с умеренной нагрузкой на диск, а не с отсутствием пользователей. Подойдёт спокойное время суток, когда нет тяжёлых отчётов и массовых импортов. Стоит также учесть, что SHARE UPDATE EXCLUSIVE мешает другим операциям обслуживания таблицы: пока идёт валидация, вручную запущенный VACUUM или создание индекса на orders будут ждать. Плановые задачи обслуживания лучше развести по времени, чтобы они не стояли в очереди и не стартовали вперемешку.

Если валидация прервётся из-за ошибки или отмены, ограничение останется в статусе «не проверено», и вы просто запустите команду снова. Ничего не сломано: новые строки по-прежнему защищены. Это одна из причин, по которой путь через NOT VALID спокойнее: у каждого шага есть понятное состояние, в котором можно остановиться и подумать.

Что с заказами, которые приходят прямо сейчас

Во время валидации новые заказы создаются и сразу проверяются ограничением. Если приложение до сих пор иногда писало заказ с ссылкой на клиента, которого ещё нет, например из-за порядка вставок в разных транзакциях, то после появления ограничения эти попытки начнут падать с ошибкой нарушения внешнего ключа. Это не побочный эффект миграции, а раскрытие давней проблемы. Поэтому полезно заранее просмотреть код, создающий клиента и заказ, и убедиться, что родитель вставляется раньше дочерней записи в той же или предыдущей транзакции. Также проверьте поведение при удалении клиентов: по умолчанию удаление родителя, на которого есть ссылки, будет отклонено. Хотите другое, например каскад, это решение принимается в определении ключа заранее, а не после запуска.

Завершение: когда ограничение становится полноценным

После успешной валидации ограничение полностью подтверждено. Проверить его состояние можно через системный каталог:

SELECT conname, convalidated
FROM pg_constraint
WHERE conrelid = 'orders'::regclass
  AND contype = 'f';

Столбец convalidated показывает, прошла ли проверка существующих строк. Пока там false, историческая целостность не доказана, даже если новые данные уже под защитой. Такой запрос удобно включить в чек миграции, чтобы незавершённая валидация не потерялась на неделях рутины: легко создать ограничение, увидеть успех и забыть про второй шаг.

Итоговая последовательность для рабочей базы получается такой:

1. На копии измерить длительность этапов и посмотреть блокировки.
2. Найти старых нарушителей запросом и решить, как их исправить.
3. Проверить длинные транзакции, выставить lock_timeout и добавить ограничение с NOT VALID.
4. Исправить найденные данные, пока ограничение защищает от новых нарушений.
5. Запустить VALIDATE CONSTRAINT в спокойное для диска время.
6. Убедиться по convalidated, что ограничение подтверждено.

Ничто в этой схеме не обещает полностью безболезненной операции. Блокировки есть на обоих этапах, просто они разные по силе и по длительности, а полный проход по таблице всё равно создаёт нагрузку. Выигрыш в том, что вы управляете каждой частью по отдельности и не склеиваете самое дорогое с самым неудобным.

В конце у вас будет база, которая отказывает в записи заказа без клиента и при этом может подтвердить, что старые заказы тоже не висят в воздухе. Ограничение перестаёт быть договорённостью в коде и превращается в свойство самих данных, а флаг convalidated в каталоге служит этому доказательством.