Perché usiamo ClickHouse per le analisi dei click (e non Postgres)

Postgres è eccellente. Lo usiamo intensamente e ci fidiamo di esso. Archivia ogni link, ogni workspace, ogni record di fatturazione, ogni utente. Ma intorno al 90° giorno su un URL shortener in crescita, incontri un muro se stai anche archiviando lì gli eventi di click grezzi. Il muro non è drammatico - Postgres non crolla, rallenta semplicemente nel modo specifico e frustrante in cui le query aggregate su grandi tabelle append-only rallentano. Le scansioni degli indici smettono di aiutare. L'autovacuum non riesce a tenere il passo. Un dashboard che si caricava in 800ms ora impiega 4 secondi. Aggiungi altri indici. Peggiora.

Questo post spiega perché abbiamo spostato gli eventi di click su ClickHouse, come appare lo schema, come funziona il percorso di ingestione e dove si trovano i punti critici. È un companion al post sulla latenza dei redirect e copre il tier di dati che si trova a valle del redirect - la parte che trasforma gli eventi di click fire-and-forget grezzi nei dashboard nella pagina delle soluzioni di analisi.

TL;DR#

• Gli eventi di click sono append-only e hanno la forma delle query analitiche. Il columnar storage vince su entrambi i fronti.
• Il sovraccarico per riga di Postgres, il bloat degli indici e la contesa dell'autovacuum si compoundano oltre ~30M eventi su una tabella condivisa.
• ClickHouse con ORDER BY (workspace_id, link_id, created_at) restituisce rollup di 90 giorni per workspace in meno di 100ms senza indici aggiuntivi.
• I metadati dei link rimangono in Postgres (mutabili, transazionali); gli eventi di click rimangono in ClickHouse (append-only, analitici). La suddivisione è netta perché i due non devono sovrapporsi al momento della scrittura.

Perché Postgres fatica con questo workload#

La forma del workload è il problema. Un evento di click è:

• Scritto una volta e mai aggiornato.
• Scritto a ritmi elevati e irregolari - un rapporto scrittura-lettura di 50:1 è una stima approssimativa, e nel giorno del lancio di una campagna il rapporto aumenta ulteriormente.
• Interrogato in aggregato: click totali per link, suddivisione geo per workspace, split dispositivi sui 100 link più cliccati di una campagna. Quasi nessuno interroga direttamente una singola riga di click.

Postgres è costruito su un diverso insieme di assunzioni. Ogni riga porta un header di tupla, campi ID transazione (xmin, xmax) e metadati di visibilità. Per i workload transazionali dove si aggiornano le righe in place e si ha bisogno che MVCC gestisca letture e scritture concorrenti, quel sovraccarico è il prezzo da pagare per la correttezza. Per i dati analitici append-only che non si toccano mai più, è puro spreco.

Il problema degli indici è peggiore. Gli indici B-tree di Postgres funzionano bene per i point-lookup selettivi - trovare la riga con link_id = 'xyz'. Aiutano meno quando si esegue GROUP BY link_id, country su 10 milioni di righe per un workspace. L'indice restringe la scansione, ma la query deve comunque estrarre dati di riga decompressi dalle heap page, deserializzarli e aggregarli. A 30M righe la query è lenta. A 100M righe è dolorosa.

L'autovacuum peggiora le cose in pratica. Gli insert ad alto throughput generano tuple morte (versioni di righe MVCC che non sono più visibili alle transazioni attive ma non sono ancora state ripulite). L'autovacuum le recupera, ma compete con le query live per l'I/O. Vedrai picchi periodici di latenza nei tuoi dashboard di Postgres che corrispondono all'autovacuum in esecuzione sulla tabella degli eventi. La documentazione sulla compressione e sull'architettura dei chunk di TimescaleDB vale la pena di leggere per i dettagli su come un'estensione di Postgres cerca di aggirare questo problema - il problema è abbastanza reale da far nascere un layer dedicato di serie temporali sopra Postgres per affrontarlo.

