Warum wir ClickHouse für Klick-Analytik verwenden (und nicht Postgres)

Postgres ist exzellent. Wir nutzen es intensiv und vertrauen ihm. Es speichert jeden Link, jeden Workspace, jeden Abrechnungsdatensatz, jeden Nutzer. Aber bei etwa der 90-Tage-Marke auf einem wachsenden URL-Shortener stoßen Sie an eine Wand, wenn Sie dort auch rohe Klick-Ereignisse speichern. Die Wand ist nicht dramatisch - Postgres fällt nicht um, es wird einfach langsamer, in genau der spezifischen, frustrierenden Weise, in der aggregierte Abfragen auf großen append-only Tabellen langsamer werden. Index-Scans helfen nicht mehr. Autovacuum kommt nicht hinterher. Ein Dashboard, das in 800 ms geladen wurde, lädt jetzt in 4 Sekunden. Sie fügen mehr Indizes hinzu. Es wird schlimmer.

Dieser Beitrag erklärt, warum wir Klick-Ereignisse nach ClickHouse verschoben haben, wie das Schema aussieht, wie der Ingestion-Pfad funktioniert und wo die scharfen Kanten sind. Es ist ein Begleitstück zum Redirect-Latenz-Beitrag und behandelt die Datenebene, die nachgelagert zum Redirect sitzt - der Teil, der rohe Fire-and-forget-Klick-Ereignisse in die Dashboards auf der Analytics-Lösungsseite verwandelt.

TL;DR#

• Klick-Ereignisse sind append-only und aggregat-abfrage-geformt. Spaltenbasierte Speicherung gewinnt bei beiden.
• Der Postgres-Overhead pro Zeile, die Index-Aufblähung und die Vacuum-Konkurrenz potenzieren sich jenseits von ~30 Mio. Ereignissen in einer gemeinsamen Tabelle.
• ClickHouse mit ORDER BY (workspace_id, link_id, created_at) liefert 90-tägige Per-Workspace-Rollups in unter 100 ms ohne zusätzliche Indizes zurück.
• Link-Metadaten bleiben in Postgres (veränderlich, transaktional); Klick-Ereignisse bleiben in ClickHouse (append-only, analytisch). Die Trennung ist sauber, weil sich die beiden zur Schreibzeit nicht überschneiden müssen.

Warum Postgres mit diesem Workload kämpft#

Die Form des Workloads ist das Problem. Ein Klick-Ereignis ist:

• Einmal geschrieben und nie aktualisiert.
• Mit hohen, schubweisen Raten geschrieben - 50:1 Schreib-zu-Lese-Verhältnis ist eine grobe Schätzung, und am Tag eines Kampagnenstarts spitzt sich das Verhältnis weiter zu.
• In Aggregat-Form abgefragt: Gesamtklicks pro Link, Geo-Aufschlüsselung für einen Workspace, Geräteaufteilung über die Top-100-Links einer Kampagne. Fast niemand fragt eine einzelne Klick-Zeile direkt ab.

Postgres ist für eine andere Reihe von Annahmen gebaut. Jede Zeile trägt einen Tupel-Header, Transaktions-ID-Felder (xmin, xmax) und Sichtbarkeits-Metadaten. Für transaktionale Workloads, bei denen Sie Zeilen an Ort und Stelle aktualisieren und MVCC benötigen, um gleichzeitige Lese- und Schreibvorgänge zu handhaben, ist dieser Overhead der Preis, den Sie für Korrektheit zahlen. Für append-only analytische Daten, die Sie nie wieder anrühren, ist es reine Verschwendung.

Das Index-Problem ist schlimmer. Postgres' B-Tree-Indizes funktionieren gut für selektive Punkt-Lookups - die Zeile mit link_id = 'xyz' zu finden. Sie helfen weniger, wenn Sie GROUP BY link_id, country über 10 Millionen Zeilen für einen Workspace ausführen. Der Index schmälert den Scan, aber die Abfrage muss immer noch dekomprimierte Zeilendaten aus Heap-Seiten holen, sie deserialisieren, sie aggregieren. Bei 30M Zeilen ist die Abfrage langsam. Bei 100M Zeilen wird es schmerzhaft.

