Pourquoi nous utilisons ClickHouse pour les analyses de clics (et pas Postgres)

Postgres est excellent. Nous l'utilisons abondamment, et nous lui faisons confiance. Il stocke chaque lien, chaque espace de travail, chaque enregistrement de facturation, chaque utilisateur. Mais aux alentours de la barre des 90 jours sur un raccourcisseur d'URL en croissance, vous vous heurtez à un mur si vous y stockez aussi les événements de clic bruts. Le mur n'est pas dramatique - Postgres ne s'effondre pas, il devient simplement plus lent de cette façon spécifique et frustrante dont les requêtes d'agrégation sur de grandes tables append-only deviennent plus lentes. Les balayages d'index cessent d'aider. L'autovacuum ne suit plus. Un tableau de bord qui se chargeait en 800 ms se charge maintenant en 4 secondes. Vous ajoutez plus d'index. Cela empire.

Cet article explique pourquoi nous avons déplacé les événements de clic vers ClickHouse, à quoi ressemble le schéma, comment fonctionne le chemin d'ingestion et où se trouvent les arêtes vives. C'est un compagnon à l'article sur la latence de redirection et couvre le palier de données qui se trouve en aval de la redirection - la partie qui transforme les événements de clic fire-and-forget bruts en tableaux de bord sur la page solutions analytiques.

TL;DR#

• Les événements de clic sont append-only et en forme de requête d'agrégation. Le stockage columnar gagne sur les deux comptes.
• Le surcoût ligne-par-événement de Postgres, le gonflement d'index et la contention vacuum se cumulent au-delà de ~30M d'événements sur une table partagée.
• ClickHouse avec ORDER BY (workspace_id, link_id, created_at) retourne les rollups par espace de travail sur 90 jours en moins de 100 ms sans index supplémentaires.
• Les métadonnées de liens restent dans Postgres (mutables, transactionnelles) ; les événements de clic restent dans ClickHouse (append-only, analytiques). La séparation est propre car les deux n'ont pas besoin de se chevaucher au moment de l'écriture.

Pourquoi Postgres galère avec cette charge de travail#

La forme de la charge de travail est le problème. Un événement de clic est :

• Écrit une fois et jamais mis à jour.
• Écrit à des taux élevés et en rafale - un ratio écriture/lecture de 50:1 est une estimation grossière, et lors d'un jour de lancement de campagne le ratio monte plus haut.
• Interrogé en agrégation : total des clics par lien, ventilation géo pour un espace de travail, répartition par appareil sur les 100 meilleurs liens d'une campagne. Presque personne n'interroge directement une seule ligne de clic.

Postgres est construit pour un ensemble d'hypothèses différent. Chaque ligne porte un en-tête de tuple, des champs d'ID de transaction (xmin, xmax) et des métadonnées de visibilité. Pour les charges transactionnelles où vous mettez à jour des lignes sur place et avez besoin de MVCC pour gérer les lectures et écritures concurrentes, ce surcoût est le prix à payer pour la correction. Pour des données analytiques append-only auxquelles vous ne touchez plus jamais, c'est du pur gaspillage.

Le problème des index est pire. Les index B-tree de Postgres fonctionnent bien pour les recherches par point sélectives - trouver la ligne avec link_id = 'xyz'. Ils aident moins lorsque vous exécutez GROUP BY link_id, country sur 10 millions de lignes pour un espace de travail. L'index réduit le balayage, mais la requête doit encore tirer les données de ligne décompressées des pages de tas, les désérialiser, les agréger. À 30M de lignes la requête est lente. À 100M de lignes c'est douloureux.

L'autovacuum aggrave cela en pratique. Les insertions à haut débit génèrent des tuples morts (versions de ligne MVCC qui ne sont plus visibles pour les transactions actives mais n'ont pas encore été nettoyées). L'autovacuum les récupère, mais il rivalise avec les requêtes en cours pour l'I/O. Vous verrez des pics de latence périodiques sur vos tableaux de bord Postgres qui correspondent à l'autovacuum s'exécutant sur la table d'événements. Les docs de compression et d'architecture de chunks TimescaleDB valent la peine d'être lues pour les détails de la façon dont une extension Postgres essaie de contourner cela - le problème est suffisamment réel pour qu'une couche dédiée de séries temporelles au-dessus de Postgres existe pour le résoudre.

