Partial UNIQUE Indexes: Uniqueness Over a Subset of Rows

Обычный UNIQUE говорит базе:

В этой колонке не должно быть повторов во всей таблице.

Например:

UNIQUE (email)

означает, что два пользователя с одинаковым email появиться не могут.

Это понятно и удобно. Но в реальных проектах часто нужно правило чуть тоньше:

Email должен быть уникальным только среди активных пользователей.

Или:

У пользователя может быть только одна активная корзина.

Или:

У сотрудника может быть только один основной отдел.

То есть уникальность нужна не по всей таблице, а только по части строк.

Для таких случаев в PostgreSQL есть частичный уникальный индекс:

CREATE UNIQUE INDEX ...
ON table_name (columns)
WHERE condition;

Он работает как UNIQUE, но только для строк, которые подходят под условие WHERE.

---

Зачем вообще нужна частичная уникальность

Представим обычную таблицу пользователей:

CREATE TABLE users (
    id         bigint PRIMARY KEY,
    email      text NOT NULL,
    name       text NOT NULL,
    country    text,
    deleted_at timestamptz,
    created_at timestamptz NOT NULL DEFAULT now()
);

Здесь есть поле:

deleted_at

Оно нужно для мягкого удаления.

Мягкое удаление — это когда строку физически не удаляют из таблицы, а просто помечают как удалённую.

Например:

UPDATE users
SET deleted_at = now()
WHERE id = 10;

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

Теперь представим, что мы добавили обычный UNIQUE на email:

ALTER TABLE users
ADD CONSTRAINT users_email_unique UNIQUE (email);

На первый взгляд всё хорошо: два активных пользователя не смогут зарегистрироваться с одним email.

Но появляется проблема.

Пользователь удалил аккаунт. Его строка осталась в таблице, просто получила deleted_at.

Потом он решил зарегистрироваться снова с тем же email.

И база не даст это сделать, потому что старая строка всё ещё существует:

email уже занят

Хотя с точки зрения продукта email уже должен быть свободен.

Вот здесь обычный UNIQUE слишком грубый. Он не понимает, что нас интересуют только активные пользователи.

---

Частичный UNIQUE-индекс для soft delete

Решение — создать уникальный индекс только по активным строкам:

CREATE UNIQUE INDEX users_email_active_uniq
ON users (email)
WHERE deleted_at IS NULL;

Теперь правило звучит так:

Email должен быть уникален только среди пользователей,
у которых deleted_at IS NULL.

То есть среди живых пользователей одинаковых email быть не может.

А удалённые пользователи в этот индекс не попадают.

Пример:

id	email	deleted_at
1	bob@example.com	NULL
2	alice@example.com	NULL
3	bob@example.com	2026-05-10 12:00:00

Такая ситуация допустима: старый Bob удалён, новый Bob активен.

Но вот так уже нельзя:

id	email	deleted_at
1	bob@example.com	NULL
2	bob@example.com	NULL

Потому что две активные строки с одинаковым email нарушают частичный уникальный индекс.

---

В чём разница между обычным UNIQUE и частичным UNIQUE

Обычный UNIQUE проверяет всю таблицу:

UNIQUE (email)

Это правило говорит:

Во всей таблице не может быть двух одинаковых email.

Частичный уникальный индекс проверяет только строки, которые подходят под условие:

CREATE UNIQUE INDEX users_email_active_uniq
ON users (email)
WHERE deleted_at IS NULL;

Это правило говорит:

Среди активных пользователей не может быть двух одинаковых email.

Разница огромная.

Обычный UNIQUE не знает, что такое «активный», «удалённый», «черновик», «основной», «текущий». Он просто запрещает дубликаты везде.

Частичный уникальный индекс позволяет описать бизнес-правило точнее.

---

Пример: одна активная корзина на пользователя

Теперь возьмём интернет-магазин.

У пользователя может быть много заказов:

• оплаченные;
• отменённые;
• доставленные;
• возвращённые;
• черновики.

Но активная корзина обычно должна быть одна.

Например, таблица заказов:

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

Пусть корзина хранится как заказ со статусом draft.

Тогда правило такое:

У одного пользователя может быть только один заказ в статусе draft.

Но при этом у него может быть сколько угодно заказов в статусах paid, cancelled, shipped.

Обычный UNIQUE (user_id) нам не подходит. Он запретил бы пользователю иметь больше одного заказа вообще.

А нам нужно запретить только второй draft.

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

CREATE UNIQUE INDEX orders_one_draft_per_user
ON orders (user_id)
WHERE status = 'draft';

Теперь PostgreSQL разрешит такие строки:

id	user_id	status
1	100	draft
2	100	paid
3	100	cancelled
4	100	shipped

Но не разрешит вторую активную корзину:

id	user_id	status
1	100	draft
5	100	draft

Вторая строка нарушит индекс orders_one_draft_per_user.

---

Почему это лучше, чем проверка в приложении

Можно было бы проверять это в коде.

Например:

Перед созданием корзины проверить,
есть ли у пользователя уже draft-заказ.

Условно:

SELECT id
FROM orders
WHERE user_id = 100
  AND status = 'draft';

Если ничего не найдено — создаём новую корзину.

Но у такого подхода есть проблема: гонки.

Представим, что два запроса пришли почти одновременно.

Запрос A проверил: draft нет.
Запрос B проверил: draft нет.
Запрос A создал draft.
Запрос B тоже создал draft.

В итоге у пользователя две корзины, хотя код вроде бы проверял.

База данных — последнее место защиты. Если правило важно, его лучше закрепить в схеме.

Частичный уникальный индекс не даст создать второй draft даже при гонках, параллельных запросах и ошибках в приложении.

---

Пример: один основной адрес у пользователя

Ещё один понятный пример — адреса доставки.

Пользователь может иметь несколько адресов:

CREATE TABLE user_addresses (
    id         bigint PRIMARY KEY,
    user_id    bigint NOT NULL,
    address    text NOT NULL,
    is_default boolean NOT NULL DEFAULT false,
    created_at timestamptz NOT NULL DEFAULT now()
);

Адресов может быть сколько угодно, но адрес по умолчанию должен быть не больше одного.

Создадим индекс:

CREATE UNIQUE INDEX user_addresses_one_default
ON user_addresses (user_id)
WHERE is_default;

Теперь для одного пользователя нельзя создать два адреса с is_default = true.

Разрешено:

id	user_id	address	is_default
1	100	Bangkok, ...	true
2	100	Da Nang, ...	false
3	100	Almaty, ...	false

Не разрешено:

id	user_id	address	is_default
1	100	Bangkok, ...	true
2	100	Da Nang, ...	true

Вторая строка с is_default = true для того же user_id упадёт с ошибкой уникальности.

---

Важно: индекс гарантирует «не больше одного», а не «обязательно один»

Это важный момент.

Частичный уникальный индекс:

CREATE UNIQUE INDEX user_addresses_one_default
ON user_addresses (user_id)
WHERE is_default;

гарантирует:

У пользователя не может быть двух адресов по умолчанию.

Но он не гарантирует:

У пользователя обязательно должен быть адрес по умолчанию.

Вот такая ситуация индекс не нарушает:

id	user_id	address	is_default
1	100	Bangkok, ...	false
2	100	Da Nang, ...	false

Потому что строк с is_default = true ровно ноль. А ноль — это не больше одного.

Если бизнес-правило звучит как «должен быть ровно один адрес по умолчанию», одного частичного уникального индекса недостаточно.

Он решает только половину задачи:

не больше одного

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

---

Пример: один основной отдел у сотрудника

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

Лучше хранить это в отдельной таблице связей:

CREATE TABLE employee_departments (
    id          bigint PRIMARY KEY,
    employee_id bigint NOT NULL,
    dept        text NOT NULL,
    is_primary  boolean NOT NULL DEFAULT false
);

Теперь добавим правило:

CREATE UNIQUE INDEX employee_departments_one_primary
ON employee_departments (employee_id)
WHERE is_primary;

Оно означает:

Для одного employee_id может быть не больше одной строки,
где is_primary = true.

Разрешено:

id	employee_id	dept	is_primary
1	10	sales	true
2	10	ops	false
3	10	support	false