Abbiamo eseguito i click su Postgres per i primi 90 giorni del progetto. Un workspace con ~4M eventi di click aveva un dashboard che impiegava 3,2 secondi per renderizzare un rollup di 7 giorni per paese. Aggiungendo un indice parziale su (workspace_id, created_at) si è scesi a 1,4 secondi. Sufficiente all'epoca; chiaramente non un percorso che scala a 100M+ eventi al mese.

Perché ClickHouse si adatta#

ClickHouse è un motore di database columnar costruito esplicitamente per workload analitici - consultato il 2026-05-12. Columnar significa che i dati di ogni colonna sono archiviati in modo contiguo sul disco piuttosto che interlacciati riga per riga. Per una query come "somma il conteggio dei click per paese per il workspace X negli ultimi 30 giorni", il motore legge solo le colonne country, workspace_id e created_at. Non tocca mai user_agent_hash o referrer_host. La riduzione dell'I/O su tabelle wide con molte colonne è sostanziale.

Il rapporto di compressione è la seconda proprietà. I dati columnar si comprimono significativamente meglio dei dati a righe perché i valori ripetuti nella stessa colonna - lo stesso workspace_id che appare migliaia di volte, lo stesso insieme di circa 60 codici paese, una manciata di valori os - si comprimono quasi a nulla sotto i codec LZ4 e ZSTD di ClickHouse. I nostri eventi di click si comprimono a circa il 12-15% della loro dimensione grezza su disco. Gli stessi dati in Postgres si trovano al 35-45% della dimensione grezza con la compressione toast predefinita. Non è solo un vantaggio di archiviazione - dati più piccoli su disco significano che più di essi entrano nella cache delle pagine OS, il che significa meno letture disco per query.

La terza proprietà è la famiglia di motori MergeTree - consultata il 2026-05-12. Le tabelle MergeTree ordinano e raggruppano i dati per la chiave ORDER BY della tabella. Le query che filtrano o raggruppano sulle colonne iniziali di quella chiave leggono solo le data part che si sovrappongono con il range del filtro. Questo è l'equivalente di un indice clustered in ClickHouse, tranne per il fatto che è obbligatorio (lo si definisce al momento della creazione della tabella) e si applica a ogni query, non solo alle colonne indicizzate.

Usiamo ORDER BY (workspace_id, link_id, created_at). Una query per i click del workspace abc123 negli ultimi 7 giorni legge solo le data part dove appare workspace_id = 'abc123', poi all'interno di quelle part legge solo la slice dell'intervallo temporale. Su 90 giorni di dati di click, questo tocca tipicamente il 3-8% del totale delle data part. L'indice primario sparso che ClickHouse mantiene sulle colonne ORDER BY rende questo targeting efficiente senza richiedere un B-tree denso per ogni pattern di query. ReplicatedMergeTree aggiunge HA - due nodi ClickHouse con coordinamento equivalente a ZooKeeper (ClickHouse Keeper), che replica le part man mano che vengono scritte.

Lo schema che rilasciamo#

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

Vale la pena evidenziare alcune decisioni di schema.

LowCardinality(String) su workspace_id, country, device, os, browser, utm_source, utm_medium - queste colonne hanno cardinalità limitata (centinaia di ID workspace distinti, circa 250 codici paese, una manciata di tipi di dispositivo). LowCardinality attiva la codifica del dizionario, che archivia i valori come riferimenti interi in un dizionario di stringhe univoche. La velocità delle query su queste colonne migliora sensibilmente; la compressione migliora ulteriormente. L'abbiamo imparato dalla documentazione di ClickHouse sul tipo di dati low-cardinality - la regola empirica è che qualsiasi colonna stringa con meno di 10.000 valori distinti è un candidato.

