PostgreSQL vs ClickHouse : modèles ETL qui n’engendrent pas le chaos des données

Faits intéressants et contexte

• PostgreSQL a commencé comme POSTGRES à l’UC Berkeley dans les années 1980, explorant explicitement l’extensibilité (types, index, règles) bien avant que « plugable » ne devienne un argument commercial.
• MVCC dans PostgreSQL (contrôle de concurrence multi-version) explique pourquoi les lectures ne bloquent pas les écritures, mais c’est aussi la raison pour laquelle vacuum n’est pas optionnel si vous effectuez beaucoup de mises à jour.
• ClickHouse est sorti de Yandex dans les années 2010 pour l’analytique web à grande échelle ; ses biais sont sans complexe : « lire vite, agréger encore plus vite ».
• ClickHouse est colonne : il stocke chaque colonne séparément, ce qui lui permet de ne lire que les colonnes nécessaires à une requête, mais cela signifie aussi que de nombreuses petites colonnes peuvent augmenter la surcharge.
• Les moteurs MergeTree ne sont pas « append-only » au sens naïf ; ils sont « append puis fusionnent en arrière-plan », ce qui influence la gestion des mises à jour et de la déduplication.
• La réplication PostgreSQL a évolué par étapes : réplication physique en streaming, puis réplication logique pour des flux de changements au niveau ligne, permettant des modèles ETL sans extraction complète constante.
• Les vues matérialisées de ClickHouse sont déclenchées à l’insertion : elles transforment les données au moment de l’insertion, parfait pour les pipelines d’ingestion, mais elles ne recomputent pas rétroactivement sauf si vous réinsérez/backfillez.
• Les deux systèmes ont des histoires de compression « ça dépend » : Postgres compresse les valeurs TOASTées ; ClickHouse compresse extrêmement bien les données en colonne, surtout les colonnes à faible cardinalité.
• Le JSON est devenu une première classe différemment : Postgres a construit un JSONB robuste avec index et opérateurs ; ClickHouse supporte JSON mais attend généralement que vous normalisiez les chemins de requêtes chauds en colonnes typées.

Deux modèles mentaux : vérité OLTP vs vérité analytique

PostgreSQL est l’endroit où vous conservez la vérité. Pas « une vérité approximative ». La vérité, avec contraintes, transactions et la tâche inconfortable de dire « non » aux mauvaises données. ClickHouse est l’endroit où vous conservez la vérité analytique : hautement interrogeable, souvent dénormalisé, optimisé pour les group-bys sur des milliards de lignes.

Si vous traitez PostgreSQL comme un entrepôt, vous vous retrouverez à lutter contre le bloat, la contention des verrous et un schéma qui tente d’être à la fois normalisé et dénormalisé (et qui finit par être ni l’un ni l’autre). Si vous traitez ClickHouse comme une base transactionnelle, vous découvrirez finalement que « update » est une question philosophique, et la réponse est « pas de cette façon ».

Implication opérationnelle

Les modèles ETL qui n’engendrent pas le chaos sont ceux qui respectent les contraintes de chaque moteur :

• Dans PostgreSQL : appliquez les contraintes tôt, gardez les écritures efficaces, utilisez l’extraction incrémentale et évitez les « charges analytiques déguisées en rapports » sur les primaires de production.
• Dans ClickHouse : adoptez la sémantique append-and-merge, concevez le partitionnement pour l’élagage, et modélisez explicitement la déduplication et les données arrivant en retard.

Paraphrasant Werner Vogels (CTO d’Amazon) : vous devriez planifier la défaillance comme l’état normal, pas comme l’exception. Un ETL qui ne fonctionne que lorsque rien ne casse est une démonstration, pas un pipeline.

Ce que signifie réellement « pas de chaos des données »

« Pas de chaos des données » n’est pas une impression ; ce sont des invariants que vous pouvez tester. En production, voici ceux qui comptent :

• Idempotence : relancer un job ne crée pas de doublons ni de dérive. Vous pouvez redémarrer sans prière.
• Déterminisme : la même fenêtre d’entrée produit les mêmes lignes de sortie (ou le même état final), même avec des réessais.
• Fraîcheur observable : vous pouvez répondre à « à quel point les données sont-elles en retard ? » avec une requête, pas une supposition.
• Réconciliation : vous pouvez comparer les totaux/checksums source et sink à des frontières significatives et détecter les pertes silencieuses.
• Backfills contrôlés : retraiter des fenêtres historiques n’embrase pas le cluster ni ne modifie les métriques sans trace.
• Contrat de schéma : lorsque l’amont ajoute une colonne ou change la sémantique, le pipeline échoue bruyamment ou s’adapte en toute sécurité—pas de comportements silencieux.

Blague n°1 : ETL signifie « Eventually Things Lag ». Si le vôtre ne le fait pas, il saute probablement des étapes.

Le méchant caché : le temps

La plupart du chaos a la forme du temps : événements tardifs, dérive d’horloge, réordres, retard de réplication multi-région, et backfills qui rejouent d’anciennes données dans des partitions « courantes ». Un pipeline qui confond temps d’événement, temps d’ingestion et temps de mise à jour va générer les types de disputes de tableaux de bord qui brisent des amitiés.

Modèles ETL PostgreSQL qui restent sensés

Modèle 1 : Traitez Postgres comme la frontière de contrat (contraintes, pas espoir)

Si vous pouvez valider une donnée une fois, validez-la à l’endroit où elle entre dans votre système. Utilisez NOT NULL, des clés étrangères lorsque c’est raisonnablement opérationnel, des check constraints pour les règles de domaine, et des index uniques pour les clés d’idempotence. C’est l’endroit le moins coûteux pour attraper les ordures.

