Сверка таблиц в PostgreSQL, или О том, нужны ли для этого специальные утилиты

Лет десять назад я делал инструмент сверки таблиц для мультимастера. И делал это крайне неэффективным способом: брал снапшот и под ним просто шёл по таблице сверяя строки. Для того времени и размеров баз данных это было ОК. Однако с тех пор задача видоизменилась и стала только актуальнее: логическая репликация PostgreSQL развивается, одни и те же данные всё чаще живут в разных дата-центрах, на разных инстансах с надёжным failover’ом, и рано или поздно приходится задумываться о проверке их идентичности.

Готовые инструменты для сравнения таблиц в мире Postgres есть — pg_comparator, pgCompare, ACE, и т.д. Однако давайте сегодня поразмышляем, стоит ли ради большей эффективности городить огород с дополнительным софтом. Может быть, хватит простого golang-скрипта, выполняющего SQL на обоих инстансах?

В статье Fabien Coelho, DBKDA’2011 необходимость такой технологии обосновывается нагрузкой на сеть: при простом сравнении по сети уходит двойной объём данных. Это, в общем-то, верно. Однако при современных скоростях сети ещё большой вопрос, что обходится дороже: передать данные или нагрузить процессор сериализацией, вычислением хэш-сумм (типовая техника инструментов сравнения) и спиллингом на диск.

Соберём методику

Итак, наша задача — сравнить две таблицы на разных серверах.

Ограничимся простым и понятным (с точки зрения реляционной теории) кейсом: у обеих таблиц одинаковый primary key.

Тогда простейший алгоритм сравнения — потоковое слияние строк из двух источников, а сортировку обеспечивает сам primary key. Первая задача — выставить одинаковые настройки сессий, чтобы обе стороны одинаково представляли данные.

Какие именно? Для корректной сериализации строк на обеих сторонах должны совпадать TimeZone, DateStyle, IntervalStyle, bytea_output, extra_float_digits, quote_all_identifiers, client_encoding и, на всякий пожарный, search_path.

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

Порядок колонок в реляционной СУБД не гарантирован, поэтому перед сравнением нужно составить точный список колонок и их типов для каждой таблицы. Более того, логическая репликация Postgres позволяет ограничить реплицируемые атрибуты, ввести фильтр строк и даже реплицировать в другой (но совместимый) тип: например, timestamp в timestamptz.

Отдельный момент — COLLATION первичного ключа. Он должен совпадать у обеих таблиц, иначе мы рискуем сравнивать не то. Можно было бы, конечно, задать правило вручную — ORDER BY <PK> COLLATE "C", — но тогда запрос рискует свалиться в явную сортировку, а для гигабайтных таблиц это малореально. Поэтому будем просто предупреждать, если COLLATION не совпадает.

Есть и менее очевидный источник расхождений — версии glibc и ICU. Два инстанса, скорее всего, живут в разных зонах и обновляются по разным графикам, так что версии системных библиотек у них вполне могут разойтись. К REINDEX после обновления библиотек мы уже как-то привыкли, а вот следят ли за версиями на разных серверах — не факт. Проверять будем так (linux only):

SELECT DISTINCT pg_collation_actual_version(oid) AS glibc
FROM pg_collation
WHERE collprovider = 'c'
  AND pg_collation_actual_version(oid) IS NOT NULL;

 glibc
-------
 2.42

SELECT icu_unicode_version();

 icu_unicode_version
---------------------
 16.0

Опытные сервероведы сразу скажут, что такая проверка не даёт 100 % гарантии, но мы здесь за другим и просто обозначили проблему.

Блоки и хэширование

Упомянув слияние по первичному ключу, я тут же поймал себя на мысли, что внешнему по отношению к СУБД инструменту реализовать эту операцию будет непросто. Во-первых, никто не гарантирует, что оператор сравнения для данного типа в СУБД ведёт себя так же, как его аналог в том же Golang. Если же сливать по строковому представлению, легко попасть в неоднозначности вроде -0.0 и 0.0 для float или Infinity для timestamp. Если преобразовывать значения ключа во внутреннее представление языка, то ещё хуже — даже если тип формально поддерживается, можно нарваться на потерю точности при преобразовании numeric → float64 или семантики timestamp → time.Time. А что делать с кастомными типами в качестве ключа?

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

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

