Pourquoi ClickHouse plutôt que Postgres pour les clics

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, timestamp) 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 quand le volume de clics croît de plusieurs ordres de grandeur.

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_code, workspace_id et timestamp. Il ne touche jamais à user_agent ou referer. 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 device - se compressent à presque rien sous les codecs LZ4 et ZSTD de ClickHouse. Les colonnes à faible cardinalité comme celles-ci sont celles où les codecs columnar gagnent le plus sur le stockage par ligne de Postgres. 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, timestamp). Une requête pour les clics de l'espace de travail 123 au cours des 7 derniers jours ne lit que les parties de données où workspace_id = 123 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 ou plus avec une coordination équivalente à ZooKeeper (ClickHouse Keeper), répliquant les parties au fur et à mesure qu'elles sont écrites. Nous ne l'exploitons pas encore : la production est un hôte unique, avec un seul serveur ClickHouse en MergeTree simple. La réplication est la prochaine étape prévue, quand un seul serveur ne suffira plus.

Le schéma que nous livrons

CREATE TABLE IF NOT EXISTS click_events
(
    timestamp       DateTime64(3, 'UTC'),
    link_id         UInt64,
    workspace_id    UInt64,
    domain_id       UInt64,
    slug            LowCardinality(String),
    host            LowCardinality(String),
    user_agent      String,
    referer         String,
    ip              String,
    country_code    LowCardinality(FixedString(2)) DEFAULT '',
    device          LowCardinality(String) DEFAULT '',
    browser         LowCardinality(String) DEFAULT '',
    destination     String DEFAULT '',
    retention_days  UInt16 DEFAULT 730,
    INDEX idx_workspace workspace_id TYPE bloom_filter GRANULARITY 4,
    INDEX idx_link      link_id      TYPE bloom_filter GRANULARITY 4
)
ENGINE = MergeTree()
PARTITION BY toYYYYMM(timestamp)
ORDER BY (workspace_id, link_id, timestamp)
TTL toDateTime(timestamp) + toIntervalDay(retention_days);

C'est la table de base. Des migrations ultérieures ajoutent la région, la ville et les coordonnées approximatives, un hash de visiteur quotidien, et des drapeaux de bot, de scanner et de suspicion avec ALTER TABLE ... ADD COLUMN.

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

LowCardinality(String) sur slug, host, country_code, device, browser - ces colonnes ont des valeurs bornées ou très répétitives (quelques centaines de noms d'hôte personnalisés, 250 et quelques codes pays, une poignée de types d'appareils). Les ID sont de simples UInt64, qui se compressent bien seuls. 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(timestamp) crée une partition de données séparée par mois calendaire. Des partitions entières et anciennes peuvent être supprimées (ou déplacées vers un stockage plus lent) avec une seule instruction ALTER TABLE click_events DROP PARTITION '202501', qui est instantanée - pas de balayage de table complet. Une partition contient toutefois les clics de tous les espaces de travail pour ce mois, donc la rétention par offre ne peut pas être une suppression de partition. À la place, chaque ligne est estampillée à l'ingestion avec la fenêtre de son espace de travail dans retention_days (90 jours sur Free, 365 sur Pro, 730 sur Business), et la clause TTL supprime les lignes expirées au fil des fusions de parties.

ip stocke l'adresse tronquée : l'edge met à zéro le dernier octet d'une adresse IPv4 (203.0.113.0 et non 203.0.113.42) et conserve les 48 premiers bits d'une adresse IPv6, avant que l'événement de clic ne quitte le processus de redirection. La recherche géo a lieu à l'edge avant la troncature : le pays, la région, la ville et les coordonnées approximatives sont donc stockés avec le clic, ainsi que le user agent complet. Une IP tronquée associée à la ville et au user agent doit toujours être traitée comme une donnée personnelle. Voir solutions/analytics pour l'architecture de confidentialité complète.

Le chemin d'ingestion

Le handler de redirection du service edge est fire-and-forget. Il ajoute un événement de clic à un topic Redpanda (clicks en production) 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. La production exécute un seul ingester aujourd'hui ; le groupe de consommateurs est ce qui permettra à une deuxième instance de reprendre des partitions, et de basculer dans le délai de heartbeat du groupe, quand nous en ajouterons une.