Autovacuum macht dies in der Praxis schlimmer. Inserts mit hohem Durchsatz erzeugen tote Tupel (MVCC-Zeilenversionen, die für aktive Transaktionen nicht mehr sichtbar sind, aber noch nicht aufgeräumt wurden). Autovacuum holt sie zurück, aber es konkurriert mit Live-Abfragen um I/O. Sie sehen periodische Latenz-Spitzen auf Ihren Postgres-Dashboards, die mit Autovacuum-Läufen auf der Ereignistabelle korrespondieren. Die TimescaleDB-Kompressions- und Chunk-Architektur-Docs sind die Details wert, wie eine Postgres-Erweiterung versucht, dies zu umgehen - das Problem ist real genug, dass eine dedizierte Zeitreihen-Schicht auf Postgres existiert, um es zu adressieren.

Wir haben Klicks in den ersten 90 Tagen des Projekts in Postgres laufen lassen. Ein Workspace mit ~4 Mio. Klick-Ereignissen hatte ein Dashboard, das 3,2 Sekunden brauchte, um einen 7-Tage-Rollup nach Land zu rendern. Das Hinzufügen eines partiellen Index auf (workspace_id, created_at) brachte es auf 1,4 Sekunden. Damals gut genug; klar kein Pfad, der zu 100M+ Ereignissen pro Monat skaliert.

Warum ClickHouse passt#

ClickHouse ist eine spaltenbasierte Datenbank-Engine, die explizit für analytische Workloads gebaut wurde - abgerufen am 12.05.2026. Spaltenbasiert bedeutet, dass die Daten für jede Spalte zusammenhängend auf der Festplatte gespeichert werden, statt zeilenweise verschachtelt. Für eine Abfrage wie „Klickanzahl nach Land summieren für Workspace X über die letzten 30 Tage" liest die Engine nur die Spalten country, workspace_id und created_at. Sie berührt nie user_agent_hash oder referrer_host. Die I/O-Reduzierung auf breiten Tabellen mit vielen Spalten ist erheblich.

Das Kompressionsverhältnis ist die zweite Eigenschaft. Spaltenbasierte Daten komprimieren signifikant besser als Zeilen-Daten, weil wiederholte Werte in derselben Spalte - derselbe workspace_id, der tausende Male erscheint, dasselbe Set von etwa 60 Ländercodes, eine Handvoll os-Werten - unter ClickHouses LZ4- und ZSTD-Codecs auf fast nichts komprimieren. Unsere Klick-Ereignisse komprimieren auf etwa 12-15 % ihrer Rohgröße auf der Festplatte. Die gleichen Daten in Postgres liegen bei 35-45 % der Rohgröße mit Standard-TOAST-Kompression. Das ist nicht nur ein Speichergewinn - kleinere On-Disk-Daten bedeuten, dass mehr davon in den OS-Page-Cache passt, was weniger Disk-Reads pro Abfrage bedeutet.

Die dritte Eigenschaft ist die MergeTree-Engine-Familie - abgerufen am 12.05.2026. MergeTree-Tabellen sortieren und clustern Daten nach dem ORDER BY-Schlüssel der Tabelle. Abfragen, die nach den führenden Spalten dieses Schlüssels filtern oder gruppieren, lesen nur die Datenteile, die sich mit dem Filterbereich überschneiden. Das ist ClickHouses Äquivalent zu einem geclusterten Index, außer dass er obligatorisch ist (Sie definieren ihn bei der Tabellenerstellung) und für jede Abfrage gilt, nicht nur für indizierte Spalten.

Wir verwenden ORDER BY (workspace_id, link_id, created_at). Eine Abfrage für die Klicks des Workspace abc123 in den letzten 7 Tagen liest nur die Datenteile, in denen workspace_id = 'abc123' erscheint, und liest dann innerhalb dieser Teile nur das Zeitbereichs-Slice. Bei 90 Tagen Klick-Daten berührt das typischerweise 3-8 % der gesamten Datenteile. Der spärliche Primärindex, den ClickHouse über die ORDER BY-Spalten pflegt, macht dieses Targeting effizient, ohne einen dichten B-Tree für jedes Abfragemuster zu erfordern. ReplicatedMergeTree fügt HA hinzu - zwei ClickHouse-Knoten mit ZooKeeper-äquivalenter Koordination (ClickHouse Keeper), die Teile replizieren, sobald sie geschrieben werden.

