CollapsingMergeTree in ClickHouse: Update ohne UPDATE

CollapsingMergeTree: Wie man Aggregate ohne UPDATE in ClickHouse aktualisiert

1. Warum CollapsingMergeTree benötigt wird – Das Problem der Aktualisierung von Aggregaten

Kehren wir zu unserem Online-Casino zurück. Jeder Spieler hat ein Guthaben. Wenn ein Spieler einen Einsatz tätigt, verringert sich das Guthaben. Wenn er gewinnt, erhöht es sich. Wenn ein Administrator eine verdächtige Transaktion storniert, ändert sich das Guthaben erneut.

In einer regulären Datenbank (PostgreSQL) würde man einfach UPDATE players SET balance = balance - 100 WHERE user_id = 123 ausführen. Einfach und klar.

Aber ClickHouse kann keine Daten aktualisieren. Gar nicht. Punkt. Warum? Weil ClickHouse für Analysen entwickelt wurde, bei denen Daten nur angehängt werden. Updates sind für spaltenorientierte Speicherung schmerzhaft, da Daten in komprimierten Blöcken vorliegen. Um eine einzelne Zelle zu ändern, müssten ganze Blöcke neu geschrieben werden.

Wie ändert man also das Guthaben? Man ändert nicht den alten Datensatz. Man fügt einen NEUEN Datensatz hinzu, der besagt: „Storniere die vorherige Änderung“ und „Füge die neue hinzu“. Dies wird als Materialisieren von Änderungen durch Stornierung bezeichnet.

Analogie aus dem echten Leben: Stellen Sie sich ein Buchhaltungsbuch vor, in das alle Einträge mit Tinte geschrieben werden. Man kann nicht radieren und korrigieren. Stattdessen schreibt man unten: „Zeile #45 – fehlerhaft, storniert. Neue Zeile #46 – korrekter Betrag.“ Bei der Berechnung der Summe liest man dann alle Zeilen und berücksichtigt die Stornierungen.

CollapsingMergeTree ist eine ClickHouse-Engine, die automatisch Paare von „+1“ und „-1“ während Hintergrund-Merges zusammenführt. Sie fungiert wie dieser Buchhalter: Sie sieht ein stornierendes Paar und verwirft beide Zeilen.

2. Das Prinzip der sign-Spalte – Die Mathematik der Stornierung

Die Hauptidee von CollapsingMergeTree ist eine spezielle sign-Spalte mit zwei möglichen Werten:

• +1 – „hinzufügen“ (aktuelle Version)
• -1 – „stornieren“ (veraltete Version)

Während eines Merges von Datenteilen sucht ClickHouse nach Paaren von Zeilen mit demselben Sortierschlüssel (ORDER BY), bei denen eine sign = +1 und die andere sign = -1 hat. Wenn ein solches Paar gefunden wird, werden beide Zeilen gelöscht. Es bleiben nur Zeilen ohne Paar übrig – also solche ohne Stornierung.

Warum das funktioniert: Jede Datenänderung wird als Stornierung der alten Version und Hinzufügen einer neuen dargestellt. Das Paar (+1, -1) ergibt in der Summe Null. Wenn Sie SUM(amount * sign) ausführen, heben sich alte Versionen auf und neue bleiben übrig.

Analogie zur doppelten Buchführung: In der Buchhaltung wird jede Transaktion zweimal erfasst: Soll und Haben. CollapsingMergeTree macht dasselbe – jede Änderung hat ihr Gegenteil. Beim Summieren heben sie sich gegenseitig auf.

3. CREATE TABLE – Schritt für Schritt

-- Erstellen einer Tabelle für Spielerguthaben mit Änderungshistorie
CREATE TABLE player_balance
(
    user_id      UInt64,              -- Spieler-ID
    date         Date,                -- Datum der Guthabenänderung
    amount       Int64,               -- Guthabenänderung (+100, -50, etc.)
    balance_after Int64,              -- Guthaben nach der Operation (optional)
    sign         Int8,                -- +1 – hinzufügen, -1 – stornieren
    updated_at   DateTime DEFAULT now()  -- Zeitstempel der Operation
)
ENGINE = CollapsingMergeTree(sign)    -- Collapsing-Engine, sign-Spalte angeben
ORDER BY (user_id, date)              -- Schlüssel für Gruppierung und Collapsing

Was hier wichtig ist:

• ENGINE = CollapsingMergeTree(sign) – der einzige obligatorische Parameter ist der Name der sign-Spalte (normalerweise sign oder is_active). Diese Spalte muss vom Typ Int8 sein (Ganzzahl von -128 bis 127), aber tatsächlich werden nur +1 und -1 verwendet.

• ORDER BY (user_id, date) – die Spalten in diesem Schlüssel bestimmen, welche Zeilen als „Paar“ betrachtet werden. Zwei Zeilen mit identischen Werten in allen ORDER BY-Spalten und entgegengesetztem sign (+1 und -1) werden zusammengeführt.

Was passiert, wenn ORDER BY nicht alle erforderlichen Felder enthält? Wenn Sie zum Beispiel user_id nicht aufnehmen, könnten Zeilen verschiedener Benutzer zusammengeführt werden – eine Katastrophe. Alle Felder, nach denen Sie Operationen unterscheiden möchten, müssen in ORDER BY enthalten sein.

Warum nicht PRIMARY KEY? Aus denselben Gründen wie bei anderen MergeTree-Engines – ORDER BY steuert die physische Reihenfolge und das Merging, während PRIMARY KEY (falls angegeben) nur den Index steuert.

4. Einfügen – Wie man Daten korrekt aktualisiert

Angenommen, ein Spieler hat ein Guthaben von 1000 Rubel. Er setzt 100 Rubel. Statt das Guthaben in einer einzelnen Zeile zu ändern, führen Sie zwei Inserts durch:

-- Schritt 1: Stornieren der alten Version des Guthabens (war 1000, sollte jetzt 900 sein)
-- Alte Version: user_id=123, date='2025-06-01', amount = 1000 (Guthaben VOR der Operation)
-- Zum Stornieren eine Zeile mit sign = -1 einfügen
INSERT INTO player_balance VALUES 
    (123, '2025-06-01', 1000, 1000, -1, now());   -- Altes Guthaben stornieren

-- Schritt 2: Neue Version des Guthabens hinzufügen (jetzt 900 nach Einsatz)
INSERT INTO player_balance VALUES 
    (123, '2025-06-01', -100, 900, +1, now());   -- Neue Version: Änderung -100, Ergebnis 900

Aber das ist umständlich. In der Praxis ist es einfacher, in „Änderungen“ statt in „vollständigem Guthaben“ zu denken. Hier ist ein typischeres Muster:

-- Spieler setzt 100 Rubel (Guthaben sinkt)
-- Nur eine Zeile mit sign = +1 einfügen, wobei amount die Guthabenänderung ist (-100)
INSERT INTO player_balance VALUES 
    (123, '2025-06-01', -100, 900, +1, now());

-- Wenn dieser Einsatz storniert werden muss (z.B. wegen eines technischen Fehlers)
-- Ein stornierendes Paar einfügen
INSERT INTO player_balance VALUES 
    (123, '2025-06-01', -100, 900, -1, now()),   -- Einsatz stornieren
    (123, '2025-06-01', +100, 1000, +1, now());   -- Guthaben wiederherstellen

Warum das funktioniert: Wenn der Merge-Zeitpunkt kommt, findet ClickHouse ein Paar von Zeilen mit derselben user_id, date und amount (wenn amount in ORDER BY ist) und unterschiedlichem sign – und löscht sie. Nur das aktuelle Guthaben bleibt übrig.

Wichtig: Sie müssen selbst für die Korrektheit der Paare sorgen. ClickHouse überprüft nicht, ob die Summe der Änderungen ausgeglichen ist. Es führt lediglich Zeilen mit entgegengesetztem sign und demselben ORDER BY-Schlüssel zusammen.

5. SELECT mit SUM von sign – Wie man korrekt liest

Beim Lesen von Daten müssen Sie das sign in der Aggregation berücksichtigen. Das Hauptmuster:

-- Aktuelles Guthaben jedes Spielers abrufen
SELECT 
    user_id,
    SUM(amount * sign) AS current_balance
FROM player_balance
WHERE sign != 0   -- Versehentliche Nullen herausfiltern (sollte es nicht geben)
GROUP BY user_id;

Logik im Detail:

• amount * sign – wenn sign = +1, ist der Term amount; wenn sign = -1, ist der Term -amount (hebt das Vorherige auf)
• SUM(...) – alle stornierenden Paare heben sich in der Summe gegenseitig auf
• GROUP BY user_id – nach Benutzer aggregieren

Warum nicht FINAL? Im Gegensatz zu ReplacingMergeTree verwenden Sie bei CollapsingMergeTree immer die Aggregation mit SUM(amount * sign). Dies funktioniert sowohl vor als auch nach Merges korrekt, da die sign-Mathematik nicht davon abhängt, ob Zeilen physisch zusammengeführt wurden oder nicht.