PARTITION BY toYYYYMM(created_at) crea una partizione dati separata per mese di calendario. Le vecchie partizioni possono essere eliminate (o spostate su storage più lento) con una singola istruzione ALTER TABLE clicks DROP PARTITION '202501', che è istantanea - nessuna full-table scan. Per l'applicazione della retention dei dati GDPR, questo è praticamente importante: eliminare i click storici di un workspace è una singola operazione per partizione mensile piuttosto che un DELETE WHERE workspace_id = ? distribuito.

ip_truncated archivia l'indirizzo IP con l'ultimo ottetto azzerato (203.0.113.0 non 203.0.113.42). L'IP completo non viene mai archiviato. La forma troncata è sufficiente per la geo-lookup - che effettuiamo al momento dell'ingestione, prima dell'archiviazione - e non costituisce dato personale ai sensi della definizione dell'Articolo 4 del GDPR combinata con le nostre pratiche di minimizzazione dei dati. Vedi solutions/analytics per l'architettura completa della privacy.

Il percorso di ingestione#

Il redirect handler a ogni edge POP è fire-and-forget. Aggiunge un evento di click a un topic Redpanda (clicks.v1) e invia il 302. Non aspetta l'acknowledgement da ClickHouse. Se Redpanda non è disponibile in quel momento, il click viene perso - non il redirect. La gerarchia è deliberata: un click perso è recuperabile (possiamo ricostruire conteggi approssimativi dai log di accesso); un redirect fallito è visibile all'utente.

I consumer group di Redpanda - consultati il 2026-05-12 - forniscono la garanzia di consegna tra l'edge e l'ingester. Il servizio click-ingester viene eseguito come consumer group contro il topic clicks.v1. Le partizioni vengono assegnate automaticamente; se un'istanza dell'ingester fallisce, le sue partizioni vengono ribilanciate alle istanze sopravvissute entro il timeout heartbeat del gruppo.

L'ingester accumula eventi in memoria e li scarica in batch: 1.000 righe o 250ms, a seconda di quale evento avviene per primo. Questo è importante. Il motore MergeTree di ClickHouse è ottimizzato per gli insert massivi - scrive ogni insert come una nuova data part, e molti insert piccoli generano molte part piccole, che attivano un merge aggressivo in background e rallentano le query durante il carico di merge. La documentazione di ClickHouse raccomanda insert di almeno 1.000 righe per mantenere gestibile il numero di part. Scarichiamo a 1.000 righe / 250ms e non abbiamo mai riscontrato problemi di conteggio delle part in condizioni operative normali.

La backpressure si propaga attraverso Redpanda. Se ClickHouse è lento o si sta riavviando, il lag del consumer dell'ingester aumenta, che monitoriamo come indicatore guida dei problemi di freschezza dei dati. La configurazione della retention di Redpanda (14 giorni) ci dà una finestra di replay significativa se ClickHouse deve essere ricostruito da zero.

L'architettura è descritta in maggiore dettaglio su /docs/architecture/click-ingester.

Postgres vs ClickHouse: la suddivisione#

Due database per un servizio è un costo che richiede giustificazione. La giustificazione qui è che i due workload sono genuinamente incompatibili su scala, e i dati non si sovrappongono in un modo che richieda coordinamento sincrono al momento della scrittura.

Postgres possiede i metadati dei link: le tabelle links, workspaces, users, billing, rules. Questi sono mutabili (i link vengono archiviati, gli URL di destinazione cambiano, i record di fatturazione vengono aggiornati), transazionali (creare un link e coniare il suo slug deve essere atomico) e read-heavy rispetto alle scritture. Gli indici B-tree e MVCC sono esattamente ciò di cui si ha bisogno.

ClickHouse possiede gli eventi di click: la tabella clicks. Questi sono append-only (un click, una volta scritto, non viene mai aggiornato), write-heavy e interrogati in pattern aggregati che il columnar storage gestisce meglio. Non ci sono join cross-database al momento della scrittura - l'ingester inserisce gli eventi di click con workspace_id e link_id come campi stringa denormalizzati. Al momento della query, l'API di analisi può cercare i metadati dei link da Postgres e i conteggi dei click da ClickHouse separatamente, poi unirli nel codice applicativo. Questo è un round-trip di rete in più; è un compromesso molto migliore rispetto a mettere il carico di query analitiche su Postgres.

