PostgreSQL vs ClickHouse : où stocker les logs en firehose sans douleur

Le cadre de décision : ce que vous choisissez vraiment

Quand les gens disent « stocker des logs », ils veulent souvent dire trois choses différentes :

• Buffer d’ingestion : accepter des écritures à haut débit, même pendant les pics. La durabilité compte, la latence moins.
• Recherche opérationnelle : retrouver « cet request_id » ou un message d’erreur précis dans la dernière heure. La faible latence compte. L’indexation compte.
• Analytique : group-bys sur des milliards de lignes, tableaux de bord, détection d’anomalies, longévité de rétention et agrégations lourdes.

PostgreSQL est une base généraliste avec une excellente exactitude, une riche palette d’index et une sémantique transactionnelle. Il est confortable quand vous avez besoin de contraintes fortes et de recherches point-à-point qui doivent être correctes. Il l’est moins lorsque vous lui demandez de conserver et de faire tourner des pétaoctets de données majoritairement en append-only tout en servant des requêtes OLTP.

ClickHouse est une base analytique en colonnes optimisée pour les scans rapides et les agrégations. Elle dévore les logs au petit-déjeuner — jusqu’à ce que vous partitionniez comme si c’était du Postgres, ou que vous ignoriez les merges, ou que vous la traitiez comme un système transactionnel strict. Ce n’est pas un Postgres moins bon ; c’est un animal différent.

Voici donc une règle empiriquement opinionnée :

• Si les logs font partie de votre produit transactionnel (audit, trails de conformité avec garanties strictes, ou si vous les joignez constamment à des tables OLTP), Postgres peut être un choix sensé — avec partitionnement temporel et rétention disciplinée.
• Si les logs servent principalement à la recherche, aux dashboards et aux agrégations, et que le volume est un « firehose », ClickHouse est le choix par défaut — avec une conception MergeTree appropriée et des garde-fous opérationnels.
• Si vous essayez d’utiliser Postgres comme entrepôt de logs parce que « nous l’avons déjà », vous ne faites pas d’économie ; vous empruntez de la douleur à un taux d’intérêt variable.

Une idée paraphrasée de Werner Vogels (CTO d’Amazon) : on construit la fiabilité en concevant pour la défaillance, pas en espérant qu’elle n’arrivera pas. Les logs sont l’endroit où cette philosophie est testée — parce que votre système tombe en panne plus souvent que votre budget ne l’admet.

Faits intéressants et contexte historique

1. La lignée de PostgreSQL remonte au projet de recherche POSTGRES à l’UC Berkeley dans les années 1980, avec un biais de conception pour la correction et l’extensibilité.
2. MVCC dans Postgres signifie que les mises à jour/suppressions créent de nouvelles versions de lignes ; pour les tables de logs avec churn (suppressions de rétention), cela devient du stockage et du travail de vacuum que vous devez payer.
3. JSONB est arrivé dans Postgres 9.4 et a rendu les « logs semi-structurés dans une BD relationnelle » faciles — parfois trop faciles, car cela tente les équipes à sauter la discipline de schéma.
4. ClickHouse a été développé chez Yandex pour alimenter de l’analytique en temps réel à très grande échelle, ce qui correspond exactement à la forme des « logs comme analytique ».
5. Le stockage en colonnes n’est pas nouveau ; les magasins en colonnes sont étudiés depuis des décennies. Le gain moderne vient de la combinaison compression, exécution vectorisée et disques bon marché.
6. Les moteurs MergeTree dans ClickHouse reposent sur des merges en arrière-plan ; ignorer la santé des merges revient à ignorer autovacuum dans Postgres — à terme cela génère de la dette.
7. TTL dans ClickHouse peut appliquer la rétention au niveau du stockage. Postgres peut gérer la rétention, mais c’est généralement une pratique côté application ou job (drop de partitions, delete, vacuum).
8. TimescaleDB (extension Postgres) existe en grande partie parce que les patterns time-series stressent le Postgres vanilla ; c’est un signal que « logs comme séries temporelles » est spécial.

Schémas de charge qui font ou défont chaque système

1) Écritures en rafale avec clés prévisibles

L’ingestion n’est pas une question de débit moyen. Il s’agit de survivre au 99e centile : tempêtes de déploiement, retries, défaillances en cascade, et ce client qui active le debug en production « juste pour une heure ». Si vous avez des rafales, vous avez besoin de :

• Un chemin d’écriture qui ne s’écroule pas sous la contention.
• Une stratégie de backpressure (queue, buffer, inserts par lots).
• Un agencement de stockage qui maintient les écritures séquentielles ou au moins amorties.

ClickHouse aime les inserts par lots et peut absorber d’énormes volumes efficacement. Postgres peut aussi ingérer rapidement, mais vous atteindrez plus tôt le volume WAL, la maintenance des index et le surcoût de vacuum.

2) Recherche « aiguille dans une botte de foin »

Les opérateurs demandent : « Montre-moi les logs pour request_id = X » et « Donne-moi les 200 dernières lignes autour de l’erreur ». Postgres avec des index B-tree sur request_id et timestamp peut être extrêmement rapide pour cela, surtout si le working set tient en mémoire.