Beispiel aus der Praxis:

-- Anfangsdaten (vor Merge):
-- (123, -100, +1)   – Einsatz 100 Rub
-- (123, -100, -1)   – Einsatz stornieren
-- (123, +100, +1)   – Guthaben wiederherstellen

-- Abfrage: SUM(amount * sign) = (-100*1) + (-100*-1) + (100*1) = -100 + 100 + 100 = 100
-- Korrekt: Guthaben um 100 erhöht (Einsatzstornierung gab das Geld zurück)

Wenn Sie die Historie ohne Aggregation sehen möchten (z.B. alle Operationen in chronologischer Reihenfolge), zeigt ein einfaches SELECT * alle Zeilen an, einschließlich der stornierten. Das ist normal – so ist es gewollt.

6. Fallstricke – Das Problem der Zeilenreihenfolge

Die größte Falle: CollapsingMergeTree erfordert, dass Zeilen mit demselben Schlüssel in der richtigen Reihenfolge eintreffen – zuerst +1, dann -1 (oder umgekehrt? Lassen Sie uns das klären).

ClickHouse prüft keine Zeitstempel. Es betrachtet die Reihenfolge der Zeilen innerhalb jedes Teils während des Merges. Wenn ein Teil ein Paar (+1, -1) enthält, wird es zusammengeführt. Aber wenn +1 in einem Teil und -1 in einem anderen ist, werden sie erst zusammengeführt, wenn diese Teile zu einem verschmelzen (was eine Weile dauern kann).

Warum ist das in einem verteilten System ein Problem?

Stellen Sie sich vor, Ihre Daten kommen über Kafka (eine Nachrichtenwarteschlange) von drei verschiedenen Servern. Server #1 sendet „Einsatz 100 Rub“ (+1). Server #2 sendet „Einsatz stornieren“ (-1). Server #3 sendet „Guthaben wiederherstellen“ (+1). Sie könnten in verschiedenen ClickHouse-Teilen in unterschiedlicher Reihenfolge landen.

Wenn ein Teil nur +1 und ein anderer -1 enthält, ist das Guthaben vorübergehend falsch (die Summe zeigt zusätzliches Geld). Wenn die Teile zusammengeführt werden, korrigieren sich die Daten, aber das kann eine Stunde dauern.

Analogie: Es ist, als würde man Briefe in zwei verschiedene Ordner legen. Ein Ordner enthält „Schuld 100 Rubel“ (+1), der andere „Schuld erlassen“ (-1). Bis Sie die Ordner zu einem zusammenführen, denkt Ihr Buchhaltungssystem, dass Ihnen 100 Rubel geschuldet werden.

7. VersionedCollapsingMergeTree – Lösung des Reihenfolgeproblems

Um das Reihenfolgeproblem zu überwinden, haben die ClickHouse-Entwickler VersionedCollapsingMergeTree hinzugefügt. Es fügt eine dritte Spalte hinzu – Version (normalerweise version oder timestamp).

CREATE TABLE player_balance_versioned
(
    user_id  UInt64,
    date     Date,
    amount   Int64,
    version  UInt64,          -- Monoton steigende Versionsnummer
    sign     Int8
)
ENGINE = VersionedCollapsingMergeTree(sign, version)   -- Zwei Parameter!
ORDER BY (user_id, date);

Wie es funktioniert:

• Während eines Merges sucht ClickHouse nach Paaren (+1, -1) mit demselben ORDER BY-Schlüssel und derselben Version.
• Wenn Zeilen denselben Schlüssel, aber unterschiedliche Versionen haben, werden sie NICHT zusammengeführt. Stattdessen bleibt die Zeile mit der höchsten Version (dem neuesten Zustand) erhalten.
• Die Version ermöglicht eine korrekte Handhabung, selbst wenn Zeilen in falscher Reihenfolge eintreffen – solange die Stornierung dieselbe Version wie die ursprüngliche Operation hat.

Warum das das Reihenfolgeproblem löst: Selbst wenn +1 nach -1 eintrifft, sieht ClickHouse, dass sie unterschiedliche Versionen haben (oder dieselbe – dann wird zusammengeführt). Bei gleichen Versionen werden Paare unabhängig von der physischen Reihenfolge in den Teilen zusammengeführt. Bei unterschiedlichen Versionen bleibt die neuere erhalten.

Beispiel für die Verwendung von Versionen:

-- Operation 1: Einsatz 100 Rub (Version 1001)
INSERT INTO player_balance_versioned VALUES (123, '2025-06-01', -100, 1001, +1);

-- Denselben Einsatz stornieren (gleiche Version 1001, sign = -1)
INSERT INTO player_balance_versioned VALUES (123, '2025-06-01', -100, 1001, -1);

-- Neuer korrekter Einsatz 50 Rub (Version 1002)
INSERT INTO player_balance_versioned VALUES (123, '2025-06-01', -50, 1002, +1);

Selbst wenn alle drei Zeilen in unterschiedlicher Reihenfolge und in verschiedene Teile gelangen, werden sie beim Merge mit denselben Versionen zusammengeführt. Nur der 50-Rub-Einsatz mit Version 1002 bleibt übrig.

8. Praxisbeispiel: Echtzeit-Spielerguthaben in einem Casino

Stellen Sie sich vor, Sie haben einen „Guthaben“-Mikroservice, der das aktuelle Spielerguthaben auf den Cent genau mit einer Verzögerung von höchstens 5 Sekunden anzeigen muss.

Workflow:

-- Tabelle für alle Guthabenoperationen
CREATE TABLE balance_operations
(
    user_id      UInt64,
    operation_id String,          -- Eindeutige Operations-ID (Einsatz, Auszahlung, Stornierung)
    amount       Int64,           -- Änderung (+1000 Gewinn, -500 Einsatz)
    version      UInt64,          -- Monotone Versionsnummer
    sign         Int8,            -- +1 = neue Operation, -1 = Stornierung
    created_at   DateTime DEFAULT now()
)
ENGINE = VersionedCollapsingMergeTree(sign, version)
ORDER BY (user_id, operation_id, version);

Szenario 1: Spieler setzt 100 Rub

-- Eine Zeile einfügen (sign = +1)
INSERT INTO balance_operations VALUES (123, 'bet_001', -100, 1001, +1, now());

Szenario 2: Spieler gewinnt 500 Rub (Auszahlung)

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

Szenario 3: Administrator storniert Einsatz bet_001 (Spieler hat betrogen?)

-- Alten Einsatz stornieren (gleiche operation_id, sign = -1, gleiche Version 1001)
INSERT INTO balance_operations VALUES (123, 'bet_001', -100, 1001, -1, now());
-- Korrekturoperation hinzufügen (100 Rub zurückgeben)
INSERT INTO balance_operations VALUES (123, 'admin_correction_bet_001', +100, 1003, +1, now());

Wie man das Guthaben in Echtzeit liest:

-- Abfrage für Dashboard (läuft alle 3 Sekunden)
SELECT 
    user_id,
    SUM(amount * sign) AS current_balance
FROM balance_operations
WHERE user_id = 123 AND created_at > now() - interval 1 day  -- zeitlich begrenzen
GROUP BY user_id;

Bei korrekter Verwendung von Versionen liefert diese Abfrage selbst bei chaotischer Einfügereihenfolge das richtige Guthaben.

9. Vergleich der Ansätze: CollapsingMergeTree vs ReplacingMergeTree für Guthaben

Viele Anfänger fragen: „Warum nicht einfach ReplacingMergeTree verwenden und das Guthaben als Version der Zeile aktualisieren?“

Vergleichen wir.

Eigenschaft	CollapsingMergeTree	ReplacingMergeTree
Wie eine Änderung dargestellt wird	Zwei Zeilen: stornieren (-1) und neu (+1)	Eine neue Zeile mit höherer Version
Vollständige Historie speichern	Ja, bis zum Merge	Ja, bis zum Merge
Leseansatz	SUM(amount * sign)	argMax(amount, version) oder FINAL
Einfügekomplexität	Höher (Paare müssen bedacht werden)	Niedriger (nur neue Version)
Lesekomplexität	Niedriger (einfache Aggregation)	Höher (FINAL ist langsam oder argMax)
Fehlerrisiko	Paare stimmen nicht überein (Logikfehler)	Version nicht monoton (Client-Fehler)

Wann CollapsingMergeTree wählen:

• Sie müssen häufig dieselben Schlüssel ändern (z.B. Spielerguthaben ändert sich 100 Mal pro Stunde).
• Sie möchten eine einfache Aggregation SUM(amount * sign) verwenden und nicht von FINAL abhängig sein.
• Sie haben eine kontrollierte Einfügereihenfolge oder verwenden VersionedCollapsingMergeTree.
• Sie müssen Operationen rückgängig machen (einen Einsatz stornieren) – in ReplacingMergeTree müssten Sie dazu eine neue Zeile mit erhöhter Version einfügen, was nicht explizit „Stornierung“ widerspiegelt.

