Сменили collation — и уникальные индексы перестали быть уникальными

MAATRIX

Уникальный индекс на email существовал в таблице пользователей три года и ни разу не подвёл — приложение честно ловило duplicate key value violates unique constraint при попытке завести вторую учётку на тот же адрес. А потом саппорт начал получать тикеты про два личных кабинета с одинаковым email, и SELECT ... GROUP BY email HAVING count(*) > 1 подтверждал: дубли реально лежат в базе, при живом и валидном уникальном индексе. Разбираемся, как плановое обновление пакетов на сервере тихо испортило сортировку в PostgreSQL и почему constraint при этом ни разу не упал с ошибкой.

Что сломалось: индекс есть, constraint валиден, а дубли в таблице реальны

Схема была обычной: таблица users, колонка email типа text, на ней UNIQUE INDEX users_email_idx. Регистрация шла через INSERT ... ON CONFLICT (email) DO NOTHING, а не через «проверить, потом вставить» — то есть классическую гонку между двумя запросами разработчики закрыли правильно, атомарно, на уровне БД.

Тикет от саппорта выглядел странно: пользователь жаловался, что у него два аккаунта с одинаковым email и разными паролями, и оба открываются по одному и тому же адресу при логине то в один, то в другой — в зависимости от того, какая строка попадётся первой при поиске. Первая реакция была «это баг в форме регистрации, где-то теряется проверка». Проверили код — вставка шла ровно через ON CONFLICT, без обходных путей.

Тогда посмотрели прямо в базу:

SELECT email, count(*) 
FROM users 
GROUP BY email 
HAVING count(*) > 1;

Запрос вернул несколько строк с одинаковым email и разными id. При этом:

\d users
-- ...
-- "users_email_idx" UNIQUE, btree (email)

Индекс на месте, помечен как UNIQUE, pg_index.indisvalid = true. Попытка вручную вставить ещё одну строку с уже существующим email тут же падала с ожидаемой ошибкой — то есть constraint на текущий момент работал исправно. Получалось противоречие: сейчас уникальность соблюдается железно, а в таблице лежат строки, которые эту уникальность явно нарушают. Дубли не появлялись «только что» — они уже сидели в данных, и вопрос был, когда и как они туда попали, раз система их не пропускала бы сегодня.

Первые следы: что показывали логи и метрики

Первым делом подняли логи PostgreSQL за период, когда предположительно появились дубли (по created_at вторых аккаунтов это укладывалось в окно примерно в пару недель). В логах не было ни одной строки про duplicate key value violates unique constraint за это время — то есть ни один INSERT не был отклонён по этому индексу, хотя по факту в таблицу попали две строки с одинаковым значением ключевой колонки.

Посмотрели pg_stat_user_indexes — по индексу шли обычные idx_scan, ничего похожего на аномалию, никаких предупреждений о bloat выше типичного для этой таблицы. VACUUM и ANALYZE по расписанию отрабатывали штатно, автовакуум не жаловался. Проверили размер и структуру индекса через pgstattuple — индекс не выглядел повреждённым в смысле «страницы битые» или «I/O ошибки»: чтение и запись в него проходили без сбоев на уровне диска.

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

WARNING:  database "app" has a collation version mismatch
DETAIL:  The database was created using collation version 2.31, but the operating system provides version 2.35.
HINT:  Rebuild all objects affected by this database or collation change and run ALTER DATABASE app REFRESH COLLATION VERSION.

Рестарт кластера был плановым — после обновления пакетов на сервере, включая libc6. Это обновление никто не связывал с базой: patch-цикл ОС шёл по своему расписанию, а PostgreSQL просто «перезапустился и работает».

Гипотеза номер один: гонка в приложении — и почему она не подтвердилась

Первая версия — классическая гонка между двумя параллельными запросами регистрации до того, как индекс успел зафиксировать первую вставку. Это самая частая причина дублей «несмотря на unique constraint» в реальной практике, поэтому с неё и начали.

Версию закрыли быстро: во-первых, вставка шла через INSERT ... ON CONFLICT (email) DO NOTHING в одной транзакции — PostgreSQL гарантирует атомарность такой проверки на уровне индекса, гонка тут структурно невозможна, если сам индекс работает корректно. Во-вторых, попробовали руками воспроизвести гонку — запустили два параллельных инсерта с одинаковым email через pgbench-скрипт с явной синхронизацией по времени. Один инсерт неизменно проходил, второй неизменно падал в ON CONFLICT ветку — то есть сегодня, на актуальном индексе, гонки не было вообще. Раз constraint ловит дубль прямо сейчас при явном стресс-тесте, значит дело не в архитектуре кода регистрации.

Гипотеза номер два: расхождение приложения и базы — тоже мимо

Вторая версия — что где-то в коде есть путь мимо ORM, который пишет напрямую через отдельное соединение с другими настройками, например через batch-скрипт импорта пользователей из старой системы или через админскую консоль, где кто-то мог выполнить INSERT без ON CONFLICT. Проверили все места в кодовой базе, которые пишут в таблицу users — их оказалось три: форма регистрации, приём вебхука от партнёра при связке аккаунтов и разовый скрипт миграции пользователей, который выполнялся задолго до появления найденных дублей.

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

