Почему ключи сортировки в ClickHouse могут стать причиной появления дублей или пропусков при CDC-передаче изменений из PostgreSQL и как этого избежать: особенности логической репликации из транзакционной базы данных в аналитическую.
Влияние ключей сортировки на CDC-передачу изменений из PostgreSQL в ClickHouse
Продолжая разбираться с дублированием данных при передачи изменений из PostgreSQL в Clickhouse, сегодня поговорим о том, как на качество загруженных данных влияет ключ сортировки. Ключи сортировки для таблиц ClickHouse очень важны: они определяют, как данные сортируются на диске и индексируются. При репликации из PostgreSQL с помощью PeerDB и основанном на этом ETL/ELT-инструменте репликации интеграционного движка для облачной версии ClickPipes в ClickHouse автоматически создаются таблицы с движком ReplacingMergeTree, столбцы которых имеют тип данных, который наиболее точно соответствует исходному типу. Особенностью ReplacingMergeTree является характер дедубликации: он удаляет повторяющиеся записи с одинаковым значением ключа сортировки по столбцу с оператором ORDER BY, а не по первичному ключу PRIMARY KEY. Это происходит только во время слияния, которое выполняется в фоновом режиме в неизвестное время. При экономии дискового пространства, это не избавляет от дублей, что мы описывали здесь. Хотя можно увеличить частоту слияний, настроив параметры min_age_to_force_merge_seconds и min_age_to_force_merge_on_partition_only, в некоторые моменты времени в ClickHouse могут возникнуть несогласованные результаты. Причем, это может случиться из-за разных ключей сортировки.
При репликации из PostgreSQL CDC-инструменты устанавливают первичный ключ исходной таблицы PostgreSQL в качестве ключа сортировки для целевой таблицы в ClickHouse. В большинстве случаев это работает эффективно, поскольку ClickHouse оптимизирован для быстрого сканирования, а пользовательские ключи сортировки требуются не часто. Но иногда приходится включать в ключи сортировки ClickHouse дополнительные столбцы, кроме первичного ключа PostgreSQL, чтобы оптимизировать выполнение SQL-запросов к колоночному хранилищу.
Если ключ сортировки ClickHouse по умолчанию, отличается от первичного ключа PostgreSQL, это может вызвать проблемы с дедупликацией данных. Это происходит потому, что для строк, где столбец в ключе сортировки обновляется, PeerDB реплицирует последнюю версию из PostgreSQL, но ClickHouse не дедуплицирует эту строку с предыдущей версией из-за измененного значения ключа сортировки. Это приводит к появлению дополнительной строки в ClickHouse, которая по факту является дублем.
При удалении строк из PostgreSQL, движок PeerDB реплицирует удаленную строку с нулевыми значениями во всех столбцах, включая ключи сортировки, кроме первичного ключа. Это происходит потому, что логическая репликация PostgreSQL предоставляет только значение столбца первичного ключа для удаляемой строки. В результате ClickHouse не дедуплицирует существующую строку, а новая строка помечается как удаленная, поскольку значения столбца ключа сортировки входящей удаленной строки отличаются от значений существующей строки. Такое мягкое удаление снова приводит к появлению дополнительных строк в ClickHouse, которых быть не должно. Избежать этого помогут следующие меры:
- использовать первичный ключ PostgreSQL как суффикс к ключу сортировки Clickhouse. Это необходимо для точной дедупликации, поскольку первичный ключ в PostgreSQL представляет собой самую низкую гранулярность для строки. Определение ключа сортировки без первичного ключа может привести к потере строк.
- определить в PostgreSQL настройку REPLICA IDENTITY как индекс (FULL или UNIQUE) для столбцов в ключе сортировки. Если в таблице PostgreSQL нет подходящего ключа, ее можно установить на REPLICA IDENTITY FULL, когда вся строка становится ключом. В транзакционных сценариях это считается неэффективным решением и рекомендуется в случаях, когда другие решения невозможны. Тем не менее, при выполнении операций UPDATE и DELETE в PostgreSQL включаются только старые значения столбцов идентификаторов. Избежать этого, чтобы получать значения до и после изменения, поможет установка идентификатора реплики в значение FULL. Кроме того, события UPDATE WAL не включают TOAST-столбцы, если они не менялись в текущей мутации. Это означает, что если в столбцах есть значения более 8 КБ, они не включаются в события обновления. Поэтому установка идентификатора реплики в FULL гарантирует, что логическую репликацию всех данных. А, благодаря тому, что логическая репликация PostgreSQL предоставляет как старые, так и новые версии строки с заполненными значениями столбца ключа сортировки, PeerDB может точно обрабатывать сценарии обновления и удаления данных. Для обеспечения точной дедупликации PeerDB внутренне обрабатывает обновление как DELETE старой строки и INSERT новой строки, что позволяет ClickHouse правильно управлять дедупликацией.
Таким образом, чтобы обеспечить CDC-передачу данных из PostgreSQL в ClickHouse без дублей и потерь, важно установить REPLICA IDENTITY на реплицируемых таблицах, включив в них столбцы ключей сортировки. Иначе CDC-инструменты не будут захватывать значения столбцов, отличных от первичного ключа, согласно ограничению логического декодирования PostgreSQL. Все столбцы ключа сортировки, кроме первичного ключа в PostgreSQL, будут иметь значения NULL. Это влияет на дедупликацию: предыдущая версия строки не может быть дедуплицирована с последней удаленной версией, где _peerdb_is_deleted равно 1.
Чтобы определить REPLICA IDENTITY FULL на стороне PostgreSQL, надо изменить таблицу с помощью запроса ALTER TABLE:
ALTER TABLE table_name REPLICA IDENTITY FULL;
Чтобы определить REPLICA IDENTITY как UNIQUE INDEX, используется команда CREATE:
CREATE UNIQUE INDEX orderingkey_index ON table_name (ordering_key_column1, ordering_key_column2, ...);
и изменение таблицы через ALTER TABLE
ALTER TABLE table_name REPLICA IDENTITY USING INDEX orderingkey_index;
Обновляемые материализованные представления и проекции для CDC
Чтобы оптимизировать работу с пользовательскими ключами сортировки, можно применить обновляемые материализованные представления, которые будут периодически копировать всю таблицу с нужным ключом сортировки. Рассмотрим пример обновляемого материализованного представления с пользовательским ключом сортировки и обязательной дедупликацией в ClickHouse:
CREATE MATERIALIZED VIEW posts_final REFRESH EVERY 10 second ENGINE = ReplacingMergeTree(_peerdb_version) ORDER BY (owneruserid,id) -- другой ключ сортировки, но с суффиксом из первичного ключа postgres AS SELECT * FROM posts FINAL WHERE _peerdb_is_deleted = 0; -- дедупликация
Материализованное представление posts_final создается для хранения актуальных и уникальных записей из таблицы posts, исключая удалённые записи. Использование ReplacingMergeTree вместе с указанием столбца _peerdb_version обеспечивает автоматическую замену старых записей на новые при обновлении данных. Периодическое обновление каждые 10 секунд гарантирует, что представление остаётся синхронизированным с исходными данными.
Если базовая таблица меняется очень часто, поддержание материализованного представления может создавать значительную нагрузку. Это может привести к задержкам в обработке данных и снизить общую производительность системы. Кроме того, при работе с очень большими таблицами материализованные представления могут потреблять много дискового пространства и оперативной памяти, увеличивая время выполнения запросов. Избежать этого можно, выбрав в качестве ключей сортировки для ClickHouse, помимо первичного ключа из PostgreSQL, столбцы, которые не изменяются для данной строки. Так можно предотвратить проблемы с согласованностью данных и дедупликацией в движке ReplacingMergeTree.
При этом следует очень внимательно выбирать ключи сортировки согласно типовым вариантам использования данных. Например, в многопользовательском SaaS-приложении с общей инфраструктурой в качестве ключа сортировки можно выбрать идентификатор арендатор tenant_id. Каждый арендатор имеет свой собственный tenant_id, который обеспечивает изоляцию данных и настроек между различными клиентами, гарантируя безопасность и приватность информации.
Чтобы ускорить работу с пользовательскими ключами сортировки, можно использовать проекции – операции выбора определенных столбцов из таблицы при получении данных. Благодаря снижению объема данных, SQL-запрос выполняется быстрее. В ClickHouse проекции полезны для выполнения запросов по столбцу, который не является частью первичного ключа. Хотя использование проекций увеличивает объем хранимых данных из-за фактического создания дополнительных копий в агрегированном виде, в ClickHouse это почти незаметно из-за активного использования материальных представлений данных внутри одной таблицы для оптимизации специфических запросов. Проекции хранятся как отдельные физические структуры, которые могут содержать предварительно агрегированные данные. Благодаря тому, что проекции уже содержат необходимую информацию, сложные агрегатные запросы выполняются быстрее.
Будучи основанными на материализованных представлениях, проекции в ClickHouse автоматически обновляются вместе с изменением исходных данных и хранят данные в формате, оптимизирующем выполнение запросов. Это особенно полезно для запросов по столбцу, который не является частью первичного ключа.
Можно определить одну или несколько проекций для таблицы, и во время анализа запроса ClickHouse выберет проекцию с наименьшим объемом данных для сканирования, не изменяя пользовательский SQL-запрос. Оптимизатор запросов ClickHouse автоматически анализирует доступные проекции и сам выбирает наиболее подходящую, если она может ускорить выполнение запроса. Проекции в ClickHouse создают скрытую таблицу, чтобы ускорить фильтрацию данных с фильтром по конкретному столбцу. Но при этом возможно дублирование данных, если первичный ключ в проекции отличается от исходной таблицы. Кроме того, проекции не используются в запросах с ключевым словом FINAL и не учитывают дедупликацию. Поэтому использовать их рекомендуется в случаях, когда исходная таблица меняется редко, т.е. операции обновления или удаления происходят нечасто. Таким образом, сами по себе проекции не являются способом устранения дублей в Clickhouse.
Впрочем, дедупликация – не единственная проблема при передаче изменений из PostgreSQL в Clickhouse. Будучи транзакционным хранилищем PostgreSQL обычно содержит сильно нормализованые модели данных из сотни связанных таблиц. В ClickHouse, наоборот, рекомендуется минимальное использование JOIN-операций. О том, как эффективно соединять таблицы и выполнять денормализацию, поговорим в новой статье.
Освойте ClickHouse на специализированных курсах в нашем лицензированном учебном центре обучения и повышения квалификации для разработчиков, менеджеров, архитекторов, инженеров, администраторов, Data Scientist’ов и аналитиков Big Data в Москве:
Источники
