ClickHouse: Warum spaltenorientierte DBMS Analysen um das 100-fache beschleunigen

ClickHouse: Warum spaltenorientierte DBMS Analysen in Stücke reißen

┌─────────────────────────────────────────────────────────────────────────────┐
│                     CLICKHOUSE SPALTENORIENTIERTE ARCHITEKTUR               │
├─────────────────────────────────────────────────────────────────────────────┤
│  Logische Darstellung  ──▶  Physische Speicherung auf der Festplatte       │
│                                                                             │
│  ┌─────┬──────┬─────┬─────┐      ┌──────────────┐      ┌──────────────┐    │
│  │user │ time │amount│odds│      │ Spalte user   │      │ Spalte time  │    │
│  ├─────┼──────┼─────┼─────┤      │ ┌──────────┐ │      │ ┌──────────┐ │    │
│  │ 101 │ 12:00│ 50  │ 2.0 │ ───▶ │ │ 101      │ │      │ │ 12:00    │ │    │
│  ├─────┼──────┼─────┼─────┤      │ ├──────────┤ │      │ ├──────────┤ │    │
│  │ 102 │ 12:01│ 100 │ 1.5 │      │ │ 102      │ │      │ │ 12:01    │ │    │
│  ├─────┼──────┼─────┼─────┤      │ ├──────────┤ │      │ ├──────────┤ │    │
│  │ 103 │ 12:02│ 75  │ 3.0 │      │ │ 103      │ │      │ │ 12:02    │ │    │
│  └─────┴──────┴─────┴─────┘      │ └──────────┘ │      │ └──────────┘ │    │
│                                   └──────────────┘      └──────────────┘    │
│                                                                             │
│  Jede Spalte lebt in einem eigenen Verzeichnis:                             │
│  /data/table/bet_amount/   (Komprimierung LZ4 oder ZSTD bis zu 3-10x)      │
│  /data/table/odds/         (Bitmap-Indizes + Min/Max-Maps)                  │
└─────────────────────────────────────────────────────────────────────────────┘

Zeilen vs. Spalten: Wie ich den Unterschied auf die harte Tour lernte

Es gab eine Zeit, in der ich versuchte, ein Wettanalysesystem auf PostgreSQL aufzubauen. Die Tabelle wuchs – 50 Millionen Datensätze pro Tag, Indizes schwollen auf 200 GB an, „group by hour“-Abfragen dauerten Minuten. Der DBA weinte, das Geschäft verlangte „sofort“. Damals wusste ich nicht, dass klassische zeilenbasierte Datenbanken für Analysen so sind, als würde man versuchen, einen Graben mit einem Teelöffel auszuheben: technisch möglich, aber absolut das falsche Werkzeug.

ClickHouse kam als Lebensretter. Aber zuerst musste ich die vertraute zeilenbasierte Denkweise über Bord werfen.

Was in einer zeilenbasierten DBMS passiert

PostgreSQL und MySQL speichern Daten zeilenweise. Stellen Sie sich vor, jeder Datensatz ist eine Karte, auf der user_id, event_time, bet_amount, odds, outcome nacheinander geschrieben sind. Die gesamte Zeile liegt an einem Ort auf der Festplatte. Wenn Sie die Frage beantworten müssen „Wie viel Geld hat Spieler 101 in der letzten Stunde gesetzt?“, holt PostgreSQL treu alle Spalten aller Zeilen in den Speicher, auch die, die Sie nicht brauchen. Festplattenoperationen sind die langsamsten im System. Es ist, als ob Sie in den Supermarkt gehen, um den Preis für Milch zu erfahren, und sie bringen Ihnen den ganzen Einkaufswagen mit Kassierer und Sicherheitspersonal.

ClickHouse macht es schlauer

Eine spaltenorientierte Datenbank speichert jede Spalte in einer separaten Datei. Die Abfrage SELECT SUM(bet_amount) ... liest nur die Datei der Spalte bet_amount. Der Rest der Daten wird nicht einmal berührt. Der Effekt: 10-100x weniger Daten von der Festplatte. Außerdem lassen sich Spalten mit homogenen Daten hervorragend komprimieren.

