Warum wir ClickHouse für Klick-Analytik nutzen, 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, timestamp) 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 skaliert, wenn das Klickvolumen um Größenordnungen wächst.

Warum ClickHouse passt

ClickHouse ist eine spaltenorientierte Datenbank-Engine, die ausdrücklich für analytische Workloads gebaut wurde - abgerufen am 2026-05-12. Spaltenorientiert heißt, dass die Daten jeder Spalte zusammenhängend auf der Platte liegen statt zeilenweise verschränkt. Für eine Abfrage wie „Summe der Klicks nach Land für Workspace X der letzten 30 Tage“ liest die Engine nur die Spalten country_code, workspace_id und timestamp. Sie fasst nie user_agent oder referer an. Die I/O-Reduktion bei breiten Tabellen mit vielen Spalten ist erheblich.

Das Kompressionsverhältnis ist die zweite Eigenschaft. Spaltendaten komprimieren deutlich besser als Zeilendaten, weil wiederholte Werte in derselben Spalte - dieselbe workspace_id, die tausendfach auftaucht, derselbe Satz von etwa 60 Ländercodes, eine Handvoll device-Werte - unter den LZ4- und ZSTD-Codecs von ClickHouse auf fast nichts schrumpfen. Spalten mit niedriger Kardinalität wie diese sind dort, wo Spalten-Codecs gegenüber der Zeilenspeicherung von Postgres am meisten gewinnen. Das ist nicht nur ein Speichergewinn: Kleinere Daten auf der Platte bedeuten, dass mehr davon in den OS-Page-Cache passt, also weniger Plattenlesezugriffe pro Abfrage.

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, timestamp). Eine Abfrage der Klicks von Workspace 123 der letzten 7 Tage liest nur die Datenteile, in denen workspace_id = 123 vorkommt, und dann innerhalb dieser Teile nur den Zeitbereichs-Ausschnitt. Bei 90 Tagen Klickdaten berührt das typischerweise 3-8 % aller Datenteile. Der dünn besetzte Primärindex, den ClickHouse über die ORDER BY-Spalten führt, macht diese Eingrenzung effizient, ohne einen dichten B-Baum für jedes Abfragemuster zu verlangen. ReplicatedMergeTree ergänzt HA - zwei oder mehr ClickHouse-Knoten mit ZooKeeper-ähnlicher Koordination (ClickHouse Keeper), die Teile beim Schreiben replizieren. Das betreiben wir noch nicht: Die Produktion ist ein einzelner Host mit einem ClickHouse-Server auf einfachem MergeTree. Replikation ist der geplante nächste Schritt, sobald ein Server nicht mehr reicht.

Das Schema, das wir ausliefern

CREATE TABLE IF NOT EXISTS click_events
(
    timestamp       DateTime64(3, 'UTC'),
    link_id         UInt64,
    workspace_id    UInt64,
    domain_id       UInt64,
    slug            LowCardinality(String),
    host            LowCardinality(String),
    user_agent      String,
    referer         String,
    ip              String,
    country_code    LowCardinality(FixedString(2)) DEFAULT '',
    device          LowCardinality(String) DEFAULT '',
    browser         LowCardinality(String) DEFAULT '',
    destination     String DEFAULT '',
    retention_days  UInt16 DEFAULT 730,
    INDEX idx_workspace workspace_id TYPE bloom_filter GRANULARITY 4,
    INDEX idx_link      link_id      TYPE bloom_filter GRANULARITY 4
)
ENGINE = MergeTree()
PARTITION BY toYYYYMM(timestamp)
ORDER BY (workspace_id, link_id, timestamp)
TTL toDateTime(timestamp) + toIntervalDay(retention_days);

Das ist die Basistabelle. Spätere Migrationen ergänzen Region, Stadt und ungefähre Koordinaten, einen täglichen Besucher-Hash sowie Bot-, Scanner- und Verdachts-Flags per ALTER TABLE ... ADD COLUMN.

Einige Schema-Entscheidungen, die hervorzuheben sind.

LowCardinality(String) auf slug, host, country_code, device, browser - diese Spalten haben begrenzte oder stark repetitive Werte (einige hundert eigene Hostnamen, etwa 250 Ländercodes, eine Handvoll Gerätetypen). Die IDs sind einfache UInt64, die für sich schon gut komprimieren. LowCardinality aktiviert Wörterbuchkodierung, die die Werte als Integer-Verweise in ein Wörterbuch eindeutiger Strings speichert. Die Abfragegeschwindigkeit auf diesen Spalten verbessert sich spürbar; die Kompression verbessert sich weiter. Wir haben das aus der ClickHouse-Dokumentation zum Low-Cardinality-Datentyp gelernt - die Faustregel lautet, dass jede String-Spalte mit weniger als 10.000 verschiedenen Werten ein Kandidat ist.

