Чек-лист: мягкое удаление и уникальные индексы в PostgreSQL

После мягкого удаления пользователя бизнес обычно хочет разрешить регистрацию нового аккаунта с тем же email. Но обычный UNIQUE продолжает учитывать удалённую строку, а проверка только в приложении не защитит от двух параллельных запросов. Для PostgreSQL 16 надёжный вариант — частичный уникальный индекс по активным записям; перед продакшеном важно проверить миграцию, восстановление и точное правило сравнения значений.

Сначала зафиксируйте, что значит «удалён»

В примерах ниже активной считается строка с deleted_at IS NULL, а удалённой — строка с заполненным временем удаления. Это не просто способ скрыть запись из интерфейса: именно условие активности определяет, какие строки индекс обязан считать уникальными.

Для почты часто нужна уникальность без учёта регистра. В PostgreSQL 16 это можно задать индексом по выражению:

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

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

Теперь для активных пользователей значения Sam@example.com и sam@example.com конфликтуют, а удалённые строки в проверку не попадают. Если регистр важен для вашего идентификатора, используйте обычный индекс по email, а не lower(email). Пробелы и другие правила нормализации индекс сам не угадывает: задайте их явно и одинаково применяйте при записи и поиске.

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

Мягкое удаление не освобождает уникальное значение само по себе. Его освобождает только правило индекса, которое исключает удалённую строку.

Выберите способ, который действительно обеспечивает уникальность

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

Чек-лист перед выкладкой

1. Проверьте модель данных. Убедитесь, что активность везде означает одно и то же: например, только deleted_at IS NULL. Если часть кода использует флаг is_deleted, а другая — дату, зафиксируйте, какое поле является источником истины.
2. Найдите уже существующие дубликаты. До создания индекса выполните проверку ниже. Если запрос вернул строки, сначала разберите каждую группу и определите, какую запись оставить активной. Проверка по lower(email) должна совпадать с выражением индекса.
3. Добавьте индекс и проверьте его в окружении, близком к продакшену. Для тестовых данных повторите вставку с одинаковым email в разном регистре: вторая активная запись должна завершиться ошибкой уникальности. Удалённая запись с таким email не должна блокировать создание новой.
4. Проверьте все пути записи. Создание, импорт, API, фоновые задачи и админские операции должны обрабатывать конфликт от базы. Предварительный SELECT полезен для текста ошибки, но не заменяет индекс.
5. Пройдите сценарий восстановления. Если старую строку снова сделать активной, а email уже занят новой записью, обновление должно быть отклонено. Решите заранее, что увидит оператор или пользователь: отказ, смену email или ручное объединение данных.
6. Проверьте план миграции и отката. Создание индекса на большой таблице может влиять на запись и потребовать времени. Для индекса, создаваемого конкурентно, учитывайте ограничения PostgreSQL на запуск команды и предусмотрите проверку результата, если операция завершилась ошибкой.

Поиск конфликтов до миграции:

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

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

Что ломается на практике

Восстановление записи неожиданно падает

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

Индекс не создаётся из-за дубликатов

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

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

Запрос вида «есть ли уже такой email» не резервирует значение. Два процесса могут получить ответ «нет» до того, как любой из них выполнит INSERT. Обрабатывайте нарушение уникальности от PostgreSQL и преобразуйте его в ожидаемую ошибку домена. Именно база остаётся последней линией защиты.

Условие запроса не совпадает с условием индекса

Планировщик PostgreSQL использует частичный индекс, когда может доказать, что условие запроса подразумевает предикат индекса. Для поиска активных пользователей явно добавляйте deleted_at IS NULL; не рассчитывайте, что планировщик поймёт произвольное эквивалентное выражение или бизнес-логику из приложения.

Что настроить дальше

Перед выпуском проверьте сценарии создания двух активных записей, повторного использования значения после удаления и восстановления старой строки. В продакшене наблюдайте за ошибками уникальности и временем миграции. Если таблица большая, отрепетируйте создание индекса на копии с сопоставимым объёмом данных; для конкурентного создания изучите ограничения PostgreSQL 16 и предусмотрите проверку, не остался ли после сбоя недействительный индекс. Следующий отдельный вопрос — как именно приложение фильтрует удалённые строки: частичный индекс обеспечивает уникальность, но не скрывает записи из запросов автоматически.