Как Postgres проверяет уникальность при наложении уникального ограничения

Что проверяют

Знаете ли вы, что за ограничением стоит индекс, и понимаете ли, как проверка работает при конкурентных транзакциях. Частый уточняющий вопрос: что будет, если две транзакции одновременно вставят один email, и почему existsByEmail перед save не спасает.

Как отвечать

• индекс под ограничением: UNIQUE и PRIMARY KEY автоматически создают уникальный B-tree индекс, проверка идёт по нему;
• момент проверки: при вставке ключа в индекс Postgres ищет такой же ключ. Из-за MVCC в индексе лежат и записи удалённых или ещё не закоммиченных строк, поэтому он проверяет видимость самой строки в таблице;
• конкурентная вставка: если найденная строка принадлежит незавершённой транзакции, вторая транзакция ждёт её исхода. Первая закоммитилась: вторая получает ошибку 23505 unique_violation. Первая откатилась: вставка проходит;
• когда проверяется: по умолчанию сразу для каждой строки. С DEFERRABLE проверка переносится на конец команды, с INITIALLY DEFERRED на коммит, так можно поменять местами значения двух строк;
• NULL: по умолчанию несколько NULL дубликатами не считаются. С PostgreSQL 15 это меняется опцией NULLS NOT DISTINCT.

В Java ошибка приходит как PSQLException с SQLState 23505, Spring оборачивает её в DataIntegrityViolationException. Правильный приём: уникальность гарантирует база, а код ловит нарушение и отвечает понятной ошибкой. Либо сразу INSERT ... ON CONFLICT DO NOTHING или DO UPDATE.

Где спотыкаются

Проверяют уникальность в коде через SELECT перед вставкой. Между проверкой и записью другая транзакция успевает вставить то же значение, и без ограничения в базе появляется дубль.

Считают, что @Column(unique = true) в JPA что-то проверяет. Это подсказка генератору схемы, при миграциях через Flyway или Liquibase она ничего не делает.

Не учитывают регистр и живую таблицу. Ivan@mail.ru и ivan@mail.ru для обычного индекса разные значения, нужен индекс по lower(email) или тип citext. А ограничение на большой таблице строит индекс под блокировкой, поэтому индекс сначала создают через CREATE UNIQUE INDEX CONCURRENTLY, потом подключают ADD CONSTRAINT ... UNIQUE USING INDEX.