Мягкое удаление и уникальные индексы: типичные ошибки

После мягкого удаления пользователя вы создаёте нового с тем же email — и получаете ошибку уникальности. Это ожидаемо: обычный уникальный индекс не знает, что означает поле deleted_at. В PostgreSQL 16 надёжный способ ограничить уникальность активными строками — частичный уникальный индекс, но он не решает автоматически конфликт при восстановлении записи.

Сначала определите, что значит «уникальный»

В примерах ниже email должен быть уникальным внутри арендатора (tenant), но только пока запись активна. Удалённая запись остаётся в таблице для истории, а значение email можно занять повторно. Если уникальность должна быть глобальной или учитывать другой набор полей, измените ключ индекса соответственно.

Термин: частичный индекс — индекс, в который попадают не все строки таблицы, а только те, что подходят под условие. Здесь условие простое: deleted_at IS NULL означает, что запись активна.

Для PostgreSQL 16 схема может выглядеть так:

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, lower(email))
    WHERE deleted_at IS NULL;

Индекс обеспечивает уникальность пары tenant_id и email без учёта регистра — только среди строк с deleted_at IS NULL. NOT NULL важен: по умолчанию PostgreSQL считает значения NULL различными в уникальном индексе. Если tenant_id или email могут быть NULL, вы можете получить несколько строк, которые бизнес-правило считает одинаковыми.

Три подхода и цена каждого

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

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

Где обычно ошибаются

• Оставляют старый UNIQUE. Если на email уже стоит обычное уникальное ограничение или индекс, добавление частичного индекса не отменит старое правило. При повторном использовании email старая запись всё равно будет блокировать вставку. Проверьте фактические ограничения таблицы перед миграцией.
• Проверяют уникальность запросом перед записью и останавливаются на этом. Между проверкой и INSERT другая транзакция может сохранить такое же значение. Уникальность должен обеспечивать индекс; приложение должно обработать отказ записи.
• Индексируют исходный email, хотя регистр не важен. Уникальный индекс по email разрешит Alice@example.com и alice@example.com как разные значения. Выражение lower(email) задаёт нечувствительное к регистру сравнение, но правила регистра и Unicode-нормализации стоит согласовать с продуктовой логикой.
• Не проверяют восстановление. Удалённую запись можно восстановить только если её ключ снова свободен. После повторного использования email обновление deleted_at в NULL нарушит индекс.
• Меняют условие индекса, забывая о запросах. Если индекс включает только deleted_at IS NULL, фильтры запросов должны отражать это условие, когда вы рассчитываете на индекс для поиска активных записей.

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

Удаление обычно выглядит как обновление поля:

UPDATE accounts
SET deleted_at = CURRENT_TIMESTAMP
WHERE id = 42;

Чтобы восстановить запись, приложение может выполнить обратное обновление:

UPDATE accounts
SET deleted_at = NULL
WHERE id = 42;

Если активная запись с тем же tenant_id и email уже существует, PostgreSQL отклонит операцию: обычно приложение получит ошибку вида duplicate key value violates unique constraint или unique index, с SQLSTATE 23505. Не превращайте её в общую ошибку сервера. Покажите понятный конфликт и предложите допустимое действие: оставить запись удалённой, изменить email или пройти предусмотренное бизнес-правилами объединение.

Проверка перед восстановлением может улучшить сообщение, но сама по себе не защищает от гонки: другая транзакция способна занять email сразу после проверки. Поэтому окончательное решение всё равно принимает уникальный индекс, а приложение должно корректно обработать его отказ.

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

Когда частичный индекс не подходит

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

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

Что проверить перед выпуском

1. Зафиксируйте правило: какие поля уникальны, в каком объёме — глобально или внутри tenant — и считаются ли заглавные буквы значимыми.
2. Проверьте существующие дубликаты среди активных строк. Если они есть, индекс не создастся, пока вы не разберёте конфликт.
3. Убедитесь, что старый обычный уникальный индекс или ограничение не продолжает блокировать повторное использование значения.
4. Создайте частичный индекс с условием, соответствующим признаку активной записи, и проверьте миграцию на копии данных.
5. Проверьте три сценария: повторное создание после удаления, две конкурентные попытки занять значение и восстановление старой строки после повторного использования значения.

Дальше измеряйте не только скорость запросов, но и частоту конфликтов уникальности: она покажет, где пользователи чаще всего пытаются восстановить занятую запись. Если таблица большая, отдельно изучите порядок создания индекса и блокировки для вашей миграции — это зависит от размера и нагрузки конкретной базы.