ClickHouse en production : ingestion, merges et coût à 2 milliards de lignes

Lorsque nous avons commencé à construire la couche analytique de tracio.ai, nous avions besoin d'une base de données capable de gérer notre charge de travail spécifique : ingérer 50 000 événements d'identification d'appareils par seconde, stocker plus de 2 milliards de lignes et répondre à des requêtes analytiques en moins d'une seconde. Nous avons évalué PostgreSQL (trop lent pour les agrégations à cette échelle), Elasticsearch (trop coûteux pour l'analyse de séries temporelles) et ClickHouse. ClickHouse l'a emporté de façon décisive.

Pourquoi ClickHouse

ClickHouse est une base de données OLAP orientée colonnes conçue pour l'analyse en temps réel. Son principal avantage pour notre charge de travail est qu'elle ne lit que les colonnes nécessaires à chaque requête. Lorsqu'un analyste antifraude demande « montre-moi le taux de fraude par pays sur les 7 derniers jours », ClickHouse ne lit que les colonnes country, timestamp et risk_score — ignorant les 40 autres colonnes de la table d'événements. Sur une table de 2 milliards de lignes, cela réduit les E/S de 95 %.

ClickHouse compresse aussi extrêmement bien les données. Notre table d'événements de 2 milliards de lignes occupe 340 Go sur le disque — environ 170 octets par ligne compressée, contre 1,2 Ko par ligne non compressée. Le taux de compression de 7:1 signifie que davantage de données tiennent en mémoire, ce qui se traduit directement par des requêtes plus rapides.

Conception du schéma

Notre table principale stocke une ligne par événement d'identification :

La table utilise le moteur MergeTree, trié par (workspace_id, toDate(timestamp), visitor_hash). Ce tri est essentiel — il signifie que les requêtes filtrées par workspace et par plage de dates lisent un minimum de données. La colonne visitor_hash permet des recherches rapides par identifiant visiteur sans index secondaire.

Nous avons choisi LowCardinality(String) pour country, device_type, browser_family et os_family car ces colonnes ont moins de 10 000 valeurs distinctes. ClickHouse stocke les colonnes LowCardinality sous forme d'entiers encodés par dictionnaire, réduisant le stockage de 80 % par rapport aux chaînes simples et accélérant les opérations GROUP BY.

Stratégie de sharding

Nous répartissons la table d'événements sur 6 nœuds à l'aide d'un hachage de workspace_id. Cela garantit que tous les événements d'un client donné se trouvent sur le même shard, ce qui signifie que la plupart des requêtes (filtrées par workspace_id) atteignent un seul shard. Les requêtes inter-shards ne sont nécessaires que pour l'analyse interne.

Chaque shard possède 2 répliques pour la haute disponibilité. La réplication utilise le moteur ReplicatedMergeTree intégré à ClickHouse avec coordination ZooKeeper. Le basculement est automatique — si un shard tombe, les requêtes sont routées vers la réplique sans aucune modification côté client.

Pipeline d'ingestion

Les événements circulent de notre topic Kafka vers ClickHouse via un service Go personnalisé qui regroupe les insertions par lots. Nous insérons par lots de 10 000 lignes toutes les 500ms — cela équilibre la latence d'ingestion (moins d'une seconde) et l'efficacité d'insertion (ClickHouse fonctionne mieux avec de grands lots).

Le service d'ingestion gère la contre-pression avec élégance. Si ClickHouse est lent à accepter les insertions (pendant les merges ou sous forte charge de requêtes), le service met en tampon jusqu'à 1 million d'événements en mémoire et applique une contre-pression au consommateur Kafka. En 18 mois de production, nous n'avons jamais perdu un événement.

Optimisation des requêtes

Vues matérialisées

Pour les requêtes courantes des tableaux de bord, nous utilisons des vues matérialisées qui préagrègent les données. Notre tableau de bord du taux de fraude, par exemple, lit à partir d'une vue matérialisée qui agrège les comptes fraud_detected par workspace, pays et heure. La vue réduit les données scannées pour cette requête de 2 milliards de lignes à 5 millions de lignes.

Tri par projection

Les projections de ClickHouse nous permettent de définir des ordres de tri alternatifs pour une table sans dupliquer les données. Nous avons ajouté une projection triée par (workspace_id, visitor_hash, timestamp) pour les requêtes de chronologie de visiteur. Sans la projection, ces requêtes scannaient des plages de dates entières. Avec elle, elles ne lisent que les blocs contenant le visiteur cible.

Fonctions approximatives

Pour les requêtes de tableau de bord où les comptes exacts ne sont pas critiques, nous utilisons les fonctions approximatives de ClickHouse : uniqCombined pour les comptes distincts (marge d'erreur de 2 %, 10x plus rapide que uniqExact) et quantileTDigest pour les calculs de centiles. Le tableau de bord d'analyse antifraude utilise exclusivement des fonctions approximatives, ce qui maintient toutes les requêtes de tableau de bord sous 200ms.

Chiffres de performance

Voici des benchmarks de requêtes représentatifs sur notre cluster de production de 2 milliards de lignes :

Taux de fraude par pays, 7 derniers jours : 120ms. Chronologie de visiteur (50 événements) : 8ms. Visiteurs uniques par jour, 30 derniers jours : 340ms. Distribution du score de risque, 24 dernières heures : 95ms. Top 100 des appareils par nombre d'événements, 30 derniers jours : 210ms.

Ces chiffres incluent l'aller-retour réseau de nos serveurs applicatifs vers le cluster ClickHouse. Le temps d'exécution pur des requêtes est généralement inférieur de 30 à 50 %.

Leçons opérationnelles

Leçon 1 : surveiller le retard de merge

Le moteur MergeTree de ClickHouse fusionne en continu les petites parts de données en parts plus grandes. Si les merges prennent du retard (à cause d'un taux d'insertion élevé ou d'une contention d'E/S disque), les performances des requêtes se dégradent car les requêtes doivent scanner davantage de parts. Nous surveillons le nombre de parts par partition et alertons lorsqu'il dépasse 300.

Leçon 2 : éviter les grosses opérations ALTER TABLE

Ajouter une colonne à une table de 2 milliards de lignes dans ClickHouse est instantané (c'est une opération de métadonnées uniquement). Mais changer le type d'une colonne nécessite de réécrire toutes les parts de données — un processus qui a pris 6 heures sur notre cluster. Nous traitons désormais le schéma comme append-only : les nouvelles colonnes sont ajoutées librement, mais les changements de type passent par une table de migration.

Leçon 3 : le TTL avec prudence

ClickHouse prend en charge l'expiration automatique des données via TTL. Nous avons défini un TTL de 90 jours sur notre table d'événements. Le piège : la suppression TTL se produit pendant les merges, ce qui signifie que les données supprimées peuvent persister pendant des heures ou des jours après l'expiration du TTL. Pour les suppressions critiques en matière de conformité, nous exécutons des requêtes ALTER TABLE DELETE explicites de façon planifiée.

Coût

Notre cluster ClickHouse de 6 nœuds (chaque nœud : 32 vCPU, 128 Go de RAM, 2 To de NVMe) coûte environ 8 400 $/mois sur un hébergement bare metal. Il stocke 2 milliards de lignes avec une rétention de 90 jours et gère 50 K insertions/seconde plus 200 requêtes de tableau de bord concurrentes. Le coût par événement stocké est de 0,0000042 $ — plusieurs ordres de grandeur moins cher qu'une analyse comparable sur des bases de données cloud managées.