La documentazione di Citus distributed Postgres descrive l'alternativa: estensioni di Postgres distribuite progettate per gestire workload analitici di serie temporali. Abbiamo valutato questo percorso e concluso che le performance delle query di ClickHouse e il rapporto di compressione sono abbastanza avanti per i workload puramente analitici che il sovraccarico operativo di due database vale la pena. Se il tuo workload è misto - qualcosa di analitico, molte mutazioni a livello di riga - l'approccio Citus ha più senso.

Pattern di query che eseguiamo ogni ora#

Il dashboard si aggiorna per ogni workspace approssimativamente su un ciclo di 60 secondi. Ci sono tre query pesanti:

Rollup dei click per link, ultime 24 ore. La parte superiore di ogni lista di link mostra uno sparkline. Questa è una GROUP BY link_id, toHour(created_at) sulle ultime 24 ore per il workspace.

Suddivisione per paese per una campagna. Un team di marketing che controlla le performance della campagna vuole GROUP BY country, COUNT(*) filtrato da utm_campaign su un intervallo di date.

Split dispositivi sui 100 link più visitati di un workspace. La scheda di analisi mostra una suddivisione per dispositivo/OS. Questa è GROUP BY device, os, COUNT(*) per i link ad alto traffico del workspace negli ultimi 30 giorni.

Tutti e tre i pattern sono serviti bene dal clustering ORDER BY (workspace_id, link_id, created_at). Il filtro sul workspace restringe immediatamente la scansione alla fetta giusta dei dati; le colonne link_id e created_at la restringono ulteriormente per i primi due pattern. La query dello split dispositivi scansiona più colonne ma beneficia comunque del clustering a livello di workspace.

Una query rappresentativa per i 100 link principali per conteggio di click nelle ultime 24 ore:

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

Su un workspace con 12 milioni di eventi di click che coprono 90 giorni, questa query restituisce in circa 45-80ms a seconda del numero di data part nella partizione rilevante. La stessa query su una tabella Postgres di dimensioni equivalenti gira in 2-4 secondi con la migliore configurazione di indici che abbiamo provato.

Non usiamo le viste materializzate di ClickHouse per questi pattern. Il clustering ORDER BY e il partition pruning sono sufficienti, e le viste materializzate aggiungono complessità nella migrazione degli schema. Se la latenza delle query dovesse mai diventare un problema con volumi di eventi più elevati, le viste materializzate sono la prossima leva - non ulteriori indici.

Failure mode che abbiamo incontrato#

Vale la pena documentare tre incidenti di produzione.

Blip di ClickHouse Keeper. ClickHouse Keeper è il layer di coordinamento equivalente a ZooKeeper per ReplicatedMergeTree. In un'occasione, il riavvio di un nodo Keeper durante un merge in background ha causato la pausa degli insert nella tabella replicata per circa 90 secondi mentre il nuovo leader veniva eletto. L'ingester ha riprovato con backoff esponenziale (2s, 4s, 8s, cap 60s) - questa è la logica di retry Go standard con gestione di context.DeadlineExceeded. Durante la finestra di interruzione, circa 1.800 eventi di click si sono accumulati nel buffer in-memory dell'ingester e sono stati scaricati con successo una volta che Keeper si è ripreso. L'unico effetto visibile all'utente è stato un gap di ~2 minuti nei dati del dashboard live. La correzione è stata assicurare che i nodi Keeper siano distribuiti su host fisici separati con circuiti elettrici separati sulla rete Hetzner.