ClickHouse peut aussi le faire, mais il préfère scanner des colonnes et filtrer. Vous pouvez l’optimiser avec un ordering primaire approprié et des skip indexes (data skipping), mais il faut le concevoir.

3) Agrégations et tableaux de bord

Si votre réalité quotidienne est GROUP BY service, status, endpoint, 5-minute bucket sur des semaines de données, Postgres lutte contre sa nature. Vous pouvez ajouter des vues matérialisées, des rollups et des partitions. Ça marche — jusqu’à ce que vous manquiez une fenêtre de maintenance et que vous vous retrouviez avec un backlog de vacuum/analyze et un disque qui semble s’être multiplié.

ClickHouse est conçu pour cela. Il compresse bien, scanne vite et parallélise. La question opérationnelle devient : pouvez-vous garder les merges sains et les parts sous contrôle ?

4) Rétention : delete vs drop vs TTL

La politique de rétention n’est pas « nous gardons 30 jours ». C’est : « Comment supprimer des données de 29 jours sans détruire le système ? »

• Dans Postgres, drop de partitions est propre et rapide. Les suppressions ligne par ligne sont coûteuses et créent de la dette de vacuum.
• Dans ClickHouse, TTL peut supprimer automatiquement les anciennes données, mais c’est toujours un travail physique fait par des processus en arrière-plan. C’est généralement moins cher que des deletes Postgres, mais cela peut saturer les disques si mal configuré.

PostgreSQL pour les logs : ses atouts et ses punitions

Quand Postgres est le bon choix

• Vous avez besoin de transactions strictes : les logs font partie d’un processus métier (événements d’audit, trails de type ledger).
• Vous avez besoin de jointures relationnelles entre les logs et des entités OLTP, avec une sémantique cohérente.
• Votre volume est modéré : « beaucoup » mais pas « firehose », ou la fenêtre de rétention est courte et les drops de partitions sont routiniers.
• Vous avez besoin d’indexation flexible : B-tree, GIN sur JSONB, recherche trigram, index partiels.

Ce pour quoi Postgres vous punira

Postgres stocke des lignes, pas des colonnes. Pour l’analytique de logs, vous lisez souvent quelques champs sur une grande plage temporelle. Cela devient intensif en I/O. Et avec MVCC, « supprimer les anciens logs » signifie tuples morts et travail de vacuum. Le partitionnement aide, mais il n’est pas facultatif.

Il y a aussi le WAL. Le WAL est votre ami — jusqu’à ce que votre table de logs devienne le principal contributeur au volume WAL, au lag de réplication et au turnover des backups.

Comment rendre Postgres moins pénible pour les logs

• Partitionnez par temps (quotidien ou horaire selon le volume) et droppez les partitions pour la rétention.
• Minimisez les index sur les tables à ingestion chaude. Chaque index est une taxe d’amplification d’écriture.
• Utilisez JSONB prudemment : stockez les clés fréquemment interrogées en colonnes typées ; laissez le reste en JSONB.
• Séparez les préoccupations : n’hébergez pas l’ingestion de logs lourde sur le même primaire que l’OLTP critique si vous pouvez l’éviter.

Petite blague #1 : Traiter Postgres comme un entrepôt de logs, c’est comme utiliser une voiture de sport pour transporter du gravier — possible, mais la suspension va se plaindre.

ClickHouse pour les logs : ses atouts et ses punitions

Quand ClickHouse est le bon choix

• Ingestion en firehose avec inserts par lots (ou un broker devant) et beaucoup de données append-only.
• Requêtes orientées analytique : dashboards, agrégations, percentiles de latence, top-N, estimations de cardinalité.
• Rétention longue avec compression et tiering de stockage.
• Concurrence de nombreux lecteurs effectuant des scans et des group-bys.

Ce pour quoi ClickHouse vous punira

ClickHouse vous punira si vous pensez que « clé de partition » signifie « ce que je filtre le plus ». Dans ClickHouse, le partitionnement sert à gérer les parts et le cycle de vie des données ; l’ordering (clé primaire) sert à l’élagage des requêtes et à la localité. Si vous vous trompez, vous créerez une apocalypse de petits parts où les merges ne rattrapent jamais.

Il vous punira aussi si vous prétendez que c’est un système transactionnel strict. Les inserts sont atomiques par lot, mais vous n’obtenez pas la même sémantique que Postgres. Si vous avez besoin d’« exactly once », vous le construisez au-dessus de la base avec des stratégies d’idempotence et de déduplication.

Comment rendre ClickHouse moins pénible pour les logs

• Inserts par lots (blocs plus gros, moins de parts). Si votre pipeline insère des lignes une par une, vous payez une taxe en parts et merges.
• Choisissez ORDER BY pour vos filtres les plus courants (généralement timestamp + service + élément comme host ou trace id).
• Gardez des partitions grossières (souvent par jour) sauf si le volume force des partitions plus petites. Trop de partitions augmente le metadata et la surcharge de merge.
• Surveillez merges et parts comme vous surveillez le CPU. La dette de merge est une dette opérationnelle.

Petite blague #2 : Un cluster ClickHouse avec des parts incontrôlées, c’est comme un garage plein de « piles organisées » — techniquement trié, émotionnellement dévastateur.

Schéma et conception de table qui résistent à la réalité

Les logs ne sont pas totalement sans schéma

