ClickHouse in productie draaien: ingestie, merges en kosten bij 2 miljard rijen

Toen we begonnen met het bouwen van de analytics-laag van tracio.ai, hadden we een database nodig die onze specifieke workload aankon: 50.000 device-identificatie-events per seconde ingesten, 2+ miljard rijen opslaan en analytische queries in minder dan één seconde beantwoorden. We evalueerden PostgreSQL (te traag voor aggregaties op deze schaal), Elasticsearch (te duur voor time-series-analytics) en ClickHouse. ClickHouse won overtuigend.

Waarom ClickHouse

ClickHouse is een kolomgeoriënteerde OLAP-database die is ontworpen voor real-time analytics. Het belangrijkste voordeel voor onze workload is dat het alleen de kolommen leest die voor elke query nodig zijn. Wanneer een fraude-analist vraagt "toon me het fraudepercentage per land voor de laatste 7 dagen", leest ClickHouse alleen de kolommen country, timestamp en risk_score — en negeert het de andere 40+ kolommen in de event-tabel. Op een tabel van 2 miljard rijen vermindert dit de I/O met 95%.

ClickHouse comprimeert data ook uitzonderlijk goed. Onze events-tabel van 2 miljard rijen neemt 340 GB op schijf in beslag — ongeveer 170 bytes per rij gecomprimeerd, tegenover 1,2 KB per rij ongecomprimeerd. De compressieverhouding van 7:1 betekent dat er meer data in het geheugen past, wat direct vertaalt naar snellere queries.

Schema-ontwerp

Onze primaire tabel slaat één rij per identificatie-event op:

De tabel gebruikt de MergeTree-engine, gesorteerd op (workspace_id, toDate(timestamp), visitor_hash). Deze sortering is cruciaal — het betekent dat queries die gefilterd zijn op workspace en datumbereik minimale data lezen. De kolom visitor_hash maakt snelle lookups op visitor-ID mogelijk zonder secundaire index.

We kozen voor LowCardinality(String) voor country, device_type, browser_family en os_family omdat deze kolommen minder dan 10.000 verschillende waarden hebben. ClickHouse slaat LowCardinality-kolommen op als dictionary-gecodeerde integers, wat de opslag met 80% verlaagt vergeleken met gewone strings en GROUP BY-operaties versnelt.

Sharding-strategie

We sharden de events-tabel over 6 nodes met een hash van workspace_id. Dit zorgt ervoor dat alle events voor een bepaalde klant op dezelfde shard staan, wat betekent dat de meeste queries (gefilterd op workspace_id) één enkele shard raken. Cross-shard-queries zijn alleen nodig voor interne analytics.

Elke shard heeft 2 replica's voor hoge beschikbaarheid. Replicatie gebruikt de ingebouwde ReplicatedMergeTree-engine van ClickHouse met ZooKeeper-coördinatie. Failover is automatisch — als een shard uitvalt, worden queries naar de replica gerouteerd zonder wijzigingen aan de clientkant.

Ingestie-pipeline

Events stromen van ons Kafka-topic naar ClickHouse via een custom Go-service die inserts batcht. We voegen in batches van 10.000 rijen elke 500ms in — dit balanceert de ingestie-latentie (sub-seconde) met de insert-efficiëntie (ClickHouse presteert het best met grote batches).

De ingestie-service handelt back-pressure netjes af. Als ClickHouse traag is met het accepteren van inserts (tijdens merges of zware querybelasting), buffert de service tot 1 miljoen events in het geheugen en past het back-pressure toe op de Kafka-consumer. In 18 maanden productie hebben we nog nooit een event verloren.

Query-optimalisatie

Materialized views

Voor veelvoorkomende dashboard-queries gebruiken we materialized views die data vooraf aggregeren. Ons fraudepercentage-dashboard leest bijvoorbeeld uit een materialized view die fraud_detected-tellingen aggregeert per workspace, land en uur. De view verlaagt de gescande data voor deze query van 2 miljard rijen naar 5 miljoen rijen.

Projection-sortering

Met ClickHouse-projections kunnen we alternatieve sorteervolgordes voor een tabel definiëren zonder data te dupliceren. We voegden een projection toe gesorteerd op (workspace_id, visitor_hash, timestamp) voor visitor-timeline-queries. Zonder de projection scanden deze queries hele datumbereiken. Met de projection lezen ze alleen de blokken die de doel-visitor bevatten.

Approximate functions

Voor dashboard-queries waarbij exacte tellingen niet cruciaal zijn, gebruiken we de approximate functions van ClickHouse: uniqCombined voor distinct-tellingen (2% foutmarge, 10x sneller dan uniqExact) en quantileTDigest voor percentielberekeningen. Het fraude-analytics-dashboard gebruikt uitsluitend approximate functions, wat alle dashboard-queries onder 200ms houdt.

Performance-cijfers

Hier zijn representatieve query-benchmarks op ons productiecluster van 2 miljard rijen:

Fraudepercentage per land, laatste 7 dagen: 120ms. Visitor-timeline (50 events): 8ms. Unieke visitors per dag, laatste 30 dagen: 340ms. Risk-score-verdeling, laatste 24 uur: 95ms. Top 100 devices op event-aantal, laatste 30 dagen: 210ms.

Deze cijfers zijn inclusief de netwerk-round-trip van onze applicatieservers naar het ClickHouse-cluster. De pure query-uitvoeringstijd is doorgaans 30-50% lager.

Operationele lessen

Les 1: monitor merge-lag

De MergeTree-engine van ClickHouse voegt continu kleine data-parts samen tot grotere. Als merges achterlopen (door een hoge insert-rate of disk-I/O-contentie), gaat de queryprestatie achteruit omdat queries meer parts moeten scannen. We monitoren het aantal parts per partitie en alarmeren wanneer dit de 300 overschrijdt.

Les 2: vermijd grote ALTER TABLE-operaties

Een kolom toevoegen aan een tabel van 2 miljard rijen in ClickHouse is instantaan (het is alleen metadata). Maar het wijzigen van een kolomtype vereist het herschrijven van alle data-parts — een proces dat op ons cluster 6 uur duurde. We behandelen het schema nu als append-only: nieuwe kolommen worden vrij toegevoegd, maar typewijzigingen gaan via een migratietabel.

Les 3: TTL met voorzichtigheid

ClickHouse ondersteunt automatische data-expiratie via TTL. We stelden een TTL van 90 dagen in op onze events-tabel. De valkuil: TTL-verwijdering gebeurt tijdens merges, wat betekent dat verwijderde data uren of dagen na het verstrijken van de TTL kan blijven bestaan. Voor compliance-kritische verwijdering draaien we volgens een schema expliciete ALTER TABLE DELETE-queries.

Kosten

Ons ClickHouse-cluster met 6 nodes (elke node: 32 vCPU, 128 GB RAM, 2 TB NVMe) kost ongeveer $8.400/maand op bare-metal hosting. Dit slaat 2 miljard rijen op met 90 dagen retentie en verwerkt 50K inserts/seconde plus 200 gelijktijdige dashboard-queries. De kosten per opgeslagen event bedragen $0,0000042 — orden van grootte goedkoper dan vergelijkbare analytics op managed cloud-databases.