Das Schema, das wir ausliefern#

CREATE TABLE clicks
(
    workspace_id    LowCardinality(String),
    link_id         String,
    click_id        UUID,
    created_at      DateTime64(3, 'UTC'),
    country         LowCardinality(FixedString(2)),
    device          LowCardinality(String),
    os              LowCardinality(String),
    browser         LowCardinality(String),
    referrer_host   String,
    utm_source      LowCardinality(String),
    utm_medium      LowCardinality(String),
    utm_campaign    String,
    utm_term        String,
    utm_content     String,
    ip_truncated    String,
    user_agent_hash FixedString(64)
)
ENGINE = ReplicatedMergeTree('/clickhouse/tables/{shard}/clicks', '{replica}')
PARTITION BY toYYYYMM(created_at)
ORDER BY (workspace_id, link_id, created_at)
SETTINGS index_granularity = 8192;

Einige Schema-Entscheidungen, die hervorzuheben sind.

LowCardinality(String) auf workspace_id, country, device, os, browser, utm_source, utm_medium - diese Spalten haben begrenzte Kardinalität (hunderte unterschiedlicher Workspace-IDs, etwa 250 Ländercodes, eine Handvoll Gerätetypen). LowCardinality aktiviert Dictionary-Encoding, das die Werte als ganzzahlige Referenzen in ein Wörterbuch einzigartiger Strings speichert. Die Abfragegeschwindigkeit auf diesen Spalten verbessert sich materiell; die Kompression verbessert sich weiter. Wir haben das aus den ClickHouse-Docs zum LowCardinality-Datentyp gelernt - die Faustregel ist, dass jede String-Spalte mit weniger als 10.000 verschiedenen Werten ein Kandidat ist.

PARTITION BY toYYYYMM(created_at) erstellt eine separate Datenpartition pro Kalendermonat. Alte Partitionen können (oder können in langsameren Speicher verschoben werden) mit einer einzigen ALTER TABLE clicks DROP PARTITION '202501'-Anweisung gelöscht werden, was sofort ist - kein Full-Table-Scan. Für DSGVO-Datenaufbewahrungs-Durchsetzung ist das praktisch wichtig: Das Löschen der historischen Klicks eines Workspaces ist eine einzelne Operation pro Monatspartition statt eines verteilten DELETE WHERE workspace_id = ?.

ip_truncated speichert die IP-Adresse mit auf null gesetztem letzten Oktett (203.0.113.0 nicht 203.0.113.42). Die volle IP wird nie gespeichert. Die trunkierte Form reicht für die Geo-Lookup aus - die wir zum Ingest-Zeitpunkt, vor der Speicherung, durchführen - und stellt keine personenbezogenen Daten nach der DSGVO-Artikel-4-Definition dar, wenn sie mit unseren Datenminimierungspraktiken kombiniert wird. Siehe solutions/analytics für die vollständige Datenschutzarchitektur.

Der Ingestion-Pfad#

Der Redirect-Handler an jedem Edge-POP ist fire-and-forget. Er hängt ein Klick-Ereignis an ein Redpanda-Topic (clicks.v1) an und gibt die 302 aus. Er wartet nicht auf eine Bestätigung von ClickHouse. Wenn Redpanda in diesem Moment nicht verfügbar ist, wird der Klick verworfen - die Weiterleitung nicht. Die Hierarchie ist absichtlich: Ein verpasster Klick ist wiederherstellbar (wir können näherungsweise Zähler aus Access-Logs rekonstruieren); eine fehlgeschlagene Weiterleitung ist für den Nutzer sichtbar.

Redpanda-Consumer-Groups - abgerufen am 12.05.2026 - bieten die Zustellungsgarantie zwischen Edge und Ingester. Der click-ingester-Dienst läuft als Consumer-Group gegen das clicks.v1-Topic. Partitionen werden automatisch zugewiesen; wenn eine Ingester-Instanz ausfällt, werden ihre Partitionen innerhalb des Heartbeat-Timeouts der Gruppe auf überlebende Instanzen umverteilt.