Алгоритм

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

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

Порядок действий:

1. На входе минимум: DSN1 и DSN2 (строки подключения к инстансам), имя таблицы и список полей для сравнения. Дополнительно на вход может быть подан replication filter.

2. Выполняем согласование структуры таблиц, формируем SQL-запрос, который будет отправлен на каждый инстанс.

3. Отправляем запрос на оба инстанса в асинхронном режиме.

4. Стандартным алгоритмом слияния проходим по PRIMARY KEY по всем строкам обеих таблиц.

Результат пишется в файл: либо match, либо по строке на каждое расхождение в формате <значение ключа> <строка таблицы 1> <строка таблицы 2>. Если строки нет в одной из таблиц, на её месте пишем NULL.

А что, если ключ произвольного типа, например numeric или text, и при переносе в типы выбранного языка программирования значения могут исказиться?

Первый ответ — перенести код внутрь PostgreSQL, то есть сделать расширение, а этого мы пока как раз пытаемся избежать. Другой вариант — написать собственный тип. Так себе идея: предсказать появление новых кастомных типов и их поведение не так-то просто. Сравнивать строковое представление, полученное от сервера, тоже нельзя: нюансы вылезут даже в простом случае вроде '10' < '9'. Остаётся бинарная кодировка (memcomparable), по аналогии с SQLite. Но это тоже — то ещё развлечение, скорее на любителя теории баз данных.

Поэтому вторым этапом реализуем поблочное сравнение таблиц, как описано выше.

Замечание про параллелизм. В этой задаче — простая агрегация по каждой строке большой таблицы с доступом по диапазонам PRIMARY KEY — параллелизм удобен и эффективен, что бывает редко. Основной нюанс — выбор границ для параллельных задач. Здесь мы выбираем диапазоны по гистограмме. Однако при протухшей статистике или составном PRIMARY KEY этот приём работает уже не так уверенно. Возможно, стоит заранее «нарезать» диапазоны и даже хранить нарезку где-то постоянно. Сделать это можно разными способами — например, через TABLESAMPLE или сохранив границы блоков от предыдущей операции.

Получившийся код можно посмотреть здесь.

Эксперименты

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

Итак, три варианта таблицы по 100 млн строк каждая:

1. «узкая» (ширина строки ~80 B, 8 ГБ);

2. «типовая» (ширина строки ~500 B, 50 ГБ);

3. «широкая» (ширина строки ~3.3 kB, 10 ГБ heap, 320 ГБ TOAST).

Ключ во всех трёх таблицах самый простой — bigint, но строки перемешаны так, чтобы значение ключа не коррелировало с номером страницы на диске.

Два инстанса n2-highmem-16 с БД и инстанс c4-standard-32 компаратора — все в одной зоне. Между таблицами десять расхождений — чтобы убедиться, что инструменты действительно их находят.

Измеряем время выполнения, трафик до клиента и объемы чтения данных с диска для широкой таблицы.

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

Второе: без побочной нагрузки параллелизм действительно эффективен. Ускорение хорошо видно на нашем самописном скрипте, и его точно стоит требовать от промышленных инструментов. Замечу, что pgCompare и pg_comparator в наших экспериментах читали таблицы последовательно.

У ACE и pgcmp запас по параллелизму внутри запроса, скорее всего, меньше — они используют IndexScan. Однако при достаточно широких строках и крупных блоках запрос можно попробовать переписать под Parallel Append.

Наш примитивный pgcmp гоняет по сети больше всех данных и сильно зависит от ширины строки. Однако простота и параллелизм держат его производительность в разумных пределах. Трафик pg_comparator’а зависит только от числа различающихся строк, а трафик pgCompare — только от числа строк в таблице.

