オーダリングキー

Ordering Keys (a.k.a. sorting keys) は、ClickHouseのテーブルのデータがディスク上でどのようにソートされ、インデックスされるかを定義します。Postgresからレプリケーションを行う場合、ClickPipesはPostgresの主キーをClickHouse内の対応するテーブルのOrdering Keyとして設定します。ほとんどの場合、Postgresの主キーは十分なOrdering Keyとして機能します。ClickHouseはすでに高速なスキャン用に最適化されているため、カスタムOrdering Keyはしばしば必要ありません。

移行ガイドで説明されているように、大規模なユースケースでは、ClickHouseのOrdering KeyにはPostgresの主キーに加えて追加のカラムを含めて、クエリを最適化することをお勧めします。

デフォルトでCDCを使用している場合、Postgresの主キーとは異なるOrdering Keyを選択すると、ClickHouseでデータの重複除去の問題が発生する可能性があります。これは、ClickHouseのOrdering Keyが二重の役割を果たすために発生します。つまり、データのインデックスとソートを制御すると同時に、重複除去のキーとして機能します。この問題に対処する最も簡単な方法は、更新可能なMaterialized Viewを定義することです。

更新可能なMaterialized Viewの使用

カスタムOrdering Key（ORDER BY）を定義する簡単な方法は、更新可能なMaterialized View（MVs）を使用することです。これにより、希望するOrdering Keyを持つテーブル全体を定期的に（例：5分または10分ごとに）コピーできます。

以下は、カスタムORDER BYおよび必要な重複除去を備えた更新可能なMVの例です：

更新可能なMaterialized ViewなしのカスタムOrdering Key

データのスケールのために更新可能なMaterialized Viewが機能しない場合、より大きなテーブルでカスタムOrdering Keyを定義し、重複除去に関連する問題を克服するためのいくつかの推奨事項を以下に示します。

特定の行で変更されないOrdering Keyカラムを選択する

ClickHouseのOrdering KeyにPostgresの主キー以外の追加のカラムを含める場合は、各行で変更されないカラムを選択することをお勧めします。これにより、ReplacingMergeTreeでデータの整合性や重複除去の問題を防ぐのに役立ちます。

例えば、マルチテナントSaaSアプリケーションでは、(tenant_id, id)をOrdering Keyとして使用するのが良い選択です。これらのカラムは各行を一意に識別し、tenant_idは他のカラムが変更された場合でもidに対して一定です。idによる重複除去が(tenant_id, id)による重複除去と一致するため、tenant_idが変更された場合に発生する可能性のあるデータの重複除去の問題を回避するのに役立ちます。

PostgresテーブルのレプリカアイデンティティをカスタムOrdering Keyに設定する

PostgresのCDCが期待通りに機能するためには、テーブルのREPLICA IDENTITYをOrdering Keyカラムを含むように変更することが重要です。これはDELETE操作を正確に処理するために必要です。

REPLICA IDENTITYにOrdering Keyカラムが含まれない場合、PostgresのCDCは主キー以外のカラムの値をキャプチャしません - これはPostgresの論理デコーディングの制限です。Postgresの主キー以外のすべてのOrdering Keyカラムはnullになります。これにより、重複除去に影響を及ぼし、行の以前のバージョンが最新の削除されたバージョン（_peerdb_is_deletedが1に設定されている）と重複除去されない可能性があります。

owneruseridとidを用いた上記の例では、主キーがすでにowneruseridを含まない場合、(owneruserid, id)の上にUNIQUE INDEXを持ち、それをテーブルのREPLICA IDENTITYとして設定する必要があります。これにより、PostgresのCDCは正確なレプリケーションと重複除去に必要なカラムの値をキャプチャします。

以下は、eventsテーブルでこれを行う方法の例です。変更されたOrdering Keyを持つすべてのテーブルに適用することを確認してください。