NOT VALID + VALIDATE: Add Constraints Without Long Locks

Когда база маленькая, добавить новое ограничение обычно не проблема.

Написали:

ALTER TABLE orders
ADD CONSTRAINT chk_amount_positive CHECK (amount > 0);

и всё быстро отработало.

Но на боевой таблице, где лежат миллионы или десятки миллионов строк, такой ALTER TABLE может стать неприятным сюрпризом. PostgreSQL должен проверить все существующие строки и убедиться, что они уже соответствуют новому правилу.

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

Чтобы избежать такой ситуации, в PostgreSQL есть очень полезная связка:

NOT VALID

и

VALIDATE CONSTRAINT

Она позволяет добавить ограничение в два этапа:

1. Быстро зарегистрировать правило и начать применять его к новым данным.
2. Отдельно проверить старые данные, уже без агрессивной блокировки записи.

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

---

В чём вообще проблема обычного ALTER TABLE

Представим таблицу заказов:

CREATE TABLE orders (
    id         bigint PRIMARY KEY,
    user_id    bigint,
    amount     numeric(12,2),
    status     text,
    created_at timestamptz DEFAULT now()
);

Допустим, таблица уже давно живёт в проде. В ней 50 миллионов заказов.

И мы решили добавить правило:

ALTER TABLE orders
ADD CONSTRAINT chk_amount_positive
CHECK (amount > 0);

Смысл правила простой: сумма заказа должна быть больше нуля.

Но PostgreSQL не может просто поверить нам на слово. Ему нужно проверить все уже существующие строки:

А вдруг в старых данных уже есть amount = 0?
А вдруг где-то amount = -100?

Поэтому PostgreSQL начинает сканировать таблицу.

На маленькой таблице это занимает доли секунды. На большой — секунды, минуты, а иногда и дольше.

И проблема не только в самом скане. Проблема в блокировках.

Пока идёт изменение схемы, другие запросы могут ждать. Особенно неприятно, если в эту таблицу постоянно идут INSERT, UPDATE или DELETE.

То есть обычный сценарий такой:

1. Запустили ALTER TABLE.
2. PostgreSQL начал проверять старые строки.
3. Запись в таблицу начала ждать.
4. Приложение начало тормозить.
5. Пользователи начали страдать.

Именно для таких случаев нужен NOT VALID.

---

Что делает NOT VALID

NOT VALID говорит PostgreSQL:

Добавь ограничение в схему, но пока не проверяй все старые строки.

Например:

ALTER TABLE orders
ADD CONSTRAINT chk_amount_positive
CHECK (amount > 0)
NOT VALID;

Что произойдёт после этой команды?

PostgreSQL быстро зарегистрирует ограничение. Старые данные он пока полностью сканировать не будет.

Но важный момент: для новых данных ограничение уже начнёт работать.

То есть после добавления такого CHECK новый заказ с отрицательной суммой уже не пройдёт:

INSERT INTO orders (id, user_id, amount, status)
VALUES (1001, 7, -500, 'new');

Такой запрос упадёт, потому что amount > 0 нарушено.

То есть NOT VALID не означает «ограничение выключено».

Он означает другое:

Старые строки пока не проверены.
Новые строки уже проверяются.

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

---

Пример с CHECK

Допустим, у нас есть таблица заказов, и мы хотим запретить нулевые и отрицательные суммы.

Правильное бизнес-правило такое:

amount должен быть больше 0

На пустой таблице можно было бы спокойно написать:

ALTER TABLE orders
ADD CONSTRAINT chk_amount_positive
CHECK (amount > 0);

Но на большой боевой таблице безопаснее сделать так:

ALTER TABLE orders
ADD CONSTRAINT chk_amount_positive
CHECK (amount > 0)
NOT VALID;

Теперь PostgreSQL:

• добавит ограничение;
• не будет сразу сканировать всю таблицу;
• начнёт проверять новые вставки и обновления;
• пометит ограничение как ещё не подтверждённое для старых данных.