Der Ingester sammelt Ereignisse im Speicher und flusht in Batches: 1.000 Zeilen oder 250 ms, je nachdem, was zuerst eintritt. Das ist wichtig. ClickHouses MergeTree-Engine ist für Bulk-Inserts optimiert - sie schreibt jeden Insert als neuen Datenteil, und viele winzige Inserts erzeugen viele winzige Teile, was aggressive Hintergrund-Merges auslöst und Abfragen während der Merge-Last verlangsamt. Die ClickHouse-Docs empfehlen Inserts von mindestens 1.000 Zeilen, um die Anzahl der Teile handhabbar zu halten. Wir flushen bei 1.000 Zeilen / 250 ms und haben im Normalbetrieb keine Teil-Anzahl-Probleme gesehen.

Backpressure propagiert durch Redpanda. Wenn ClickHouse langsam ist oder neu startet, erhöht sich der Consumer-Lag des Ingesters, was wir als Frühindikator für Datenfrische-Probleme überwachen. Redpandas Retention-Konfiguration (14 Tage) gibt uns ein bedeutsames Replay-Fenster, falls ClickHouse von Grund auf neu aufgebaut werden muss.

Die Architektur wird detaillierter unter /docs/architecture/click-ingester beschrieben.

Postgres vs ClickHouse: die Trennung#

Zwei Datenbanken für einen Dienst sind ein Kostenfaktor, der gerechtfertigt werden muss. Die Rechtfertigung hier ist, dass die beiden Workloads bei Skalierung wirklich inkompatibel sind und sich die Daten nicht in einer Weise überschneiden, die synchrone Koordination erfordert.

Postgres besitzt Link-Metadaten: die Tabellen links, workspaces, users, billing, rules. Diese sind veränderlich (Links werden archiviert, Ziel-URLs ändern sich, Abrechnungsdatensätze werden aktualisiert), transaktional (das Erstellen eines Links und das Erzeugen seines Slugs müssen atomar sein) und lese-lastig im Verhältnis zu Schreibvorgängen. B-Tree-Indizes und MVCC sind genau das, was Sie wollen.

ClickHouse besitzt Klick-Ereignisse: die Tabelle clicks. Diese sind append-only (ein einmal geschriebener Klick wird nie aktualisiert), schreib-lastig und werden in Aggregat-Mustern abgefragt, die spaltenbasierte Speicherung besser handhabt. Es gibt keine Cross-Database-Joins zur Schreibzeit - der Ingester fügt Klick-Ereignisse mit workspace_id und link_id als denormalisierten String-Feldern ein. Zur Abfragezeit kann die Analytics-API Link-Metadaten aus Postgres und Klickzähler aus ClickHouse separat nachschlagen und dann im Anwendungscode verbinden. Das ist eine zusätzliche Netzwerk-Round-Trip; es ist ein viel besserer Trade als die analytische Abfragelast auf Postgres zu legen.

Die Citus-Distributed-Postgres-Dokumentation beschreibt die Alternative: verteilte Postgres-Erweiterungen, die für analytische Zeitreihen-Workloads konzipiert sind. Wir haben diesen Pfad evaluiert und sind zu dem Schluss gekommen, dass ClickHouses Abfrage-Performance und Kompressionsverhältnis für rein analytische Workloads weit genug voraus sind, dass der operative Overhead von zwei Datenbanken es wert ist. Wenn Ihr Workload gemischt ist - etwas analytisch, viele Zeilen-Mutationen - macht der Citus-Ansatz mehr Sinn.

Abfragemuster, die wir stündlich treffen#

Das Dashboard aktualisiert sich für jeden Workspace ungefähr in einem 60-Sekunden-Zyklus. Es gibt drei schwere Abfragen:

Per-Link-Klick-Rollup, letzte 24 Stunden. Der obere Teil jeder Link-Liste zeigt eine Sparkline. Das ist ein GROUP BY link_id, toHour(created_at) über die letzten 24 Stunden für den Workspace.

