Какой индекс ставится по умолчанию на уникальный ключ в PostgreSQL

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

Знаете ли вы, что ограничение уникальности в PostgreSQL физически держится на индексе, и понимаете ли разницу между ограничением и индексом. Следом часто спрашивают, создаётся ли индекс на внешний ключ и как уникальность ведёт себя с NULL.

Как отвечать

Короткий ответ: B-tree. Объявление UNIQUE или PRIMARY KEY автоматически создаёт уникальный B-tree индекс, отдельный CREATE INDEX не нужен. Другого варианта нет: в PostgreSQL уникальными могут быть только B-tree индексы, хэш или GIN с UNIQUE не создать.

Что стоит добавить:

• имена: для email UNIQUE в таблице users появится индекс users_email_key, для первичного ключа users_pkey. Их видно в \d users или в представлении pg_indexes;
• NULL: по умолчанию NULL не равен NULL, поэтому строк с пустым email может быть сколько угодно. Начиная с PostgreSQL 15 это меняется через UNIQUE NULLS NOT DISTINCT;
• ограничение или индекс: CREATE UNIQUE INDEX умеет то, чего не умеет ограничение: частичную уникальность (WHERE deleted_at IS NULL) и уникальность по выражению (lower(email)). Зато ограничение можно объявить DEFERRABLE и проверять в конце транзакции;
• внешний ключ: REFERENCES индекс на ссылающейся колонке не создаёт, его ставят руками, иначе удаление родителя и джойны сканируют дочернюю таблицу целиком.

Из мира Java: @Column(unique = true) или @Table(uniqueConstraints = ...) в JPA влияют только на генерацию схемы Hibernate. В проде схему ведут миграциями Flyway или Liquibase, а нарушение уникальности приходит в Spring как DataIntegrityViolationException, под которым лежит SQLSTATE 23505.

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

Создают поверх UNIQUE ещё и обычный индекс по той же колонке. Получается два одинаковых B-tree, и каждая запись платит за оба.

Думают, что уникальность запрещает несколько NULL. Без NULLS NOT DISTINCT не запрещает, и дубликаты «пустых» значений спокойно проходят.

Проверяют уникальность в Java кодом «сначала existsByEmail, потом save». Между двумя вызовами успеет вставить строку параллельный запрос. Гарантию даёт только ограничение в базе, а приложение должно корректно обработать ошибку 23505.