Pour l’extraction ETL, vous voulez des identifiants stables et un curseur monotone. Cela peut être :

• une clé primaire basée sur une séquence (bonne pour les insertions, pas pour les mises à jour),
• un timestamp last_updated (bon si correctement maintenu),
• ou un slot de réplication logique / flux CDC (meilleur quand vous avez besoin de tous les changements).

Modèle 2 : Chargements incrémentaux avec watermarks (et fenêtre de retour en arrière)

« Tout recharger à chaque fois » est un plan valide quand tout est petit. Cela cesse de l’être dès que quelqu’un dit « nous devrions conserver plus d’historique ». Les chargements incrémentaux sont la norme. Mais les chargements incrémentaux basés sur des timestamps nécessitent une fenêtre de lookback pour gérer les commits tardifs et la dérive d’horloge.

Ce que vous faites :

• Maintenez un watermark par table/pipeline (par ex. le dernier updated_at réussi vu).
• À chaque exécution, extrayez updated_at > watermark - lookback.
• Upservez dans une table de staging/warehouse avec une clé déterministe.

Ce que vous évitez : utiliser now() comme frontière sans l’enregistrer, ou supposer que les timestamps sont strictement croissants. Ils ne le sont pas, surtout avec les réessais et les systèmes multi-écrivains.

Modèle 3 : Utilisez CDC quand « les mises à jour comptent »

Si vos lignes source changent et que vous avez besoin que ces changements se reflètent en aval, CDC est l’approche propre. La réplication logique (ou un plugin de décodage) vous donne des événements de changement ordonnés avec positions LSN. Le gain opérationnel : vous arrêtez de deviner ce qui a changé.

Le coût opérationnel : vous exécutez maintenant des consommateurs stateful et devez surveiller le lag du slot de réplication. Mais vous gérez déjà des systèmes stateful ; vous les appelez simplement « pipelines » et faites semblant qu’ils sont sans état.

Modèle 4 : Séparez OLTP de la charge d’extraction

Exécutez les extractions lourdes contre une réplica, pas le primaire. Si vous devez extraire depuis le primaire, utilisez des prédicats soigneusement indexés, de petits lots et des timeouts de requête explicites. Les requêtes analytiques sur les primaires sont la façon dont « data engineering » devient une catégorie d’incident.

Modèle 5 : Staging dans Postgres seulement si vous pouvez le garder propre

Les tables de staging dans Postgres sont acceptables quand elles sont éphémères et vacuumées, et quand vous ne transformez pas la base en zone de dépôt pour des événements bruts non bornés. Utilisez des tables UNLOGGED pour le staging transitoire si vous pouvez tolérer une perte de données en cas de crash et que vous voulez de la vitesse. Pour une ingestion brute durable à grande échelle, Postgres n’est généralement pas la meilleure zone d’atterrissage.

Modèle 6 : Upsert avec intention (pas avec de l’espoir)

INSERT ... ON CONFLICT est un cadeau. C’est aussi une arme chargée. Si vous upsertez de gros lots dans des tables très sollicitées sans penser aux index et au bloat, vous découvrirez ce que signifie « autovacuum scale factor » à 2h du matin.

Pour le staging ETL, utilisez des clés déterministes et incluez une colonne source-version (comme LSN source, ou updated_at, ou un hash) afin de détecter les mises à jour sans effet et d’éviter de réécrire inutilement des lignes.

Modèles ETL ClickHouse qui restent sensés

Modèle 1 : Insérez en blocs, pas ligne par ligne

ClickHouse adore les inserts volumineux. Votre objectif est moins de gros blocs qui se compressent bien et produisent moins de parts. Beaucoup de petits inserts créent trop de parts, et les merges en arrière-plan passeront leur vie à nettoyer après vous.

Modèle 2 : Concevez les tables MergeTree autour des requêtes, pas du « schéma source »

La conception d’une table ClickHouse commence par :

• clé de partition (ce que vous élaguez),
• ORDER BY (comment vous regroupez pour les scans en plage et la déduplication),
• clé primaire (index sparse sur les données ordonnées).

Si vous choisissez une clé de partition qui génère des partitions minuscules (comme par user_id) vous créez un zoo de partitions et de merges. Si vous partitionnez trop grossièrement, vous perdez l’élagage et les backfills deviennent coûteux. Les partitions basées sur le temps (jour/semaine/mois) sont courantes car elles correspondent à la gestion des données tardives et aux TTL.

Modèle 3 : Gérez explicitement les duplicatas avec ReplacingMergeTree (et comprenez le compromis)

ReplacingMergeTree est le moteur pour « j’ai des duplicatas et je veux une vue last-write-wins ». Il fonctionne en effondrant les doublons pendant les merges en se basant sur la clé de tri (et une colonne de version optionnelle). Le point non évident : la déduplication est éventuelle. Les requêtes peuvent voir des doublons tant que les merges n’ont pas eu lieu, sauf si vous forcez une requête FINAL (coûteuse) ou concevez autour de cela.

Utilisez-le quand :

• vous pouvez tolérer des doublons temporaires,
• vous avez une clé de déduplication stable,
• et vous pouvez fournir une colonne de version (ex. source updated_at ou séquence d’ingestion).

Évitez-le quand vous avez besoin d’une unicité transactionnelle stricte au moment de la lecture. Ce n’est pas la mission de ClickHouse.

Modèle 4 : Pour les faits « append-only », restez simple : MergeTree + lignes immuables