Länder-Aufschlüsselung für eine Kampagne. Ein Marketingteam, das die Kampagnen-Performance prüft, möchte GROUP BY country, COUNT(*) gefiltert nach utm_campaign über einen Datumsbereich.

Geräteaufteilung über die Top-100-Links eines Workspaces. Der Analytik-Tab zeigt eine Geräte/OS-Aufschlüsselung. Das ist GROUP BY device, os, COUNT(*) für die verkehrsstärksten Links des Workspaces über die letzten 30 Tage.

Alle drei Muster werden vom ORDER BY (workspace_id, link_id, created_at)-Clustering gut bedient. Der Workspace-Filter beschneidet den Scan sofort auf das richtige Datenslice; die Spalten link_id und created_at schmälern es weiter für die ersten beiden Muster. Die Geräteaufteilungs-Abfrage scannt mehr Spalten, profitiert aber dennoch vom Clustering auf Workspace-Ebene.

Eine repräsentative Abfrage für die Top-100-Links nach Klickanzahl über die letzten 24 Stunden:

SELECT
    link_id,
    count() AS clicks_24h
FROM clicks
WHERE
    workspace_id = 'ws_abc123'
    AND created_at >= now() - INTERVAL 1 DAY
GROUP BY link_id
ORDER BY clicks_24h DESC
LIMIT 100

Auf einem Workspace mit 12 Millionen Klick-Ereignissen, die sich über 90 Tage erstrecken, gibt diese Abfrage in etwa 45-80 ms zurück, abhängig von der Anzahl der Datenteile in der relevanten Partition. Dieselbe Abfrage gegen eine Postgres-Tabelle gleicher Größe läuft 2-4 Sekunden mit der besten Index-Konfiguration, die wir versucht haben.

Wir verwenden keine ClickHouse-Materialized-Views für diese Muster. Das ORDER BY-Clustering und das Partition-Pruning sind ausreichend, und Materialized Views fügen Schema-Migrations-Komplexität hinzu. Falls die Abfrage-Latenz bei höheren Ereignisvolumina jemals zum Problem wird, sind Materialized Views der nächste Hebel - nicht zusätzliche Indizierung.

Versagensmodi, die wir getroffen haben#

Drei Produktions-Vorfälle sind dokumentationswürdig.

ClickHouse-Keeper-Blip. ClickHouse Keeper ist die ZooKeeper-äquivalente Koordinationsschicht für ReplicatedMergeTree. Bei einer Gelegenheit verursachte ein Keeper-Knoten-Neustart während eines Hintergrund-Merges eine Pause der replizierten Tabelle für etwa 90 Sekunden, während der neue Leader gewählt wurde. Der Ingester wiederholte mit exponentiellem Backoff (2 s, 4 s, 8 s, Cap 60 s) - das ist Standard-Go-Retry-Logik mit context.DeadlineExceeded-Handling. Während des Ausfallfensters stauten sich etwa 1.800 Klick-Ereignisse im In-Memory-Puffer des Ingesters auf und wurden erfolgreich geflusht, sobald Keeper sich erholt hatte. Der einzige für den Nutzer sichtbare Effekt war eine ~2-Minuten-Lücke in den Live-Dashboard-Daten. Der Fix war, sicherzustellen, dass Keeper-Knoten über separate physische Hosts mit separaten Stromkreisen im Hetzner-Netzwerk verteilt sind.

Partition-Imbalance auf Redpanda. Das clicks.v1-Topic verwendet 12 Partitionen. Nach dem Hinzufügen einer zweiten Ingester-Instanz wies die Partition-Zuweisung von Redpandas Standard-Balancer 9 Partitionen einer Instanz und 3 der anderen zu - es verwendete die Total-Partitionen-pro-Consumer-Strategie und die Instanz-Zähler ergaben zufällig ein 3:1-Ungleichgewicht. Das war kein Datenverlust-Risiko, aber die überzugewiesene Instanz batchte langsam unter der Last, was die Klick-Verarbeitungs-Latenz auf etwa 800 ms erhöhte. Der Fix war ein manuelles Partition-Rebalancing über die Redpanda-Admin-API und der Wechsel zum rack-aware-Balancer, um Round-Robin-Zuweisung zu verwenden.

