CollapsingMergeTree dans ClickHouse : mise à jour sans UPDATE

CollapsingMergeTree : Comment mettre à jour des agrégats sans UPDATE dans ClickHouse

1. Pourquoi CollapsingMergeTree est nécessaire — Le problème de la mise à jour des agrégats

Revenons à notre casino en ligne. Chaque joueur a un solde. Lorsqu'un joueur place un pari, le solde diminue. Quand il gagne, il augmente. Quand un administrateur annule une transaction suspecte, le solde change à nouveau.

Dans une base de données classique (PostgreSQL), vous feriez simplement UPDATE players SET balance = balance - 100 WHERE user_id = 123. Simple et clair.

Mais ClickHouse ne peut pas mettre à jour les données. Pas du tout. Point final. Pourquoi ? Parce que ClickHouse est conçu pour l'analyse, où les données sont en ajout seulement. Les mises à jour sont problématiques pour le stockage columnar, où les données résident dans des blocs compressés. Pour modifier une seule cellule, des blocs entiers devraient être réécrits.

Alors, comment changer le solde ? Vous ne modifiez pas l'ancien enregistrement. Vous ajoutez un NOUVEL enregistrement qui dit : « annuler le changement précédent » et « ajouter le nouveau ». C'est ce qu'on appelle matérialiser les changements par annulation.

Analogie avec la vie réelle : Imaginez un grand livre comptable où toutes les écritures sont faites à l'encre. Vous ne pouvez pas effacer et corriger. Au lieu de cela, vous écrivez en bas : « Ligne n°45 — erronée, annulée. Nouvelle ligne n°46 — montant correct. » Ensuite, lors du calcul du total, vous lisez toutes les lignes en tenant compte des annulations.

CollapsingMergeTree est un moteur ClickHouse qui effondre automatiquement les paires de « +1 » et « -1 » lors des fusions en arrière-plan. Il agit comme ce comptable : il voit une paire d'annulation et supprime les deux lignes.

2. Le principe de la colonne sign — Les mathématiques de l'annulation

L'idée principale de CollapsingMergeTree est une colonne spéciale sign avec deux valeurs possibles :

• +1 — « ajouter » (version actuelle)
• -1 — « annuler » (version obsolète)

Lors d'une fusion de parties de données, ClickHouse recherche des paires de lignes avec la même clé de tri (ORDER BY) où l'une a sign = +1 et l'autre sign = -1. Lorsqu'une telle paire est trouvée, les deux lignes sont supprimées. Seules les lignes sans paire subsistent — c'est-à-dire celles sans annulation.

Pourquoi cela fonctionne : Tout changement de données est représenté en annulant l'ancienne version et en ajoutant une nouvelle. La paire (+1, -1) donne zéro. Quand vous faites SUM(amount * sign), les anciennes versions s'annulent et les nouvelles restent.

Analogie avec la comptabilité en partie double : En comptabilité, chaque transaction est enregistrée deux fois : débit et crédit. CollapsingMergeTree fait de même — chaque changement a son opposé. Lors de la somme, ils s'annulent mutuellement.

3. CREATE TABLE — Analyse détaillée

-- Créer une table pour le solde des joueurs avec historique des modifications
CREATE TABLE player_balance
(
    user_id      UInt64,              -- ID du joueur
    date         Date,                -- Date du changement de solde
    amount       Int64,               -- Changement de solde (+100, -50, etc.)
    balance_after Int64,              -- Solde après opération (optionnel)
    sign         Int8,                -- +1 — ajouter, -1 — annuler
    updated_at   DateTime DEFAULT now()  -- Horodatage de l'opération
)
ENGINE = CollapsingMergeTree(sign)    -- Moteur d'effondrement, spécifier la colonne sign
ORDER BY (user_id, date)              -- Clé pour le regroupement et l'effondrement

Ce qui est important ici :

• ENGINE = CollapsingMergeTree(sign) — le seul paramètre obligatoire est le nom de la colonne sign (généralement sign ou is_active). Cette colonne doit être de type Int8 (entier de -128 à 127), mais seuls +1 et -1 sont réellement utilisés.