Squilibrio delle partizioni su Redpanda. Il topic clicks.v1 usa 12 partizioni. Dopo aver aggiunto una seconda istanza dell'ingester, l'assegnazione delle partizioni da parte del bilanciatore predefinito di Redpanda ha assegnato 9 partizioni a un'istanza e 3 all'altra - ha usato la strategia del totale-partizioni-per-consumer e il numero di istanze ha prodotto uno squilibrio 3:1. Non era un rischio di perdita di dati, ma l'istanza sovraccarica faceva il batching lentamente sotto il carico, aumentando la latenza di elaborazione dei click a circa 800ms. La correzione è stata un riequilibrio manuale delle partizioni usando l'API admin di Redpanda e il passaggio al bilanciatore rack-aware per usare l'assegnazione round-robin.

ALTER TABLE su una tabella grande è asincrono. Abbiamo aggiunto una colonna browser_version a gennaio. L'ALTER TABLE ... ADD COLUMN di ClickHouse dà conferma immediatamente e applica la modifica dello schema in modo asincrono durante i merge delle part in background. Per circa 20 minuti dopo l'alter, le query che facevano riferimento alla nuova colonna restituivano NULL per le righe nelle part non mutate. Nulla si è rotto - stavamo aggiungendo una colonna, non modificandone una esistente - ma una query che assumesse browser_version IS NOT NULL avrebbe restituito risultati errati. La lezione: trattare ALTER TABLE su grandi tabelle ClickHouse come eventualmente consistente, non sincrono.

Cosa non facciamo in ClickHouse#

Due cose che emergono nelle conversazioni e che abbiamo deliberatamente evitato.

Join per evento alle tabelle di Postgres. Alcune query analitiche vogliono naturalmente unire gli eventi di click ai metadati dei link - "mostrami tutti i click per link taggati 'campaign-q2', con i loro URL di destinazione". Fare questo come query federata (ClickHouse clicks unito a Postgres links tramite una funzione tabella JDBC di ClickHouse) è possibile ma lento. Invece, o denormalizziamo i campi rilevanti nell'evento di click al momento dell'ingestione (come utm_campaign, che è derivato dal template UTM del link) o risolviamo il join nel codice applicativo: due query separate, unite nel layer dell'API di analisi. Questo mantiene entrambi i database operativi all'interno dei loro range di performance.

Aggiornamenti per riga. ClickHouse supporta ALTER TABLE UPDATE per le mutazioni a livello di riga, ma è un'operazione asincrona e resource-intensive che va contro le assunzioni del motore. Se ti ritrovi ad aver bisogno di aggiornare un evento di click dopo il fatto - diciamo, per contrassegnarlo retroattivamente come click bot - hai costruito l'architettura nel modo sbagliato. Il pattern corretto è archiviare una tabella separata click_quality in ClickHouse che mappa click_id a un quality score, calcolato dal servizio url-scanner in modo asincrono, e fare il JOIN al momento della query. Le letture sono economiche; gli aggiornamenti retroattivi sono costosi e dovrebbero essere rari.

---

Per il quadro infrastrutturale più ampio - come il redirect edge interagisce con Redpanda, come funziona la deduplicazione dei click e come il servizio click-ingester si inserisce nella topologia dei servizi - vedi /docs/architecture/click-ingester. La storia lato edge (pubblicazione fire-and-forget su Redpanda, budget di latenza) è nel post sul p95 dei redirect. Per ciò che i dati abilitano a valle - suddivisioni geo, split dispositivi, dashboard di attribuzione UTM - la pagina solutions/analytics copre la superficie del prodotto. Se stai costruendo campagne di link tramite l'API e ti interessa quali pattern di query espone l'SDK, solutions/developers è il punto di ingresso rilevante.

Per i team che valutano Elido come infrastruttura di link, la scelta architetturale qui - ClickHouse per i click, Postgres per tutto il resto - è anche ciò che ci consente di offrire analisi senza campionamento. Il post su smart links explained copre come le decisioni di routing che generano questi eventi di click funzionano all'edge.

---

Marius Voß è DevRel e infra edge di Elido. Ha contribuito a progettare la pipeline click-ingester e ha trascorso più tempo di quanto vorrebbe a debuggare i timing di elezione del ClickHouse Keeper.