L'ingester accumule les événements en mémoire et les vide par lots : 1 000 lignes ou 5 secondes, 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 / 5 secondes et n'avons pas vu de problèmes de nombre de parties en fonctionnement normal.

La contre-pression se propage à travers Redpanda. L'ingester ne valide l'offset d'un enregistrement qu'après l'écriture de son lot dans ClickHouse : si ClickHouse est lent ou redémarre, les lots échoués sont retentés et le décalage du consommateur augmente, ce qui est l'indicateur précurseur de problèmes de fraîcheur des données. Si l'ingester lui-même redémarre, les enregistrements non validés sont relus depuis Redpanda, dans la fenêtre de rétention du topic.

L'architecture est décrite plus en détail sur /docs/guides/clickhouse-export.

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 click_events. 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 numériques 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(timestamp) 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_code, COUNT(*) filtré sur les liens de la campagne 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, browser, 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, timestamp). Le filtre par espace de travail élague immédiatement le balayage à la bonne tranche de données ; les colonnes link_id et timestamp 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 click_events
WHERE
    workspace_id = 123
    AND timestamp >= now() - INTERVAL 1 DAY
GROUP BY link_id
ORDER BY clicks_24h DESC
LIMIT 100

La requête ne lit que les partitions de la plage de dates et que les colonnes qu'elle nomme : son coût croît donc avec la tranche demandée plutôt qu'avec la table entière. La requête équivalente dans Postgres doit parcourir les lignes, d'où venaient les 1,4 seconde ci-dessus.

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 anticipons

La production exécute un serveur ClickHouse, un broker Redpanda et un ingester sur un seul hôte : les modes de défaillance qui valent la peine d'être écrits sont donc ceux de cette configuration aujourd'hui et ceux qui arriveront avec le passage à l'échelle que nous avons prévu.

Redémarrages de ClickHouse. Sur un seul serveur, un redémarrage ou une mise à jour de ClickHouse met les insertions en pause. L'ingester garde le lot échoué en mémoire, le retente, et ne valide les offsets Redpanda qu'après une insertion réussie : les clics attendent donc au lieu de disparaître. L'effet visible est un écart dans les données du tableau de bord en direct jusqu'à ce que l'arriéré se résorbe. Une lacune que nous connaissons : le buffer en mémoire n'a pas de borne supérieure tant que ClickHouse est arrêté, ce qui est dans notre backlog. La réplication avec ReplicatedMergeTree et ClickHouse Keeper est la correction prévue pour cette pause, et elle apporte la sienne : une élection de leader Keeper bloque aussi un court moment les insertions sur les tables répliquées, donc les nœuds Keeper doivent se trouver sur des hôtes séparés.

Équilibre des partitions de l'ingester. Aujourd'hui, un seul ingester possède toutes les partitions du topic clicks. Quand nous ajouterons une deuxième instance, la stratégie d'assignation du groupe de consommateurs décidera de l'équité du partage des partitions, et un partage inégal se voit par une instance qui traite les lots lentement pendant que l'autre reste inactive. Ce qu'il faut surveiller, c'est le décalage de consommateur par instance, pas seulement le total du groupe.

ALTER TABLE sur une grande table est async. Nous avons ajouté des colonnes à click_events plusieurs fois (region, city, lat et lng, visitor_hash, les drapeaux de scanner). Le ALTER TABLE ... ADD COLUMN de ClickHouse accuse réception immédiatement et ne réécrit pas les parties existantes ; les lignes des parties plus anciennes renvoient la valeur par défaut de la colonne (une chaîne vide ou zéro) tant que les fusions ne l'ont pas matérialisée. Rien ne casse, mais une requête qui traite une city vide comme « pas de ville » comptera mal les anciennes lignes. La leçon : traitez ALTER TABLE sur de grandes tables ClickHouse comme cohérent à terme, et choisissez des valeurs par défaut que vous pouvez distinguer des vraies valeurs.

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 click_events 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 destination, l'URL vers laquelle le visiteur a réellement été envoyé) 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. Nous décidons ces drapeaux avant l'insertion : l'edge note les clics suspects et classe les scanners de liens et les réseaux d'anonymisation avant d'émettre l'événement, si bien que is_suspicious, is_scanner et anonymizer_type sont écrits avec la ligne. Si un score devait un jour être calculé plus tard, le motif serait une table séparée jointe 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/guides/clickhouse-export. 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 à lire des logs de fusion ClickHouse.