Ограничение на большой таблице либо держит блокировку записи на время полного скана, либо ставится в два шага без остановки. Внешний ключ без индекса на дочернем столбце молча превращает DELETE родителя в полный скан. Синтаксис — в статьях про CREATE TABLE и ALTER TABLE; здесь — цена ограничений.
DELETE родителя: проверка внешнего ключа без индекса и с ним
Опыт 1: DELETE родителя при дочерней таблице с индексом и без
Стенд: родитель на 1 001 строку, дочерняя таблица child на 10 000 000 строк по 10 000 на родителя. Индекс есть только на первичном ключе child.id; на parent_id его нет. Таблица занимает 422 МБ, индекс первичного ключа — 214 МБ.
docker run -d --name dba-constraints-as-dba-tool -e POSTGRES_PASSWORD=pg postgres:18
docker exec -i dba-constraints-as-dba-tool psql -U postgres <<'SQL'
CREATE EXTENSION btree_gist;
CREATE TABLE parent (id int PRIMARY KEY, name text);
CREATE TABLE child (id bigserial PRIMARY KEY, parent_id int NOT NULL REFERENCES parent(id), pad text);
INSERT INTO parent SELECT g, 'p'||g FROM generate_series(1,1001) g;
INSERT INTO child (parent_id, pad) SELECT 1 + g % 1000, 'x' FROM generate_series(1,10000000) g;
INSERT INTO parent SELECT g, 'q' FROM generate_series(1002,1051) g;
VACUUM ANALYZE parent, child;
SQLРодители 1002–1051 созданы без детей: их удаление разрешено внешним ключом, и вся цена остаётся в проверке. Внешние ключи без индекса находит запрос по каталогу: ссылающиеся столбцы должны стоять в начале какого-нибудь валидного индекса.
SELECT c.conrelid::regclass AS tbl, c.conname, pg_get_constraintdef(c.oid) AS def
FROM pg_constraint c
WHERE c.contype = 'f'
AND NOT EXISTS (
SELECT 1 FROM pg_index i
WHERE i.indrelid = c.conrelid
AND i.indisvalid
AND (i.indkey::int2[])[0:cardinality(c.conkey) - 1] @> c.conkey
AND (i.indkey::int2[])[0:cardinality(c.conkey) - 1] <@ c.conkey); tbl | conname | def
-------+----------------------+-----------------------------------------------
child | child_parent_id_fkey | FOREIGN KEY (parent_id) REFERENCES parent(id)Дальше — DELETE одного родителя в откатываемой транзакции; EXPLAIN ANALYZE показывает время триггера внешнего ключа отдельной строкой, а счётчики pg_stat_user_tables — как читалась child:
SELECT pg_stat_reset();
BEGIN; EXPLAIN (ANALYZE, COSTS off) DELETE FROM parent WHERE id = 1002; ROLLBACK;
SELECT pg_stat_force_next_flush();
SELECT seq_scan, seq_tup_read, idx_scan FROM pg_stat_user_tables WHERE relname = 'child';Trigger for constraint child_parent_id_fkey: time=212.103 calls=1
Execution Time: 212.198 ms
1|10000000|0Сам DELETE занял около 0,1 мс (разность Execution Time и времени триггера), а 212 мс ушло в триггер: один seq_scan по child и 10 000 000 прочитанных строк (при другом прогоне — 206 мс). Запрос триггера собирает функция ri_restrict, а комментарий в ней показывает его вид (ri_triggers.c, строки 771–773, тег REL_18_6):
* The query string built is
* SELECT 1 FROM [ONLY] <fktable> x WHERE $1 = fkatt1 [AND ...]
* FOR KEY SHARE OF xЗапрос обычный, и планировщик выбирает для него то, что есть: без индекса на parent_id это полный скан (seq_scan = 1). Тот же опыт после CREATE INDEX child_parent_id_idx ON child (parent_id) (66 МБ) и ANALYZE child:
Trigger for constraint child_parent_id_fkey: time=0.225 calls=1
Execution Time: 0.317 ms
0|0|1212,103 мс против 0,225 мс (при другом прогоне 206,107 и 0,243): примерно в 900 раз. Пакет страшнее одной строки: DELETE FROM parent WHERE id BETWEEN 1002 AND 1051 запускает триггер 50 раз.
Trigger for constraint child_parent_id_fkey: time=10721.964 calls=50
Trigger for constraint child_parent_id_fkey: time=0.888 calls=50Первая строка — без индекса, вторая — с ним: 10,7 секунды против 0,89 мс при тех же 50 строках. Измерено на прогретом кеше: вся таблица в 422 МБ лежала в памяти.
Проверка держит и замок: триггер берёт FOR KEY SHARE на строку родителя. Сессия A вставила в child строку с parent_id = 1001 и не закрыла транзакцию; сессия B с lock_timeout = '1s' обновила parent (UPDATE ... SET name) за 1,5 мс, а DELETE FROM parent WHERE id = 1001 получила ERROR: canceling statement due to lock timeout с CONTEXT: while deleting tuple (5,109) in relation "parent" после 1000,268 мс. Эти замки участвуют в ожиданиях наравне с остальными, а цикл из ожиданий находит детектор взаимоблокировок.
Опыт 2: NOT VALID и VALIDATE под второй сессией
Новое ограничение CHECK сначала проверяет все существующие строки. Сессия A добавляет его обычной командой в транзакции и держит её четыре секунды (pg_sleep), чтобы сессия B успела посмотреть pg_locks и попробовать записать; в автокоммите замок снимается в конце оператора.
-- сессия A
BEGIN;
ALTER TABLE child ADD CONSTRAINT child_pad_chk2 CHECK (pad <> '');
SELECT pg_sleep(4);
COMMIT;
-- сессия B, на 2,5-й секунде
SELECT l.mode, l.granted FROM pg_locks l
WHERE l.relation = 'child'::regclass AND l.locktype = 'relation' AND l.pid <> pg_backend_pid();
SET lock_timeout = '1s';
INSERT INTO child (parent_id, pad) VALUES (1, 'y'); mode | granted
---------------------+---------
AccessExclusiveLock | t
ERROR: canceling statement due to lock timeout
Time: 1000.150 ms (00:01.000)Скан 10 млн строк занял 271 мс (ALTER в сессии A), замок держался до конца транзакции, и запись встала. Чтения за ACCESS EXCLUSIVE тоже выстраиваются в очередь. Теперь в два шага, сессия B пробует запись и другой DDL:
-- сессия A
ALTER TABLE child ADD CONSTRAINT child_pad_chk CHECK (pad <> '') NOT VALID;
BEGIN;
ALTER TABLE child VALIDATE CONSTRAINT child_pad_chk;
SELECT pg_sleep(4);
COMMIT;
-- сессия B, на 2,5-й секунде: тот же запрос к pg_locks, затем
INSERT INTO child (parent_id, pad) VALUES (1, 'y');
UPDATE child SET pad = 'z' WHERE id = 5;
SET lock_timeout = '1s';
ALTER TABLE child ADD COLUMN extra int; mode | granted
--------------------------+---------
ShareUpdateExclusiveLock | t
Time: 5.195 ms
Time: 2.282 ms
Time: 0.186 ms
ERROR: canceling statement due to lock timeout
Time: 1000.292 ms (00:01.000)INSERT (5,2 мс) и UPDATE (2,3 мс) прошли, ALTER TABLE не дождался. VALIDATE CONSTRAINT взял ShareUpdateExclusiveLock: пускает запись, не пускает другой DDL и VACUUM. Этот уровень записан в AlterTableGetLockLevel (tablecmds.c, строки 4824–4826):
case AT_ValidateConstraint: /* Uses MVCC in getConstraints() */
cmd_lockmode = ShareUpdateExclusiveLock;
break;Сам скан занял 261 мс, а первый шаг, NOT VALID, — 2,6 мс, но в транзакции он держит AccessExclusiveLock на child, поэтому её не оставляют открытой. Для внешнего ключа pg_locks в той же сессии показал (строки AccessShareLock от самого запроса опущены):
ALTER TABLE child ADD CONSTRAINT child_fk2 FOREIGN KEY (parent_id) REFERENCES parent(id) NOT VALID;
child | ShareRowExclusiveLock
parent | ShareRowExclusiveLock
ALTER TABLE child VALIDATE CONSTRAINT child_fk2; (436 мс)
child | ShareUpdateExclusiveLock
parent | RowShareLockShareRowExclusiveLock конфликтует с записью на обеих таблицах, но данные не читает: скан уходит в VALIDATE под более слабыми замками.
После NOT VALID новые строки проверяются сразу, старые — только при VALIDATE:
CREATE TABLE t (id int, v int); INSERT INTO t VALUES (1, -5);
ALTER TABLE t ADD CONSTRAINT v_pos CHECK (v > 0) NOT VALID;
INSERT INTO t VALUES (2, -1);
ALTER TABLE t VALIDATE CONSTRAINT v_pos;
SELECT conname, convalidated FROM pg_constraint WHERE conrelid = 't'::regclass;ERROR: new row for relation "t" violates check constraint "v_pos"
DETAIL: Failing row contains (2, -1).
ERROR: check constraint "v_pos" of relation "t" is violated by some row
conname | convalidated
---------+--------------
v_pos | fconvalidated остаётся f: старую строку (1, -5) придётся исправить и повторить VALIDATE.
Опыт 3: EXCLUDE, WITHOUT OVERLAPS и DEFERRABLE
UNIQUE не умеет запрещать пересекающиеся интервалы: два бронирования одной комнаты с разными началами — разные ключи. Для этого есть EXCLUDE, он строит GiST-индекс и проверяет оператор && на каждую вставку (для room WITH = в GiST нужно расширение btree_gist, оно создано в начале опыта 1):
CREATE TABLE booking (
room int NOT NULL,
during tstzrange NOT NULL,
EXCLUDE USING gist (room WITH =, during WITH &&)
);
INSERT INTO booking VALUES (1, '[2026-10-28 10:00, 2026-10-28 12:00)');
INSERT INTO booking VALUES (2, '[2026-10-28 11:00, 2026-10-28 12:00)');
INSERT INTO booking VALUES (1, '[2026-10-28 12:00, 2026-10-28 13:00)');
INSERT INTO booking VALUES (1, '[2026-10-28 11:30, 2026-10-28 12:30)');INSERT 0 1
INSERT 0 1
INSERT 0 1
ERROR: conflicting key value violates exclusion constraint "booking_room_during_excl"
DETAIL: Key (room, during)=(1, ["2026-10-28 11:30:00+00","2026-10-28 12:30:00+00")) conflicts with existing key (room, during)=(1, ["2026-10-28 10:00:00+00","2026-10-28 12:00:00+00")).Первые три прошли, включая [12:00, 13:00) встык с [10:00, 12:00): правая граница полуоткрытого интервала не входит в него. Четвёртая пересекла первую и отклонена. Примечания к PostgreSQL 18 добавляют WITHOUT OVERLAPS для PRIMARY KEY и UNIQUE и PERIOD для внешних ключей: «all applied to the last specified column». В контейнере 18.6:
CREATE TABLE booking_p (
room int NOT NULL,
during tstzrange NOT NULL,
PRIMARY KEY (room, during WITHOUT OVERLAPS)
);
INSERT INTO booking_p VALUES (1, '[2026-10-28 10:00, 2026-10-28 12:00)');
INSERT INTO booking_p VALUES (1, '[2026-10-28 11:30, 2026-10-28 12:30)');SELECT c.conname, c.contype, c.conperiod, i.indisexclusion AS excl, am.amname
FROM pg_constraint c
JOIN pg_index i ON i.indexrelid = c.conindid
JOIN pg_class ic ON ic.oid = i.indexrelid
JOIN pg_am am ON am.oid = ic.relam
WHERE c.conrelid IN ('booking'::regclass, 'booking_p'::regclass) AND c.contype IN ('p', 'x')
ORDER BY 1;ERROR: conflicting key value violates exclusion constraint "booking_p_pkey"
DETAIL: Key (room, during)=(1, ["2026-10-28 11:30:00+00","2026-10-28 12:30:00+00")) conflicts with existing key (room, during)=(1, ["2026-10-28 10:00:00+00","2026-10-28 12:00:00+00")).
conname | contype | conperiod | excl | amname
--------------------------+---------+-----------+------+--------
booking_p_pkey | p | t | t | gist
booking_room_during_excl | x | f | t | gistВнутри это тот же механизм: WITHOUT OVERLAPS создаёт GiST-индекс с флагом indisexclusion (excl = t), ошибка называется «exclusion constraint». Отличие в том, что это настоящий первичный ключ (contype = p, conperiod = t), на который по примечаниям к выпуску можно сослаться внешним ключом с PERIOD; EXCLUDE остаётся для случаев, когда нужны другие операторы.
DEFERRABLE переносит проверку с оператора на COMMIT. Пара «отдел — руководитель, сотрудник — отдел» ссылается друг на друга:
CREATE TABLE dept (id int PRIMARY KEY, head int);
CREATE TABLE emp (id int PRIMARY KEY, dept int NOT NULL REFERENCES dept(id));
ALTER TABLE dept ADD CONSTRAINT dept_head_fk FOREIGN KEY (head) REFERENCES emp(id);
BEGIN;
INSERT INTO dept VALUES (1, 10);ERROR: insert or update on table "dept" violates foreign key constraint "dept_head_fk"
DETAIL: Key (head)=(10) is not present in table "emp".Отдел с руководителем падает на первом операторе: сотрудника 10 ещё нет. После ALTER TABLE dept ALTER CONSTRAINT dept_head_fk DEFERRABLE INITIALLY DEFERRED те же две вставки в одной транзакции проходят, а отдел с несуществующим руководителем падает на COMMIT:
BEGIN; INSERT INTO dept VALUES (1, 10); INSERT INTO emp VALUES (10, 1); COMMIT;
BEGIN
INSERT 0 1
INSERT 0 1
COMMIT
BEGIN; INSERT INTO dept VALUES (2, 20); COMMIT;
BEGIN
INSERT 0 1
ERROR: insert or update on table "dept" violates foreign key constraint "dept_head_fk"
DETAIL: Key (head)=(20) is not present in table "emp".Цена отсрочки — ошибка приходит поздно, из кода, который уже считает операции удавшимися.
Что из этого следует
- Ограничение на живой таблице — два шага.
VALIDATE CONSTRAINTсканирует 10 млн строк за 261 мс (CHECK) и 436 мс (внешний ключ), не останавливая запись; самNOT VALIDзанял 2,6 мс. Одним оператором то же сканирование держалоACCESS EXCLUSIVE: вставка не прошла за 1 с. - Каждому внешнему ключу нужен индекс на ссылающемся столбце. Без него один
DELETEродителя читает всю дочернюю таблицу: 212 мс на 10 млн строк, 10,7 с на 50 родителей. Индекс стоит 66 МБ и сводит это к 0,23 мс и 0,89 мс. - Пересекающиеся интервалы запрещает
EXCLUDEилиWITHOUT OVERLAPS, а не код приложения. Оба строят GiST-индекс; в PostgreSQL 18 диапазонный первичный ключ записывается прямо в схеме. DEFERRABLE— для циклических ссылок, не для всех ключей. Он снимает проблему порядка вставки, но переносит ошибку наCOMMIT.
Чек-лист
- Перед
ADD CONSTRAINTна большой таблице используйNOT VALID, потомVALIDATE CONSTRAINTв отдельной транзакции; следи, чтобы транзакция сNOT VALIDне оставалась открытой. - Раз в релиз гоняй запрос поиска внешних ключей без индекса и для каждого ответа реши: индекс или осознанный отказ (родитель никогда не удаляется).
- Проверь
convalidatedвpg_constraint:fзначит, что старые строки не проверены. - Для бронирований и действующих периодов бери
EXCLUDE ... gistилиWITHOUT OVERLAPS(PostgreSQL 18). - Убери контейнер вместе с данными:
docker rm -f -v dba-constraints-as-dba-tool.