Ключи упорядочивания

Ключи упорядочивания (также называемые ключами сортировки) определяют, как данные упорядочиваются на диске и индексируются в таблице ClickHouse. При репликации из Postgres ClickPipes по умолчанию использует первичный ключ таблицы в Postgres в качестве ключа упорядочивания для соответствующей таблицы в ClickHouse. В большинстве случаев первичный ключ Postgres является достаточным ключом упорядочивания, так как ClickHouse уже оптимизирован для быстрого сканирования данных, и пользовательские ключи упорядочивания часто не требуются.

Как описано в руководстве по миграции, для более крупных сценариев использования вам следует включать в ключ упорядочивания ClickHouse дополнительные столбцы помимо первичного ключа Postgres для оптимизации запросов.

По умолчанию при использовании CDC выбор ключа упорядочивания, отличного от первичного ключа Postgres, может привести к проблемам с дедупликацией данных в ClickHouse. Это происходит потому, что ключ упорядочивания в ClickHouse играет двойную роль: он управляет индексированием и сортировкой данных, одновременно выступая в качестве ключа дедупликации. Самый простой способ решить эту проблему — определить обновляемые материализованные представления.

Используйте обновляемые материализованные представления

Проще всего задать пользовательские ключи сортировки (ORDER BY) с помощью обновляемых материализованных представлений (MV). Они позволяют периодически (например, каждые 5 или 10 минут) копировать всю таблицу с нужным ключом сортировки.

Ниже приведён пример обновляемого материализованного представления с пользовательским ORDER BY и необходимой дедупликацией:

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; -- здесь происходит дедупликация

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

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

Выбирайте столбцы ключа сортировки, которые не меняются для заданной строки

При добавлении дополнительных столбцов в ключ сортировки для ClickHouse (помимо первичного ключа из Postgres) рекомендуется выбирать столбцы, которые не меняются для каждой строки. Это помогает предотвратить проблемы с согласованностью данных и дедупликацией при использовании ReplacingMergeTree.

Например, в многотенантном SaaS‑приложении использование (tenant_id, id) в качестве ключа сортировки — удачный выбор. Эти столбцы однозначно идентифицируют каждую строку, и tenant_id остаётся постоянным для id, даже если другие столбцы меняются. Поскольку дедупликация по id совпадает с дедупликацией по (tenant_id, id), это помогает избежать проблем с дедупликацией данных, которые могли бы возникнуть, если бы tenant_id изменялся.

Установите Replica Identity в таблицах Postgres на пользовательский ключ сортировки

Чтобы Postgres CDC работал как ожидается, важно изменить REPLICA IDENTITY в таблицах так, чтобы он включал столбцы ключа сортировки. Это критично для корректной обработки операторов DELETE.

Если REPLICA IDENTITY не включает столбцы ключа сортировки, Postgres CDC не будет фиксировать значения столбцов, отличных от первичного ключа — это ограничение механизма логического декодирования Postgres. Все столбцы ключа сортировки, кроме первичного ключа в Postgres, будут иметь значения NULL. Это влияет на дедупликацию: предыдущая версия строки может не быть дедуплицирована с последней удалённой версией (в которой _peerdb_is_deleted установлено в 1).

В приведённом выше примере с owneruserid и id, если первичный ключ ещё не включает owneruserid, необходимо создать UNIQUE INDEX по (owneruserid, id) и установить его в качестве REPLICA IDENTITY для таблицы. Это гарантирует, что Postgres CDC будет фиксировать необходимые значения столбцов для корректной репликации и дедупликации.

Ниже приведён пример того, как сделать это для таблицы events. Убедитесь, что вы применили это ко всем таблицам с изменёнными ключами сортировки.

-- Создайте уникальный индекс для (owneruserid, id)
CREATE UNIQUE INDEX posts_unique_owneruserid_idx ON posts(owneruserid, id);
-- Установите REPLICA IDENTITY для использования этого индекса
ALTER TABLE posts REPLICA IDENTITY USING INDEX posts_unique_owneruserid_idx;