Si vos données sont naturellement immuables (événements, logs, page views), n’inventez pas de mises à jour. Utilisez un simple MergeTree ou ReplicatedMergeTree, partitionnez par temps, ordonnez selon les clés de requête et laissez faire.

Modèle 5 : Utilisez les vues matérialisées pour transformer à l’ingestion, pas comme baguette magique

Les vues matérialisées sont excellentes pour :

• pré-agrégations (ex. rollups horaires),
• dénormaliser des attributs de référence à l’ingestion,
• scinder un flux brut en plusieurs tables façonnées.

Mais elles ne traitent que ce qui est inséré après leur création. Si vous créez une vue en attendant que des données historiques apparaissent, vous apprendrez une leçon utile sur la causalité. Les triggers d’insertion ne voyagent pas dans le temps.

Modèle 6 : Données arrivant en retard : stratégie de partition + « fenêtre de grâce » pour les merges

Les données tardives sont normales. Votre travail est de les rendre peu coûteuses. Utilisez des partitions temporelles qui s’alignent sur le retard maximal attendu, et gardez les partitions récentes « favorables aux merges ». Une approche courante :

• Conserver des partitions quotidiennes pour les N derniers jours.
• Fusionner agressivement ces partitions.
• Après N jours, envisager de compacter ou déplacer les partitions plus anciennes vers un stockage moins coûteux via TTL.

Modèle 7 : Agrégations : AggregatingMergeTree est puissant, mais ce n’est pas gratuit

Si vous utilisez AggregatingMergeTree, vous stockez des états d’agrégat et les fusionnez. Cela peut être extrêmement efficace pour les rollups, mais complique les requêtes ad hoc et le débogage car les données stockées ne sont pas des faits bruts. Utilisez-le pour des métriques stables et bien définies. Conservez une table de faits bruts pour la réconciliation et le retraitement.

Blague n°2 : les merges de ClickHouse sont comme le linge—ignorez-les assez longtemps et tout s’accumule, ça sent mauvais, et ça vous coûte le week-end.

Le transfert : staging, contrats et réconciliation

Cessez de penser « ETL », commencez à penser « contrats »

Le plus grand saut vers la fiabilité des pipelines est de définir le contrat à la frontière : qu’est-ce qu’une ligne valide, quelles clés la rendent unique, ce que signifie « update », et quel timestamp est faisant autorité.

Un contrat utile pour des données de type événement comprend typiquement :

• event_id ou clé d’idempotence déterministe,
• event_time (quand ça s’est produit),
• ingest_time (quand vous l’avez vu),
• source_version (LSN, updated_at, ou similaire),
• schema_version (pour évoluer en sécurité).

Stratégie de staging : atterrir brut, puis façonner

Il y a deux approches de staging raisonnables :

1. Brut dans ClickHouse, façonné dans ClickHouse : atterrir une table brute (colonnes typées pour les champs chauds, plus un blob/JSON pour le reste), puis des vues matérialisées construisent les tables façonnées. C’est courant quand ClickHouse est le magasin analytique principal.
2. Brut en dehors des deux, charger les deux : atterrir dans un stockage objet ou une file, puis charger Postgres (pour usages opérationnels) et ClickHouse (pour l’analytique). Cela réduit le couplage et rend les replays plus sûrs.

Quand Postgres est la source de vérité, un pattern courant est le CDC de Postgres vers ClickHouse. Ça fonctionne bien si vous traitez le flux CDC comme votre contrat brut, et capturez explicitement les suppressions/mises à jour.

Réconciliation : les comptes ne suffisent pas, mais c’est un début

Les comptes de lignes sont l’alarme incendie. Ils ne vous diront pas quelle pièce brûle, mais ils vous diront que l’immeuble n’est pas en ordre. Mieux : réconciliez par partitions/fenêtres en utilisant :

• comptes par jour/heure,
• comptes distincts de clés,
• checksums/hashes sur des projections stables,
• taux de données tardives (événements arrivant > X heures en retard).

Si vous ne pouvez pas réconcilier, vous ne gérez pas un pipeline ; vous gérez un moulin à rumeurs.

12+ tâches pratiques avec commandes, sorties et décisions

Voici le type de commandes que vous exécutez quand vous êtes en on-call pour les données. Chacune inclut ce que la sortie signifie et quelle décision prendre ensuite.

Tâche 1 : Vérifier les symptômes de bloat de table Postgres via les dead tuples

cr0x@server:~$ psql -h pg-prod-replica -U app -d appdb -c "SELECT relname, n_live_tup, n_dead_tup, round(100.0*n_dead_tup/nullif(n_live_tup+n_dead_tup,0),2) AS dead_pct FROM pg_stat_user_tables ORDER BY dead_pct DESC LIMIT 10;"
      relname       | n_live_tup | n_dead_tup | dead_pct
------------------+------------+------------+----------
events            |   92034112 |   18010233 |    16.37
orders            |    1203321 |     220331 |    15.48
...

Sens : Un n_dead_tup élevé suggère des mises à jour/suppressions fréquentes sans progrès de vacuum suffisant.

Décision : Si dead_pct est élevé sur des tables upsertées par ETL, réduisez le churn des mises à jour (évitez les mises à jour sans effet), ajustez les réglages d’autovacuum, ou passez à un modèle append + rebuild périodique.

Tâche 2 : Vérifier si autovacuum suit la cadence (par table)

