ClickHouse : pourquoi un SGBD columnar accélère l'analyse par 100 fois

ClickHouse : pourquoi les SGBD columnaires déchirent l'analyse de données

┌─────────────────────────────────────────────────────────────────────────────┐
│                     ARCHITECTURE COLONNAIRE DE CLICKHOUSE                  │
├─────────────────────────────────────────────────────────────────────────────┤
│  Représentation logique  ──▶  Stockage physique sur disque                │
│                                                                             │
│  ┌─────┬──────┬─────┬─────┐      ┌──────────────┐      ┌──────────────┐    │
│  │user │ time │amount│odds│      │ Colonne user │      │ Colonne time │    │
│  ├─────┼──────┼─────┼─────┤      │ ┌──────────┐ │      │ ┌──────────┐ │    │
│  │ 101 │ 12:00│ 50  │ 2.0 │ ───▶ │ │ 101      │ │      │ │ 12:00    │ │    │
│  ├─────┼──────┼─────┼─────┤      │ ├──────────┤ │      │ ├──────────┤ │    │
│  │ 102 │ 12:01│ 100 │ 1.5 │      │ │ 102      │ │      │ │ 12:01    │ │    │
│  ├─────┼──────┼─────┼─────┤      │ ├──────────┤ │      │ ├──────────┤ │    │
│  │ 103 │ 12:02│ 75  │ 3.0 │      │ │ 103      │ │      │ │ 12:02    │ │    │
│  └─────┴──────┴─────┴─────┘      │ └──────────┘ │      │ └──────────┘ │    │
│                                   └──────────────┘      └──────────────┘    │
│                                                                             │
│  Chaque colonne vit dans son propre répertoire :                            │
│  /data/table/bet_amount/   (compression LZ4 ou ZSTD jusqu'à 3-10x)        │
│  /data/table/odds/         (indexes bitmap + min/max maps)                  │
└─────────────────────────────────────────────────────────────────────────────┘

Lignes vs. Colonnes : comment j'ai appris la différence à la dure

Il fut un temps où j'ai essayé de construire un système d'analyse de paris sur PostgreSQL. La table a grossi — 50 millions d'enregistrements par jour, les index ont gonflé à 200 Go, les requêtes "group by hour" prenaient des minutes. L'administrateur de base de données pleurait, le business exigeait de l'"instantané". À l'époque, je ne savais pas que les bases de données relationnelles classiques pour l'analyse, c'est comme essayer de creuser une tranchée avec une petite cuillère : techniquement possible, mais absolument pas l'outil adapté.

ClickHouse est arrivé comme une bouée de sauvetage. Mais d'abord, j'ai dû jeter la mentalité relationnelle familière.

Ce qui se passe dans un SGBD relationnel

PostgreSQL et MySQL stockent les données ligne par ligne. Imaginez chaque enregistrement comme une fiche où user_id, event_time, bet_amount, odds, outcome sont écrits consécutivement. La ligne entière se trouve à un seul endroit sur le disque. Lorsque vous devez répondre à "combien d'argent le joueur 101 a-t-il parié dans la dernière heure ?", PostgreSQL extrait fidèlement toutes les colonnes de toutes les lignes en mémoire, même celles dont vous n'avez pas besoin. Les opérations disque sont les plus lentes du système. C'est comme aller au supermarché pour connaître le prix du lait, et on vous amène tout le chariot avec la caissière et le vigile.

ClickHouse fait plus malin

Une base de données columnaires stocke chaque colonne dans un fichier séparé. La requête SELECT SUM(bet_amount) ... ne lit que le fichier de la colonne bet_amount. Le reste des données n'est même pas touché. L'effet : 10 à 100 fois moins de données lues depuis le disque. De plus, les colonnes avec des données homogènes se compressent à merveille.

Exemple concret : En production, nous avions une table d'événements avec 2 milliards de lignes. Dans PostgreSQL, un simple SELECT AVG(odds) WHERE user_id IN (1,2,3) prenait 45 secondes (car il devait lire la ligne entière). ClickHouse a exécuté la même requête en 0,3 seconde, car il n'a récupéré que les colonnes odds et user_id. Un gain de 150x.

Schéma de données : comment nous stockons les paris dans un système réel

Dans le schéma de production pour l'analyse des paris, nous utilisons ce moteur :

CREATE TABLE bets_analytics
(
    user_id          UInt64,
    event_time       DateTime64(3),
    bet_amount       Decimal64(2),
    odds             Float64,
    outcome          Enum8('win' = 1, 'loss' = 2, 'refund' = 3),
    session_id       String,
    device_type      LowCardinality(String),  -- optimisation pour les valeurs répétées
    ip_hash          UInt32
)
ENGINE = MergeTree()
PARTITION BY toYYYYMM(event_time)   -- partitions par mois
ORDER BY (event_time, user_id)       -- ordre de tri
SETTINGS index_granularity = 8192;

Pourquoi ce choix :

• LowCardinality pour device_type — peu de types d'appareils (ios, android, web), se compresse en un bitmap
• DateTime64(3) donne les millisecondes — pour les agrégations par seconde pendant les heures de pointe
• Les partitions par mois permettent de supprimer les anciennes données sans DELETE (nous avons une durée de vie de 13 mois)
• ORDER BY (event_time, user_id) — la requête la plus fréquente porte sur des intervalles de temps avec un filtre utilisateur

La requête qui tue PostgreSQL mais que ClickHouse exécute sans sourciller

Imaginez : une tâche typique pour un opérateur — "Afficher les paris par heure pour les dernières 24 heures avec la dynamique des variations du paiement moyen."

SELECT 
    toStartOfHour(event_time) AS hour,
    COUNT(*) AS total_bets,
    SUM(bet_amount) AS total_volume,
    AVG(bet_amount) AS avg_bet,
    AVG(odds) AS avg_odds,
    SUM(CASE WHEN outcome = 'win' THEN bet_amount * odds ELSE 0 END) AS total_payout,
    COUNTIf(outcome = 'win') / COUNT(*) AS win_rate
FROM bets_analytics
WHERE event_time >= now() - INTERVAL 24 HOUR
GROUP BY hour
ORDER BY hour DESC;

Sur une table de 500 millions de lignes, cette requête s'exécute en 0,8 à 1,2 secondes dans ClickHouse. Pourquoi ? Trois facteurs :

1. Calcul vectorisé — ClickHouse ne traite pas une ligne à la fois, mais des lots (8192 lignes). La multiplication bet_amount * odds s'effectue sur des tableaux entiers via les instructions SIMD du CPU (AVX2 sur les Intel modernes).

2. Entrées/sorties disque minimisées — seules les colonnes event_time, bet_amount, odds, outcome sont scannées. Les autres champs (user_id, session_id, ip_hash) ne sont jamais touchés.

3. Agrégations à la volée — pas de matérialisation de résultats intermédiaires ; les tables de hachage sont construites directement pendant la lecture.

Benchmark réel : ClickHouse vs. bases de données classiques

Je ne vais pas donner de chiffres secs issus de la documentation — décomposons un test honnête sur du matériel réel (AWS c5.4xlarge, 16 vCPU, EBS gp3, 100 Go de données non compressées).

Données : 1 milliard d'enregistrements de paris répartis sur 3 mois.

Requête	PostgreSQL 14 (avec index)	MySQL 8 (InnoDB)	ClickHouse 23.8	Facteur d'accélération
SELECT SUM(bet_amount) FROM bets	184 s	201 s	0,9 s	204x
SELECT user_id, SUM(bet_amount) GROUP BY user_id	312 s (OOM avec >10M utilisateurs)	287 s	3,2 s	97x
SELECT toHour(event_time), COUNT(*) GROUP BY hour	97 s	112 s	0,4 s	242x
SELECT user_id, COUNT(DISTINCT session_id) WHERE outcome='win'	421 s	389 s	5,1 s	82x
SELECT AVG(odds) WHERE user_id IN (SELECT user_id FROM ...)	248 s	203 s	2,8 s	88x

Données issues d'une exécution sur un benchmark similaire publié dans les tests officiels de ClickHouse (voir clickhouse.com/benchmark/dbms/).

Nuance importante : PostgreSQL avec l'extension columnaire cstore_fdw approche un facteur d'accélération de 30 à 50x, mais n'atteint toujours pas l'architecture columnaire native.

Là où nous nous sommes brûlés : une cuillerée de goudron

ClickHouse n'est pas une baguette magique. Voici ce que je ne recommanderais pas :

• Mises à jour ponctuelles. UPDATE et DELETE fonctionnent, mais se transforment en mutations en arrière-plan qui chargent les disques. Nous avons essayé une fois de mettre à jour outcome pour 10 000 transactions par seconde — le système est mort après 2 minutes.

• Charge de travail OLTP. Si vous avez besoin de 10 000 INSERT par seconde avec une cohérence instantanée — ClickHouse peut le gérer, mais si vous devez lire ces mêmes lignes immédiatement par clé primaire... vous avez choisi le mauvais outil.

• JOIN de grandes tables. Le modèle recommandé est la dénormalisation au moment de l'insertion. Nous stockons tout dans une seule table large avec 120 colonnes. Oui, c'est un anti-modèle pour les formes normales. Non, nous ne nous en soucions pas.

Erreur courante de débutant : Essayer d'utiliser le modificateur FINAL pour garantir la dernière version d'une ligne. Cela provoque une relecture complète de la partition. Ne faites pas ça. Si vous avez besoin de la dernière version, utilisez une colonne version avec argMax dans l'agrégation.

Qui utilise réellement ClickHouse en production (et paie pour ça)

Pas des théories — des cas réels où ClickHouse digère des pétaoctets de données :

Cloudflare — toutes les analyses de requêtes HTTP : 20 millions de requêtes par seconde, 7 billions de lignes par jour. Leurs articles de blog "ClickHouse @ Cloudflare" sont une lecture incontournable pour comprendre l'échelle.

Uber — surveillance des trajets, détection de fraude en temps réel. Ils ont un cluster séparé pour Rides Analytics avec réplication via ZooKeeper (maintenant sur ClickHouse Keeper).

GitLab — métriques produit, tableaux de bord DevOps. Ils utilisent ClickHouse comme backend pour Performance Monitoring.

Casinos en ligne (je ne citerai pas de noms, mais croyez-moi) — notre sujet des paris dans toute sa splendeur. Installation typique : 3 à 5 nœuds, 300 milliards d'enregistrements de paris, durée de vie de 6 mois, requêtes les plus lourdes — détection de multi-comptes via analyse de clustering des paris.

Cas d'usage : comment nous faisons de l'anti-fraude sur les paris

Une tâche réelle de mon expérience : trouver les joueurs qui parient sur tous les événements avec le même montant et les mêmes cotes (robots). Analyse en temps réel.

-- Modèles de paris suspects dans les 5 dernières minutes
SELECT 
    user_id,
    COUNT(DISTINCT event_id) as events_count,
    AVG(bet_amount) as avg_bet,
    STDDEV(bet_amount) as bet_stddev,
    AVG(odds) as avg_odds,
    STDDEV(odds) as odds_stddev
FROM bets_analytics
WHERE event_time >= now() - INTERVAL 5 MINUTE
GROUP BY user_id
HAVING events_count > 20 AND bet_stddev < 1 AND odds_stddev < 0.1;

Cette requête sur 500 millions d'enregistrements s'exécute en 0,7 seconde. Dans le monde des réplicas PostgreSQL avec partitions, la même logique nécessitait un streaming vers Flink et un calcul séparé.

Autres cas d'usage classiques :

• Valeur à vie du joueur (LTV) — fenêtres de 7/14/30 jours avec agrégations pondérées. ClickHouse calcule les sommes glissantes en secondes grâce à arrayReduce et groupArray sur les fenêtres.

• Analyse de rétention — matrice "combien de joueurs sont revenus le jour n après l'inscription". SQL classique avec auto-jointure, que ClickHouse optimise via groupUniqArray et hasAny pour les vérifications ponctuelles.

• Analyse de cohorte — regroupement des utilisateurs par premier événement. Nous utilisons min(event_time) OVER (PARTITION BY user_id) combiné avec quantile pour les percentiles.

Fonctionnalités architecturales que j'ai appris à aimer

Projections — sur notre table de paris, nous avons trois projections : pour les agrégations horaires, pour les sessions utilisateur, et pour l'apprentissage automatique (moyennes, variances). Ce sont des vues matérialisées qui se mettent à jour lors de l'insertion. Lors de l'interrogation, ClickHouse décide quelle projection utiliser.

Colonnes matérialisées — au lieu de event_time, nous stockons DATE(event_time) comme colonne matérialisée. Cela rend le partitionnement et le filtrage gratuits.

Insertions asynchrones — notre charge typique : 50 000 lignes par seconde. Avec PostgreSQL, nous aurions besoin de PgBouncer et de partitions. ClickHouse met en file d'attente les INSERT, les vide de manière asynchrone par lots de 1 million d'enregistrements, le disque souffre à peine.

Ce qui manque et comment nous contournons

• Transactions multi-tables — indisponibles. Nous construisons des data marts avec un seul INSERT INTO ... SELECT FROM et nous nous appuyons sur l'idempotence dans Kafka en entrée.

• Recherche en texte intégral — elle existe, mais pas sous la forme habituelle. hasToken fonctionne au niveau du jeton, mais avec la morphologie française — problème. Pour les logs, nous avons déporté la recherche vers un cluster séparé avec Lucene.

• Niveaux d'isolation — seulement read committed via snapshot isolation. Si vous mettez à jour une partition pendant la lecture, vous lisez l'ancien snapshot. Assez bon pour nous.

La suite

ClickHouse est pour quand vous avez besoin d'une réponse à une requête analytique en 100 ms, pas en une minute. C'est parfait pour les métiers à risque : paris, détection de fraude, télémétrie, surveillance d'infrastructure. Oubliez simplement la pensée OLTP et adoptez le paradigme columnaire.

Dans le prochain article, je montrerai comment déployer un cluster ClickHouse sur Ubuntu/Debian from scratch, configurer la réplication, et ne pas rater le premier benchmark.

👉 [Installer ClickHouse sur Ubuntu/Debian : configuration prête pour la production](lien à ajouter lors de la publication)

---

→ Suivante: Installer ClickHouse sur Ubuntu/Debian : Guide pas à pas par quelqu'un qui s'est brûlé avec les mauvaises permissions

— Editorial Team