Ещё одно интересное наблюдение — pgcmp и ACE, проходя таблицу в порядке следования ключа, а не страниц на диске, в итоге читают с диска в 4–7 раз больше, чем занимают сами данные. При этом есть приличная разница между блочным (ACE) и непрерывным (pgcmp) сканированием, что я могу объяснить разве что особенностями механизма readahead ядра ОС.

Понятно, что пересылка строк вместо хэшей требует хорошего канала. Ограничим пропускную способность и посмотрим, что получится (pgcmp, 16 потоков, время в секундах):

У остальных инструментов эффект сети уложился в 1–3 % времени выполнения.

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

(1) — различия распределены равномерно по таблице; (2) — различия расположены сплошным блоком в начале таблицы.

Самыми устойчивыми к расхождениям оказались pgcmp и pgCompare. pg_comparator держится достаточно хорошо для практически значимой доли строк — 0.1 %. ACE реагирует сильнее всех: уже при 0.0075 % равномерно разбросанных расхождений его время вырастает с 25.5 до 205.8 с. Видимо, дело в том, что при расхождении ему приходится скачивать весь блок и сверять строки попарно.

Вероятно, всему виной его алгоритм поиска несоответствующих строк. Подозреваю, что можно попробовать что-то вроде следующего: на несовпавшем блоке строить hash map по <primary key><hash> одной “опорной” таблицы, а потом матчить соответствующий блок второй таблицы и скачивать на клиента только несовпавшие строки.

Находки

Судя по испытаниям, потенциал оптимизации у изученных инструментов ещё далеко не исчерпан. Во-первых, сам механизм хэширования. Построковый хэш заведомо проиграет агрегату с промежуточным состоянием: агрегат избавляет от инициализации состояния на каждой строке и вдобавок позволяет использовать параллелизм там, где это возможно. Во-вторых, лишняя сериализация бинарных значений в текст — особенно ROW(..)::text — универсальна, но на каждой строке обходится весьма дорого. Если хэширующий агрегат принимает VARIADIC-аргументы, большую часть лишних сериализаций — например, для bigint или float — можно убрать.

Также Я заметил, что pgCompare пропускает крайние случаи в представлении данных — например, для NULL и float. Видимо, это плата за универсальность: он сравнивает данные между разными СУБД.

Итого

Итак, что даёт специализированный инструмент по сравнению с простым скриптом? Судя по замерам, это в первую очередь экономия сети и параллелизм:

1. Память под контролем, трафик — не всегда. Хэш строки или блок фиксированного размера держит потребление памяти ровным. На широкой таблице pgcmp прокачал 642 ГБ, ACE — 0,3 ГБ, pg_comparator — практически ноль. На гигабитном канале скрипт упирается в сеть и проигрывает всем трём инструментам.

2. Параллелизм. Параллелизм здесь даёт очень много: pgcmp на 16 потоках ускорился в 8–10 раз. Хитрые трюки вроде гистограмм, TABLESAMPLE и сохранённых границ блоков могут дать дополнительный выигрыш и стабилизировать время выполнения.

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

С другой стороны, чтобы написать свой более-менее адекватный скрипт, придётся разобраться со следующим:

1. Семантика сравнения. Порядок и равенство задаёт класс операторов типа, а не его текстовое представление: '10' < '9', -0.0 и 0.0, infinity. Пока ключ — integer или bigint, всё просто; дальше нужно понимать поведение каждого типа.

2. Одинаковое представление данных на обоих серверах. Это одинаковые настройки сессий, согласованные типы колонок (логическая репликация вполне допускает timestamp → timestamptz), одинаковый COLLATION первичного ключа и одинаковые версии glibc/ICU.

3. Цена сериализации. numeric_out, timestamptz_out, jsonb_out, array_out и record_out тратят процессор на каждой строке — и в скрипте, и в инструментах, которые хэшируют ROW(...)::text.

4. Цена сети. Для широких таблиц передача строк целиком упирается в канал раньше, чем в процессор.

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

6. Область применимости. Всё построено на PRIMARY KEY, поэтому за бортом остаются таблицы без PK, материализованные представления, large objects и иерархии наследования, где PK на наследников не распространяется.

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