Les logs purement sans schéma sont une fantaisie. Vous avez toujours quelques champs que vous interrogez constamment : timestamp, service, environment, severity, host, request_id/trace_id, status, et peut-être user_id. Ces champs doivent être des colonnes typées à la fois dans Postgres et ClickHouse. Tout le reste peut aller dans une payload JSON ou une structure Map-like.

Postgres : stratégie de partitionnement

Utilisez le partitionnement déclaratif par temps. Les partitions quotidiennes sont un bon départ. Les partitions horaires peuvent convenir pour un très haut volume, mais vous paierez en nombre de tables et en overhead opérationnel. Le coup gagnant est de rendre la rétention une opération DROP TABLE, pas une opération DELETE.

ClickHouse : stratégie MergeTree

Pour les logs, un schéma courant est :

• PARTITION BY toDate(ts) (ou toYYYYMM(ts) pour une rétention très longue)
• ORDER BY (service, environment, toDateTime(ts), trace_id) ou similaire
• TTL ts + INTERVAL 30 DAY

Le bon ORDER BY dépend de vos filtres. Si vous filtrez toujours par service et plage temporelle, placez service tôt. Si vous faites beaucoup de point-lookups par request_id, incluez-le — mais ne prétendez pas qu’il se comportera comme un index unique.

Compression et types de données

Dans ClickHouse, choisir des types appropriés (LowCardinality pour les noms de service, Enum pour le niveau de log, IPv4/IPv6 pour les adresses, DateTime64 pour les timestamps) apporte une vraie compression et rapidité. Dans Postgres, une normalisation prudente peut aider, mais une normalisation excessive peut nuire à l’ingestion. Les logs sont un endroit où « légèrement dénormalisé mais typé » gagne souvent.

Tâches pratiques : commandes, sorties, décisions

Ce sont des vérifications réelles que vous pouvez exécuter en production. Chacune inclut : la commande, ce que signifie la sortie, et la décision à prendre.

Task 1: Confirm Postgres table and index bloat

cr0x@server:~$ sudo -u postgres psql -d app -c "SELECT relname, n_live_tup, n_dead_tup, ROUND(100.0*n_dead_tup/GREATEST(n_live_tup,1),2) AS dead_pct FROM pg_stat_user_tables ORDER BY dead_pct DESC LIMIT 5;"
  relname       | n_live_tup | n_dead_tup | dead_pct
----------------+------------+------------+----------
logs_2025_12_28 |   98000000 |   34000000 |   34.69
logs_2025_12_27 |   99000000 |   21000000 |   21.21
...

Sens : Les tuples morts sont du stockage et du travail de vacuum. 35% de morts dans une partition de logs signifie généralement que des suppressions/mises à jour ont lieu ou que l’autovacuum n’arrive pas à suivre.

Décision : Arrêtez de supprimer des lignes pour la rétention ; passez aux drops de partitions. Si vous avez déjà des partitions, investiguez pourquoi des tuples meurent (mises à jour, ou vacuum retardé) et ajustez autovacuum pour ces partitions.

Task 2: Check whether autovacuum is actually running on hot partitions

cr0x@server:~$ sudo -u postgres psql -d app -c "SELECT relname, last_autovacuum, last_autoanalyze FROM pg_stat_user_tables WHERE relname LIKE 'logs_%' ORDER BY last_autovacuum NULLS FIRST LIMIT 10;"
   relname      |     last_autovacuum     |     last_autoanalyze
---------------+--------------------------+--------------------------
logs_2025_12_28 |                          |
logs_2025_12_27 | 2025-12-30 08:14:02+00   | 2025-12-30 08:15:31+00
...

Sens : Un last_autovacuum NULL sur une partition active et volumineuse est un signal d’alerte : vacuum peut être bloqué, désactivé ou affamé.

Décision : Si c’est une table à ingestion lourde, envisagez d’abaisser les seuils autovacuum pour cette table, ou repensez la rétention et l’indexation. Si vacuum est bloqué, trouvez les bloqueurs.

Task 3: Find blocking sessions in Postgres

cr0x@server:~$ sudo -u postgres psql -d app -c "SELECT a.pid, a.state, a.query, pg_blocking_pids(a.pid) AS blockers FROM pg_stat_activity a WHERE a.datname='app' AND a.wait_event_type IS NOT NULL;"
 pid  | state  |                 query                  | blockers
------+--------+----------------------------------------+----------
18422 | active | VACUUM (ANALYZE) logs_2025_12_28;      | {17201}
...

Sens : Vacuum attend un verrou détenu par un autre PID. Si ce PID est une transaction longue, il peut empêcher le nettoyage.

Décision : Corrigez le comportement applicatif (pas de sessions longues en idle-in-transaction), ou tuez les coupables. Puis revérifiez la progression du vacuum.

Task 4: Measure WAL pressure from log ingestion

cr0x@server:~$ sudo -u postgres psql -d app -c "SELECT pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(),'0/0')) AS wal_bytes_since_boot;"
 wal_bytes_since_boot
----------------------
 812 GB

Sens : C’est un signal grossier, mais si le WAL croît de façon explosive pendant les pics de logs, cela impactera la réplication et les sauvegardes.

Décision : Envisagez de déplacer les logs hors du cluster OLTP primaire, réduisez les index, batchez les inserts, ou basculez vers ClickHouse pour les logs analytiques.

