ReplacingMergeTree dans ClickHouse : Guide complet

ReplacingMergeTree : Comment Vaincre les Doublons dans ClickHouse Sans Douleur

1. Pourquoi ReplacingMergeTree est Nécessaire — Le Problème des Doublons dans le Monde Réel

Imaginez que vous développez un casino en ligne. Un joueur clique sur le bouton "Placer le pari" — 1000 roubles sur le noir. À ce moment, le serveur qui traite la requête plante soudainement (surchauffe, panne réseau, qui sait). Le client n'a pas reçu de réponse et pense : "Le pari n'a pas été pris." Le joueur reclique. Le serveur redémarre et accepte les deux requêtes. Dans la base de données — deux paris identiques. Le joueur est furieux : 2000 roubles ont été déduits au lieu de 1000.

C'est un problème classique d'idempotence (du latin idem — même, potens — capable). Une opération est idempotente si la répéter donne le même résultat que la faire une fois. Dans le monde des bases de données, nous avons besoin d'un mécanisme qui détermine : "J'ai déjà vu ce pari, je vais ignorer la deuxième version."

Dans ClickHouse, il y a ReplacingMergeTree pour cela. C'est un moteur de table qui supprime automatiquement les doublons lors du merge des parties de données. Mais je vous préviens d'avance : ce n'est pas magique — il a des particularités dont nous allons parler.

Analogie réelle : ReplacingMergeTree est comme une secrétaire qui tient un registre des réunions. Les gens viennent vous voir avec des demandes. Parfois le même client apporte deux demandes identiques (par exemple, a raté un train et demande un remboursement, puis rappelle avec la même demande). La secrétaire ne jette pas les doublons à la porte — elle met simplement tous les papiers dans un dossier. Une fois par jour, elle parcourt le dossier et ne garde que la dernière demande de chaque client. Si quelqu'un demande "combien de demandes d'Ivanov ?" avant le tri — il en verra deux. Après — une.

2. Comment Fonctionne ReplacingMergeTree — Pierre par Pierre

Les Doublons Proviennent d'une Livraison Non Fiable

ClickHouse a été conçu à l'origine pour l'analyse à grande échelle, où un oubli ou un doublon occasionnel n'est pas critique. Mais ensuite, les gens ont commencé à l'utiliser pour des données critiques — et se sont brûlés. ReplacingMergeTree est la réponse à cette douleur.

Pourquoi les doublons apparaissent-ils ?

• Le client a envoyé des données, n'a pas reçu d'accusé de réception (timeout), et a renvoyé.
• Le système de file d'attente (Kafka, RabbitMQ) garantit at-least-once — au moins une livraison, des répétitions possibles.
• Un bug dans le processus ETL (Extract, Transform, Load) — le pipeline a été exécuté deux fois.

Mécanisme : Merge par Clé ORDER BY

Lors de la création d'une table avec ReplacingMergeTree, vous devez spécifier une clé de tri — ORDER BY (colonne1, colonne2). Ce n'est pas une clé primaire au sens classique (comme dans PostgreSQL), mais une façon d'ordonner physiquement les données sur le disque. ClickHouse stocke les données dans des parties — des morceaux triés par cette clé.

Lorsque deux parties fusionnent en une seule (un processus en arrière-plan appelé merge), ReplacingMergeTree parcourt les lignes ayant la même valeur de clé ORDER BY et n'en garde qu'une. Laquelle ? Par défaut — la dernière par heure d'insertion. Mais vous pouvez spécifier une colonne version numérique, alors la ligne avec la valeur de version maximale reste.

Analogie Git : ReplacingMergeTree lors du merge se comporte comme Git lorsque vous résolvez un conflit : parmi deux modifications du même fichier, la plus récente est conservée (si vous ne spécifiez pas de stratégie explicitement). Seulement ici le fichier est une ligne de la table, et la clé est ORDER BY.

Versioning : Comment ReplacingMergeTree(version) Change les Règles

