Clés d’ordonnancement

Les clés d’ordonnancement (autrement dit, les clés de tri) définissent la manière dont les données sont triées sur disque et indexées pour une table dans ClickHouse. Lors de la réplication depuis Postgres, ClickPipes utilise par défaut la clé primaire Postgres d’une table comme clé d’ordonnancement pour la table correspondante dans ClickHouse. Dans la plupart des cas, la clé primaire Postgres constitue une clé d’ordonnancement suffisante, car ClickHouse est déjà optimisé pour des scans rapides et des clés d’ordonnancement personnalisées ne sont souvent pas nécessaires. Comme indiqué dans le guide de migration, pour les cas d’usage à plus grande échelle, vous devez inclure des colonnes supplémentaires, au-delà de la clé primaire Postgres, dans la clé d’ordonnancement de ClickHouse afin d’optimiser les requêtes. Par défaut, avec CDC, choisir une clé d’ordonnancement différente de la clé primaire Postgres peut entraîner des problèmes de déduplication des données dans ClickHouse. Cela s’explique par le fait que la clé d’ordonnancement dans ClickHouse remplit un double rôle : elle contrôle l’indexation et le tri des données, tout en servant de clé de déduplication. Le moyen le plus simple de résoudre ce problème consiste à définir des vues matérialisées actualisables.

Utiliser des vues matérialisées actualisables

Une façon simple de définir des clés d’ordonnancement personnalisées (ORDER BY) consiste à utiliser des vues matérialisées actualisables (MV). Elles permettent de recopier périodiquement (par exemple, toutes les 5 ou 10 minutes) l’intégralité de la table avec la clé d’ordonnancement souhaitée. Vous trouverez ci-dessous un exemple de MV actualisable avec un ORDER BY personnalisé et la déduplication requise :

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

Clés d’ordonnancement personnalisées sans vues matérialisées actualisables

Si les vues matérialisées actualisables ne sont pas adaptées en raison du volume de données, voici quelques recommandations pour définir des clés d’ordonnancement personnalisées sur des tables volumineuses et surmonter les problèmes liés à la déduplication.

Choisissez des colonnes de clé d’ordonnancement qui ne changent pas pour une ligne donnée

Lorsque vous ajoutez des colonnes supplémentaires à la clé d’ordonnancement de ClickHouse (en plus de la clé primaire de Postgres), nous vous recommandons de choisir des colonnes qui ne changent pas pour chaque ligne. Cela permet d’éviter les problèmes de cohérence des données et de déduplication avec ReplacingMergeTree. Par exemple, dans une application SaaS multilocataire, utiliser (tenant_id, id) comme clé d’ordonnancement est un bon choix. Ces colonnes identifient chaque ligne de manière unique, et tenant_id reste constant pour un id, même si d’autres colonnes changent. Comme la déduplication par id correspond à la déduplication par (tenant_id, id), cela permet d’éviter des problèmes de déduplication qui pourraient survenir si tenant_id venait à changer.

Définir `REPLICA IDENTITY` sur les tables Postgres pour une clé d’ordonnancement personnalisée

Pour que Postgres CDC fonctionne correctement, il est important de modifier REPLICA IDENTITY sur les tables afin d’y inclure les colonnes de la clé d’ordonnancement. C’est indispensable pour traiter correctement les DELETE. Si REPLICA IDENTITY n’inclut pas les colonnes de la clé d’ordonnancement, Postgres CDC ne capturera pas les valeurs des colonnes autres que la clé primaire : il s’agit d’une limitation du logical decoding de Postgres. Toutes les colonnes de la clé d’ordonnancement autres que la clé primaire dans Postgres auront des valeurs NULL. Cela affecte la déduplication : la version précédente de la ligne risque de ne pas être dédupliquée avec la dernière version supprimée (où _peerdb_is_deleted est défini sur 1). Dans l’exemple ci-dessus avec owneruserid et id, si la clé primaire n’inclut pas déjà owneruserid, vous devez créer un UNIQUE INDEX sur (owneruserid, id) et le définir comme REPLICA IDENTITY pour la table. Cela garantit que Postgres CDC capture les valeurs de colonnes nécessaires pour une réplication et une déduplication précises. Vous trouverez ci-dessous un exemple montrant comment procéder sur la table events. Veillez à appliquer cette configuration à toutes les tables dont les clés d’ordonnancement ont été modifiées.

-- 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;

ClickPipes pour les flux de données

ClickPipes pour le stockage d’objets

ClickPipes pour CDC

Accès par programmation

Réseau

Utiliser des vues matérialisées actualisables

Clés d’ordonnancement personnalisées sans vues matérialisées actualisables

Choisissez des colonnes de clé d’ordonnancement qui ne changent pas pour une ligne donnée

Définir `REPLICA IDENTITY` sur les tables Postgres pour une clé d’ordonnancement personnalisée

​Utiliser des vues matérialisées actualisables

​Clés d’ordonnancement personnalisées sans vues matérialisées actualisables

​Choisissez des colonnes de clé d’ordonnancement qui ne changent pas pour une ligne donnée

​Définir REPLICA IDENTITY sur les tables Postgres pour une clé d’ordonnancement personnalisée

Utiliser des vues matérialisées actualisables

Clés d’ordonnancement personnalisées sans vues matérialisées actualisables

Choisissez des colonnes de clé d’ordonnancement qui ne changent pas pour une ligne donnée

Définir `REPLICA IDENTITY` sur les tables Postgres pour une clé d’ordonnancement personnalisée