Task 5: Check replication lag (Postgres)

cr0x@server:~$ sudo -u postgres psql -d app -c "SELECT application_name, state, pg_size_pretty(pg_wal_lsn_diff(sent_lsn, replay_lsn)) AS lag FROM pg_stat_replication;"
 application_name |   state   |  lag
------------------+-----------+--------
 standby-a        | streaming | 14 GB

Sens : 14 GB de lag pendant une rafale de logs signifie généralement que le replica n’arrive pas à suivre la génération de WAL ou le débit I/O/réseau.

Décision : Si les logs sont le principal contributeur, découplez-les. Sinon, investiguez la saturation I/O sur le replica et les paramètres du WAL receiver.

Task 6: Confirm Postgres partition count and size skew

cr0x@server:~$ sudo -u postgres psql -d app -c "SELECT inhparent::regclass AS parent, count(*) AS partitions FROM pg_inherits WHERE inhparent::regclass::text='logs';"
 parent | partitions
--------+------------
 logs   | 62

Sens : Trop de partitions peut nuire au planning et à la maintenance ; trop peu peut rendre la rétention coûteuse.

Décision : Si les partitions dépassent ce que votre cadence opérationnelle peut gérer, passez d’horaire à quotidien. Si les partitions sont énormes et que des suppressions ont lieu, fractionnez et droppez.

Task 7: Check ClickHouse ingestion health via system.parts

cr0x@server:~$ clickhouse-client --query "SELECT table, count() AS parts, sum(rows) AS rows FROM system.parts WHERE database='logs' AND active GROUP BY table ORDER BY parts DESC LIMIT 5;"
log_events	12480	9812234451
log_errors	2210	88233412

Sens : 12k parts actives pour une table est souvent trop. De petites parts impliquent des lots d’insert trop petits ou un partitionnement trop granulaire.

Décision : Augmentez la taille des batches d’insert, ajustez le partitionnement, et envisagez des paramètres réduisant le churn de parts. Vérifiez aussi le backlog des merges ensuite.

Task 8: Check ClickHouse merge backlog

cr0x@server:~$ clickhouse-client --query "SELECT database, table, sum(rows) AS rows_in_merges, sum(bytes_on_disk) AS bytes_in_merges FROM system.parts WHERE database='logs' AND active=0 GROUP BY database, table ORDER BY bytes_in_merges DESC LIMIT 5;"
logs	log_events	1823311220	412339922944

Sens : Les parts non-actives représentent des parts en cours de merge ou des parts anciennes. D’énormes octets « en merges » suggèrent une pression de merge et des ralentissements potentiels des requêtes.

Décision : Si les merges n’arrivent pas à suivre, réduisez la fragmentation des inserts, augmentez les ressources pour les merges en arrière-plan, ou retravaillez la conception de table (order key, partition key).

Task 9: Check ClickHouse query hot spots

cr0x@server:~$ clickhouse-client --query "SELECT query_duration_ms, read_rows, read_bytes, result_rows, substring(query,1,120) AS q FROM system.query_log WHERE type='QueryFinish' ORDER BY read_bytes DESC LIMIT 5;"
8421	9122333441	188233992110	120	SELECT service, count() FROM log_events WHERE ts >= now()-INTERVAL 7 DAY GROUP BY service

Sens : Une seule requête de dashboard lisant 188 GB n’est pas « un peu lente », c’est une revue de conception. Soit le filtre est trop large, soit l’ORDER BY est mauvais pour le pattern d’accès.

Décision : Ajoutez de la pré-agrégation, resserrez les filtres, révisez l’ORDER BY, ou créez des vues matérialisées pour les dashboards.

Task 10: Validate ClickHouse partition distribution

cr0x@server:~$ clickhouse-client --query "SELECT partition, count() AS parts, sum(rows) AS rows FROM system.parts WHERE database='logs' AND table='log_events' AND active GROUP BY partition ORDER BY parts DESC LIMIT 5;"
20251230	980	812233441
20251229	910	799334221

Sens : Des centaines de parts par partition journalière sentent mauvais. Cela signifie généralement que les batches d’insert sont trop petits ou que vous avez trop de writers parallèles.

Décision : Batchez les inserts et/ou canalisez les écritures via moins de flux d’insertion concurrents par shard.

Task 11: Check disk I/O saturation on a Linux host

cr0x@server:~$ iostat -xm 1 3
Linux 6.5.0 (server) 	12/30/2025 	_x86_64_	(32 CPU)

Device            r/s     w/s   rMB/s   wMB/s  await  %util
nvme0n1         320.0   900.0   180.0   420.0   28.5  99.2

Sens : 99% d’utilisation et un await élevé indiquent que vous êtes lié par l’I/O. Postgres et ClickHouse auront l’air « lents » pour des raisons différentes, mais la racine est le disque.

Décision : Réduisez l’amplification d’écriture (index/parts), ajoutez des disques, séparez le WAL, passez à un stockage plus rapide, ou ajustez la concurrence merge/vacuum pour éviter des tempêtes I/O auto-infligées.

Task 12: Check memory pressure and cache behavior

cr0x@server:~$ free -h
               total        used        free      shared  buff/cache   available
