ClickHouse im Produktivbetrieb: Ingestion, Merges & Kosten bei 2 Mrd. Zeilen

Als wir mit dem Aufbau der Analytics-Schicht von tracio.ai begannen, brauchten wir eine Datenbank, die unsere spezifische Last bewältigen konnte: 50.000 Geräteidentifikations-Ereignisse pro Sekunde aufnehmen, 2+ Milliarden Zeilen speichern und analytische Queries in unter einer Sekunde beantworten. Wir evaluierten PostgreSQL (zu langsam für Aggregationen in diesem Maßstab), Elasticsearch (zu teuer für Zeitreihen-Analytik) und ClickHouse. ClickHouse gewann eindeutig.

Warum ClickHouse

ClickHouse ist eine spaltenorientierte OLAP-Datenbank, die für Echtzeit-Analytik konzipiert ist. Ihr entscheidender Vorteil für unsere Last besteht darin, dass sie nur die für jede Query benötigten Spalten liest. Wenn ein Betrugsanalyst fragt „zeig mir die Betrugsrate nach Land für die letzten 7 Tage“, liest ClickHouse nur die Spalten country, timestamp und risk_score — und ignoriert die anderen 40+ Spalten der Ereignistabelle. Bei einer Tabelle mit 2 Mrd. Zeilen reduziert dies die I/O um 95 %.

ClickHouse komprimiert Daten zudem außerordentlich gut. Unsere Ereignistabelle mit 2 Mrd. Zeilen belegt 340 GB auf der Festplatte — etwa 170 Byte pro Zeile komprimiert gegenüber 1,2 KB pro Zeile unkomprimiert. Das Kompressionsverhältnis von 7:1 bedeutet, dass mehr Daten in den Arbeitsspeicher passen, was sich direkt in schnelleren Queries niederschlägt.

Schema-Design

Unsere Haupttabelle speichert eine Zeile pro Identifikations-Ereignis:

Die Tabelle nutzt die MergeTree-Engine, geordnet nach (workspace_id, toDate(timestamp), visitor_hash). Diese Ordnung ist entscheidend — sie bewirkt, dass Queries, die nach Workspace und Datumsbereich gefiltert werden, minimale Daten lesen. Die Spalte visitor_hash ermöglicht schnelle Lookups per Besucher-ID ohne einen Sekundärindex.

Wir wählten LowCardinality(String) für country, device_type, browser_family und os_family, weil diese Spalten weniger als 10.000 verschiedene Werte haben. ClickHouse speichert LowCardinality-Spalten als wörterbuch-kodierte Ganzzahlen, was den Speicherbedarf gegenüber einfachen Strings um 80 % reduziert und GROUP-BY-Operationen beschleunigt.

Sharding-Strategie

Wir sharden die Ereignistabelle über 6 Knoten mittels eines Hashs von workspace_id. Das stellt sicher, dass alle Ereignisse eines bestimmten Kunden auf demselben Shard liegen, wodurch die meisten Queries (gefiltert nach workspace_id) einen einzigen Shard treffen. Cross-Shard-Queries werden nur für interne Analytik benötigt.

Jeder Shard hat 2 Replikate für Hochverfügbarkeit. Die Replikation nutzt ClickHouses eingebaute ReplicatedMergeTree-Engine mit ZooKeeper-Koordination. Das Failover ist automatisch — fällt ein Shard aus, werden Queries ohne clientseitige Änderungen an das Replikat geroutet.

Ingestion-Pipeline

Ereignisse fließen aus unserem Kafka-Topic über einen eigenen Go-Dienst in ClickHouse, der Inserts bündelt. Wir fügen in Batches von 10.000 Zeilen alle 500 ms ein — das balanciert die Ingestion-Latenz (Sub-Sekunde) mit der Insert-Effizienz aus (ClickHouse arbeitet am besten mit großen Batches).

Der Ingestion-Dienst bewältigt Gegendruck (Back-Pressure) elegant. Ist ClickHouse langsam bei der Annahme von Inserts (während Merges oder unter hoher Query-Last), puffert der Dienst bis zu 1 Million Ereignisse im Arbeitsspeicher und übt Gegendruck auf den Kafka-Consumer aus. In 18 Monaten Produktivbetrieb haben wir nie ein Ereignis verloren.

Query-Optimierung