cr0x@server:~$ psql -h pg-prod-replica -U app -d appdb -c "SELECT relname, last_autovacuum, last_autoanalyze, autovacuum_count, autoanalyze_count FROM pg_stat_user_tables ORDER BY last_autovacuum NULLS FIRST LIMIT 10;"
   relname   |     last_autovacuum     |    last_autoanalyze     | autovacuum_count | autoanalyze_count
------------+--------------------------+--------------------------+------------------+-------------------
events      | 2025-12-30 02:11:41+00   | 2025-12-30 02:15:03+00   |             182  |               210
...

Sens : Un last_autovacuum ancien sur des tables chaudes implique que vacuum ne s’exécute pas assez ou est bloqué.

Décision : Si les tables ETL privent autovacuum, réduisez les facteurs d’échelle pour ces tables ou changez l’ETL vers insert-new + swap.

Tâche 3 : Valider l’utilisation d’index pour le prédicat d’extraction incrémentale

cr0x@server:~$ psql -h pg-prod-replica -U app -d appdb -c "EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM orders WHERE updated_at > now() - interval '2 hours';"
                                                     QUERY PLAN
--------------------------------------------------------------------------------------------------------------------
Index Scan using orders_updated_at_idx on orders  (cost=0.43..12842.11 rows=24000 width=312)
  Index Cond: (updated_at > (now() - '02:00:00'::interval))
  Buffers: shared hit=802 read=19
  Execution Time: 48.221 ms

Sens : Vous obtenez un index scan avec peu de buffers lus. Bien.

Décision : Si c’est un sequential scan, ajoutez/ajustez l’index ou changez le prédicat (ex. utilisez un curseur numérique, évitez les fonctions sur la colonne).

Tâche 4 : Vérifier le lag du slot de réplication (santé CDC)

cr0x@server:~$ psql -h pg-prod-primary -U repl -d postgres -c "SELECT slot_name, active, pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) AS restart_lag, pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), confirmed_flush_lsn)) AS flush_lag FROM pg_replication_slots;"
   slot_name    | active | restart_lag | flush_lag
----------------+--------+-------------+-----------
ch_cdc_slot     | t      | 12 GB       |  420 MB

Sens : Le WAL est retenu parce que le consommateur n’a pas avancé. Le restart lag affecte l’utilisation disque ; le flush lag est « à quel point le consommateur est en retard ».

Décision : Si le lag augmente, bridez les producteurs, corrigez le consommateur, ou augmentez le disque WAL. Ne supprimez pas le slot à la légère ; c’est ainsi que vous perdez l’historique des changements.

Tâche 5 : Inspecter les verrous Postgres qui peuvent bloquer l’ETL

cr0x@server:~$ psql -h pg-prod-primary -U app -d appdb -c "SELECT pid, wait_event_type, wait_event, query FROM pg_stat_activity WHERE state <> 'idle' AND wait_event IS NOT NULL ORDER BY pid LIMIT 5;"
 pid  | wait_event_type |  wait_event   |                       query
------+-----------------+---------------+--------------------------------------------------
48211 | Lock            | relation      | ALTER TABLE orders ADD COLUMN promo_code text;
...

Sens : Votre ETL peut être en file derrière un verrou DDL ou l’inverse.

Décision : Déplacez les DDL en fenêtres de maintenance, utilisez des builds d’index concurrentes, et définissez des timeouts de requête pour les sessions ETL.

Tâche 6 : Vérifier l’explosion de parts ClickHouse (trop petits inserts)

cr0x@server:~$ clickhouse-client -h ch01 -q "SELECT table, sum(parts) AS parts, formatReadableSize(sum(bytes_on_disk)) AS size FROM system.parts WHERE active AND database='analytics' GROUP BY table ORDER BY parts DESC LIMIT 5;"
events_raw	18234	1.21 TiB
sessions	 3921	211.33 GiB

Sens : Un nombre élevé de parts actives indique de nombreuses petites parts ; les merges auront du mal et les requêtes deviennent lentes.

Décision : Batcher les inserts, ajuster les inserts asynchrones, ou repenser l’ingestion pour réduire la création de parts. Envisagez un ajustement du partitionnement si les partitions sont trop granulaires.

Tâche 7 : Voir si les merges ClickHouse prennent du retard

cr0x@server:~$ clickhouse-client -h ch01 -q "SELECT database, table, sum(rows) AS rows_merging, count() AS merge_jobs FROM system.merges GROUP BY database, table ORDER BY rows_merging DESC LIMIT 5;"
analytics	events_raw	1289031123	7
analytics	sessions	 210331119	3

Sens : Beaucoup de lignes en merge signifie que le travail en arrière-plan est lourd ; une amplification d’écriture se produit.

Décision : Réduisez le nombre de parts d’insert, évitez les backfills pendant les pics, augmentez soigneusement les ressources de merge, ou arrêtez temporairement les jobs d’optimisation lourds.

Tâche 8 : Vérifier les hotspots de requêtes ClickHouse et le volume de lecture

cr0x@server:~$ clickhouse-client -h ch01 -q "SELECT query_duration_ms, read_rows, formatReadableSize(read_bytes) AS read, query FROM system.query_log WHERE type='QueryFinish' AND event_time > now() - INTERVAL 10 MINUTE ORDER BY read_bytes DESC LIMIT 3;"
8421	1290341122	78.12 GiB	SELECT * FROM events_raw WHERE ...
2103	230113112	12.03 GiB	SELECT user_id, count() FROM events_raw ...

Sens : Quelqu’un effectue un large SELECT * ou manque d’élagage de partitions.

Décision : Corrigez les requêtes, ajoutez des projections/rollups matérialisés, ou imposez des limites de requête. Aussi : arrêtez de sélectionner * depuis des tables de faits bruts sauf si vous aimez paginer.