Если в таблице уже были строки с amount <= 0, они пока останутся. Но новые такие строки появиться уже не смогут.

---

Пример с FOREIGN KEY

С внешними ключами ситуация похожая.

Допустим, у нас есть пользователи:

CREATE TABLE users (
    id    bigint PRIMARY KEY,
    email text UNIQUE NOT NULL
);

И есть заказы:

CREATE TABLE orders (
    id      bigint PRIMARY KEY,
    user_id bigint,
    amount  numeric(12,2)
);

Мы хотим добавить внешний ключ:

orders.user_id должен ссылаться на users.id

То есть нельзя, чтобы заказ ссылался на пользователя, которого не существует.

Обычный вариант:

ALTER TABLE orders
ADD CONSTRAINT fk_orders_user
FOREIGN KEY (user_id)
REFERENCES users(id);

Но если orders огромная, PostgreSQL должен проверить все существующие заказы и убедиться, что для каждого user_id есть пользователь в users.

На проде лучше добавить внешний ключ так:

ALTER TABLE orders
ADD CONSTRAINT fk_orders_user
FOREIGN KEY (user_id)
REFERENCES users(id)
NOT VALID;

После этого новые заказы уже будут проверяться.

Например, если пользователя с id = 777 нет:

INSERT INTO orders (id, user_id, amount)
VALUES (1001, 777, 2500);

PostgreSQL не даст вставить такую строку.

Но старые строки пока не проверены. Если в таблице уже были «осиротевшие» заказы с несуществующим user_id, они временно останутся до отдельной проверки.

---

Второй шаг: VALIDATE CONSTRAINT

После того как ограничение добавлено с NOT VALID, его нужно довести до нормального состояния.

Для этого используется команда:

ALTER TABLE orders
VALIDATE CONSTRAINT chk_amount_positive;

или для внешнего ключа:

ALTER TABLE orders
VALIDATE CONSTRAINT fk_orders_user;

Вот теперь PostgreSQL пойдёт и проверит старые строки.

Главное отличие от обычного ALTER TABLE ADD CONSTRAINT в том, что валидация проходит с более мягкой блокировкой. Обычные чтения и записи в таблицу могут продолжаться.

То есть приложение не должно вставать только из-за того, что PostgreSQL проверяет старые данные.

При этом важно понимать: VALIDATE CONSTRAINT всё равно читает таблицу и потребляет ресурсы. Это не магия и не бесплатная операция. Просто она намного дружелюбнее к работающему сервису, чем добавление ограничения «в лоб».

Поэтому на очень больших таблицах валидацию всё равно лучше запускать в спокойное время: ночью, в период низкой нагрузки или во время технического окна.

---

Что будет, если старые данные плохие

Допустим, мы добавили ограничение:

ALTER TABLE orders
ADD CONSTRAINT chk_amount_positive
CHECK (amount > 0)
NOT VALID;

А потом запустили валидацию:

ALTER TABLE orders
VALIDATE CONSTRAINT chk_amount_positive;

Если в старых данных есть хотя бы одна строка с amount <= 0, команда упадёт с ошибкой.

Но это не катастрофа.

Ограничение просто останется в состоянии NOT VALID. Новые строки по-прежнему будут проверяться, а старые данные можно спокойно почистить и попробовать снова.

Например, можно заранее найти проблемные заказы:

SELECT id, amount
FROM orders
WHERE amount <= 0;

Потом решить, что с ними делать:

• исправить сумму;
• удалить тестовые или мусорные записи;
• перенести их в архив;
• согласовать с бизнесом, почему такие данные вообще появились.

После очистки можно снова запустить:

ALTER TABLE orders
VALIDATE CONSTRAINT chk_amount_positive;

Если теперь все строки корректные, ограничение станет полностью валидным.

---

Как заранее найти проблемы для FOREIGN KEY

Перед валидацией внешнего ключа полезно проверить, есть ли в таблице «осиротевшие» строки.

Например, заказы, которые ссылаются на несуществующих пользователей:

SELECT o.id, o.user_id
FROM orders o
LEFT JOIN users u ON u.id = o.user_id
WHERE o.user_id IS NOT NULL
  AND u.id IS NULL;

Что здесь происходит:

• берём заказы из orders;
• пытаемся найти для каждого заказа пользователя в users;
• оставляем только те строки, где пользователь не найден.

Если запрос что-то вернул, значит внешний ключ пока не провалидируется.

Например:

id	user_id
1001	777
1002	888

Это значит, что в orders есть заказы, которые ссылаются на пользователей 777 и 888, но таких пользователей нет в таблице users.

Перед VALIDATE CONSTRAINT такие данные нужно исправить.

Варианты зависят от бизнес-логики:

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

---

Полный безопасный сценарий для CHECK

Допустим, мы хотим добавить правило:

amount должен быть больше 0

Безопасный порядок будет таким.

Сначала добавляем ограничение без проверки старых строк:

ALTER TABLE orders
ADD CONSTRAINT chk_amount_positive
CHECK (amount > 0)
NOT VALID;

Потом отдельно ищем плохие данные:

SELECT id, amount
FROM orders
WHERE amount <= 0;

Если нашли проблемы — исправляем:

UPDATE orders
SET amount = 1
WHERE amount <= 0;

Это просто пример. В реальном проекте лучше не исправлять финансовые данные вслепую, а сначала понять, почему они стали такими.

После очистки запускаем валидацию:

ALTER TABLE orders
VALIDATE CONSTRAINT chk_amount_positive;

Проверяем статус:

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

Если convalidated = true, значит ограничение полностью проверено.

---

Полный безопасный сценарий для FOREIGN KEY

Теперь пример с внешним ключом.

Сначала добавляем FK с NOT VALID:

ALTER TABLE orders
ADD CONSTRAINT fk_orders_user
FOREIGN KEY (user_id)
REFERENCES users(id)
NOT VALID;

Затем ищем строки, которые нарушают будущую валидацию:

SELECT o.id, o.user_id
FROM orders o
LEFT JOIN users u ON u.id = o.user_id
WHERE o.user_id IS NOT NULL
  AND u.id IS NULL;

Если запрос ничего не вернул, можно валидировать:

ALTER TABLE orders
VALIDATE CONSTRAINT fk_orders_user;

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

---

Почему ADD CONSTRAINT и VALIDATE лучше делать разными транзакциями

Есть важный практический момент.

Не стоит делать так:

BEGIN;

ALTER TABLE orders
ADD CONSTRAINT fk_orders_user
FOREIGN KEY (user_id)
REFERENCES users(id)
NOT VALID;

ALTER TABLE orders
VALIDATE CONSTRAINT fk_orders_user;

COMMIT;

На первый взгляд всё красиво: одна транзакция, всё атомарно.

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

Лучше делать так:

ALTER TABLE orders
ADD CONSTRAINT fk_orders_user
FOREIGN KEY (user_id)
REFERENCES users(id)
NOT VALID;

Потом отдельно:

ALTER TABLE orders
VALIDATE CONSTRAINT fk_orders_user;

То есть:

первая миграция — быстро добавили ограничение;
вторая миграция — отдельно проверили старые данные.

Так безопаснее для продакшена.

---

Как проверить, что ограничение ещё NOT VALID

В PostgreSQL статус ограничений можно посмотреть через системный каталог pg_constraint.

Например:

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

Результат может быть таким:

conname	convalidated
fk_orders_user	false
chk_amount_positive	true

Если convalidated = false, значит ограничение ещё не проверено на всех старых строках.

Если convalidated = true, значит всё хорошо: ограничение полностью валидно.

В psql также можно использовать:

\d+ orders

У непроверенных ограничений PostgreSQL покажет пометку NOT VALID.

---

Важный нюанс: пока ограничение NOT VALID, ему не всегда можно доверять

С точки зрения новых данных ограничение уже работает.

Но с точки зрения всей таблицы PostgreSQL пока не может быть уверен, что правило выполняется для всех строк.

Почему?