Mem:           256Gi       210Gi       3.1Gi       2.0Gi        43Gi        39Gi
Swap:           16Gi        12Gi       4.0Gi

Sens : Un usage intensif du swap sur un hôte de base de données est une falaise de performance. Pour Postgres, cela peut ruiner l’efficacité de shared buffers ; pour ClickHouse, cela peut paralyser les merges et l’exécution de requêtes.

Décision : Réduisez l’empreinte mémoire (baisse de la concurrence, ajustement des caches), ajoutez de la RAM, ou isolez les charges. Si vous swappez pendant des merges/vacuum, limitez le travail en arrière-plan.

Task 13: Inspect Postgres slow queries and whether indexes help

cr0x@server:~$ sudo -u postgres psql -d app -c "EXPLAIN (ANALYZE, BUFFERS) SELECT count(*) FROM logs_2025_12_28 WHERE ts >= now()-interval '1 hour' AND service='api';"
Aggregate  (cost=...)
  Buffers: shared hit=120 read=54000
  ->  Seq Scan on logs_2025_12_28 ...

Sens : Un Seq Scan lisant 54k buffers pour une fenêtre d’une heure suggère soit que la table est trop grosse, soit que le filtre n’est pas sélectif, soit que des index manquent/sont inutilisés à cause de statistiques pauvres.

Décision : Envisagez un BRIN sur timestamp pour de grosses partitions append-only, ou revérifiez le partition pruning et les stats. Si l’analytique domine, envisagez de déplacer cette charge vers ClickHouse.

Task 14: Check ClickHouse table definition for ORDER BY/partition/TTL sanity

cr0x@server:~$ clickhouse-client --query "SHOW CREATE TABLE logs.log_events"
CREATE TABLE logs.log_events
(
    `ts` DateTime64(3),
    `service` LowCardinality(String),
    `env` LowCardinality(String),
    `level` LowCardinality(String),
    `trace_id` String,
    `message` String
)
ENGINE = MergeTree
PARTITION BY toDate(ts)
ORDER BY (service, env, ts)
TTL ts + INTERVAL 30 DAY
SETTINGS index_granularity = 8192

Sens : Ceci est généralement sensé pour des filtres « service/env/plage temporelle ». Si vos requêtes principales filtrent uniquement par trace_id, cet ORDER BY n’aidera pas beaucoup.

Décision : Alignez l’ORDER BY avec vos vrais prédicats de requête. Si vous avez besoin de lookup par trace-id, envisagez un index de skipping secondaire ou un magasin de lookup dédié.

Playbook de diagnostic rapide

Quand le magasin de logs est « lent », vous n’avez pas le temps pour l’idéologie. Vous avez besoin d’une méthode en trois passes pour trouver le goulet sans deviner.

Premier : L’hôte est-il la cause (CPU, mémoire, disque, réseau) ?

• Saturation disque : lancez iostat -xm 1. Si %util est près de 100% et que l’attente augmente, vous êtes lié par l’I/O.
• Pression mémoire : lancez free -h et vérifiez le swap. L’usage de swap sur des hôtes DB est une confession de performance.
• CPU : lancez mpstat -P ALL 1 (non montré ci-dessus). Si le CPU est saturé et l’iowait faible, vous êtes lié par le calcul (souvent compression, parsing, regex ou group-bys).
• Réseau : sur les clusters, vérifiez si la réplication ou les requêtes distribuées saturent les NIC.

Décision : Si l’hôte est le goulot, ajustez la BD plus tard ; d’abord arrêtez l’auto-dommage (réduisez la concurrence merge/vacuum, baissez la concurrence des requêtes, batchez les inserts), puis ajoutez de la capacité ou séparez les rôles.

Second : Est-ce le chemin d’écriture ou de lecture ?

• Symptômes écriture : lag d’ingestion, profondeur de queue croissante, augmentation du nombre de parts (ClickHouse), montée du WAL et lag de réplication (Postgres).
• Symptômes lecture : dashboards qui time-out, read_bytes élevé dans les logs de requête (ClickHouse), scans séquentiels et lectures de buffers (Postgres).

Décision : Si les écritures échouent, priorisez batching, sanity du partitionnement et santé de la maintenance en arrière-plan. Si les lectures échouent, priorisez schéma/order keys, pré-agrégation et contraintes de requête.

Troisième : Identifiez la dette de maintenance (vacuum vs merges)

• Postgres : tuples morts, transactions longues bloquant le vacuum, autovacuum qui ne tourne pas ou pas assez agressif pour les partitions de logs.
• ClickHouse : trop de parts, merges qui n’arrivent pas à suivre, disque occupé par I/O de merges, nettoyage TTL qui s’accumule.

Décision : La dette de maintenance ne se résout pas toute seule. Soit vous changez les patterns d’ingestion (batching), soit vous changez les patterns de cycle de vie (drop de partitions / TTL), soit vous payez indéfiniment.

Trois mini-histoires d’entreprise (toutes anonymisées, douloureusement plausibles)

Incident : la mauvaise hypothèse (« les logs sont append-only, donc Postgres ne gonflera pas »)