Tâche 9 : Vérifier que le partition pruning fonctionne dans ClickHouse

cr0x@server:~$ clickhouse-client -h ch01 -q "EXPLAIN indexes=1 SELECT count() FROM events_raw WHERE event_date = toDate('2025-12-29') AND customer_id = 42;"
Expression (Projection)
  Aggregating
    ReadFromMergeTree (analytics.events_raw)
    Indexes:
      MinMax
        Keys: event_date
        Condition: (event_date in [2025-12-29, 2025-12-29])
        Parts: 3/120
        Granules: 512/20480

Sens : Seulement 3 sur 120 parts sont lues. C’est de l’élagage. Votre clé de partition fait un vrai travail.

Décision : Si elle lit la plupart des parts, revisitez la clé de partition, les prédicats de requête, ou envisagez l’ajout d’un index de saut secondaire pour les filtres courants.

Tâche 10 : Détecter les clés dupliquées dans l’ingestion ClickHouse (avant que les merges ne les cachent)

cr0x@server:~$ clickhouse-client -h ch01 -q "SELECT event_id, count() AS c FROM events_raw WHERE event_date >= today()-1 GROUP BY event_id HAVING c > 1 ORDER BY c DESC LIMIT 5;"
e_9f3b2	4
e_1a77c	3

Sens : Des duplicatas arrivent. C’est peut-être attendu (réessais) ou un bug (producteur non idempotent).

Décision : Si les duplicatas sont attendus, assurez-vous que la stratégie de dédup (ReplacingMergeTree/version) correspond à la sémantique. Si ce n’est pas attendu, corrigez l’idempotence du producteur et ajoutez une déduplication au niveau ingestion.

Tâche 11 : Vérifier la queue de réplication ClickHouse (si vous utilisez ReplicatedMergeTree)

cr0x@server:~$ clickhouse-client -h ch01 -q "SELECT database, table, count() AS queue, sum(num_tries) AS tries FROM system.replication_queue GROUP BY database, table ORDER BY queue DESC LIMIT 5;"
analytics	events_raw	23	41

Sens : Des tâches de réplication sont en attente ; les réessais suggèrent des problèmes réseau/disque ou une surcharge.

Décision : Si la queue grossit, vérifiez la santé de ZooKeeper/ClickHouse Keeper, le réseau, la latence disque, et si les merges saturent l’I/O.

Tâche 12 : Valider la « fraîcheur » dans Postgres et ClickHouse avec des watermarks explicites

cr0x@server:~$ psql -h pg-prod-replica -U app -d appdb -c "SELECT max(updated_at) AS pg_max_updated_at FROM orders;"
     pg_max_updated_at
-----------------------------
2025-12-30 08:41:12.912+00

cr0x@server:~$ clickhouse-client -h ch01 -q "SELECT max(source_updated_at) AS ch_max_updated_at FROM orders_dim;"
2025-12-30 08:39:58

Sens : ClickHouse a ~74 secondes de retard sur la source pour ce jeu de données.

Décision : Si le lag dépasse le SLA, inspectez le lag CDC, les files d’ingestion, les merges et le nombre de parts. Si c’est dans le SLA, laissez-le tranquille.

Tâche 13 : Suivre la pression disque ClickHouse et les octets écrits par les merges

cr0x@server:~$ clickhouse-client -h ch01 -q "SELECT name, value FROM system.asynchronous_metrics WHERE name IN ('DiskUsed_default','DiskAvailable_default','MergesBytesWritten') ORDER BY name;"
DiskAvailable_default	2.31e+12
DiskUsed_default	5.84e+12
MergesBytesWritten	1.19e+12

Sens : L’utilisation disque et le volume d’écriture des merges sont élevés ; vous pouvez être en territoire d’amplification d’écriture.

Décision : Mettez en pause les gros backfills, réduisez les parts, et assurez-vous que les TTL de déplacement/suppression ne provoquent pas un churn constant.

Tâche 14 : Vérifier la position d’un consommateur CDC par rapport au LSN Postgres

cr0x@server:~$ psql -h pg-prod-primary -U repl -d postgres -c "SELECT pg_current_wal_lsn() AS current_lsn;"
 current_lsn
-------------
 5A3/1F2B9C0

cr0x@server:~$ psql -h pg-prod-primary -U repl -d postgres -c "SELECT confirmed_flush_lsn FROM pg_replication_slots WHERE slot_name='ch_cdc_slot';"
 confirmed_flush_lsn
---------------------
 5A3/1A90D10

Sens : Le consommateur est en retard d’une distance WAL mesurable.

Décision : Si elle croît, scalez le consommateur, corrigez la pression en aval (souvent merges/disque ClickHouse), ou réduisez temporairement le volume de changements amont pour les tables non critiques.

Playbook de diagnostic rapide

Quand l’ETL ralentit ou que les métriques dérivent, vous n’avez pas le temps de devenir philosophe. Vous avez besoin d’un chemin de triage rapide qui restreint le domaine de la défaillance en quelques minutes.

Première étape : est-ce la fraîcheur, la correction ou le coût ?

• Problème de fraîcheur : les données sont en retard ; les timestamps max accusent du retard.
• Problème de correction : doublons, lignes manquantes, jointures erronées, ou « les chiffres ont changé après un backfill ».
• Problème de coût : pics CPU/I/O, merges hors de contrôle, requêtes en timeout.

Deuxième étape : localiser la frontière du goulot