ALTER TABLE auf einer großen Tabelle ist async. Wir haben im Januar eine browser_version-Spalte hinzugefügt. ClickHouses ALTER TABLE ... ADD COLUMN bestätigt sofort und wendet die Schema-Änderung asynchron über Hintergrund-Teil-Merges an. Für etwa 20 Minuten nach dem Alter gaben Abfragen, die auf die neue Spalte referenzierten, NULL für Zeilen in unmutierten Teilen zurück. Nichts brach - wir fügten eine Spalte hinzu, änderten keine bestehende - aber eine Abfrage, die browser_version IS NOT NULL annahm, hätte falsche Ergebnisse zurückgegeben. Die Lehre: Behandeln Sie ALTER TABLE auf großen ClickHouse-Tabellen als eventuell konsistent, nicht synchron.

Was wir in ClickHouse nicht tun#

Zwei Dinge, die in Gesprächen aufkommen und die wir bewusst vermieden haben.

Per-Event-Joins zu Postgres-Tabellen. Einige Analytics-Abfragen wollen natürlich Klick-Ereignisse mit Link-Metadaten verbinden - „zeige mir alle Klicks für Links, die mit 'campaign-q2' getaggt sind, mit ihren Ziel-URLs". Dies als föderierte Abfrage (ClickHouse clicks verbunden mit Postgres links über eine ClickHouse-JDBC-Tabellenfunktion) ist möglich, aber langsam. Stattdessen denormalisieren wir entweder relevante Felder zur Ingest-Zeit ins Klick-Ereignis (wie utm_campaign, das aus der UTM-Vorlage des Links abgeleitet wird) oder lösen den Join im Anwendungscode auf: zwei separate Abfragen, in der Analytics-API-Schicht zusammengeführt. Das hält beide Datenbanken innerhalb ihrer Performance-Hüllen.

Per-Zeilen-Updates. ClickHouse unterstützt ALTER TABLE UPDATE für Zeilen-Mutationen, aber es ist eine async, ressourcenintensive Operation, die gegen die Annahmen der Engine arbeitet. Wenn Sie sich dabei wiederfinden, ein Klick-Ereignis im Nachhinein aktualisieren zu müssen - etwa um es retrospektiv als Bot-Klick zu markieren - haben Sie die Architektur falsch gebaut. Das richtige Muster ist, eine separate click_quality-Tabelle in ClickHouse zu speichern, die click_id auf einen Qualitätsscore abbildet, asynchron vom url-scanner-Dienst berechnet, und sie zur Abfragezeit zu JOINen. Lesen ist günstig; retrospektive Updates sind teuer und sollten selten sein.

Für das breitere Infrastrukturbild - wie der Edge-Redirect mit Redpanda interagiert, wie Klick-Deduplizierung funktioniert und wie der click-ingester-Dienst in die Service-Topologie passt - siehe /docs/architecture/click-ingester. Die Edge-Seitige Geschichte (Fire-and-forget Redpanda-Veröffentlichung, Latenzbudget) ist im Redirect-p95-Beitrag. Für das, was die Daten nachgelagert ermöglichen - Geo-Aufschlüsselungen, Geräteaufteilungen, UTM-Attribution-Dashboards - deckt die Solutions/Analytics-Seite die Produktoberfläche ab. Wenn Sie Link-Kampagnen gegen die API bauen und sich für die Abfragemuster interessieren, die das SDK exponiert, ist solutions/developers der relevante Einstiegspunkt.

Für Teams, die Elido als Link-Infrastruktur evaluieren, ist die Architekturentscheidung hier - ClickHouse für Klicks, Postgres für alles andere - auch das, was uns ermöglicht, Analytik ohne Sampling anzubieten. Der Smart-Links-erklärt-Beitrag behandelt, wie die Routing-Entscheidungen, die diese Klick-Ereignisse generieren, am Edge funktionieren.

Marius Voß ist DevRel und Edge-Infra bei Elido. Er half beim Design der click-ingester-Pipeline und hat mehr Zeit als ihm lieb ist mit dem Debuggen von ClickHouse Keeper Election Timing verbracht.