Не разрешено:

id	employee_id	dept	is_primary
1	10	sales	true
2	10	ops	true

Так мы переносим важное правило из приложения в базу.

---

Как выглядит ошибка при нарушении

Допустим, у нас уже есть индекс:

CREATE UNIQUE INDEX orders_one_draft_per_user
ON orders (user_id)
WHERE status = 'draft';

И в таблице уже есть строка:

INSERT INTO orders (id, user_id, amount, status)
VALUES (1, 100, 500.00, 'draft');

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

INSERT INTO orders (id, user_id, amount, status)
VALUES (2, 100, 700.00, 'draft');

PostgreSQL вернёт ошибку уникальности.

Смысл ошибки будет такой:

duplicate key value violates unique constraint/index

Потому что в частичном индексе уже есть строка с user_id = 100.

А вот такой заказ пройдёт:

INSERT INTO orders (id, user_id, amount, status)
VALUES (3, 100, 700.00, 'paid');

Почему?

Потому что status = 'paid' не подходит под условие индекса:

WHERE status = 'draft'

Значит, эта строка в индекс не попадает и уникальность по ней не проверяется.

---

Частичный индекс — это именно индекс, а не UNIQUE constraint

В PostgreSQL частичную уникальность нельзя объявить вот так:

ALTER TABLE users
ADD CONSTRAINT users_email_active_uniq
UNIQUE (email)
WHERE deleted_at IS NULL;

Такой синтаксис не сработает.

UNIQUE constraint в PostgreSQL не поддерживает WHERE.

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

CREATE UNIQUE INDEX users_email_active_uniq
ON users (email)
WHERE deleted_at IS NULL;

Это важное отличие.

Обычный уникальный constraint:

ALTER TABLE users
ADD CONSTRAINT users_email_unique UNIQUE (email);

и уникальный индекс:

CREATE UNIQUE INDEX users_email_active_uniq
ON users (email)
WHERE deleted_at IS NULL;

похожи по эффекту, но не полностью одинаковы по возможностям.

---

На частичный UNIQUE нельзя сослаться внешним ключом

Внешний ключ должен ссылаться на значение, которое уникально в родительской таблице целиком.

Например:

FOREIGN KEY (user_id)
REFERENCES users(id)

Работает, потому что users.id — это PRIMARY KEY.

Но частичный уникальный индекс уникален только для части строк.

Например:

CREATE UNIQUE INDEX users_email_active_uniq
ON users (email)
WHERE deleted_at IS NULL;

Он гарантирует уникальность email только среди активных пользователей.

Вне условия deleted_at IS NULL могут быть дубликаты.

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

Иначе база не могла бы гарантировать ссылочную целостность для всех строк.

---

ON CONFLICT и частичный уникальный индекс

Частичные уникальные индексы можно использовать с INSERT ... ON CONFLICT, но есть нюанс.

Если индекс частичный, нужно указать не только колонку, но и условие.

Например, у нас есть индекс:

CREATE UNIQUE INDEX users_email_active_uniq
ON users (email)
WHERE deleted_at IS NULL;

Тогда upsert для активного пользователя можно писать так:

INSERT INTO users (id, email, name)
VALUES (1, 'bob@example.com', 'Bob')
ON CONFLICT (email) WHERE deleted_at IS NULL
DO UPDATE
SET name = EXCLUDED.name;

Вот эта часть важна:

ON CONFLICT (email) WHERE deleted_at IS NULL

PostgreSQL должен понять, какой именно уникальный индекс использовать для поиска конфликта.

Если забыть условие, база может не сопоставить ON CONFLICT с частичным индексом.

---

NULL и уникальность

Уникальность в SQL имеет важный нюанс: NULL обычно не считается равным другому NULL.

Это значит, что обычный уникальный индекс может разрешить несколько строк с NULL в ключевой колонке.

Например:

CREATE UNIQUE INDEX users_phone_uniq
ON users (phone);

Если phone допускает NULL, то несколько пользователей с phone = NULL обычно не конфликтуют друг с другом.

Почему?

Потому что NULL означает «значение неизвестно», а не конкретное значение.