• ORDER BY (user_id, date) — les colonnes de cette clé déterminent quelles lignes sont considérées comme une « paire ». Deux lignes avec des valeurs identiques dans toutes les colonnes ORDER BY et des sign opposés (+1 et -1) seront effondrées.

Que se passe-t-il si ORDER BY n'inclut pas tous les champs nécessaires ? Par exemple, si vous n'incluez pas user_id, des lignes de différents utilisateurs pourraient être effondrées — une catastrophe. Tous les champs par lesquels vous voulez distinguer les opérations doivent être dans ORDER BY.

Pourquoi pas PRIMARY KEY ? Mêmes raisons qu'avec les autres moteurs MergeTree — ORDER BY contrôle l'ordre physique et la fusion, tandis que PRIMARY KEY (si spécifié) ne contrôle que l'index.

4. Insertion — Comment mettre à jour correctement les données

Supposons qu'un joueur ait un solde de 1000 roubles. Il place un pari de 100 roubles. Au lieu de modifier le solde dans une seule ligne, vous effectuez deux insertions :

-- Étape 1 : Annuler l'ancienne version du solde (était 1000, maintenant devrait être 900)
-- Ancienne version : user_id=123, date='2025-06-01', amount = 1000 (solde AVANT opération)
-- Pour annuler, insérer une ligne avec sign = -1
INSERT INTO player_balance VALUES 
    (123, '2025-06-01', 1000, 1000, -1, now());   -- Annuler l'ancien solde

-- Étape 2 : Ajouter la nouvelle version du solde (maintenant 900 après le pari)
INSERT INTO player_balance VALUES 
    (123, '2025-06-01', -100, 900, +1, now());   -- Nouvelle version : changement -100, résultat 900

Mais c'est peu pratique. En pratique, il est plus facile de raisonner en termes de « changements » plutôt que de « solde complet ». Voici un modèle plus typique :

-- Le joueur place un pari de 100 roubles (le solde diminue)
-- Insérer une seule ligne avec sign = +1, où amount est le changement de solde (-100)
INSERT INTO player_balance VALUES 
    (123, '2025-06-01', -100, 900, +1, now());

-- Si vous devez annuler ce pari (par exemple, en raison d'une erreur technique)
-- Insérer une paire d'annulation
INSERT INTO player_balance VALUES 
    (123, '2025-06-01', -100, 900, -1, now()),   -- Annuler le pari
    (123, '2025-06-01', +100, 1000, +1, now());   -- Restaurer le solde

Pourquoi cela fonctionne : Lors de la fusion, ClickHouse trouvera une paire de lignes avec le même user_id, date et amount (si amount est dans ORDER BY) et des sign différents — et les supprimera. Seul le solde actuel reste.

Important : Vous devez vous assurer vous-même de la correction des paires. ClickHouse ne vérifie pas que la somme des changements s'équilibre. Il effondre simplement les lignes avec des signes opposés et la même clé ORDER BY.

5. SELECT avec SUM de sign — Comment lire correctement

Lors de la lecture des données, vous devez agréger en tenant compte du signe. Le modèle principal :

-- Obtenir le solde actuel de chaque joueur
SELECT 
    user_id,
    SUM(amount * sign) AS current_balance
FROM player_balance
WHERE sign != 0   -- Filtrer les zéros accidentels (ne devrait pas exister)
GROUP BY user_id;

Analyse de la logique :

• amount * sign — si sign = +1, le terme est amount ; si sign = -1, le terme est -amount (annule le précédent)
• SUM(...) — toutes les paires d'annulation s'annulent mutuellement dans la somme
• GROUP BY user_id — agréger par utilisateur

Pourquoi pas FINAL ? Contrairement à ReplacingMergeTree, pour CollapsingMergeTree vous devez toujours utiliser une agrégation avec SUM(amount * sign). Cela fonctionne correctement avant et après les fusions car le calcul du signe ne dépend pas du fait que les lignes soient physiquement effondrées ou non.

Exemple concret :

-- Données initiales (avant fusion) :
-- (123, -100, +1)   — pari 100 roubles
-- (123, -100, -1)   — annuler le pari
-- (123, +100, +1)   — restaurer le solde

-- Requête : SUM(amount * sign) = (-100*1) + (-100*-1) + (100*1) = -100 + 100 + 100 = 100
-- Correct : le solde a augmenté de 100 (l'annulation du pari a rendu l'argent)

Si vous voulez voir l'historique sans agrégation (par exemple, toutes les opérations dans l'ordre chronologique), un simple SELECT * affichera toutes les lignes, y compris celles annulées. C'est normal — c'est voulu.

