Дедупликация записей в PostgreSQL: алгоритмы и практика

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

Сначала определите, что считать дублем

Алгоритм не знает, что два профиля принадлежат одному клиенту. Он применяет критерий, который вы задали. Совпадение номера заказа может означать повторную загрузку одной строки, а совпадение email — общий семейный адрес. У разных сущностей разные правила.

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

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

Дедупликация — не поиск похожих строк. Это решение о том, какие различия в данных допустимы, а какие означают разные сущности.

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

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

Найти и удалить дубли по email

Пример ниже рассчитан на PostgreSQL 16 и таблицу customer_records со столбцами id, email и updated_at. Правило намеренно простое: сравниваем email без пробелов по краям и без различия регистра. Пустые и отсутствующие адреса не объединяем. Перед удалением посмотрите, какие строки попадут в группу и какую запись запрос оставит.

WITH ranked AS (
    SELECT
        id,
        lower(btrim(email)) AS normalized_email,
        row_number() OVER (
            PARTITION BY lower(btrim(email))
            ORDER BY updated_at DESC NULLS LAST, id DESC
        ) AS duplicate_rank
    FROM customer_records
    WHERE email IS NOT NULL
      AND btrim(email) <> ''
)
SELECT id, normalized_email, duplicate_rank
FROM ranked
WHERE duplicate_rank > 1
ORDER BY normalized_email, duplicate_rank;

Оконная функция row_number() нумерует строки внутри каждой группы. Здесь запись с самой поздней датой получает номер 1; если даты одинаковы или отсутствуют, решает больший id. Это правило нужно согласовать с владельцами данных: «оставить самую новую» не всегда значит «оставить самую полную».

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

BEGIN;

WITH ranked AS (
    SELECT
        id,
        row_number() OVER (
            PARTITION BY lower(btrim(email))
            ORDER BY updated_at DESC NULLS LAST, id DESC
        ) AS duplicate_rank
    FROM customer_records
    WHERE email IS NOT NULL
      AND btrim(email) <> ''
)
DELETE FROM customer_records AS target
USING ranked
WHERE target.id = ranked.id
  AND ranked.duplicate_rank > 1;

-- Проверьте число удалённых строк и состояние данных до COMMIT.
-- Если результат неверный, выполните ROLLBACK вместо COMMIT.
COMMIT;

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

Нечёткое сравнение: кандидаты, а не приговор

Опечатки в именах и адресах требуют другого алгоритма. Расстояние редактирования оценивает, сколько изменений нужно, чтобы превратить одну строку в другую; триграммное сравнение оценивает близость текстовых фрагментов. В PostgreSQL для триграмм есть расширение pg_trgm, но один порог сходства не превращает похожие имена в доказательство, что это один клиент.

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

Когда выбирать каждый подход

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

В PostgreSQL 16 после очистки можно ограничить повторное появление дублей уникальным индексом по выражению, если правило действительно должно быть общим для всех строк:

CREATE UNIQUE INDEX customer_records_email_normalized_uq
ON customer_records (lower(btrim(email)))
WHERE email IS NOT NULL
  AND btrim(email) <> '';

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

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

• Склеиваются пустые значения. Если не исключить пустые строки, одинаковые пустые email окажутся одной группой. Для идентификатора без значения это обычно не дубль.
• Выбрана не та запись. Самая свежая строка может быть неполной, а дата обновления — отражать технический импорт, а не качество профиля. Проверьте правило выбора победителя и план слияния связанных данных.
• Два процесса создают дубль одновременно. Проверка перед вставкой сама по себе не гарантирует уникальность при параллельных запросах. Если ключ уникален по смыслу, закрепите это ограничением или индексом и обработайте конфликт вставки в приложении.
• Нормализация зависит от локали. Приведение регистра и сравнение текста могут зависеть от правил базы и выбранной сортировки. Проверьте результат на реальных языках и символах, прежде чем считать выражение универсальным.
• Удаление блокирует рабочую нагрузку. Большая чистка может занять время и повлиять на параллельные запросы. Измерьте план через EXPLAIN и тестируйте на копии данных; для крупной таблицы разделите работу на контролируемые порции и следите за блокировками и журналом транзакций.

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

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

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

Инвалидация кеша и построение полноценного процесса слияния сущностей — отдельные задачи; здесь речь только о критериях поиска дублей в PostgreSQL 16.