PARTITION BY toYYYYMM(timestamp) erzeugt pro Kalendermonat eine eigene Datenpartition. Ganze alte Partitionen lassen sich mit einem einzigen Statement ALTER TABLE click_events DROP PARTITION '202501' löschen (oder auf langsameren Speicher verschieben), das sofort wirkt - ohne Full-Table-Scan. Eine Partition enthält jedoch die Klicks aller Workspaces für diesen Monat, sodass die Aufbewahrung pro Plan kein Partition-Drop sein kann. Stattdessen wird jede Zeile beim Ingest mit dem Fenster ihres Workspaces in retention_days versehen (90 Tage in Free, 365 in Pro, 730 in Business), und die TTL-Klausel entfernt abgelaufene Zeilen, wenn ClickHouse Teile zusammenführt.

ip speichert die gekürzte Adresse: Das Edge setzt das letzte Oktett einer IPv4-Adresse auf null (203.0.113.0 nicht 203.0.113.42) und behält bei IPv6 die ersten 48 Bit, bevor das Klick-Event den Redirect-Prozess verlässt. Die Geo-Abfrage passiert am Edge vor der Kürzung, sodass Land, Region, Stadt und ungefähre Koordinaten mit dem Klick gespeichert werden, ebenso der vollständige User-Agent. Eine gekürzte IP plus Stadt und User-Agent sollte weiterhin als personenbezogene Daten behandelt werden. Siehe solutions/analytics für die vollständige Datenschutzarchitektur.

Der Ingestion-Pfad

Der Redirect-Handler im Edge-Service ist Fire-and-Forget. Er hängt ein Klick-Event an ein Redpanda-Topic an (in der Produktion clicks) und gibt den 302 aus. Er wartet nicht auf eine Bestätigung von ClickHouse. Ist Redpanda in diesem Moment nicht erreichbar, wird der Klick verworfen - der Redirect nicht. Die Hierarchie ist bewusst: Ein verpasster Klick ist wiederherstellbar (wir können ungefähre Zählungen aus Access-Logs rekonstruieren); ein fehlgeschlagener Redirect ist für Nutzer sichtbar.

Redpanda-Consumer-Groups - abgerufen am 2026-05-12 - liefern die Zustellgarantie zwischen Edge und Ingester. Der Service click-ingester läuft als Consumer Group gegen das Topic clicks. Die Produktion betreibt heute einen Ingester; die Consumer Group ist das, was später einer zweiten Instanz erlaubt, Partitionen zu übernehmen und innerhalb des Heartbeat-Timeouts der Gruppe zu übernehmen, wenn wir eine hinzufügen.

Der Ingester sammelt Events im Speicher und schreibt sie in Batches: 1.000 Zeilen oder 5 Sekunden, je nachdem, was zuerst eintritt. Das ist wichtig. Die MergeTree-Engine von ClickHouse ist auf Bulk-Inserts optimiert - sie schreibt jeden Insert als neuen Datenteil, und viele winzige Inserts erzeugen viele winzige Teile, was aggressives Hintergrund-Merging auslöst und Abfragen unter der Merge-Last verlangsamt. Die ClickHouse-Dokumentation empfiehlt Inserts von mindestens 1.000 Zeilen, damit die Zahl der Teile beherrschbar bleibt. Wir schreiben bei 1.000 Zeilen / 5 Sekunden und hatten im Normalbetrieb keine Probleme mit der Teilezahl.

Backpressure propagiert durch Redpanda. Der Ingester committet den Offset eines Records erst, nachdem sein Batch in ClickHouse geschrieben ist; ist ClickHouse also langsam oder startet neu, werden fehlgeschlagene Batches wiederholt und der Consumer-Lag wächst, was der führende Indikator für Datenfrische-Probleme ist. Startet der Ingester selbst neu, werden nicht committete Records innerhalb des Retention-Fensters des Topics erneut aus Redpanda gelesen.

Die Architektur wird detaillierter unter /docs/guides/clickhouse-export 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-Events: die Tabelle click_events. Sie sind append-only (ein Klick wird nach dem Schreiben nie aktualisiert), schreiblastig und werden in Aggregationsmustern abgefragt, die Spaltenspeicher besser bewältigt. Es gibt beim Schreiben keine datenbankübergreifenden Joins - der Ingester fügt Klick-Events mit workspace_id und link_id als denormalisierte numerische Felder ein. Zur Abfragezeit kann die Analytics-API Link-Metadaten aus Postgres und Klickzahlen aus ClickHouse separat nachschlagen und dann im Anwendungscode joinen. Das ist ein zusätzlicher Netzwerk-Roundtrip; es ist ein deutlich besserer Tausch, 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. Oben in jeder Link-Liste steht ein Sparkline. Das ist ein GROUP BY link_id, toHour(timestamp) über die letzten 24 Stunden für den Workspace.

Länder-Aufschlüsselung für eine Kampagne. Ein Marketing-Team, das die Kampagnenperformance prüft, will GROUP BY country_code, COUNT(*), gefiltert auf die Links der Kampagne über einen Datumsbereich.

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

Alle drei Muster werden vom ORDER BY (workspace_id, link_id, timestamp)-Clustering gut bedient. Der Workspace-Filter beschneidet den Scan sofort auf das richtige Datenslice; die Spalten link_id und timestamp 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 click_events
WHERE
    workspace_id = 123
    AND timestamp >= now() - INTERVAL 1 DAY