Nous avons fait tourner les clics dans Postgres pendant les 90 premiers jours du projet. Un espace de travail avec ~4M d'événements de clic avait un tableau de bord qui prenait 3,2 secondes pour rendre un rollup de 7 jours par pays. Ajouter un index partiel sur (workspace_id, created_at) l'a fait passer à 1,4 seconde. Suffisant pour l'époque ; clairement pas un chemin qui passe à l'échelle à 100M+ d'événements par mois.

Pourquoi ClickHouse convient#

ClickHouse est un moteur de base de données columnar construit explicitement pour les charges de travail analytiques - consulté le 2026-05-12. Columnar signifie que les données pour chaque colonne sont stockées de manière contiguë sur le disque plutôt qu'entrelacées ligne par ligne. Pour une requête comme « somme du nombre de clics par pays pour l'espace de travail X sur les 30 derniers jours », le moteur ne lit que les colonnes country, workspace_id et created_at. Il ne touche jamais à user_agent_hash ou referrer_host. La réduction d'I/O sur des tables larges avec de nombreuses colonnes est substantielle.

Le ratio de compression est la deuxième propriété. Les données columnar se compressent significativement mieux que les données ligne car les valeurs répétées dans la même colonne - le même workspace_id apparaissant des milliers de fois, le même ensemble de 60 et quelques codes pays, une poignée de valeurs os - se compressent à presque rien sous les codecs LZ4 et ZSTD de ClickHouse. Nos événements de clic se compressent à environ 12-15 % de leur taille brute sur disque. Les mêmes données dans Postgres se situent à 35-45 % de la taille brute avec la compression toast par défaut. Ce n'est pas seulement un gain de stockage - des données plus petites sur disque signifient que davantage tient dans le cache de pages OS, ce qui signifie moins de lectures disque par requête.

La troisième propriété est la famille de moteurs MergeTree - consultée le 2026-05-12. Les tables MergeTree trient et regroupent les données selon la clé ORDER BY de la table. Les requêtes qui filtrent ou groupent sur les colonnes de tête de cette clé ne lisent que les parties de données qui chevauchent la plage de filtre. C'est l'équivalent ClickHouse d'un index clusterisé, sauf qu'il est obligatoire (vous le définissez au moment de la création de la table) et s'applique à chaque requête, pas seulement aux colonnes indexées.

Nous utilisons ORDER BY (workspace_id, link_id, created_at). Une requête pour les clics de l'espace de travail abc123 au cours des 7 derniers jours ne lit que les parties de données où workspace_id = 'abc123' apparaît, puis dans ces parties ne lit que la tranche de plage temporelle. Sur 90 jours de données de clic, cela touche généralement 3 à 8 % du total des parties de données. L'index primaire clairsemé que ClickHouse maintient sur les colonnes ORDER BY rend ce ciblage efficace sans nécessiter un B-tree dense pour chaque motif de requête. ReplicatedMergeTree ajoute la HA - deux nœuds ClickHouse avec une coordination équivalente à ZooKeeper (ClickHouse Keeper), répliquant les parties au fur et à mesure qu'elles sont écrites.

Le schéma que nous livrons#

CREATE TABLE clicks
(
    workspace_id    LowCardinality(String),
    link_id         String,
    click_id        UUID,
    created_at      DateTime64(3, 'UTC'),
    country         LowCardinality(FixedString(2)),
    device          LowCardinality(String),
    os              LowCardinality(String),
    browser         LowCardinality(String),
    referrer_host   String,
    utm_source      LowCardinality(String),
    utm_medium      LowCardinality(String),
    utm_campaign    String,
    utm_term        String,
    utm_content     String,
    ip_truncated    String,
    user_agent_hash FixedString(64)
)
ENGINE = ReplicatedMergeTree('/clickhouse/tables/{shard}/clicks', '{replica}')
PARTITION BY toYYYYMM(created_at)
ORDER BY (workspace_id, link_id, created_at)
SETTINGS index_granularity = 8192;

Quelques décisions de schéma qui méritent d'être soulignées.