Materialized Views

Für häufige Dashboard-Queries nutzen wir Materialized Views, die Daten vorab aggregieren. Unser Betrugsraten-Dashboard etwa liest aus einer Materialized View, die fraud_detected-Zählungen nach Workspace, Land und Stunde aggregiert. Die View reduziert die für diese Query gescannten Daten von 2 Mrd. Zeilen auf 5 Mio. Zeilen.

Projection-Ordnung

ClickHouse-Projections erlauben uns, alternative Sortierordnungen für eine Tabelle zu definieren, ohne Daten zu duplizieren. Wir fügten für Besucher-Timeline-Queries eine Projection hinzu, geordnet nach (workspace_id, visitor_hash, timestamp). Ohne die Projection scannten diese Queries ganze Datumsbereiche. Mit ihr lesen sie nur die Blöcke, die den Zielbesucher enthalten.

Approximative Funktionen

Für Dashboard-Queries, bei denen exakte Zählungen nicht kritisch sind, nutzen wir ClickHouses approximative Funktionen: uniqCombined für Distinct-Zählungen (2 % Fehlermarge, 10-mal schneller als uniqExact) und quantileTDigest für Perzentil-Berechnungen. Das Betrugsanalytik-Dashboard nutzt ausschließlich approximative Funktionen, wodurch alle Dashboard-Queries unter 200 ms bleiben.

Performance-Zahlen

Hier sind repräsentative Query-Benchmarks auf unserem Produktiv-Cluster mit 2 Mrd. Zeilen:

Betrugsrate nach Land, letzte 7 Tage: 120 ms. Besucher-Timeline (50 Ereignisse): 8 ms. Eindeutige Besucher pro Tag, letzte 30 Tage: 340 ms. Risk-Score-Verteilung, letzte 24 Stunden: 95 ms. Top 100 Geräte nach Ereignisanzahl, letzte 30 Tage: 210 ms.

Diese Zahlen schließen den Netzwerk-Roundtrip von unseren Anwendungsservern zum ClickHouse-Cluster ein. Die reine Query-Ausführungszeit liegt typischerweise 30-50 % niedriger.

Betriebliche Lektionen

Lektion 1: Merge-Lag überwachen

ClickHouses MergeTree-Engine fügt kontinuierlich kleine Datenteile (Parts) zu größeren zusammen. Fallen Merges zurück (durch hohe Insert-Rate oder Festplatten-I/O-Konkurrenz), verschlechtert sich die Query-Performance, weil Queries mehr Parts scannen müssen. Wir überwachen die Anzahl der Parts pro Partition und alarmieren, wenn sie 300 überschreitet.

Lektion 2: Große ALTER-TABLE-Operationen vermeiden

Das Hinzufügen einer Spalte zu einer Tabelle mit 2 Mrd. Zeilen ist in ClickHouse sofort erledigt (es ist rein metadatenbasiert). Doch das Ändern eines Spaltentyps erfordert das Neuschreiben aller Datenteile — ein Prozess, der auf unserem Cluster 6 Stunden dauerte. Wir behandeln das Schema nun als append-only: neue Spalten werden frei hinzugefügt, Typänderungen laufen jedoch über eine Migrationstabelle.

Lektion 3: TTL mit Vorsicht

ClickHouse unterstützt automatisches Datenablaufen per TTL. Wir setzen ein TTL von 90 Tagen auf unsere Ereignistabelle. Der Haken: Die TTL-Löschung erfolgt während Merges, was bedeutet, dass gelöschte Daten Stunden oder Tage nach Ablauf des TTL fortbestehen können. Für compliance-kritische Löschungen führen wir explizite ALTER-TABLE-DELETE-Queries nach Zeitplan aus.

Kosten

Unser ClickHouse-Cluster mit 6 Knoten (je Knoten: 32 vCPU, 128 GB RAM, 2 TB NVMe) kostet etwa $8.400/Monat auf Bare-Metal-Hosting. Er speichert 2 Mrd. Zeilen mit 90-tägiger Aufbewahrung und bewältigt 50K Inserts/Sekunde plus 200 gleichzeitige Dashboard-Queries. Die Kosten pro gespeichertem Ereignis betragen $0,0000042 — um Größenordnungen günstiger als vergleichbare Analytik auf gemanagten Cloud-Datenbanken.