Настоящая причина: glibc обновился, а индекс — нет

Возвращаемся к предупреждению collation version mismatch. PostgreSQL по умолчанию (без ICU) использует для сортировки текста локали операционной системы — то есть функции glibc, которые определяют, что «больше», что «меньше» и что «равно» для двух строк. Уникальный btree-индекс на текстовой колонке физически хранит строки отсортированными именно по этому правилу сравнения.

Проблема в том, что мажорные обновления glibc иногда меняют порядок сортировки для конкретных локалей — это не баг конкретно вашей системы, а исторически известное поведение: например, при переходе с glibc версии 20.04 на версию из 22.04 в Ubuntu (2.31 → 2.35) для ряда локалей менялись правила сортировки некоторых символов и их сочетаний. PostgreSQL при этом ничего не знает о том, что «правила сравнения под капотом» поменялись — версия ОС для него прозрачна, пока не включена явная проверка collation version.

Смысл проблемы: индекс был физически построен (отсортирован) по правилам старой версии glibc. После обновления пакетов PostgreSQL продолжает пользоваться тем же индексом, но при поиске позиции для новой строки использует уже новую версию функции сравнения. Если для конкретной пары значений старое и новое правило сравнения расходятся местами (что бывает не для всех строк подряд, а именно для отдельных проблемных сочетаний символов), то бинарный поиск по дереву индекса может пойти не по той ветке — и не найти уже существующую строку, хотя формально она там есть. ON CONFLICT в этом случае не срабатывает не потому, что он сломан, а потому, что сам механизм поиска совпадения в индексе даёт неверный ответ «такого значения ещё нет».

Проверили гипотезу так:

SELECT datname, datcollate, datcollversion
FROM pg_database
WHERE datname = 'app';

datcollversion действительно отставал от той версии, которую сейчас реально отдаёт система (ldd --version и dpkg -l libc6 на сервере показывали более свежую сборку, чем была на момент создания базы). Дальше нашли конкретные email из дублей и вручную сравнили их через strcoll()-подобную проверку сортировки на новом и на предыдущем glibc в отдельном контейнере со старой версией пакета — и для части проблемных строк порядок сравнения действительно расходился. Именно эти строки и оказались среди дублей.

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

Что сделали: пересобрали индексы, ушли от системной локали и закрыли дыру в процессе

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

REINDEX INDEX CONCURRENTLY users_email_idx;

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

ALTER DATABASE app REFRESH COLLATION VERSION;

Эта команда не чинит уже испорченные индексы сама по себе — она лишь обновляет метаданные о версии collation, чтобы PostgreSQL перестал считать текущую версию устаревшей. Сама пересборка данных всё равно нужна через REINDEX.

Дальше — ручная чистка уже накопленных дублей: для каждой пары решали вручную (через саппорт, с уведомлением пользователей), какую учётку оставить основной, а какую — слить или переименовать email с пометкой. Автоматическое «оставить самую раннюю по created_at» не годилось само по себе, потому что в паре могла быть активная учётка с историей заказов у обеих сторон — тут без ручной проверки было не обойтись.

Стратегически ушли от зависимости от системной локали для колонок, где важна именно уникальность идентификаторов, а не «человеческая» сортировка. Для email, username и подобных полей сравнение по алфавиту разных языков не нужно — важна предсказуемость и байтовая стабильность:

ALTER TABLE users 
  ALTER COLUMN email TYPE text COLLATE "C";

Collation C — это побайтовое сравнение, оно не зависит от локалей ОС и не меняется при обновлении glibc, потому что не использует его функции сортировки вовсе. Для полей, где реально нужна лингвистическая сортировка (например, имена в интерфейсе для алфавитной сортировки списка), оставили ICU-коллации (CREATE COLLATION ... (provider = icu, locale = '...')) — версия ICU фиксируется явно в самой коллации и не подтягивается автоматически при апдейте пакетов ОС, в отличие от системной glibc-локали.

На уровне процесса добавили две вещи. Во-первых, в чек-лист перед плановым обновлением пакетов ОС на серверах с PostgreSQL добавили пункт: если апдейт затрагивает libc6 — после рестарта проверить pg_database.datcollversion на всех базах и при расхождении сразу планировать REINDEX CONCURRENTLY по всем btree-индексам на текстовых колонках, а не только на «подозрительных». Во-вторых, поставили простой еженедельный запрос-канарейку, который сверяет datcollversion с фактической версией glibc в системе и шлёт алерт при расхождении — это дешевле, чем ловить дубли постфактум через тикеты саппорта.

Отдельно проговорили с командой инфраструктуры: автоматические security-обновления (unattended-upgrades) на серверах с базами данных не должны молча тянуть libc6 без последующей ручной проверки collation. Это не значит «выключить обновления безопасности» — значит добавить шаг после них, а не полагаться на то, что PostgreSQL сам предупредит достаточно громко, чтобы предупреждение не потерялось в потоке обычных логов рестарта.

Частые вопросы