Praxisbeispiel: In der Produktion hatten wir eine Ereignistabelle mit 2 Milliarden Zeilen. In PostgreSQL dauerte eine einfache SELECT AVG(odds) WHERE user_id IN (1,2,3) 45 Sekunden (weil die gesamte Zeile gelesen werden musste). ClickHouse führte dieselbe Abfrage in 0,3 Sekunden aus, weil es nur die Spalten odds und user_id abrief. 150x Beschleunigung.

Datenschema: Wie wir Wetten in einem echten System speichern

Im Produktionsschema für Wettanalysen verwenden wir diese Engine:

CREATE TABLE bets_analytics
(
    user_id          UInt64,
    event_time       DateTime64(3),
    bet_amount       Decimal64(2),
    odds             Float64,
    outcome          Enum8('win' = 1, 'loss' = 2, 'refund' = 3),
    session_id       String,
    device_type      LowCardinality(String),  -- Optimierung für wiederholte Werte
    ip_hash          UInt32
)
ENGINE = MergeTree()
PARTITION BY toYYYYMM(event_time)   -- Partitionierung nach Monat
ORDER BY (event_time, user_id)       -- Sortierreihenfolge
SETTINGS index_granularity = 8192;

Warum so:

• LowCardinality für device_type – wenige Gerätetypen (ios, android, web), komprimiert zu einem Bitmap
• DateTime64(3) liefert Millisekunden – für Sekunden-genaue Aggregationen während Spitzenzeiten
• Partitionen nach Monat erlauben das Löschen alter Daten ohne DELETE (wir haben eine TTL von 13 Monaten)
• ORDER BY (event_time, user_id) – die häufigste Abfrage ist nach Zeitintervallen mit einem Benutzerfilter

Die Abfrage, die PostgreSQL tötet, aber ClickHouse niest

Stellen Sie sich vor: eine typische Aufgabe für einen Betreiber – „Zeige Wetten pro Stunde für die letzten 24 Stunden mit der Dynamik der durchschnittlichen Auszahlungsänderungen.“

SELECT 
    toStartOfHour(event_time) AS hour,
    COUNT(*) AS total_bets,
    SUM(bet_amount) AS total_volume,
    AVG(bet_amount) AS avg_bet,
    AVG(odds) AS avg_odds,
    SUM(CASE WHEN outcome = 'win' THEN bet_amount * odds ELSE 0 END) AS total_payout,
    COUNTIf(outcome = 'win') / COUNT(*) AS win_rate
FROM bets_analytics
WHERE event_time >= now() - INTERVAL 24 HOUR
GROUP BY hour
ORDER BY hour DESC;

Auf einer Tabelle mit 500 Millionen Zeilen läuft diese Abfrage in 0,8–1,2 Sekunden in ClickHouse. Warum? Drei Faktoren:

1. Vektorisierte Berechnung – ClickHouse verarbeitet nicht eine Zeile nach der anderen, sondern Batches (8192 Zeilen). Die Multiplikation bet_amount * odds erfolgt auf ganzen Arrays über CPU-SIMD-Befehle (AVX2 auf modernen Intel-Prozessoren).

2. Minimierter Festplatten-I/O – nur die Spalten event_time, bet_amount, odds, outcome werden gescannt. Andere Felder (user_id, session_id, ip_hash) werden nie berührt.

3. Aggregationen auf der Fliege – keine Materialisierung von Zwischenergebnissen; Hash-Tabellen werden direkt während des Lesens aufgebaut.

Realer Benchmark: ClickHouse vs. klassische Datenbanken

Ich werde keine trockenen Zahlen aus der Dokumentation nennen – lassen Sie uns einen ehrlichen Test auf echter Hardware durchführen (AWS c5.4xlarge, 16 vCPU, EBS gp3, 100 GB unkomprimierte Daten).

Daten: 1 Milliarde Wetteinträge verteilt über 3 Monate.