Syntaxe : ReplacingMergeTree(colonne_version). Si colonne_version est un entier (UInt* ou DateTime), la ligne avec la valeur la plus grande reste. Cela donne un contrôle manuel : vous pouvez spécifier explicitement quelle version "gagne."

Exemple : nous envoyons des paris avec updated_at = now(). Lors d'un renvoi, updated_at sera légèrement plus grand. Le merge gardera le plus récent. Si vous ne spécifiez pas version, ClickHouse prend la dernière arrivée — ce qui peut ne pas être la plus récente selon la logique métier, juste la dernière insertion physique. La différence compte.

3. CREATE TABLE avec ReplacingMergeTree — Décortiqué

-- Créer une table pour les paris avec déduplication
CREATE TABLE bets_dedup
(
    user_id    UInt64,           -- ID du joueur (à qui appartient le pari)
    bet_id     String,           -- ID unique du pari (généré côté client)
    amount     Decimal(10,2),    -- Montant en roubles
    created_at DateTime,         -- Heure de création du pari
    updated_at DateTime          -- Heure de dernière mise à jour (pour la version)
)
ENGINE = ReplacingMergeTree(updated_at)   -- Moteur avec version par updated_at
ORDER BY (user_id, bet_id)                -- Clé de déduplication : (user_id, bet_id)

Que se passe-t-il ligne par ligne :

• ENGINE = ReplacingMergeTree(updated_at) — spécifie qu'il s'agit de ReplacingMergeTree, et que la colonne updated_at sera utilisée comme version. Lors du merge, parmi deux lignes avec le même ORDER BY, celle avec le updated_at le plus grand (la plus récente) reste. Si updated_at est égal — la dernière insérée physiquement reste (mais il vaut mieux ne pas compter là-dessus).

• ORDER BY (user_id, bet_id) — le paramètre le plus important ! Cet ensemble de colonnes définit ce qui est considéré comme un doublon. Deux lignes sont considérées comme des doublons si elles ont les mêmes valeurs pour toutes les colonnes de ORDER BY. Ici : un pari de l'utilisateur user_id avec bet_id — est unique. Si deux lignes avec user_id=123, bet_id='abc-456' arrivent — elles fusionneront en une seule.

Pourquoi ORDER BY et pas PRIMARY KEY ? Dans ClickHouse, PRIMARY KEY n'a pas besoin d'être unique. C'est un indice pour l'index, tandis que ORDER BY est l'ordre physique sur le disque. ReplacingMergeTree se base sur ORDER BY, même si PRIMARY KEY est plus court. Si vous ne spécifiez pas PRIMARY KEY, il correspond à ORDER BY.

Que faire si ORDER BY est trop large ? Par exemple, inclure amount. Alors deux paris avec des montants différents (même identiques en user_id, bet_id) ne seront pas considérés comme des doublons — les deux resteront. La déduplication ne fonctionnera pas. Piège n°1 (nous y reviendrons à la fin).

4. Pourquoi SELECT Peut Retourner des Doublons Avant le Merge — et Comment Vivre Avec

La nuance principale : ReplacingMergeTree supprime les doublons uniquement lors du merge des parties de données. C'est un processus en arrière-plan qui ne se produit pas instantanément. Entre l'insertion des doublons et leur suppression physique, il peut s'écouler de quelques secondes à plusieurs heures (selon les paramètres et la charge).

Qu'est-ce que cela signifie en pratique ?

Insérons deux doublons :

-- Première insertion
INSERT INTO bets_dedup VALUES (123, 'bet-001', 1000, now(), now());

