Разбираемся в дефолтных и откладываемых (деферрабельных) констрейнах в PostgreSQL

В постгре по умолчанию все констрейны проверяются немедленно, сразу после каждой операции(NOT DEFERRABLE INITIALLY IMMEDIATE). Это означает, что уникальные ограничения и внешние ключи всегда валидируются мгновенно, как только происходит изменение данных. Такой подход гарантирует целостность данных, но иногда это создает проблемы. Например, если вы пытаетесь изменить значения в столбце с уникальным индексом, сдвинув их все на единицу, то получите ошибку:

UPDATE numbers SET number = number + 1;
-- Ошибка: Duplicate key value violates unique constraint

Почему так происходит? Дело в том, что проверка ограничений выполняется для каждой строки в транзакции, и если хотя бы одна из них нарушает уникальность, операция завершается ошибкой. Это неудобно, особенно если вы хотите массово изменить данные в рамках одной транзакции. Но постгрес так же позволяет создавать деферрабельные (отложенные) констрейны, которые проверяются не сразу, а только в момент завершения транзакции при выполнении команды COMMIT.

Отложенные ограничения можно настроить двумя способами. Первый — DEFERRABLE INITIALLY IMMEDIATE — позволяет отложить проверку ограничений вручную, изменив поведение внутри транзакции с помощью команды SET CONSTRAINTS <constraint_name> DEFERRED. Второй вариант — DEFERRABLE INITIALLY DEFERRED — изначально предполагает, что проверка всегда будет происходить в конце транзакции, что особенно полезно в случаях, когда временное нарушение ограничений не критично и будет исправлено до завершения транзакции.

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

Также стоит учитывать, что отложенные проверки нельзя использовать для ограничений типа NOT NULL и CHECK. Эти типы ограничений всегда проверяются немедленно, что ограничивает применение деферрабельных настроек.

больше примеров использование деферрабельных констрейнов можно посмотреть тут:
https://emmer.dev/blog/deferrable-constraints-in-postgresql/