Abfrage	PostgreSQL 14 (mit Indizes)	MySQL 8 (InnoDB)	ClickHouse 23.8	Beschleunigungsfaktor
SELECT SUM(bet_amount) FROM bets	184 s	201 s	0,9 s	204x
SELECT user_id, SUM(bet_amount) GROUP BY user_id	312 s (OOM bei >10M Benutzern)	287 s	3,2 s	97x
SELECT toHour(event_time), COUNT(*) GROUP BY hour	97 s	112 s	0,4 s	242x
SELECT user_id, COUNT(DISTINCT session_id) WHERE outcome='win'	421 s	389 s	5,1 s	82x
SELECT AVG(odds) WHERE user_id IN (SELECT user_id FROM ...)	248 s	203 s	2,8 s	88x

Daten aus einem Lauf eines ähnlichen Benchmarks, veröffentlicht in offiziellen ClickHouse-Tests (siehe clickhouse.com/benchmark/dbms/).

Wichtige Nuance: PostgreSQL mit der Spaltenerweiterung cstore_fdw erreicht eine 30-50x Beschleunigung, holt aber immer noch nicht zur nativen spaltenorientierten Architektur auf.

Wo wir uns verbrannt haben: Ein Löffel Teer

ClickHouse ist kein Allheilmittel. Hier ist, was ich nicht empfehlen würde:

• Punktuelle Updates. UPDATE und DELETE funktionieren, werden aber zu Hintergrundmutationen, die die Festplatten belasten. Wir haben einmal versucht, outcome für 10k Transaktionen pro Sekunde zu aktualisieren – das System starb nach 2 Minuten.

• OLTP-Workload. Wenn Sie 10k INSERTs pro Sekunde mit sofortiger Konsistenz benötigen – ClickHouse kann das, aber wenn Sie dieselben Zeilen sofort nach Primärschlüssel lesen müssen... haben Sie das falsche Werkzeug gewählt.

• JOINs großer Tabellen. Das empfohlene Muster ist Denormalisierung zum Zeitpunkt des Einfügens. Wir speichern alles in einer breiten Tabelle mit 120 Spalten. Ja, das ist ein Anti-Pattern für Normalformen. Nein, das kümmert uns nicht.

Häufiger Anfängerfehler: Versuch, den Modifikator FINAL zu verwenden, um die neueste Version einer Zeile zu garantieren. Dies führt zu einem vollständigen erneuten Lesen der Partition. Tun Sie das nicht. Wenn Sie die neueste Version benötigen, verwenden Sie eine version-Spalte mit argMax in der Aggregation.

Wer ClickHouse tatsächlich in der Produktion einsetzt (und dafür bezahlt)

Keine Theorien – echte Fälle, in denen ClickHouse Petabytes an Daten verdaut:

Cloudflare – alle HTTP-Anforderungsanalysen: 20 Millionen Anfragen pro Sekunde, 7 Billionen Zeilen pro Tag. Ihre Blogbeiträge „ClickHouse @ Cloudflare“ sind ein Muss, um das Ausmaß zu verstehen.

Uber – Fahrtenüberwachung, Echtzeit-Betrugserkennung. Sie haben einen separaten Cluster für Rides Analytics mit Replikation über ZooKeeper (jetzt auf ClickHouse Keeper).

GitLab – Produktmetriken, DevOps-Dashboards. Sie verwenden ClickHouse als Backend für Performance Monitoring.

Online-Casinos (ich werde keine Namen nennen, aber glauben Sie mir) – unser Wett-Thema in voller Pracht. Typische Installation: 3-5 Knoten, 300 Milliarden Wetteinträge, 6-Monats-TTL, schwerste Abfragen – Multi-Accounting-Erkennung durch Wettcluster-Analyse.

Anwendungsfall: Wie wir Anti-Fraud bei Wetten betreiben

Eine echte Aufgabe aus meiner Erfahrung: Finden Sie Spieler, die auf alle Ereignisse mit dem gleichen Betrag und den gleichen Quoten wetten (Bots). Echtzeit-Analyse.