Wann ReplacingMergeTree besser ist:

• Sie haben seltene Aktualisierungen (z.B. Bestellstatus: erstellt → bezahlt → geliefert).
• Sie speichern nicht-numerische veränderliche Attribute anstelle von numerischen Aggregaten.
• Sie müssen die Versionshistorie jedes Datensatzes sehen.

Beispiel für Guthaben – was ist besser? Für ein hochbelastetes Konto (tausende Wetten pro Sekunde) ist VersionedCollapsingMergeTree besser. Es bietet vorhersagbare Leistung und korrektes Verhalten bei chaotischer Reihenfolge.

10. Leistung und wann es besser ist als UPDATE in PostgreSQL

Leistung von CollapsingMergeTree

• Einfügen: Sehr schnell (reguläres INSERT, keine Sperren). Sie zahlen dafür, dass Sie zwei Zeilen statt einer aktualisierten speichern – aber in einer spaltenorientierten Datenbank ist das nicht so schlimm.
• Lesen mit Aggregation: ClickHouse liest nur die Spalten amount und sign (spaltenorientierte Speicherung!), führt schnelle vektorisierte Berechnungen durch. Für eine Milliarde Zeilen – Bruchteile einer Sekunde.
• Merging: Hintergrundarbeit. Beeinträchtigt keine Inserts.

Vergleich mit PostgreSQL

In PostgreSQL das Aktualisieren eines Guthabens:

-- Atomares Update mit Zeilensperre
UPDATE players SET balance = balance - 100 WHERE user_id = 123;

Vorteile: einfach, ACID-Garantien (Atomarität, Konsistenz, Isolation, Dauerhaftigkeit), sofortige Konsistenz.

Nachteile: Bei 10.000 Updates pro Sekunde – Sperren (Zeilensperren), WAL (Write-Ahead Log), Vakuum. Sie stoßen an IO-Grenzen.

In ClickHouse mit CollapsingMergeTree:

Vorteile: 100.000+ Inserts pro Sekunde auf einem einzelnen Server, Datenkompression (10:1), keine Sperren, lineare Skalierung.

Nachteile: Keine sofortige Konsistenz (Aggregation bis zum Merge erforderlich), komplexere Logik (sign, Version), Eventual Consistency – das System erreicht den korrekten Zustand, aber nicht sofort.

Wann CollapsingMergeTree besser ist als PostgreSQL:

• Sie benötigen sehr viele Aktualisierungen (tausende bis zehntausende pro Sekunde).
• Eine Verzögerung von einigen Sekunden (für das Collapsing) ist akzeptabel.
• Sie verwenden bereits ClickHouse für Analysen.

Wann PostgreSQL immer noch besser ist:

• Sie benötigen strenge sofortige Konsistenz (Banküberweisung zwischen Konten).
• Aktualisierungen sind selten (<1000 pro Sekunde).
• Sie möchten die Architektur nicht verkomplizieren.

Wie geht es weiter

Jetzt kennen Sie CollapsingMergeTree und seinen großen Bruder VersionedCollapsingMergeTree. Nächste Themen zur Erkundung:

• Wie wählt man zwischen CollapsingMergeTree und ReplacingMergeTree – eine Checkliste für jede Aufgabe.
• Optimierung von Merges – Einstellungen wie merge_with_ttl_timeout, damit Paare schneller zusammengeführt werden.
• Muster: materialisierte Ansicht + CollapsingMergeTree – für mehrstufige Aggregate.

Zusammenfassung: CollapsingMergeTree ist ein leistungsstarkes, aber diszipliniertes Werkzeug. Es verzeiht keine Fehler in der Einfügereihenfolge oder der Korrektheit von Paaren. Aber wenn Sie es richtig einrichten (insbesondere mit Versionen), liefert es eine Leistung, die von traditionellen Datenbanken nicht erreicht wird. Denken Sie an die goldene Regel: Überprüfen Sie Ihre Abfragen immer mit SUM(amount * sign) und verwenden Sie VersionedCollapsingMergeTree in verteilten Systemen.

---

← Vorherige: SummingMergeTree und AggregatingMergeTree: Schmerzlose inkrementelle Aggregation
→ Nächste: Partitionierung in ClickHouse: So verwalten Sie Daten auf Ordnerebene

— Editorial Team