1. Source (extraction Postgres) : requêtes lentes, contention de verrous, lag de slot de réplication.
2. Transport : backlog de file, erreurs consommateur, problèmes de sizing des batches.
3. Sink (ingestion ClickHouse) : explosion de parts, merges en retard, file de réplication.
4. Requêtage : mauvaises patterns, manque d’élagage, mauvais design de table.

Troisième étape : les vérifications minimales qui trouvent généralement le problème

1. Lag de watermark : comparez les max updated/event times source vs sink (Tâche 12).
2. Lag du slot CDC : si vous utilisez CDC, vérifiez le lag du slot de réplication (Tâche 4/14).
3. Parts et merges ClickHouse : nombre de parts et backlog de merges (Tâche 6/7).
4. Top read bytes du query log : trouvez la requête qui mange le cluster (Tâche 8).
5. Vérification du partition pruning : confirmez que les requêtes chaudes élaguent les parts (Tâche 9).

Si vous faites ces cinq choses, vous saurez généralement si vous êtes face à une extraction lente, une ingestion lente, ou des lectures coûteuses. Ensuite vous pouvez corriger la bonne chose au lieu de « tout tuner », ce qui n’est que de la panique avec un tableur.

Erreurs courantes : symptômes → cause racine → correction

1) Les métriques sautent après des relances

Symptômes : relancer un job change les totaux journaliers ; sessions dupliquées ; « même fenêtre, résultat différent ».

Cause racine : chargements non idempotents (append sans dédup), ou clés d’upsert qui ne représentent pas l’unicité réelle.

Correction : définissez une clé d’idempotence déterministe, chargez dans une table de staging puis fusionnez dans les tables finales avec une dédup explicite (Postgres : ON CONFLICT ; ClickHouse : ReplacingMergeTree avec version, ou dédup explicite au niveau requête pour les tables brutes).

2) L’ingestion ClickHouse commence vite puis ralentit à l’extrême

Symptômes : les inserts expirent ; CPU et disque occupés ; les parts actives explosent.

Cause racine : trop de petits inserts causant une explosion de parts ; les merges ne suivent pas.

Correction : batcher les inserts en plus gros blocs, ajuster le buffering d’insert, et éviter les inserts par ligne. Envisagez de réduire la granularité du partitionnement et de revoir le ORDER BY pour améliorer la localité des merges.

3) Le backfill « a marché » mais les requêtes sont plus lentes

Symptômes : après un backfill, les scans lisent beaucoup plus de données ; les caches ratent ; les temps de réponse se dégradent.

Cause racine : le backfill a inséré des données hors ordre par rapport au ORDER BY, produisant de nombreuses parts et une mauvaise localité ; ou il a touché d’anciennes partitions avec TTL/merges et provoqué du churn.

Correction : backfiller dans l’ordre des partitions, utiliser des tailles de batch contrôlées, et éviter d’intercaler anciennes et nouvelles partitions. Validez l’élagage avec EXPLAIN avant et après.

4) Le primaire Postgres devient lent pendant les extractions

Symptômes : latence applicative augmente ; pool de connexions saturé ; requêtes lentes montrent les statements d’extraction.

Cause racine : lectures ETL en compétition avec les écritures OLTP ; scans séquentiels dus à des index manquants ; transactions longues retenant le vacuum.

Correction : exécutez les extractions sur des réplicas, ajoutez des index appropriés pour les prédicats incrémentaux, limitez la taille des batches, et appliquez des timeouts de requête. Gardez les transactions ETL courtes.

5) « Nous utilisons updated_at » mais des lignes disparaissent quand même

Symptômes : la source a des lignes absentes du sink ; généralement un petit pourcentage ; difficile à reproduire.

Cause racine : updated_at pas fiable sur tous les changements ; confusion de fuseaux horaires ; frontière de fenêtre d’extraction trop serrée ; dérive d’horloge des serveurs applicatifs.

Correction : ajoutez un updated_at déclenché par la base si nécessaire, incluez une fenêtre de lookback, stockez la frontière d’extraction explicitement, ou passez au CDC.

6) ClickHouse affiche des duplicatas « parfois »

Symptômes : des duplicatas apparaissent dans les tables brutes ou même dédupliquées ; ensuite ils « disparaissent ».

Cause racine : s’appuyer sur les merges de ReplacingMergeTree pour la déduplication ; la déduplication est éventuelle.

Correction : acceptez la dédup éventuelle et requêtez en conséquence (évitez FINAL sauf si nécessaire), ou dédupliquez à l’ingestion. Pour des besoins stricts, conservez une table « état courant » construite par des jobs de compaction périodiques.

7) La correction du pipeline casse après une évolution de schéma

Symptômes : colonnes décalées, nulls là où il ne faudrait pas, tableaux de bord silencieusement erronés.

Cause racine : évolution de schéma sans contrats ; utilisation de SELECT * ; import CSV positionnel ; typage faible à l’ingestion.

Correction : versionnez les schémas, épinglez les listes de colonnes, validez avec des checks (taux de null, cardinalité), et échouez rapidement sur les changements incompatibles.

Trois mini-histoires d’entreprise (douleur familière)

Mini-histoire 1 : L’incident causé par une mauvaise hypothèse

La société A gérait un produit d’abonnement. Postgres était le système de facturation ; ClickHouse l’analytique. L’équipe data a construit un extracteur incrémental basé sur updated_at de la table subscriptions. Ça avait l’air parfait en dev, ok en staging, et « globalement correct » en production.

Puis la finance a remarqué que les chiffres de churn étaient systématiquement biaisés d’un petit montant. Pas énorme. Juste assez pour provoquer des réunions hebdomadaires où chacun amenait son propre tableur comme couverture émotionnelle.

