Дедупликация в PostgreSQL: чек-лист без потери данных

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

Перед чисткой определите, что считать дублем

Совпадение всей строки — редкий и обычно неверный критерий. Для заказов дубликатом может быть повторный внешний номер, для пользователей — нормализованный адрес электронной почты, а для событий — сочетание источника и идентификатора события. Выберите поля по бизнес-правилу, а не по тому, какие колонки случайно совпали в данных.

Ниже пример для таблицы contacts: записи с одинаковым адресом электронной почты считаются дублями без учёта регистра и пробелов по краям. В качестве примера предполагаются поля id с уникальным идентификатором, email и created_at. Если в вашей системе адреса различаются по смыслу или очистка пробелов недопустима, измените правило до запуска запросов.

• Решите, какие значения исключить: например, пустые адреса и NULL часто не объединяют.
• Определите, какую запись оставить: самую раннюю, последнюю, заполненную или связанную с нужным статусом.
• Проверьте внешние ключи, историю изменений и связи с другими таблицами: удаление строки может затронуть связанные данные.
• Сделайте резервную копию и сначала проверьте запросы на тестовой копии. Для большой таблицы оцените объём WAL, свободное место и время блокировок.

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

Сначала измерьте дубли

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

SELECT lower(btrim(email)) AS dedupe_key,
       count(*) AS rows_count
FROM contacts
WHERE email IS NOT NULL
  AND btrim(email) <> ''
GROUP BY lower(btrim(email))
HAVING count(*) > 1
ORDER BY rows_count DESC, dedupe_key;

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

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

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

В примере оставляем запись с самой ранней датой создания; если даты совпали или отсутствуют, приоритет получает меньший id. Замените сортировку, если сохранение более новой или более полной записи соответствует вашим данным лучше.

WITH ranked AS (
    SELECT ctid,
           row_number() OVER (
               PARTITION BY lower(btrim(email))
               ORDER BY created_at ASC NULLS LAST, id ASC
           ) AS row_number_in_group
    FROM contacts
    WHERE email IS NOT NULL
      AND btrim(email) <> ''
)
DELETE FROM contacts AS c
USING ranked AS r
WHERE c.ctid = r.ctid
  AND r.row_number_in_group > 1;

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

ctid — физический адрес версии строки внутри таблицы. В примере он используется только в рамках одного SQL-оператора, чтобы связать найденного кандидата на удаление с исходной строкой. Не сохраняйте ctid для последующих запусков и не используйте его как постоянный идентификатор: после изменений таблицы адрес может измениться.

Закрепите правило уникальным индексом

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

CREATE UNIQUE INDEX CONCURRENTLY contacts_email_key_uq
ON contacts (lower(btrim(email)))
WHERE email IS NOT NULL
  AND btrim(email) <> '';

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

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

Какой способ выбрать

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

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

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

• Неверный ключ. Объединение только по email может склеить разные записи, если адрес не уникален по вашему правилу. Проверьте модель данных и примеры групп до удаления.
• Нет стабильного порядка. Если сортировка не задаёт однозначного приоритета, сохранённая строка может оказаться случайной. Добавьте уникальный идентификатор последним критерием.
• Проверка в приложении считается защитой. Между проверкой и вставкой другой запрос может записать такой же ключ. Источник истины — ограничение на стороне базы.
• Чистка и индекс разделены записью. Новый дубль, появившийся после DELETE, не даст создать уникальный индекс. Продумайте окно обслуживания или блокирование конфликтующих записей.
• Удаление затрагивает связи. Строка может быть родительской для других таблиц. До операции выясните, настроены ли каскадное удаление, запрет удаления или перенос ссылок.

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

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