GROUP BY link_id
ORDER BY clicks_24h DESC
LIMIT 100

Die Abfrage liest nur die Partitionen im Datumsbereich und nur die Spalten, die sie nennt, sodass ihre Kosten mit dem Ausschnitt wachsen, den Sie anfragen, nicht mit der ganzen Tabelle. Die äquivalente Postgres-Abfrage muss Zeilen durchlaufen, woher die 1,4 Sekunden oben kamen.

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.

Ausfallmodi, auf die wir uns vorbereiten

Die Produktion betreibt einen ClickHouse-Server, einen Redpanda-Broker und einen Ingester auf einem einzelnen Host, sodass die Ausfallmodi, die aufzuschreiben sich lohnt, die sind, die dieses Setup heute hat, und die, die mit dem geplanten Scale-out kommen.

ClickHouse-Neustarts. Auf einem Server pausiert ein ClickHouse-Neustart oder -Upgrade die Inserts. Der Ingester hält den fehlgeschlagenen Batch im Speicher, wiederholt ihn und committet Redpanda-Offsets erst nach einem erfolgreichen Insert, sodass Klicks warten, statt zu verschwinden. Der sichtbare Effekt ist eine Lücke in den Live-Dashboard-Daten, bis der Rückstau abgebaut ist. Eine Lücke kennen wir: Der Speicherpuffer hat keine Obergrenze, solange ClickHouse ausfällt, und das steht in unserem Backlog. Replikation mit ReplicatedMergeTree und ClickHouse Keeper ist der geplante Fix für diese Pause, und er bringt seinen eigenen mit: Eine Keeper-Leader-Wahl hält Inserts auf replizierten Tabellen ebenfalls kurz an, sodass Keeper-Knoten auf getrennten Hosts sitzen müssen.

Partition-Balance des Ingesters. Heute besitzt ein Ingester jede Partition des Topics clicks. Wenn wir eine zweite Instanz hinzufügen, entscheidet die Zuweisungsstrategie der Consumer Group, wie gleichmäßig Partitionen verteilt werden, und eine ungleiche Aufteilung zeigt sich als eine Instanz, die langsam batcht, während die andere leer läuft. Zu beobachten ist der Consumer-Lag pro Instanz, nicht nur die Gruppensumme.

Asynchrones ALTER TABLE. Wir haben click_events mehrfach um Spalten ergänzt (region, city, lat und lng, visitor_hash, die Scanner-Flags). ALTER TABLE ... ADD COLUMN in ClickHouse bestätigt sofort und schreibt bestehende Teile nicht um; Zeilen in älteren Teilen liefern den Standardwert der Spalte (leeren String oder null), bis Merges ihn materialisieren. Nichts bricht, aber eine Abfrage, die ein leeres city als „keine Stadt“ behandelt, zählt alte Zeilen falsch. Die Lehre: ALTER TABLE auf großen ClickHouse-Tabellen als letztlich konsistent behandeln und Standardwerte wählen, die sich von echten Werten unterscheiden lassen.

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. Manche Analytics-Abfragen wollen von Natur aus Klick-Events mit Link-Metadaten verbinden - „zeig mir alle Klicks für Links mit dem Tag 'campaign-q2' samt ihrer Ziel-URLs“. Das als föderierte Abfrage zu tun (ClickHouse click_events, per ClickHouse-JDBC-Tabellenfunktion mit Postgres links gejoint) ist möglich, aber langsam. Stattdessen denormalisieren wir entweder relevante Felder beim Ingest ins Klick-Event (wie destination, die URL, an die der Besucher tatsächlich geschickt wurde) oder lösen den Join im Anwendungscode: zwei getrennte Abfragen, in der Analytics-API-Schicht zusammengeführt. So bleiben beide Datenbanken innerhalb ihrer Leistungsgrenzen.

Per-Zeilen-Updates. ClickHouse unterstützt ALTER TABLE UPDATE für Mutationen auf Zeilenebene, aber es ist eine asynchrone, ressourcenintensive Operation, die gegen die Annahmen der Engine arbeitet. Wenn Sie ein Klick-Event im Nachhinein aktualisieren müssen - etwa um es rückwirkend als Bot-Klick zu markieren -, haben Sie die Architektur falsch gebaut. Wir entscheiden diese Flags vor dem Insert: Der Edge bewertet verdächtige Klicks und klassifiziert Link-Scanner und anonymisierende Netzwerke, bevor er das Event ausgibt, sodass is_suspicious, is_scanner und anonymizer_type mit der Zeile geschrieben werden. Müsste ein Score je später berechnet werden, wäre das Muster eine separate Tabelle, die zur Abfragezeit gejoint wird. Lesen ist billig; nachträgliche 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/guides/clickhouse-export. 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 Entwurf der click-ingester-Pipeline und hat mehr Zeit, als ihm lieb ist, damit verbracht, ClickHouse-Merge-Logs zu lesen.