-- Après 5 secondes — deuxième (le serveur n'a pas eu de confirmation et a renvoyé)
INSERT INTO bets_dedup VALUES (123, 'bet-001', 1000, now(), now() + interval 5 second);

Maintenant, exécutons un SELECT * FROM bets_dedup WHERE user_id = 123 normal. Que verrons-nous ? Deux lignes. Parce que le merge n'a pas encore eu lieu. Les données sont dans des parties différentes. Chaque partie est triée par ORDER BY en interne, mais les doublons peuvent être dans des parties différentes.

Comment obtenir une seule ligne garantie ? Utilisez FINAL :

SELECT * FROM bets_dedup FINAL WHERE user_id = 123;

FINAL force ClickHouse à à la volée fusionner toutes les parties pour cette requête, en appliquant la logique de ReplacingMergeTree. Vous obtiendrez une seule ligne — avec le updated_at maximum (ou la dernière par heure d'insertion si pas de version).

Pourquoi FINAL est-il lent ? ClickHouse lit toutes les parties de la table, les trie en mémoire par la clé ORDER BY, supprime les doublons, et seulement ensuite retourne le résultat. Sur les grandes tables (milliards de lignes), cela peut prendre des secondes ou des minutes. L'optimiseur ne peut pas utiliser efficacement les index — il doit scanner beaucoup de données.

Conseil : N'utilisez pas FINAL en temps réel sur les grandes tables. Utilisez-le pour :

• Les requêtes ponctuelles pour un seul user_id (l'index aide quand même).
• Les tâches en arrière-plan où le temps n'est pas critique (rapports nocturnes).
• Les petites tables (jusqu'à des millions de lignes).

Pour les charges de production, il existe un meilleur modèle — une vue matérialisée sans FINAL.

5. Performance de FINAL — Quand C'est Acceptable, Quand Ça Ne L'est Pas

Quand FINAL est OK :

• La table est petite (jusqu'à 10–20 millions de lignes par serveur).
• Vous interrogez un seul utilisateur par index (WHERE user_id = spécifique).
• Vous avez une agrégation en arrière-plan une fois par heure, et 10 secondes d'attente sont acceptables.
• Exportation de données une fois par jour pour un rapport.

Quand FINAL est un tueur :

• Table >100 millions de lignes.
• Requête sans filtrage (SELECT * FROM table FINAL) — ClickHouse lira tout.
• Scénario OLTP à forte charge (dizaines de requêtes par seconde avec FINAL).
• Mises à jour fréquentes des mêmes clés — beaucoup de parties s'accumulent, FINAL les lit toutes.

Analogie : SELECT ... FINAL est comme trier manuellement tous les papiers d'une archive pour trouver la dernière version d'un document, au lieu de consulter un "journal des versions courantes." Ça marche, mais pas pour chaque demande client.

Comment vérifier si une requête utilise FINAL ?

ClickHouse a la commande EXPLAIN :

EXPLAIN SELECT * FROM bets_dedup FINAL WHERE user_id = 123;

Cherchez ReadFromMergeTree avec le drapeau final. Si vous le voyez — la requête parcourt honnêtement les parties.

6. Modèle : Agrégation en Arrière-plan Sans FINAL via Vue Matérialisée

C'est ma façon préférée de contourner FINAL. L'idée : laissez ReplacingMergeTree vivre sa vie, les doublons se résorbent progressivement en arrière-plan. Pour la lecture, nous créons une vue matérialisée qui est périodiquement reconstruite et contient des données déjà "propres" sans doublons.

Comment ça se présente :

-- 1. Table de base — sale, avec doublons
CREATE TABLE bets_raw
(
    user_id UInt64,
    bet_id String,
    amount Decimal(10,2),
    created_at DateTime,
    updated_at DateTime
)
ENGINE = ReplacingMergeTree(updated_at)
ORDER BY (user_id, bet_id);

-- 2. Table cible — propre, sans doublons
CREATE TABLE bets_clean
(
    user_id UInt64,
    bet_id String,
    amount Decimal(10,2),
    created_at DateTime,
    updated_at DateTime
)
ENGINE = MergeTree()                    -- MergeTree normal sans déduplication
ORDER BY (user_id, bet_id);

-- 3. Vue matérialisée — transfère les données à l'insertion
CREATE MATERIALIZED VIEW bets_mv TO bets_clean AS
SELECT
    user_id,
    argMax(amount, updated_at) AS amount,      -- prend le montant de la ligne avec le max updated_at
    argMax(created_at, updated_at) AS created_at,
    max(updated_at) AS updated_at
FROM bets_raw
GROUP BY user_id, bet_id;   -- Grouper par clé de déduplication

Points clés expliqués :

• argMax(amount, updated_at) — une fonction d'agrégation qui retourne la valeur de amount de la ligne avec le plus grand updated_at. Si nous avons des doublons avec des updated_at différents (et des amount différents — par exemple, le montant du pari a changé), le montant le plus frais reste. C'est analogue à un contrôle de version manuel.

• GROUP BY user_id, bet_id — ici nous disons explicitement : "considérer la combinaison utilisateur+ID de pari comme un doublon." Maintenant, il n'y a plus besoin d'attendre le merge — chaque INSERT dans bets_raw déclenche immédiatement (presque) un recalcul dans bets_clean via bets_mv.

• Limitation importante : Les vues matérialisées dans ClickHouse traitent les données par lots — chaque insertion séparément. Si une seule insertion contient deux doublons (user_id, bet_id) — ils se résorbent dans le lot. Si les doublons arrivent dans des insertions différentes — bets_clean peut contenir des doublons temporaires jusqu'à ce que bets_raw fusionne. Pour une propreté parfaite, vous devez soit utiliser FINAL lors de la lecture de bets_raw, soit exécuter périodiquement OPTIMIZE TABLE bets_raw (merge forcé).

Analogie : C'est comme avoir un brouillon (bets_raw) où vous mettez toutes les corrections, et une secrétaire qui toutes les 5 minutes retape une copie propre (bets_clean) sans erreurs. Les lecteurs ne regardent que la copie propre — rapide et sans doublons.

7. ReplacingMergeTree(version) avec Version Monotone Croissante — Sémantique de Mise à Jour

Un ReplacingMergeTree normal garde simplement la ligne "arrivée en dernier." C'est mauvais si des données anciennes peuvent arriver après des données nouvelles (par exemple, à cause de délais réseau). Solution : utilisez une colonne version qui croît de manière monotone (par exemple, timestamp ou ID de séquence).

Exemple : table de solde joueur avec historique des dépôts

CREATE TABLE player_balance
(
    user_id        UInt64,
    transaction_id String,        -- ID de transaction unique (UUID)
    amount         Int64,         -- Changement de solde (peut être négatif)
    balance_after  Int64,         -- Solde après transaction
    event_time     DateTime,      -- Heure de l'événement côté client
    ingestion_time DateTime       -- Heure d'insertion dans ClickHouse (version)
)
ENGINE = ReplacingMergeTree(ingestion_time)   -- Version = heure d'insertion
ORDER BY (user_id, transaction_id);

Maintenant, même si la transaction tx-001 arrive deux fois, mais avec des ingestion_time différents, celle insérée plus tard (avec un ingestion_time plus grand) reste. Cela protège contre les "doublons tardifs" — quand la première insertion était à 12:00, la seconde à 12:05 (répétition), mais à cause d'un problème réseau, la seconde est arrivée au serveur avant la première. Sans version, la plus ancienne (par heure d'insertion) resterait — ce qui pourrait être la mauvaise.

Que signifie "monotone croissante" ? À chaque nouvelle insertion, la valeur de ingestion_time doit être supérieure ou égale aux précédentes. Utilisez now() (heure actuelle sur le serveur ClickHouse) ou un compteur atomique (par exemple, depuis ZooKeeper). Ne vous fiez pas à l'heure du client — les horloges peuvent dériver.

8. Exemple Complet : Déduplication des Rechargements de Solde par transaction_id

Mettons tout ensemble. Nous avons un microservice qui accepte les rechargements de solde depuis un système de paiement. Le système de paiement envoie des webhooks (appels HTTP) — parfois des doublons.

-- Étape 1 : Créer une table pour les événements bruts
CREATE TABLE balance_events
(
    user_id        UInt64,
    transaction_id String,        -- ID unique du système de paiement
    amount         Int64,         -- +1000 rub
    event_time     DateTime,      -- Heure de déduction de l'argent de l'utilisateur
    inserted_at    DateTime DEFAULT now()  -- Défini automatiquement à l'insertion
)
ENGINE = ReplacingMergeTree(inserted_at)
ORDER BY (user_id, transaction_id);   -- Déduplication par paire (utilisateur, transaction)

-- Étape 2 : Insérer des données (supposons qu'un doublon arrive)
INSERT INTO balance_events (user_id, transaction_id, amount, event_time) 
VALUES (1, 'pay_001', 1000, '2025-06-01 10:00:00');

-- Après une minute, un doublon arrive (inserted_at sera automatiquement now() + 60 sec)
INSERT INTO balance_events (user_id, transaction_id, amount, event_time) 
VALUES (1, 'pay_001', 1000, '2025-06-01 10:00:00');

-- Étape 3 : Lire sans FINAL — nous verrons 2 lignes (mais seulement si elles n'ont pas encore fusionné)
SELECT * FROM balance_events WHERE user_id = 1;
-- Résultat : deux lignes avec le même user_id, transaction_id, amount

-- Étape 4 : Lire avec FINAL — nous voyons une ligne (avec le max inserted_at)
SELECT * FROM balance_events FINAL WHERE user_id = 1;
-- Résultat : une ligne

Pourquoi transaction_id dans ORDER BY ne suffit-il pas ? Parce que deux utilisateurs différents peuvent avoir le même transaction_id (par exemple, chaque système de paiement a son propre compteur). Ajouter user_id garantit l'unicité au sein d'un utilisateur. Si le système génère des UUID globaux (550e8400-e29b-41d4-a716-446655440000) — vous pouvez utiliser ORDER BY transaction_id seul, un UUID suffit.

9. Comparaison avec CollapsingMergeTree

CollapsingMergeTree est un autre moteur pour gérer les modifications. Il stocke des paires "plus" et "moins" et les résout lors du merge.

Différences clés :

Caractéristique	ReplacingMergeTree	CollapsingMergeTree
Mécanisme	Garde une ligne parmi les doublons	Résout les paires (+1 et -1)
Objectif	Déduplication des insertions	Mise à jour des agrégats (ex. panier)
Version nécessaire	Optionnelle (colonne version)	Obligatoire drapeau Sign (+1/-1)
Peut-on stocker l'historique	Oui, toutes les versions jusqu'au merge	Non, les paires sont détruites
FINAL pour la lecture	Oui, sans lui les doublons sont visibles	Oui, sans lui les paires non résolues sont visibles

Quand choisir ReplacingMergeTree :

• Vous avez juste besoin de supprimer les lignes en double.
• Vous avez une clé naturelle pour la déduplication (ID de transaction).
• Les données changent rarement (surtout des insertions).

Quand choisir CollapsingMergeTree :

• Vous mettez fréquemment à jour une métrique agrégée (ex. "nombre d'articles dans le panier").
• Vous avez seulement besoin de stocker le résultat, pas l'historique des modifications.

Exemple pour CollapsingMergeTree :

CREATE TABLE cart_items
(
    user_id UInt64,
    product_id UInt64,
    quantity Int16,
    sign Int8  -- +1 (ajouter), -1 (supprimer)
) ENGINE = CollapsingMergeTree(sign)
ORDER BY (user_id, product_id);

Avec ReplacingMergeTree, vous écraseriez simplement la ligne avec une nouvelle version de quantity — mais alors vous perdriez l'historique des modifications. CollapsingMergeTree permet de calculer le total (SUM(quantity * sign)) même sans FINAL.

10. Pièges Courants — et Comment les Éviter

Piège n°1 : ORDER BY N'Inclut Pas Tous les Champs Uniques

-- MAUVAIS : utiliser seulement user_id
CREATE TABLE bets_bad ENGINE = ReplacingMergeTree ORDER BY user_id;

-- Inséré deux paris pour le même utilisateur avec des bet_id différents
INSERT INTO bets_bad VALUES (1, 'bet_001', 100);
INSERT INTO bets_bad VALUES (1, 'bet_002', 200);

-- Lors du merge, ils VONT FUSIONNER en une seule ligne — car ORDER BY (user_id) est le même !
-- bet_002 perdu.

Correct : Inclure dans ORDER BY toutes les colonnes qui rendent une ligne unique — généralement un ID surrogate (transaction_id) ou une combinaison (user_id, bet_id).

Piège n°2 : Espoir Naïf d'une Déduplication Instantanée

Les débutants écrivent INSERT avec un doublon et immédiatement SELECT sans FINAL — voient des doublons. Ils sont déçus par ClickHouse. Souvenez-vous : la déduplication est asynchrone. Si vous avez besoin d'une cohérence instantanée — utilisez FINAL ou le modèle de vue matérialisée.

Piège n°3 : Utiliser une Version Qui n'Est Pas Monotone

-- MAUVAIS : la version est l'heure du client
CREATE TABLE events ENGINE = ReplacingMergeTree(client_time) ORDER BY (id);

-- L'horloge du client est en retard, il envoie une ancienne version après une nouvelle
-- Lors du merge, la mauvaise (ancienne) ligne restera

Solution : Utilisez now() côté ClickHouse ou un compteur matériel.

Piège n°4 : Optimisme Concernant FINAL sur de Grandes Données

J'ai eu un cas : un développeur a activé FINAL dans tous les rapports sur une table de 2 milliards de lignes. Les requêtes commençaient à expirer après 300 secondes. J'ai dû réécrire avec une agrégation utilisant GROUP BY et argMax.

Règle d'Or : Si vous lisez plus de 10% d'une table via FINAL — vous faites quelque chose de mal. Utilisez des vues matérialisées ou repensez l'architecture.

Piège n°5 : ReplacingMergeTree Sans ORDER BY

ClickHouse ne vous laissera pas créer une table sans ORDER BY. Mais vous pouvez spécifier ORDER BY tuple() (tuple vide). Alors toutes les lignes de la table sont considérées comme des doublons — une seule ligne restera après le premier merge. Presque jamais nécessaire.

Et Ensuite — Liens vers des Articles Connexes

Maintenant que vous maîtrisez ReplacingMergeTree, voici les prochains sujets à explorer :

1. Comment optimiser les merges — paramètres comme merge_with_ttl_timeout, number_of_free_entries_in_pool_to_lower_max_size_of_merge (ça fait peur mais utile).

2. Déduplication au niveau INSERT — le moteur ReplicatedReplacingMergeTree avec ZooKeeper. C'est un autre niveau : les doublons sont coupés immédiatement à l'insertion, mais au prix de délais et de complexité.

3. Alternative : VersionedCollapsingMergeTree — un hybride qui supporte le versioning et le collapsing simultanément.

4. Vues matérialisées en détail — comment construire des agrégations multi-niveaux pour éviter complètement FINAL.

Et enfin : ReplacingMergeTree est un outil puissant, mais il ne s'agit pas de "supprimer les doublons immédiatement." Il s'agit de "les données deviendront propres éventuellement, et vous travaillez avec en attendant." Si vous avez besoin d'une unicité stricte (comme PRIMARY KEY dans PostgreSQL) — ClickHouse n'est pas le meilleur choix. Mais pour 99% des tâches analytiques avec des insertions répétées — c'est un sauveur.

---

← Précédente: Configuration de ClickHouse : Comment j'ai mis en place la production sans me tirer une balle dans le pied
→ Suivante: SummingMergeTree et AggregatingMergeTree : Agrégation incrémentale sans douleur

— Editorial Team