Ключи сортировки (также известные как ключи упорядочивания)

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

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

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

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

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

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

CREATE MATERIALIZED VIEW posts_final
REFRESH EVERY 10 second ENGINE = ReplacingMergeTree(_peerdb_version)
ORDER BY (owneruserid,id) -- different ordering key but with suffixed postgres pkey
AS
SELECT * FROM posts FINAL 
WHERE _peerdb_is_deleted = 0; -- this does the deduplication

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

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

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

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

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

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

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

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

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

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

-- Create a UNIQUE INDEX on (owneruserid, id)
CREATE UNIQUE INDEX posts_unique_owneruserid_idx ON posts(owneruserid, id);
-- Set REPLICA IDENTITY to use this index
ALTER TABLE posts REPLICA IDENTITY USING INDEX posts_unique_owneruserid_idx;