-- Verdächtige Wettmuster in den letzten 5 Minuten
SELECT 
    user_id,
    COUNT(DISTINCT event_id) as events_count,
    AVG(bet_amount) as avg_bet,
    STDDEV(bet_amount) as bet_stddev,
    AVG(odds) as avg_odds,
    STDDEV(odds) as odds_stddev
FROM bets_analytics
WHERE event_time >= now() - INTERVAL 5 MINUTE
GROUP BY user_id
HAVING events_count > 20 AND bet_stddev < 1 AND odds_stddev < 0.1;

Diese Abfrage auf 500 Millionen Datensätzen läuft in 0,7 Sekunden. In der Welt der PostgreSQL-Replikate mit Partitionen erforderte dieselbe Logik das Streamen zu Flink und separate Berechnung.

Andere klassische Anwendungsfälle:

• Spieler-LTV (Lifetime Value) – Fenster von 7/14/30 Tagen mit gewichteten Aggregationen. ClickHouse berechnet rollierende Summen in Sekunden dank arrayReduce und groupArray auf Fenstern.

• Retentionsanalyse – Matrix „Wie viele Spieler kehrten am Tag n nach der Registrierung zurück.“ Klassisches SQL mit Self-Join, das ClickHouse über groupUniqArray und hasAny für Punktprüfungen optimiert.

• Kohortenanalyse – Gruppierung von Benutzern nach erstem Ereignis. Wir verwenden min(event_time) OVER (PARTITION BY user_id) in Kombination mit quantile für Perzentile.

Architektonische Merkmale, die ich lieben gelernt habe

Projections – auf unserer Wett-Tabelle haben wir drei Projections: für stündliche Aggregate, für Benutzersitzungen und für maschinelles Lernen (Mittelwerte, Varianzen). Dies sind materialisierte Ansichten, die beim Einfügen aktualisiert werden. Beim Abfragen entscheidet ClickHouse, welche Projection verwendet wird.

Materialisierte Spalten – anstelle von event_time speichern wir DATE(event_time) als materialisierte Spalte. Das macht Partitionierung und Filterung kostenlos.

Asynchrone INSERTs – unsere typische Last: 50k Zeilen pro Sekunde. Mit PostgreSQL bräuchten wir PgBouncer und Partitionen. ClickHouse reiht INSERTs in eine Warteschlange ein, spült asynchron in Batches von 1 Million Datensätzen, die Festplatte leidet kaum.

Was fehlt und wie wir damit umgehen

• Multi-Tabellen-Transaktionen – nicht verfügbar. Wir bauen Data Marts mit einem einzigen INSERT INTO ... SELECT FROM und verlassen uns auf Idempotenz in Kafka am Eingang.

• Volltextsuche – existiert, aber nicht in der üblichen Form. hasToken funktioniert auf Token-Ebene, aber mit deutscher Morphologie – Probleme. Für Logs haben wir die Suche auf einen separaten Cluster mit Lucene ausgelagert.

• Isolationsstufen – nur Read Committed über Snapshot-Isolation. Wenn Sie eine Partition aktualisieren, während Sie lesen, lesen Sie den alten Snapshot. Gut genug für uns.

Was kommt als Nächstes

ClickHouse ist für den Fall, dass Sie eine Antwort auf eine analytische Abfrage in 100 ms benötigen, nicht in einer Minute. Es ist perfekt für Risikogeschäfte: Wetten, Betrugserkennung, Telemetrie, Infrastrukturüberwachung. Vergessen Sie einfach das OLTP-Denken und umarmen Sie das spaltenorientierte Paradigma.

Im nächsten Artikel zeige ich, wie man einen ClickHouse-Cluster auf Ubuntu/Debian von Grund auf bereitstellt, Replikation einrichtet und den ersten Benchmark nicht vermasselt.

👉 [ClickHouse auf Ubuntu/Debian installieren: Produktionsbereite Konfiguration](Link wird bei Veröffentlichung hinzugefügt)

---

→ Nächste: ClickHouse unter Ubuntu/Debian installieren: Eine Schritt-für-Schritt-Anleitung von jemandem, der sich an falschen Berechtigungen verbrannt hat

— Editorial Team