6. Pièges — Le problème de l'ordre des lignes

Le plus grand piège : CollapsingMergeTree exige que les lignes avec la même clé arrivent dans le bon ordre — d'abord +1, puis -1 (ou l'inverse ? Voyons cela).

ClickHouse ne vérifie pas les horodatages. Il regarde l'ordre des lignes dans chaque partie lors de la fusion. Si une partie contient une paire (+1, -1), elle l'effondrera. Mais si +1 est dans une partie et -1 dans une autre, ils ne s'effondreront pas tant que ces parties ne fusionneront pas en une seule (ce qui peut prendre du temps).

Pourquoi est-ce un problème dans un système distribué ?

Imaginez que vos données arrivent via Kafka (une file de messages) depuis trois serveurs différents. Le serveur n°1 a envoyé « pari 100 roubles » (+1). Le serveur n°2 a envoyé « annuler le pari » (-1). Le serveur n°3 a envoyé « restaurer le solde » (+1). Ils peuvent aboutir dans différentes parties ClickHouse dans des ordres différents.

Si une partie ne contient que +1 et une autre contient -1, le solde sera temporairement incorrect (la somme montrera de l'argent en trop). Lorsque les parties fusionneront, les données se corrigeront, mais cela peut prendre une heure.

Analogie : C'est comme mettre des lettres dans deux dossiers différents. Un dossier contient « dette de 100 roubles » (+1), l'autre « dette annulée » (-1). Jusqu'à ce que vous fusionniez les dossiers en un seul, votre système comptable pensera qu'on vous doit 100 roubles.

7. VersionedCollapsingMergeTree — Résoudre le problème d'ordre

Pour surmonter le problème d'ordre, les développeurs de ClickHouse ont ajouté VersionedCollapsingMergeTree. Il ajoute une troisième colonne — version (généralement version ou timestamp).

CREATE TABLE player_balance_versioned
(
    user_id  UInt64,
    date     Date,
    amount   Int64,
    version  UInt64,          -- Numéro de version croissant de manière monotone
    sign     Int8
)
ENGINE = VersionedCollapsingMergeTree(sign, version)   -- Deux paramètres !
ORDER BY (user_id, date);

Comment ça marche :

• Lors d'une fusion, ClickHouse recherche des paires (+1, -1) avec la même clé ORDER BY et la même version.
• Si des lignes ont la même clé mais des versions différentes, elles ne sont PAS effondrées. Au lieu de cela, la ligne avec la version la plus élevée (l'état le plus récent) reste.
• La version permet un traitement correct même si les lignes arrivent dans le désordre — tant que l'annulation a la même version que l'opération d'origine.

Pourquoi cela résout le problème d'ordre : Même si +1 arrive après -1, ClickHouse verra qu'ils ont des versions différentes (ou la même — alors il effondre). Si les versions sont identiques, les paires s'effondrent indépendamment de l'ordre physique dans les parties. Si les versions diffèrent, la plus récente reste.

Exemple d'utilisation des versions :

-- Opération 1 : pari 100 roubles (version 1001)
INSERT INTO player_balance_versioned VALUES (123, '2025-06-01', -100, 1001, +1);

-- Annuler le même pari (même version 1001, sign = -1)
INSERT INTO player_balance_versioned VALUES (123, '2025-06-01', -100, 1001, -1);

-- Nouveau pari correct de 50 roubles (version 1002)
INSERT INTO player_balance_versioned VALUES (123, '2025-06-01', -50, 1002, +1);

Maintenant, même si les trois lignes arrivent dans des ordres différents et dans des parties différentes, lors de la fusion avec les mêmes versions, les paires s'effondreront. Seul le pari de 50 roubles avec la version 1002 reste.

8. Cas d'utilisation réel : Solde en temps réel d'un joueur dans un casino

Imaginez que vous ayez un microservice « Solde » qui doit afficher le solde actuel du joueur au centime près avec un délai de 5 secondes maximum.

Flux de travail :

-- Table pour toutes les opérations de solde
CREATE TABLE balance_operations
(
    user_id      UInt64,
    operation_id String,          -- ID unique de l'opération (pari, gain, annulation)
    amount       Int64,           -- Changement (+1000 gain, -500 pari)
    version      UInt64,          -- Numéro de version monotone
    sign         Int8,            -- +1 = nouvelle opération, -1 = annulation
    created_at   DateTime DEFAULT now()
)
ENGINE = VersionedCollapsingMergeTree(sign, version)
ORDER BY (user_id, operation_id, version);

Scénario 1 : Le joueur place un pari de 100 roubles

-- Insérer une ligne (sign = +1)
INSERT INTO balance_operations VALUES (123, 'bet_001', -100, 1001, +1, now());

Scénario 2 : Le joueur gagne 500 roubles (gain)

INSERT INTO balance_operations VALUES (123, 'win_001', +500, 1002, +1, now());

Scénario 3 : L'administrateur annule le pari bet_001 (le joueur a triché ?)

-- Annuler l'ancien pari (même operation_id, sign = -1, même version 1001)
INSERT INTO balance_operations VALUES (123, 'bet_001', -100, 1001, -1, now());
-- Ajouter une opération corrective (rendre 100 roubles)
INSERT INTO balance_operations VALUES (123, 'admin_correction_bet_001', +100, 1003, +1, now());

Comment lire le solde en temps réel :

-- Requête pour le tableau de bord (s'exécute toutes les 3 secondes)
SELECT 
    user_id,
    SUM(amount * sign) AS current_balance
FROM balance_operations
WHERE user_id = 123 AND created_at > now() - interval 1 day  -- limiter par temps
GROUP BY user_id;

Avec une utilisation correcte des versions, cette requête renverra le solde correct même avec un ordre d'insertion chaotique.

9. Comparaison des approches : CollapsingMergeTree vs ReplacingMergeTree pour le solde

Beaucoup de débutants demandent : « Pourquoi ne pas simplement utiliser ReplacingMergeTree et mettre à jour le solde comme une version de la ligne ? »

Comparons.

Caractéristique	CollapsingMergeTree	ReplacingMergeTree
Comment représenter un changement	Deux lignes : annuler (-1) et nouveau (+1)	Une nouvelle ligne avec une version plus élevée
Nécessité de stocker l'historique complet	Oui, jusqu'à l'effondrement	Oui, jusqu'à la fusion
Approche de lecture	SUM(amount * sign)	argMax(amount, version) ou FINAL
Complexité d'insertion	Plus élevée (nécessité de penser aux paires)	Plus faible (juste une nouvelle version)
Complexité de lecture	Plus faible (agrégation simple)	Plus élevée (FINAL est lent ou argMax)
Risque d'erreur	Les paires ne correspondent pas (erreur de logique)	Version non monotone (erreur client)

Quand choisir CollapsingMergeTree :

• Vous devez modifier fréquemment les mêmes clés (par exemple, le solde d'un joueur change 100 fois par heure).
• Vous voulez utiliser une agrégation simple SUM(amount * sign) et ne pas dépendre de FINAL.
• Vous avez un ordre d'insertion contrôlé ou utilisez VersionedCollapsingMergeTree.
• Vous devez annuler des opérations (annuler un pari) — dans ReplacingMergeTree, cela nécessiterait d'insérer une nouvelle ligne avec une version augmentée, ce qui ne reflète pas explicitement « l'annulation ».

Quand ReplacingMergeTree est meilleur :

• Vous avez des mises à jour peu fréquentes (par exemple, statut de commande : créée → payée → livrée).
• Vous stockez des attributs mutables non numériques plutôt que des agrégats numériques.
• Vous devez voir l'historique des versions de chaque enregistrement.

Exemple pour le solde — lequel est meilleur ? Pour un compte à forte charge (des milliers de paris par seconde), VersionedCollapsingMergeTree est meilleur. Il offre des performances prévisibles et un comportement correct en cas d'ordre chaotique.

10. Performances et quand c'est mieux que UPDATE dans PostgreSQL

Performances de CollapsingMergeTree

• Insertion : Très rapide (INSERT normal, pas de verrous). Vous payez pour stocker deux lignes au lieu d'en mettre à jour une — mais dans une base columnar, ce n'est pas si grave.
• Lecture avec agrégation : ClickHouse lit uniquement les colonnes amount et sign (stockage columnar !), effectue des calculs vectorisés rapides. Pour un milliard de lignes — des fractions de seconde.
• Fusion : Travail en arrière-plan. N'affecte pas les insertions.

Comparaison avec PostgreSQL

Dans PostgreSQL, mettre à jour un solde :

-- Mise à jour atomique avec verrou de ligne
UPDATE players SET balance = balance - 100 WHERE user_id = 123;

Avantages : simple, garanties ACID (atomicité, cohérence, isolation, durabilité), cohérence instantanée.

Inconvénients : À 10 000 mises à jour par seconde — verrous (verrous de ligne), WAL (journal des transactions), vacuum. Vous atteindrez les limites d'E/S.

Dans ClickHouse avec CollapsingMergeTree :

Avantages : 100 000+ insertions par seconde sur un seul serveur, compression des données (10:1), pas de verrous, mise à l'échelle linéaire.

Inconvénients : Pas de cohérence instantanée (agrégation nécessaire jusqu'à la fusion), logique plus complexe (sign, version), cohérence éventuelle — le système atteindra l'état correct, mais pas instantanément.

Quand CollapsingMergeTree est meilleur que PostgreSQL :

• Vous avez besoin de très nombreuses mises à jour (des milliers à des dizaines de milliers par seconde).
• Un délai de quelques secondes (pour l'effondrement) est acceptable.
• Vous utilisez déjà ClickHouse pour l'analyse.

Quand PostgreSQL est encore meilleur :

• Vous avez besoin d'une cohérence instantanée stricte (transfert bancaire entre comptes).
• Les mises à jour sont peu nombreuses (<1000 par seconde).
• Vous ne voulez pas complexifier l'architecture.

Prochaines étapes

Maintenant que vous connaissez CollapsingMergeTree et son grand frère VersionedCollapsingMergeTree. Prochains sujets à explorer :

• Comment choisir entre CollapsingMergeTree et ReplacingMergeTree — une liste de contrôle pour chaque tâche.
• Optimiser les fusions — paramètres comme merge_with_ttl_timeout pour accélérer l'effondrement des paires.
• Modèle : vue matérialisée + CollapsingMergeTree — pour des agrégats multi-niveaux.

Résumé : CollapsingMergeTree est un outil puissant mais qui exige de la discipline. Il ne pardonne pas les erreurs dans l'ordre d'insertion ou la correction des paires. Mais si vous le configurez correctement (surtout avec les versions), il offre des performances inaccessibles aux bases de données traditionnelles. Rappelez-vous la règle d'or : vérifiez toujours vos requêtes avec SUM(amount * sign) et utilisez VersionedCollapsingMergeTree dans les systèmes distribués.

---

← Précédente: SummingMergeTree et AggregatingMergeTree : Agrégation incrémentale sans douleur
→ Suivante: Partitionnement dans ClickHouse : Comment gérer les données au niveau des dossiers

— Editorial Team