Потому что старые данные ещё не проверены.

Поэтому не стоит оставлять ограничения в состоянии NOT VALID навсегда. Это временный этап миграции, а не финальное состояние схемы.

Правильный процесс такой:

1. Добавили ограничение с NOT VALID.
2. Почистили старые данные, если нужно.
3. Запустили VALIDATE CONSTRAINT.
4. Убедились, что convalidated = true.

NOT VALID — это не способ «добавить ограничение наполовину». Это способ добавить его безопасно, без резкой остановки записи.

---

Индексы тоже важны

Для внешнего ключа важно подумать об индексах.

Допустим, есть внешний ключ:

FOREIGN KEY (user_id)
REFERENCES users(id)

На users.id индекс уже есть, потому что это PRIMARY KEY.

Но на orders.user_id PostgreSQL автоматически индекс не создаёт.

Поэтому его часто стоит добавить вручную:

CREATE INDEX CONCURRENTLY idx_orders_user_id
ON orders (user_id);

Почему CONCURRENTLY?

Потому что обычный CREATE INDEX на большой таблице тоже может мешать записи. А CREATE INDEX CONCURRENTLY строит индекс более аккуратно для работающей базы.

Обычно порядок такой:

CREATE INDEX CONCURRENTLY idx_orders_user_id
ON orders (user_id);

Потом:

ALTER TABLE orders
ADD CONSTRAINT fk_orders_user
FOREIGN KEY (user_id)
REFERENCES users(id)
NOT VALID;

Потом:

ALTER TABLE orders
VALIDATE CONSTRAINT fk_orders_user;

Индекс на дочерней колонке особенно полезен для дальнейшей жизни таблицы: удалений, обновлений, проверок связей и запросов вида:

SELECT *
FROM orders
WHERE user_id = 7;

---

А как быть с NOT NULL

У NOT NULL в PostgreSQL нет прямого синтаксиса:

NOT NULL NOT VALID

Так написать нельзя.

Но есть обходной путь через CHECK.

Допустим, мы хотим сделать колонку email обязательной:

email не должен быть NULL

Сначала добавляем CHECK с NOT VALID:

ALTER TABLE users
ADD CONSTRAINT chk_users_email_not_null
CHECK (email IS NOT NULL)
NOT VALID;

Потом проверяем старые данные:

SELECT id
FROM users
WHERE email IS NULL;

Если такие строки есть — исправляем.

После этого валидируем ограничение:

ALTER TABLE users
VALIDATE CONSTRAINT chk_users_email_not_null;

А затем уже можно поставить настоящий NOT NULL:

ALTER TABLE users
ALTER COLUMN email SET NOT NULL;

В современных версиях PostgreSQL, начиная с 12, база умеет использовать уже проверенный CHECK (email IS NOT NULL) и не делать повторный полный скан таблицы при SET NOT NULL.

После этого вспомогательный CHECK можно оставить или удалить, потому что настоящее ограничение NOT NULL уже стоит на колонке:

ALTER TABLE users
DROP CONSTRAINT chk_users_email_not_null;

Получается аккуратный путь:

1. CHECK (email IS NOT NULL) NOT VALID
2. VALIDATE CONSTRAINT
3. ALTER COLUMN email SET NOT NULL
4. DROP лишний CHECK

---

А что с UNIQUE и PRIMARY KEY

Для UNIQUE и PRIMARY KEY синтаксис NOT VALID не используется.

Нельзя написать так:

ALTER TABLE users
ADD CONSTRAINT users_email_unique UNIQUE (email) NOT VALID;

Для уникальности другой безопасный подход.

Сначала создают уникальный индекс конкурентно:

CREATE UNIQUE INDEX CONCURRENTLY idx_users_email_unique
ON users (email);

А потом на его основе добавляют ограничение:

ALTER TABLE users
ADD CONSTRAINT users_email_unique
UNIQUE USING INDEX idx_users_email_unique;

То есть для FOREIGN KEY и CHECK используем:

NOT VALID + VALIDATE CONSTRAINT