Une entreprise SaaS de taille moyenne a décidé de stocker les logs applicatifs dans Postgres « temporairement ». L’hypothèse était simple : les logs sont append-only, donc l’overhead MVCC ne comptera pas. Ils ont utilisé une grosse table avec un index temporel et une payload JSONB. L’ingestion fonctionnait bien pendant des semaines. Les dashboards aussi — jusqu’à ce que la rétention entre en jeu.

La rétention était implémentée comme un DELETE FROM logs WHERE ts < now() - interval '30 days' nocturne. Il tournait pendant des heures. Puis des jours. Autovacuum commença à courir après une cible mouvante : des tuples morts s’accumulaient plus vite qu’il ne pouvait les nettoyer. L’utilisation disque monta. Le lag de réplication monta. Le primaire passait plus de temps à écrire du WAL et moins à servir les requêtes produit.

Un lundi matin, un déploiement augmenta la verbosité d’un composant bruyant. Le volume WAL explosa. Le replica prit du retard au point que les marges de sécurité pour le failover disparurent. Puis le disque atteignit un seuil et la réponse à l’incident se transforma en chasse au trésor : supprimer les anciennes données, vacuum, espérer que ça termine, répéter.

La correction n’était pas héroïque. Ils partitionnèrent la table logs par jour et changèrent la rétention pour dropper les partitions. Les paramètres autovacuum furent ajustés par partition. Le système de logs « temporaire » cessa de menacer la base OLTP. La leçon plus large : « append-only » est un mensonge au moment où vous implémentez la rétention par delete.

Optimisation qui a mal tourné : « Partitionnons ClickHouse par heure pour des suppressions plus rapides »

Une autre organisation a migré les logs vers ClickHouse pour l’analytique. Les performances initiales étaient excellentes. Quelqu’un a remarqué que le nettoyage TTL était parfois en rafales et a décidé « d’aider » en partitionnant par heure au lieu de par jour, supposant que des partitions plus petites supprimeraient plus vite.

L’ingestion venait de nombreux services, chacun insérant de petits batches. Les partitions horaires multiplièrent le nombre de partitions actives. Chaque partition accumula des parts. Les parts se multiplièrent en dizaines de milliers par table. Les merges commencèrent à prendre du retard. La latence des requêtes augmenta car le système devait ouvrir et considérer un énorme nombre de parts, et les merges en arrière-plan saturèrent le disque.

L’équipe répondit en augmentant les threads de merge en arrière-plan. Cela rendit les disques plus occupés, ce qui dégrada encore les requêtes, ce qui fit time-out aux dashboards, ce qui provoqua une vague de logique de retry côté clients. L’ingestion devint elle aussi en rafales, et tout le système développa un rythme stroboscopique : tempête de merges, tempête de requêtes, tempête de retries.

Ils revinrent au partitionnement quotidien, augmentèrent la taille des inserts batch, et réduisirent le nombre de flux d’insertion concurrents par shard. Le TTL redevint ennuyeux. La morale : des partitions plus petites ne sont pas automatiquement moins chères — les parts et les merges sont l’unité réelle de douleur.

Pratique ennuyeuse mais correcte qui a sauvé la mise : « séparer le stockage des logs, imposer des budgets et tester la rétention »

Une plateforme financière avait des exigences d’audit strictes, mais aussi d’énormes logs opérationnels. Ils refusèrent de les mélanger. Les événements d’audit vécurent dans Postgres avec des contraintes strictes et un schéma soigné. Les logs opérationnels allèrent dans ClickHouse, alimentés via une queue et un batcher.

La pratique ennuyeuse fut un « drill de rétention » trimestriel. Pas un exercice sur table. Un vrai : ils vérifièrent que droper des partitions Postgres fonctionnait, que le TTL ClickHouse supprimait les plages correctes, et que les backups/restores ne ressuscitaient pas accidentellement des données expirées. Ils imposèrent aussi un budget de requête : les dashboards avaient des limites de plage temporelle, et toute requête plus large nécessitait une table pré-agrégée.

Un jour, un déploiement bruyant fit monter le volume des logs. La queue absorba la rafale. L’ingestion ClickHouse prit du retard mais resta stable. Les opérateurs avaient un SLO clair : « logs recherchables sous X minutes ». Ils le respectèrent en scaleant temporairement les workers d’ingestion — sans toucher à la base OLTP.

Le résultat n’était pas dramatique, ce qui est le point. La bonne pratique fut la séparation des responsabilités plus des opérations de cycle de vie répétées. Quand le firehose devint plus bruyant, l’architecture ne paniqua pas.

Erreurs courantes : symptômes → cause racine → correction

1) Le disque Postgres continue de croître même après la suppression d’anciens logs

Symptômes : L’utilisation disque augmente ; des suppressions s’exécutent la nuit ; vacuum tourne en permanence ; les requêtes ralentissent avec le temps.

Cause racine : MVCC et tuples morts + suppressions de rétention. L’espace n’est pas récupéré rapidement ; vacuum n’arrive pas à suivre ; la bloat s’accumule.

Correction : Partitionnez par temps et droppez les partitions. Si vous devez supprimer, faites-le en petites opérations et vacuumez agressivement, mais considérez cela comme une mesure temporaire.

2) Pic de latence des requêtes ClickHouse pendant des rafales d’ingestion

Symptômes : Dashboards en timeout ; disques avec forte util ; nombre de parts qui grimpe vite ; backlog de merges qui augmente.