Для частичных уникальных индексов это тоже важно.

Например:

CREATE UNIQUE INDEX users_email_active_uniq
ON users (email)
WHERE deleted_at IS NULL;

Если email допускает NULL, то несколько активных пользователей с email = NULL могут не конфликтовать.

Обычно email делают NOT NULL, и проблема исчезает:

email text NOT NULL

Если же вам нужно считать NULL одинаковым значением, в PostgreSQL 15+ есть возможность использовать NULLS NOT DISTINCT:

CREATE UNIQUE INDEX users_phone_active_uniq
ON users (phone) NULLS NOT DISTINCT
WHERE deleted_at IS NULL;

Такой индекс будет считать NULL не «разными неизвестными значениями», а одним конфликтующим значением.

---

Предикат должен быть понятным и стабильным

Условие в частичном индексе должно быть выражением, которое PostgreSQL может безопасно использовать.

Хорошие условия:

WHERE deleted_at IS NULL

WHERE status = 'draft'

WHERE is_default

Плохая идея — пытаться использовать текущее время:

WHERE created_at > now() - interval '30 days'

Такой предикат нестабилен.

Сегодня строка подходит под условие, через месяц — уже нет. Но индекс не может сам «переосмысливать» строки просто потому, что время прошло.

Поэтому в предикате частичного индекса нельзя использовать изменчивые функции вроде now().

Думайте о предикате как о стабильном признаке строки:

удалена или нет;
активна или нет;
черновик или нет;
основная или нет.

---

Частичный UNIQUE и производительность

Частичный индекс полезен не только для уникальности, но и для скорости.

Он содержит не все строки таблицы, а только те, которые подходят под WHERE.

Например:

CREATE UNIQUE INDEX users_email_active_uniq
ON users (email)
WHERE deleted_at IS NULL;

Если в таблице много удалённых пользователей, но активных намного меньше, индекс будет компактнее обычного индекса по email.

Это даёт плюсы:

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

Запрос вида:

SELECT *
FROM users
WHERE email = 'bob@example.com'
  AND deleted_at IS NULL;

может использовать этот индекс.

Но запрос без условия:

SELECT *
FROM users
WHERE email = 'bob@example.com';

уже не так очевиден.

Почему?

Потому что индекс содержит только активные строки. А запрос без deleted_at IS NULL теоретически хочет найти и удалённых пользователей тоже.

PostgreSQL может использовать частичный индекс только тогда, когда понимает: условие запроса соответствует условию индекса.

Поэтому для пользы от частичного индекса запросы должны содержать совместимый фильтр.

---

Как добавить такой индекс на большой таблице

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

CREATE UNIQUE INDEX users_email_active_uniq
ON users (email)
WHERE deleted_at IS NULL;

На большой боевой таблице лучше использовать CONCURRENTLY:

CREATE UNIQUE INDEX CONCURRENTLY users_email_active_uniq
ON users (email)
WHERE deleted_at IS NULL;

CONCURRENTLY позволяет строить индекс аккуратнее для работающей базы, не блокируя обычные чтения и записи так жёстко, как обычное создание индекса.

Но есть нюансы:

• CREATE INDEX CONCURRENTLY нельзя запускать внутри обычной транзакции BEGIN ... COMMIT;
• операция может идти дольше;
• если в данных уже есть дубликаты среди строк, попадающих в условие, индекс не создастся.

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

Например, для активных email:

SELECT email, count(*)
FROM users
WHERE deleted_at IS NULL
GROUP BY email
HAVING count(*) > 1;

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

---

Как заранее найти конфликты для одной активной корзины

Перед созданием индекса:

CREATE UNIQUE INDEX CONCURRENTLY orders_one_draft_per_user
ON orders (user_id)
WHERE status = 'draft';

полезно проверить, есть ли пользователи с несколькими draft-заказами:

SELECT user_id, count(*)
FROM orders
WHERE status = 'draft'
GROUP BY user_id
HAVING count(*) > 1;

Если результат пустой — правило можно добавлять.

Если нет — нужно решить, какой черновик оставить активным, а какие перевести в другой статус или удалить.

Например:

user_id = 100, draft-заказов = 3

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

---

MySQL: как обойти отсутствие частичных индексов

В MySQL нет частичных индексов в стиле PostgreSQL:

CREATE UNIQUE INDEX ...
WHERE ...

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

Но похожее поведение часто делают через генерируемую колонку.

Идея такая:

Для активной строки в generated column кладём email.
Для удалённой строки кладём NULL.
Потом создаём обычный UNIQUE по этой generated column.

Пример:

CREATE TABLE users (
    id bigint PRIMARY KEY,
    email varchar(255) NOT NULL,
    deleted_at datetime NULL,

    email_active varchar(255)
        GENERATED ALWAYS AS (
            CASE
                WHEN deleted_at IS NULL THEN email
                ELSE NULL
            END
        ) STORED,

    UNIQUE KEY users_email_active_uniq (email_active)
);

Почему это работает?

Потому что несколько NULL в уникальном индексе обычно не конфликтуют.

Активный пользователь получает email_active = email, и email должен быть уникальным.

Удалённый пользователь получает email_active = NULL, и перестаёт мешать повторной регистрации.

Это не так красиво, как частичный индекс в PostgreSQL, но практическая идея похожая.

---

ClickHouse: уникальность не навязывается индексом

В ClickHouse другая модель.

Там PRIMARY KEY — это не такое же ограничение уникальности, как в PostgreSQL или MySQL.

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

Поэтому правило вроде:

только один активный пользователь с таким email

ClickHouse сам через уникальный индекс не обеспечит.

Обычно такие гарантии делают:

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

ClickHouse отлично подходит для аналитических сценариев, но строгие транзакционные ограничения уникальности — это не его основная роль.

---

Когда использовать частичный UNIQUE-индекс

Частичный уникальный индекс нужен, когда правило звучит так:

Значение должно быть уникальным только среди строк,
для которых выполняется определённое условие.

Хорошие примеры:

Правило	Индекс
Email уникален только среди активных пользователей	UNIQUE (email) WHERE deleted_at IS NULL
У пользователя может быть только одна корзина	UNIQUE (user_id) WHERE status = 'draft'
У пользователя может быть только один адрес по умолчанию	UNIQUE (user_id) WHERE is_default
У сотрудника может быть только один основной отдел	UNIQUE (employee_id) WHERE is_primary
У товара может быть только одна активная цена	UNIQUE (product_id) WHERE active

---

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

Обычный UNIQUE:

ALTER TABLE users
ADD CONSTRAINT users_email_unique UNIQUE (email);

означает:

email уникален во всей таблице

Частичный уникальный индекс:

CREATE UNIQUE INDEX users_email_active_uniq
ON users (email)
WHERE deleted_at IS NULL;

означает:

email уникален только среди активных пользователей

Для одной корзины:

CREATE UNIQUE INDEX orders_one_draft_per_user
ON orders (user_id)
WHERE status = 'draft';

Для одного адреса по умолчанию:

CREATE UNIQUE INDEX user_addresses_one_default
ON user_addresses (user_id)
WHERE is_default;

Для большой таблицы:

CREATE UNIQUE INDEX CONCURRENTLY users_email_active_uniq
ON users (email)
WHERE deleted_at IS NULL;

Для upsert:

INSERT INTO users (id, email, name)
VALUES (1, 'bob@example.com', 'Bob')
ON CONFLICT (email) WHERE deleted_at IS NULL
DO UPDATE
SET name = EXCLUDED.name;

---

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

Частичный уникальный индекс — это способ сказать базе:

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

Это очень полезно для реальных бизнес-правил:

• один активный аккаунт на email;
• одна открытая корзина на пользователя;
• один адрес по умолчанию;
• одна активная настройка;
• одна текущая версия записи.

Главная сила такого индекса в том, что он защищает данные на уровне базы.

Не в приложении, где можно забыть проверку.

Не в ручном скрипте, который кто-то обойдёт.

Не в надежде, что два запроса не придут одновременно.

А прямо в PostgreSQL.

Если правило звучит как:

уникально среди строк, где выполняется X

значит, перед вами хороший кандидат на частичный UNIQUE-индекс.