La mauvaise hypothèse était simple : « updated_at change à chaque modification de sens métier de la ligne ». En réalité, un job en arrière-plan togglait un booléen via une procédure stockée qui ne touchait pas updated_at. Ces lignes n’ont jamais été extraites. Elles n’étaient pas tardives. Elles étaient invisibles.

Ils ont essayé d’élargir la fenêtre d’extraction. Ça a aidé un peu, puis plafonné. Ils ont essayé un reload complet hebdomadaire. Ça « corrigeait » les chiffres et transformait le samedi en rituel désagréable.

La correction a été ennuyeuse et correcte : ils ont ajouté un trigger de base pour forcer la mise à jour de updated_at sur tout changement pertinent, et introduit une vérification de réconciliation secondaire qui comparait les comptes quotidiens et un checksum des champs clés entre Postgres et ClickHouse. Une fois fait, les mises à jour manquantes sont devenues des alertes au lieu de mystères.

Mini-histoire 2 : L’optimisation qui a échoué

La société B avait un pipeline d’événements à haut volume vers ClickHouse. Les inserts venaient de plusieurs microservices, chacun envoyant de petits batches toutes les quelques secondes. Quelqu’un a remarqué la latence d’ingestion et a « optimisé » en augmentant la concurrence : plus de threads, plus d’inserts parallèles, intervalles de retry plus courts. Les graphes avaient l’air excitants. Comme un lancement de fusée.

Deux jours plus tard, la latence des requêtes a explosé. Le CPU n’était pas en cause. Le disque si. Le nombre de parts actives par table est passé de « sain » à « est-ce un bug ? ». Les merges en arrière-plan ont commencé à occuper la vie du cluster, et les files de réplication ont grandi parce que les réplicas ne pouvaient pas suivre les fetchs de parts et les merges.

Ils avaient optimisé la mauvaise chose. Ils avaient réduit la latence par requête des producteurs au détriment de la stabilité du cluster. ClickHouse peut ingérer très vite, mais il veut de gros blocs. Beaucoup de petits blocs, c’est comme nourrir un broyeur de bois par un cure-dent à la fois : techniquement possible, émotionnellement épuisant.

Le rollback n’a pas été glamour : ils ont forcé le batching en amont (plus gros blocs, moins d’inserts), introduit un buffer/queue pour lisser les rafales, et limité les backfills aux fenêtres de faible charge. La latence d’ingestion s’est améliorée autrement car les merges sont devenus gérables. L’« optimisation » était une taxe, pas une fonctionnalité.

Mini-histoire 3 : La pratique ennuyeuse mais correcte qui a sauvé la mise

La société C gérait un marketplace. Leur analytique alimentait les décisions de pricing, donc la correction comptait. Ils avaient une règle : chaque pipeline doit publier un rapport de réconciliation quotidien dans une petite table Postgres—comptes, clés distinctes et un checksum par partition. Ce n’était pas fancy. Ce n’était pas du machine learning. C’était de l’arithmétique.

Un mardi, un déploiement a changé la génération d’IDs d’événement d’un service. Ce n’était pas malveillant. Ce n’était même pas manifestement incorrect. Mais ça a changé la sémantique d’idempotence : les réessais produisaient désormais de nouveaux IDs au lieu de réutiliser le même.

Dans ClickHouse, des duplicatas ont commencé à s’accumuler. Les tableaux de bord augmentaient. La plupart des équipes ne l’auraient pas remarqué pendant une semaine, car la dérive était graduelle et tout le monde suppose « le trafic augmente ».

Le job de réconciliation l’a détecté en quelques heures : le distinct event_id par jour a chuté par rapport au total de lignes, et le checksum a divergé. L’alerte est arrivée sur le bon canal, et l’on-call avait assez de preuves pour pointer directement « génération d’ID changée » au lieu de courir après des problèmes de merge ou de blâmer ClickHouse.

Ils ont corrigé le producteur, rejoué une petite fenêtre, et passé à autre chose. Rien d’héroïque n’est arrivé. C’est le point : des vérifications ennuyeuses préviennent des incidents excitants.

Listes de contrôle / plan étape par étape

Étape par étape : choisir le bon modèle pour chaque jeu de données

1. Classifiez le jeu de données : événements immuables, dimensions changeant lentement, entités mutables, ou métriques agrégées.
2. Définissez la clé unique : event_id, clé naturelle, ou clé synthétique + version.
3. Choisissez le temps faisant autorité : event_time vs updated_at vs CDC LSN ; documentez-le.
4. Décidez comment gérer mises à jour/suppressions : ignorer, ajouter une nouvelle version, ou appliquer des changements d’état via dédup/merge.
5. Choisissez le mécanisme d’extraction : incrémental par timestamp avec lookback, curseur numérique, ou CDC.
6. Choisissez le moteur ClickHouse : MergeTree pour immuable, ReplacingMergeTree pour « dernière version », AggregatingMergeTree pour rollups.
7. Choisissez le partitionnement : généralement basé sur le temps ; confirmez l’élagage avec EXPLAIN.
8. Choisissez ORDER BY : correspondre aux filtres et clés de regroupement les plus courants ; évitez le chaos des cardinalités élevées si cela nuit à la localité des merges.
9. Construisez la réconciliation : par partition/fenêtre ; comptes + clés distinctes + checksum.
10. Définissez la procédure de backfill : ordonnée par partition, limitée en débit, et observable.

Checklist opérationnelle : avant de déployer un nouveau pipeline ETL