LowCardinality(String) sur workspace_id, country, device, os, browser, utm_source, utm_medium - ces colonnes ont une cardinalité bornée (centaines d'ID d'espaces de travail distincts, 250 et quelques codes pays, une poignée de types d'appareils). LowCardinality active l'encodage par dictionnaire, qui stocke les valeurs comme des références entières dans un dictionnaire de chaînes uniques. La vitesse des requêtes sur ces colonnes s'améliore matériellement ; la compression s'améliore encore plus. Nous avons appris cela dans les docs ClickHouse sur le type de données low-cardinality - la règle empirique est que toute colonne de chaîne avec moins de 10 000 valeurs distinctes est un candidat.

PARTITION BY toYYYYMM(created_at) crée une partition de données séparée par mois calendaire. Les anciennes partitions peuvent être supprimées (ou déplacées vers un stockage plus lent) avec une seule instruction ALTER TABLE clicks DROP PARTITION '202501', qui est instantanée - pas de balayage de table complet. Pour l'application de rétention de données RGPD, c'est pratiquement important : supprimer les clics historiques d'un espace de travail est une seule opération par partition mensuelle plutôt qu'un DELETE WHERE workspace_id = ? distribué.

ip_truncated stocke l'adresse IP avec le dernier octet mis à zéro (203.0.113.0 et non 203.0.113.42). L'IP complète n'est jamais stockée. La forme tronquée est suffisante pour la recherche géo - que nous faisons au moment de l'ingestion, avant le stockage - et ne constitue pas une donnée personnelle selon la définition de l'Article 4 du RGPD lorsqu'elle est combinée avec nos pratiques de minimisation des données. Voir solutions/analytics pour l'architecture de confidentialité complète.

Le chemin d'ingestion#

Le handler de redirection à chaque POP edge est fire-and-forget. Il ajoute un événement de clic à un topic Redpanda (clicks.v1) et émet le 302. Il n'attend pas d'accusé de réception de ClickHouse. Si Redpanda est indisponible à ce moment-là, le clic est perdu - la redirection ne l'est pas. La hiérarchie est délibérée : un clic manqué est récupérable (nous pouvons reconstruire des comptes approximatifs à partir des logs d'accès) ; une redirection échouée est visible par l'utilisateur.

Les groupes de consommateurs Redpanda - consultés le 2026-05-12 - fournissent la garantie de livraison entre l'edge et l'ingester. Le service click-ingester s'exécute en tant que groupe de consommateurs contre le topic clicks.v1. Les partitions sont assignées automatiquement ; si une instance d'ingester échoue, ses partitions se rééquilibrent vers les instances survivantes dans le délai de heartbeat du groupe.

L'ingester accumule les événements en mémoire et les vide par lots : 1 000 lignes ou 250 ms, selon ce qui vient en premier. C'est important. Le moteur MergeTree de ClickHouse est optimisé pour les insertions en masse - il écrit chaque insertion comme une nouvelle partie de données, et beaucoup de petites insertions génèrent beaucoup de petites parties, ce qui déclenche une fusion d'arrière-plan agressive et ralentit les requêtes pendant la charge de fusion. Les docs ClickHouse recommandent des insertions d'au moins 1 000 lignes pour maintenir le nombre de parties gérable. Nous vidons à 1 000 lignes / 250 ms et n'avons pas vu de problèmes de nombre de parties en fonctionnement normal.

La contre-pression se propage à travers Redpanda. Si ClickHouse est lent ou redémarre, le décalage du consommateur de l'ingester augmente, ce que nous surveillons comme un indicateur précurseur de problèmes de fraîcheur des données. La configuration de rétention de Redpanda (14 jours) nous donne une fenêtre de rejeu significative si ClickHouse doit être reconstruit à partir de zéro.

L'architecture est décrite plus en détail sur /docs/architecture/click-ingester.

Postgres vs ClickHouse : la séparation#

Deux bases de données pour un seul service est un coût qui nécessite une justification. La justification ici est que les deux charges de travail sont véritablement incompatibles à l'échelle, et les données ne se chevauchent pas d'une manière qui nécessiterait une coordination synchrone.