Cause racine : Trop de petits inserts créant trop de parts ; les merges consomment I/O et CPU ; les requêtes de lecture entrent en concurrence.

Correction : Batch inserts (blocs plus grands), réduisez le nombre de writers concurrents par table par shard, gardez des partitions grossières (quotidiennes), et monitorisez parts/merges comme métriques de première classe.

3) Lag de réplication Postgres augmente quand les logs montent

Symptômes : Le replica streaming prend du retard ; le failover devient dangereux ; le disque WAL grossit ; les backups ralentissent.

Cause racine : Le volume WAL dominé par les logs ; la maintenance d’index amplifie les écritures ; l’I/O replica n’arrive pas à suivre.

Correction : Déplacez les logs hors du cluster OLTP, minimisez les index, batchz les inserts, ou utilisez ClickHouse pour les logs de masse et gardez seulement les événements d’audit critiques dans Postgres.

4) ClickHouse affiche des avertissements « too many parts » et ne merge pas assez vite

Symptômes : Parts actives en hausse ; overhead metadata ; requêtes lentes ; merges en arrière-plan constamment occupés.

Cause racine : Partitionnement trop fin (horaire/minute), ou batches d’inserts minuscules, ou trop de shards avec charge inégale.

Correction : Partitions plus grossières (jour/mois), batches d’inserts plus grands, et réévaluer la clé de sharding pour éviter le skew.

5) « On ne trouve pas rapidement des logs de requête spécifiques dans ClickHouse »

Symptômes : Les lookups par trace_id/request_id scannent de grandes plages ; latence imprévisible.

Cause racine : ORDER BY optimisé pour l’analytique service/temps, pas pour les lookups par id ; aucun skip index adapté pour ce prédicat.

Correction : Ajoutez une structure de lookup dédiée : une petite table cléée par trace_id avec pointeurs (ts/service), ou un data skipping index ; ou gardez les N dernières heures dans Postgres/Redis pour les recherches rapides.

6) Le planner Postgres cesse de bien élaguer les partitions

Symptômes : Les requêtes touchent beaucoup de partitions même avec des filtres temporels ; le temps de planification augmente ; le CPU du coordinateur grimpe.

Cause racine : Prédicats non sargables (fonctions sur ts), types/timezones mismatches, ou trop de partitions.

Correction : Gardez des prédicats simples (ts >= constant), standardisez le type timestamp, réduisez le nombre de partitions, et maintenez les statistiques à jour.

Checklists / plan étape par étape

Étapes : choisir Postgres vs ClickHouse pour les logs

1. Classifiez vos cas d’usage de logs : recherche opérationnelle vs analytique vs trail d’audit. Si c’est un trail d’audit, Postgres reste en jeu.
2. Écrivez vos 10 requêtes principales avec filtres réels et plages temporelles. Si la plupart sont des group-bys sur des jours, ClickHouse est favorisé.
3. Quantifiez l’ingestion : débit steady, débit en pic, taille moyenne des messages, pics de writers concurrents.
4. Fixez une SLO de rétention et de recherche : « searchable within 5 minutes », « garder 30 jours hot, 180 days cold », etc.
5. Décidez du mécanisme de cycle de vie : drop de partitions Postgres ou TTL ClickHouse. Si vous ne pouvez pas operationaliser la rétention, vous finirez par tout conserver par accident.
6. Choisissez un schéma minimal : colonnes typées pour les prédicats courants ; payload en JSON ou string.
7. Construisez un test de charge en staging qui inclut rétention et requêtes dashboard. L’ingestion est facile ; ingestion + rétention + requêtes est là où les systèmes meurent.

Checklist opérationnelle : stockage logs sur Postgres (si vous y tenez)

• Partitionnez par jour ; automatisez la création et la suppression.
• Privilégiez BRIN sur timestamp pour de grosses partitions append-only ; limitez les index B-tree.
• Surveillez les tuples morts et le retard autovacuum par partition.
• Gardez les logs hors du primaire critique OLTP si possible.
• Établissez une limite stricte sur la plage temporelle des requêtes ad-hoc ; poussez l’analytique lourde ailleurs.

Checklist opérationnelle : stockage logs sur ClickHouse (par défaut recommandé pour firehose)

• Inserts par lots ; imposez une taille minimale et une fréquence maximale d’insert.
• Utilisez des partitions quotidiennes sauf raison forte contraire.
• Définissez ORDER BY aligné avec vos filtres les plus courants ; ne le traitez pas comme un index unique.
• Surveillez : parts actives, bytes en merges, query read_bytes, util disque, erreurs d’insert.
• Définissez un TTL et validez qu’il supprime bien ce que vous attendez.
• Définissez des limites : durée max des requêtes sur dashboards ; utilisez des rollups/vues matérialisées pour les grandes fenêtres.

Plan de migration : Postgres logs → ClickHouse sans drame

1. Dual-write ou replay : commencez à envoyer les logs vers ClickHouse tout en gardant Postgres intact.
2. Backfill des partitions récentes d’abord (les 7–30 derniers jours). Ne backfillez pas tout l’historique avant d’avoir validé le nouveau système.
3. Validez les requêtes : comparez les comptes et agrégations clés. Attendez de petites différences si vous aviez des doublons ou des événements tardifs ; décidez comment les gérer.
4. Déplacez les dashboards vers ClickHouse, gardez Postgres pour des point-lookups si nécessaire.
5. Réduisez la rétention dans Postgres vers une fenêtre plus courte ; droppez les anciennes partitions.
6. Gardez une stratégie de rollback : si l’ingestion ClickHouse échoue, les logs restent dans une queue et peuvent être rejoués.