• Puis-je relancer le job sans doublons ni dérive ?
• Ai-je des watermarks explicites stockés quelque part durable ?
• Connais-je la latence maximale tolérée, et la conception la gère-t-elle ?
• Les extractions lourdes sont-elles hors du primaire Postgres ?
• L’ingestion ClickHouse batch-elle en gros blocs ?
• Puis-je prouver que le partition pruning fonctionne pour les 3 requêtes principales ?
• Ai-je une alerte sur le lag du slot CDC / lag d’ingestion / explosion de parts ?
• Puis-je backfiller un jour sans impacter la fraîcheur du jour courant ?

Plan de backfill qui ne brûlera pas la production

1. Geler la définition : versionnez le code de transformation utilisé pour le backfill.
2. Choisir des fenêtres : ne backfilez pas « tout l’historique » en un seul job. Utilisez des partitions jour/semaine.
3. Bridez : limitez le débit d’inserts ClickHouse et les merges concurrents ; évitez les heures de pointe.
4. Écrire dans une table shadow : validez comptes/checksums avant de basculer les vues ou le routage des requêtes.
5. Réconciliez : comparez source/sink par partition, pas globalement.
6. Coupez progressivement : commencez par les tableaux de bord internes avant ceux de la direction. Votre futur vous remerciera d’avoir moins de réunions surprises.

FAQ

1) Dois-je utiliser PostgreSQL ou ClickHouse comme zone de staging ?

Si le staging est transitoire et petit, Postgres peut convenir. Si le staging est volumineux, brut et append-heavy, ClickHouse ou une zone d’atterrissage externe est généralement préférable. Le staging ne doit pas devenir un tiroir à déchets non borné.

2) ClickHouse peut-il remplacer mes requêtes analytiques Postgres directement ?

Pour les lectures intensives analytiques, souvent oui. Mais ClickHouse ne remplacera pas les sémantiques transactionnelles, les contraintes strictes et les patterns de mise à jour ligne-par-ligne sans repenser votre modèle de données.

3) Quel est le plus simple chargement incrémental sûr depuis Postgres ?

Un extract incrémental utilisant un updated_at fiable plus une fenêtre de lookback, chargé dans une table de staging, puis upserté dans le modèle final avec des clés déterministes.

4) Quand passer d’un incrémental basé sur timestamp au CDC ?

Quand les mises à jour/suppressions comptent, quand vous ne pouvez pas faire confiance à updated_at, ou quand vous avez besoin d’une capture de changements exacte sans scanner de larges fenêtres. Le CDC ajoute de la complexité opérationnelle mais enlève l’ambiguïté.

5) ReplacingMergeTree suffit-il pour « faire des upserts » dans ClickHouse ?

C’est suffisant pour un « eventual last-write-wins » si vous fournissez une clé de dédup stable et idéalement une colonne de version. Si vous avez besoin d’une unicité stricte au moment de la lecture, il faudra une logique supplémentaire ou une autre approche.

6) Pourquoi les requêtes FINAL sont-elles déconseillées ?

FINAL force les merges/la déduplication au moment de la requête et peut transformer un scan rapide en opération coûteuse. C’est un outil de débogage ou un dernier recours, pas le réglage par défaut.

7) Comment éviter de casser les tableaux de bord lors d’une évolution de schéma ?

Versionnez les schémas, évitez SELECT *, validez les taux de null et les cardinalités sur les nouvelles colonnes, et utilisez des listes de colonnes explicites à l’ingestion. Échouer vite est mieux que rester silencieusement faux.

8) Quelle est la meilleure façon de gérer les suppressions Postgres dans ClickHouse ?

Soit modéliser des « tombstones » (un événement de suppression) et filtrer à la requête, soit maintenir une table d’état courant où les suppressions sont appliquées via dédup/versioning. ClickHouse peut faire des deletes, mais les suppressions fréquentes au niveau ligne sont coûteuses.

9) Comment savoir si ClickHouse est lent à cause de l’ingestion ou des requêtes ?

Vérifiez d’abord le nombre de parts actives et le backlog des merges (pression ingestion), puis le query log (octets lus et durées) pour la pression de lecture. Si les deux sont élevés, vos écritures ont créé le problème de lecture—généralement trop de parts ou un mauvais partitionnement.

10) Quelle est une base raisonnable de réconciliation ?

Par partition/fenêtre : compte de lignes, compte distinct de clés, et un checksum/hash sur une projection stable des champs clés. Ajoutez le taux d’arrivée tardive si le temps importe (et il importe).

Conclusion : prochaines étapes pour réduire le risque cette semaine

Si vous voulez un ETL qui n’engendre pas le chaos des données, ne commencez pas par tourner des boutons. Commencez par décider ce que signifie « correct », puis rendez-le observable.

1. Définissez des clés d’idempotence pour vos 3 jeux de données prioritaires et documentez-les là où les ingénieurs les verront.
2. Ajoutez des watermarks explicites (temps/version max source et sink) et alertez sur le lag.
3. Exécutez une réconciliation quotidienne pour un pipeline critique : comptes, clés distinctes, checksum par jour. Déployez-la. Itérez.
4. Dans ClickHouse, mesurez les parts et les merges et corrigez les patterns de petits-inserts avant qu’ils ne deviennent un mode de vie.
5. Retirez les extractions lourdes du primaire Postgres ou passez au CDC si les mises à jour comptent et que la table est volumineuse.

Les systèmes de production ne récompensent pas l’optimisme. Ils récompensent les contrats explicites, des modes d’échec contrôlés, et des pipelines que vous pouvez relancer un mardi sans négocier avec l’univers.