Мягкое удаление и уникальные индексы: как избежать конфликтов

Уникальность должна действовать только там, где это нужно

В PostgreSQL 16 мягкое удаление часто устроено просто: приложение проставляет записи время в поле deleted_at, а строка остаётся в таблице. Но обычный уникальный индекс не знает о смысле этого значения. Если у удалённого пользователя остался адрес team@example.com, создать нового пользователя с тем же адресом не получится — база считает, что он уже занят.

Для правила «уникально среди активных записей» нужен частичный уникальный индекс: он проверяет только строки, у которых deleted_at IS NULL. Ниже — практический разбор для PostgreSQL 16: как создать индекс, подготовить существующие данные и учесть восстановление удалённой записи.

Сначала задайте правило словами

Возьмём таблицу аккаунтов, где один адрес должен встречаться не более одного раза в рамках арендатора, но удалённый аккаунт не должен мешать регистрации нового. Это не то же самое, что глобальная уникальность адреса: ключ здесь составной — tenant_id и email.

Частичный индекс — индекс, в который попадают только строки, удовлетворяющие условию WHERE. В нашем случае активная строка — та, у которой дата удаления не задана.

Удаление строки из интерфейса и освобождение её уникального ключа — разные операции. Второе нужно явно закрепить правилом в базе.

Для новой таблицы схема и индекс могут выглядеть так:

CREATE TABLE accounts (
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    tenant_id bigint NOT NULL,
    email text NOT NULL,
    deleted_at timestamptz
);

CREATE UNIQUE INDEX accounts_active_email_uq
    ON accounts (tenant_id, email)
    WHERE deleted_at IS NULL;

Индекс не позволяет создать два активных аккаунта с одинаковой парой tenant_id, email. Удалённые строки в проверке не участвуют. Если правило должно быть нечувствительным к регистру, сначала отдельно определите это требование: индекс по email и индекс по выражению lower(email) задают разные правила равенства. Запросы приложения должны соответствовать выбранному правилу.

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

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

Для распространённой схемы с deleted_at частичный индекс обычно проще и точнее. Важно: если ключевой столбец допускает NULL, отдельно решите, должны ли несколько активных строк без значения считаться дублями. Обычный уникальный индекс PostgreSQL допускает несколько значений NULL; сделайте ключ обязательным или явно задайте требуемую семантику.

Как внедрить индекс в существующую таблицу

Нельзя рассчитывать, что индекс сам разберётся с уже накопившимися дублями. Сначала проверьте активные строки. Для примера предполагается, что tenant_id и email обязательны:

SELECT tenant_id, email, count(*) AS row_count
FROM accounts
WHERE deleted_at IS NULL
GROUP BY tenant_id, email
HAVING count(*) > 1;

Если запрос вернул строки, сначала решите, какую запись оставить активной, а что сделать с остальными. Не удаляйте дубли автоматически по принципу «оставить первую»: порядок может быть произвольным, а связанные данные — важнее даты создания. После исправления повторите проверку и только затем создавайте индекс:

CREATE UNIQUE INDEX accounts_active_email_uq
    ON accounts (tenant_id, email)
    WHERE deleted_at IS NULL;

На большой таблице создание индекса может мешать записи. PostgreSQL поддерживает вариант CREATE UNIQUE INDEX CONCURRENTLY, который снижает блокировки, но у него есть ограничения: такую команду нельзя выполнять внутри обычного блока транзакции. Проверьте, запускает ли ваша система миграций каждый шаг в транзакции. Если конкурентное построение завершилось ошибкой, проверьте состояние индекса перед повторной попыткой: неуспешная операция может оставить невалидный индекс.

У частичного уникального индекса есть ещё одно практическое отличие: это индекс, а не обычное ограничение таблицы, объявленное через UNIQUE. Если приложение использует механизм вставки с обработкой конфликта, проверьте документацию PostgreSQL для нужной формы команды и версию вашего ORM. Не подменяйте частичный индекс уникальным ограничением только потому, что ORM удобнее с ним работает.

Восстановление — отдельный сценарий конфликта

После удаления старого аккаунта с адресом team@example.com можно создать новый активный аккаунт с тем же адресом. Но при попытке восстановить старую запись обе строки окажутся активными, и частичный индекс не позволит выполнить обновление.

Это не ошибка индекса, а неразрешённая бизнес-ситуация. Выберите поведение заранее:

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

Проверка в приложении перед восстановлением полезна для понятного сообщения, но сама по себе не защищает от гонки: между проверкой и обновлением другой запрос может занять ключ. Решающее слово остаётся за индексом. Обрабатывайте конфликт уникальности, в PostgreSQL он обозначается SQLSTATE 23505, и возвращайте пользователю предусмотренный сценарий, а не общую ошибку сервера.

Что ломается чаще всего

• Фильтр приложения и индекс расходятся. Приложение считает запись активной по булевому флагу, а индекс — по deleted_at. Зафиксируйте одно правило и используйте его во всех операциях.
• Проверили только регистрацию. Удаление, восстановление, импорт и пакетное обновление тоже могут столкнуться с уникальностью. Проверьте каждый путь, который меняет ключ или статус записи.
• Индекс создали до очистки дублей. Построение завершится ошибкой, потому что среди активных строк уже нарушено правило. Сначала аудит данных, затем исправление, потом индекс.
• Забыли про область уникальности. Если адрес должен быть уникален во всём сервисе, включение tenant_id в индекс позволит одному адресу встретиться у разных арендаторов. Состав ключа должен повторять продуктовый инвариант.
• Приложение рассчитывает на имя ограничения. Частичный индекс нельзя бездумно использовать так же, как ограничение UNIQUE. Проверьте, как ваш ORM строит запросы для вставки и миграции.

Когда что выбирать

Если удалённая запись должна освобождать ключ, а история остаётся в той же таблице, выбирайте уникальный частичный индекс с условием активного состояния. Если ключ должен оставаться занятым даже после удаления, используйте обычную уникальность и не включайте удалённые строки в повторное использование ключа. Разделение на активную и архивную таблицы имеет смысл, когда для него есть отдельная причина — например, иной жизненный цикл данных, — но не как обход одного индекса.

Для PostgreSQL 16 проверьте три части правила: состав ключа, точное условие активной записи и допустимость NULL в ключевых полях. Затем прогоните тесты не только на повторную регистрацию, но и на восстановление, параллельные запросы и импорт. Инвалидация кэша и хранение истории изменений — отдельные темы; здесь речь только об уникальности строк в таблице.

Что сделать дальше

Начните с запроса на поиск активных дублей и запишите правило уникальности в одном предложении: например, «адрес уникален среди активных аккаунтов внутри арендатора». После этого добавьте частичный индекс в миграцию, проверьте её на копии данных и измерьте длительность построения на объёме, близком к рабочему. В проде отдельно наблюдайте за ошибками уникальности при восстановлении и импорте: они покажут, где приложение ещё не объясняет конфликт пользователю.