Postgres possède les métadonnées de liens : les tables links, workspaces, users, billing, rules. Celles-ci sont mutables (les liens sont archivés, les URL de destination changent, les enregistrements de facturation sont mis à jour), transactionnelles (la création d'un lien et la création de son slug doivent être atomiques) et avec beaucoup de lectures par rapport aux écritures. Les index B-tree et MVCC sont exactement ce que vous voulez.

ClickHouse possède les événements de clic : la table clicks. Ceux-ci sont append-only (un clic, une fois écrit, n'est jamais mis à jour), avec beaucoup d'écritures et interrogés selon des motifs d'agrégation que le stockage columnar gère mieux. Il n'y a pas de jointures inter-bases de données au moment de l'écriture - l'ingester insère les événements de clic avec workspace_id et link_id comme champs de chaîne dénormalisés. Au moment de la requête, l'API d'analyses peut rechercher les métadonnées de lien depuis Postgres et les comptes de clics depuis ClickHouse séparément, puis joindre dans le code applicatif. C'est un aller-retour réseau supplémentaire ; c'est un bien meilleur compromis que de mettre la charge de requête analytique sur Postgres.

La documentation Postgres distribué Citus décrit l'alternative : les extensions Postgres distribuées conçues pour gérer les charges de travail analytiques de séries temporelles. Nous avons évalué ce chemin et conclu que les performances de requête et le ratio de compression de ClickHouse sont suffisamment en avance pour les charges purement analytiques que le surcoût opérationnel de deux bases de données en vaut la peine. Si votre charge de travail est mixte - certaines analytiques, beaucoup de mutations au niveau de la ligne - l'approche Citus a plus de sens.

Motifs de requête que nous atteignons toutes les heures#

Le tableau de bord se rafraîchit pour chaque espace de travail sur un cycle d'environ 60 secondes. Il y a trois requêtes lourdes :

Rollup de clics par lien, dernières 24 heures. Le haut de chaque liste de liens montre un sparkline. C'est un GROUP BY link_id, toHour(created_at) sur les dernières 24 heures pour l'espace de travail.

Ventilation par pays pour une campagne. Une équipe marketing vérifiant la performance d'une campagne veut GROUP BY country, COUNT(*) filtré par utm_campaign sur une plage de dates.

Répartition par appareil sur les 100 meilleurs liens d'un espace de travail. L'onglet analytique montre une ventilation appareil/OS. C'est GROUP BY device, os, COUNT(*) pour les liens à plus fort trafic de l'espace de travail sur les 30 derniers jours.

Les trois motifs sont bien servis par le clustering ORDER BY (workspace_id, link_id, created_at). Le filtre par espace de travail élague immédiatement le balayage à la bonne tranche de données ; les colonnes link_id et created_at le réduisent encore pour les deux premiers motifs. La requête de répartition par appareil balaye plus de colonnes mais bénéficie toujours du clustering au niveau de l'espace de travail.

Une requête représentative pour les 100 meilleurs liens par nombre de clics sur les dernières 24 heures :

SELECT
    link_id,
    count() AS clicks_24h
FROM clicks
WHERE
    workspace_id = 'ws_abc123'
    AND created_at >= now() - INTERVAL 1 DAY
GROUP BY link_id
ORDER BY clicks_24h DESC
LIMIT 100

Sur un espace de travail avec 12 millions d'événements de clic couvrant 90 jours, cette requête retourne en environ 45-80 ms selon le nombre de parties de données dans la partition pertinente. La même requête contre une table Postgres de taille équivalente s'exécute en 2-4 secondes avec la meilleure configuration d'index que nous avons essayée.

Nous n'utilisons pas de vues matérialisées ClickHouse pour ces motifs. Le clustering ORDER BY et l'élagage de partitions sont suffisants, et les vues matérialisées ajoutent de la complexité de migration de schéma. Si la latence des requêtes devient un problème à des volumes d'événements plus élevés, les vues matérialisées sont le levier suivant - pas l'indexation supplémentaire.

Modes de défaillance que nous avons rencontrés#

Trois incidents de production valent la peine d'être documentés.

Hoquet de ClickHouse Keeper. ClickHouse Keeper est la couche de coordination équivalente à ZooKeeper pour ReplicatedMergeTree. À une occasion, un redémarrage de nœud Keeper pendant une fusion d'arrière-plan a fait que la table répliquée s'arrête d'accepter les insertions pendant environ 90 secondes pendant que le nouveau leader était élu. L'ingester a réessayé avec un backoff exponentiel (2s, 4s, 8s, plafond 60s) - c'est de la logique de réessai Go standard avec gestion de context.DeadlineExceeded. Pendant la fenêtre de panne, environ 1 800 événements de clic se sont accumulés dans le buffer en mémoire de l'ingester et ont été vidés avec succès une fois Keeper récupéré. Le seul effet visible pour l'utilisateur était un écart d'environ 2 minutes dans les données de tableau de bord en direct. La correction a été de s'assurer que les nœuds Keeper sont répartis sur des hôtes physiques séparés avec des circuits d'alimentation séparés sur le réseau Hetzner.

Déséquilibre de partition sur Redpanda. Le topic clicks.v1 utilise 12 partitions. Après l'ajout d'une deuxième instance d'ingester, l'assignation de partitions par le balancer par défaut de Redpanda a assigné 9 partitions à une instance et 3 à l'autre - il a utilisé la stratégie total-partitions-par-consommateur et les comptes d'instances ont produit un déséquilibre 3:1. Ce n'était pas un risque de perte de données, mais l'instance sur-assignée traitait par lots lentement sous la charge, augmentant la latence de traitement des clics à environ 800 ms. La correction a été un rééquilibrage manuel des partitions en utilisant l'API admin de Redpanda et le passage au balancer rack-aware pour utiliser l'assignation round-robin.

ALTER TABLE sur une grande table est async. Nous avons ajouté une colonne browser_version en janvier. Le ALTER TABLE ... ADD COLUMN de ClickHouse accuse réception immédiatement et applique le changement de schéma de manière asynchrone sur les fusions de parties d'arrière-plan. Pendant environ 20 minutes après l'altération, les requêtes qui référençaient la nouvelle colonne retournaient NULL pour les lignes dans les parties non mutées. Rien ne s'est cassé - nous ajoutions une colonne, pas en modifions une existante - mais une requête qui supposait browser_version IS NOT NULL aurait retourné des résultats incorrects. La leçon : traitez ALTER TABLE sur de grandes tables ClickHouse comme cohérent à terme, pas synchrone.

Ce que nous ne faisons pas dans ClickHouse#

Deux choses qui reviennent dans les conversations et que nous avons délibérément évitées.

Jointures par événement vers les tables Postgres. Certaines requêtes analytiques veulent naturellement joindre les événements de clic aux métadonnées de lien - « montrez-moi tous les clics pour les liens étiquetés 'campaign-q2', avec leurs URL de destination ». Faire cela en tant que requête fédérée (ClickHouse clicks joint à Postgres links via une fonction de table JDBC ClickHouse) est possible mais lent. À la place, nous dénormalisons soit les champs pertinents dans l'événement de clic au moment de l'ingestion (comme utm_campaign, qui est dérivé du modèle UTM du lien) soit nous résolvons la jointure dans le code applicatif : deux requêtes séparées, fusionnées dans la couche API d'analyses. Cela maintient les deux bases de données fonctionnant à l'intérieur de leurs enveloppes de performance.

Mises à jour par ligne. ClickHouse prend en charge ALTER TABLE UPDATE pour les mutations au niveau de la ligne, mais c'est une opération asynchrone et gourmande en ressources qui va à l'encontre des hypothèses du moteur. Si vous vous trouvez à devoir mettre à jour un événement de clic après coup - disons, pour le marquer rétroactivement comme un clic de bot - vous avez construit l'architecture de travers. Le bon motif est de stocker une table click_quality séparée dans ClickHouse qui mappe click_id vers un score de qualité, calculé par le service url-scanner de manière asynchrone, et les JOINdre au moment de la requête. Les lectures sont peu coûteuses ; les mises à jour rétroactives sont coûteuses et devraient être rares.

Pour le tableau d'infrastructure plus large - comment la redirection edge interagit avec Redpanda, comment fonctionne la déduplication des clics et comment le service click-ingester s'intègre dans la topologie de service - voir /docs/architecture/click-ingester. L'histoire côté edge (publication fire-and-forget Redpanda, budget de latence) est dans l'article p95 de redirection. Pour ce que les données permettent en aval - ventilations géo, répartitions par appareil, tableaux de bord d'attribution UTM - la page solutions/analytics couvre la surface produit. Si vous construisez des campagnes de liens contre l'API et vous souciez des motifs de requête que le SDK expose, solutions/developers est le point d'entrée pertinent.

Pour les équipes évaluant Elido comme infrastructure de liens, le choix d'architecture ici - ClickHouse pour les clics, Postgres pour tout le reste - est aussi ce qui nous permet d'offrir des analyses sans échantillonnage. L'article sur les liens intelligents expliqués couvre la façon dont les décisions de routage qui génèrent ces événements de clic fonctionnent à l'edge.

Marius Voß est DevRel et edge infra chez Elido. Il a aidé à concevoir le pipeline click-ingester et a passé plus de temps qu'il ne le voudrait à déboguer le timing d'élection de ClickHouse Keeper.