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 dashboard-queries onder één seconde 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, en de events-tabel droeg er oorspronkelijk een van 90 dagen. We hebben die verwijderd, om een reden die het doorgeven waard is: TTL-verwijdering gebeurt tijdens merges, dus rijen overleven uren of dagen na hun vervaldatum, en die vertraging wordt door niets begrensd wat jij in de hand hebt. Dat maakt TTL een opruimgereedschap in plaats van een verwijderingsgarantie — als je een klant moet beloven dat een record op een bepaalde datum weg is, houdt een expressie in een tabeldefinitie die belofte niet. Verwijdering die moet standhouden, moet expliciet, ingepland en verifieerbaar zijn.

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 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.