А для UNIQUE и PRIMARY KEY чаще используют:

CREATE INDEX CONCURRENTLY + ADD CONSTRAINT USING INDEX

Это разные задачи и разные инструменты.

---

Где этот приём особенно полезен

NOT VALID и VALIDATE CONSTRAINT особенно полезны, когда таблица:

• большая;
• активно используется приложением;
• постоянно принимает новые записи;
• не может быть остановлена ради миграции;
• уже содержит старые данные сомнительного качества.

Типичные случаи:

Добавить внешний ключ в старую таблицу заказов.

Запретить отрицательные суммы.

Проверить, что статус входит в разрешённый список.

Постепенно подготовить колонку к NOT NULL.

Навести порядок в схеме без даунтайма.

Это не просто синтаксис PostgreSQL. Это нормальный рабочий подход к миграциям на живой базе.

---

Что в MySQL

В MySQL/InnoDB такой пары, как NOT VALID + VALIDATE CONSTRAINT, нет.

Внешние ключи при создании проверяются сразу. Нельзя добавить FK и сказать: «новые строки проверяй, а старые я потом отдельно провалидирую».

С CHECK в MySQL тоже другая история. Начиная с MySQL 8.0.16, CHECK-ограничения действительно работают, но полноценного аналога PostgreSQL NOT VALID там нет.

Есть синтаксис NOT ENFORCED, но это не то же самое. Он скорее говорит: «ограничение объявлено, но не применяется». А в PostgreSQL NOT VALID работает иначе: новые данные уже проверяются, просто старые ещё не просканированы.

Современный MySQL умеет выполнять многие ALTER TABLE более аккуратно, с online DDL, но добавление внешних ключей на больших таблицах всё равно требует осторожности и проверки на конкретной версии, движке и размере данных.

---

Что в ClickHouse

В ClickHouse внешних ключей в привычном смысле нет.

Он не будет сам проверять, что orders.user_id существует в users.id.

Такая целостность обычно контролируется:

• приложением;
• ETL/ELT-пайплайном;
• процессом загрузки данных;
• отдельными проверочными запросами;
• архитектурой хранилища.

CHECK-ограничения в ClickHouse есть, но подход там другой: они проверяются при вставке данных, а не через отдельный механизм VALIDATE CONSTRAINT, как в PostgreSQL.

Поэтому NOT VALID + VALIDATE CONSTRAINT — это именно PostgreSQL-приём.

---

Короткая шпаргалка

Что нужно сделать	Как безопаснее в PostgreSQL
Добавить CHECK на большую таблицу	ADD CONSTRAINT ... CHECK (...) NOT VALID, потом VALIDATE CONSTRAINT
Добавить внешний ключ	ADD CONSTRAINT ... FOREIGN KEY (...) REFERENCES ... NOT VALID, потом VALIDATE CONSTRAINT
Найти плохие строки для CHECK	Выполнить SELECT ... WHERE NOT (condition)
Найти плохие строки для FK	Сделать LEFT JOIN на родительскую таблицу и найти строки без пары
Сделать колонку NOT NULL	Через CHECK (col IS NOT NULL) NOT VALID, затем VALIDATE, затем SET NOT NULL
Добавить UNIQUE	Через CREATE UNIQUE INDEX CONCURRENTLY, затем ADD CONSTRAINT ... USING INDEX
Проверить статус	Посмотреть pg_constraint.convalidated

---

Главное, что нужно запомнить

Обычный ALTER TABLE ADD CONSTRAINT на большой таблице может быть опасен, потому что PostgreSQL должен сразу проверить все старые строки.

Связка NOT VALID + VALIDATE CONSTRAINT позволяет сделать это спокойнее:

Сначала быстро добавляем правило.
Потом отдельно проверяем старые данные.

После NOT VALID новые строки уже обязаны соответствовать ограничению. Но старые данные ещё не считаются проверенными.

После VALIDATE CONSTRAINT PostgreSQL подтверждает: теперь правило выполняется для всей таблицы.

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