FAQ

1) Postgres peut-il gérer « beaucoup » de logs ?

Oui, si « beaucoup » correspond au modèle opérationnel : partitions temporelles, rétention par drop, index minimum, et si vous ne l’utilisez pas comme entrepôt analytique. Si vous avez besoin de group-bys multi-semaines sur des milliards de lignes, vous demandez le comportement de ClickHouse à un système optimisé pour d’autres garanties.

2) ClickHouse peut-il remplacer Elasticsearch pour les logs ?

Souvent, pour l’analytique et le filtrage structuré, oui. Pour la recherche plein-texte avec ranking de pertinence et fuzzy matching, ClickHouse peut faire une partie mais ce n’est pas sa personnalité centrale. Beaucoup d’équipes utilisent ClickHouse pour l’analytique type métriques et conservent une couche de recherche séparée pour le « grep à grande échelle ».

3) Et les logs JSON — JSONB Postgres vs Map/String ClickHouse ?

JSONB dans Postgres est excellent pour l’indexation flexible et les requêtes de contenance, mais c’est coûteux à l’échelle si vous indexez trop. ClickHouse fonctionne généralement mieux quand les champs fréquemment interrogés sont des colonnes typées et le reste est une chaîne JSON brute ou une structure Map selon l’outillage d’ingestion. Le schéma gagnant dans les deux : promouvoir les clés chaudes en colonnes.

4) Comment imposer la rétention en toute sécurité ?

Dans Postgres : droppez les partitions, ne supprimez pas les lignes. Dans ClickHouse : le TTL est généralement le levier approprié, mais validez-le et surveillez la pression de merge. Et répétez la rétention comme une fonctionnalité de production — parce que ça en est une.

5) Et si j’ai besoin d’un ingestion « exactly once » ?

Aucun des deux systèmes ne vous donne un exactly once de bout en bout sans aide. Postgres peut imposer des contraintes d’unicité, mais c’est coûteux sur des logs à haut volume. ClickHouse est typiquement ami du « at least once » ; implémentez l’idempotence en amont ou des stratégies de déduplication (stocker un event id et dédupliquer dans les requêtes ou via des engines/paramètres spécialisés).

6) Dois-je shardder ClickHouse tôt ?

Shardez lorsque un nœud unique ne peut plus répondre à vos besoins d’ingestion ou de requêtes avec une marge. Le sharding ajoute de la complexité opérationnelle : rebalancing, requêtes distribuées et modes de défaillance. Commencez par un nœud unique ou une petite configuration répliquée si possible, mais n’attendez pas d’être déjà à court de disque.

7) TimescaleDB est-il le compromis ?

Ça peut l’être, surtout si vous êtes attaché à l’écosystème Postgres et avez besoin d’optimisations time-series. Mais c’est toujours Postgres en dessous : la rétention et l’analytique lourde restent coûteuses. Si votre cœur est l’analytique de logs à grande échelle, ClickHouse reste l’ajustement le plus direct.

8) Quel est le plus grand « piège » que les gens ratent avec ClickHouse ?

Les parts et les merges. Si vous insérez des petits lots, vous créez des petites parts. Les petites parts créent une dette de merge. La dette de merge mène à la saturation disque et à la latence des requêtes. Concevez et exploitez autour de cela dès le premier jour.

9) Quel est le plus grand « piège » que les gens ratent avec Postgres ?

Les suppressions ne sont pas gratuites. MVCC signifie que le churn crée des tuples morts, et le vacuum est un vrai travail qui concurrence votre charge. Si la rétention se fait par deletes sur des tables massives, vous avez déjà choisi un incident futur.

Prochaines étapes que vous pouvez faire cette semaine

• Notez vos 10 requêtes de logs principales et classez-les : lookup point, recherche courte fenêtre, ou analytique longue fenêtre. Cette liste décide de la base plus que les opinions.
• Si vous êtes sur Postgres : implémentez le partitionnement temporel et passez la rétention aux drops de partitions. Puis mesurez à nouveau le pourcentage de tuples morts.
• Si vous êtes sur ClickHouse : vérifiez aujourd’hui les parts actives et le backlog des merges. Si les parts explosent, corrigez le batching avant d’ajouter du hardware.
• Définissez des limites opérationnelles : les dashboards ne devraient pas par défaut aller sur 30 jours si votre cluster est dimensionné pour 24 heures. Rendre les requêtes « chères » explicites.
• Séparez les logs d’audit des logs opérationnels : gardez les premiers corrects et relationnels ; faites des seconds rapides et peu coûteux à agréger.

La bonne réponse est rarement « Postgres ou ClickHouse ». C’est généralement « Postgres pour ce qui doit être correct et relationnel, ClickHouse pour le firehose ». Si vous essayez de faire d’un système les deux, vous finirez par n’en obtenir aucun — juste beaucoup de graphiques et un calendrier rempli de revues d’incidents.