ClickHouse: OLAP Colonnare per Analytics Real-Time su 10 Miliardi di Righe

Storage Colonnare e Prestazioni delle Query Analitiche

In un database orientato alle righe come PostgreSQL, i dati sono memorizzati riga per riga. Quando esegui SELECT country, COUNT(*) FROM events WHERE event = 'click' GROUP BY country, PostgreSQL legge ogni riga dal disco, incluse colonne che non servono. Su una tabella con 50 colonne, stai leggendo 50x più dati del necessario.

ClickHouse memorizza i dati colonna per colonna. Quella stessa query legge solo le colonne event e country. Su una tabella con 10 miliardi di righe e 50 colonne, è ~98% meno I/O. Ma migliora — ogni colonna è compressa indipendentemente. La colonna event potrebbe avere solo 20 valori distinti su 10 miliardi di righe, che si comprime da 80GB (stringhe grezze) a ~500MB con LZ4 + dictionary encoding.

MergeTree: Il Motore che Fa Funzionare Tutto

Ogni tabella ClickHouse è supportata da un motore tabella. MergeTree è il motore principale per workload analitici:

CREATE TABLE events (
 tenant_id UInt32,
 event_date Date,
 event_time DateTime64(3),
 event_type LowCardinality(String),
 user_id UInt64,
 session_id String,
 page_url String,
 country LowCardinality(FixedString(2)),
 device_type LowCardinality(String),
 duration_ms UInt32,
 properties Map(String, String)
)
ENGINE = MergeTree()
PARTITION BY toYYYYMM(event_date)
ORDER BY (tenant_id, event_date, event_type, user_id)
TTL event_date + INTERVAL 13 MONTH
SETTINGS index_granularity = 8192;

Decisioni chiave di design:

PARTITION BY: Partizioni mensili per data evento. Le partizioni sono l'unità di gestione dati — puoi eliminare, spostare o staccare mesi interi efficientemente.

ORDER BY: Questa è la chiave primaria e la decisione più importante. ClickHouse memorizza i dati ordinati per questa chiave e costruisce un indice sparse (un'entry ogni index_granularity righe) per il data skipping. Le query che filtrano sulle colonne iniziali della chiave ORDER BY saltano interi blocchi dati senza leggerli.

tenant_id va per primo perché ogni query in un sistema multi-tenant filtra per tenant. Poi event_date per query su range temporali, event_type per filtraggio per categoria, e user_id per drilldown a livello utente.

Viste Materializzate: Pre-Aggregazione al Momento dell'Ingest

Le viste materializzate in ClickHouse non sono quello che potresti aspettarti da PostgreSQL — si aggiornano incrementalmente al momento dell'insert, non vengono refreshate periodicamente:

CREATE MATERIALIZED VIEW events_daily_mv
ENGINE = SummingMergeTree()
PARTITION BY toYYYYMM(event_date)
ORDER BY (tenant_id, event_date, event_type, country)
AS SELECT
 tenant_id,
 event_date,
 event_type,
 country,
 count() AS event_count,
 uniqState(user_id) AS unique_users,
 sumState(duration_ms) AS total_duration_ms
FROM events
GROUP BY tenant_id, event_date, event_type, country;

Quando fai INSERT INTO events, ClickHouse trasforma automaticamente le nuove righe attraverso il SELECT della vista materializzata e inserisce i risultati aggregati nella tabella events_daily_mv.

SELECT
 event_date,
 event_type,
 sum(event_count) AS events,
 uniqMerge(unique_users) AS users,
 sumMerge(total_duration_ms) / sum(event_count) AS avg_duration_ms
FROM events_daily_mv
WHERE tenant_id = 42
 AND event_date BETWEEN '2026-01-01' AND '2026-01-31'
GROUP BY event_date, event_type
ORDER BY event_date;

Questa query scansiona la tabella pre-aggregata (~10.000 righe per un mese) invece della tabella eventi grezzi (~500 milioni di righe per lo stesso tenant e periodo). Tempo di risposta: 15ms vs 1.800ms.

Un setup tipico include quattro viste materializzate sulla tabella eventi. Overhead totale di storage per tutte e quattro le tabelle MV: ~3% della tabella eventi grezzi. Il miglioramento di performance delle query è 50-200x per le query dashboard.

Ottimizzazione Query su 10B Righe

Le tecniche di ottimizzazione reali che contano a scala:

1. Filtrare sulle colonne ORDER BY

-- Veloce: tenant_id e event_date sono le prime due colonne ORDER BY
SELECT event_type, count()
FROM events
WHERE tenant_id = 42 AND event_date = '2026-03-15'
GROUP BY event_type;
-- Scansiona: ~2M righe su 10B (0.02%)

-- Lento: country non è nella chiave ORDER BY
SELECT country, count()
FROM events
WHERE country = 'US'
GROUP BY country;
-- Scansiona: tutte le 10B righe

2. Funzioni approssimate per analytics su larga scala

-- Conteggio unici esatto: 4.2 secondi su 10B righe
SELECT uniqExact(user_id) FROM events WHERE tenant_id = 42;

-- Conteggio unici approssimato (2% errore): 0.3 secondi
SELECT uniq(user_id) FROM events WHERE tenant_id = 42;

Per dashboard dove +-2% di precisione va bene, uniq() (HyperLogLog) è 14x più veloce di uniqExact(). Gli stakeholder business non hanno mai notato la differenza.

ClickHouse vs DuckDB vs Druid: Analisi Comparativa

Valutando workload di produzione su tutti e tre, ognuno eccelle in aree diverse:

| Criterio | ClickHouse | DuckDB | Druid | |----------|-----------|--------|-------| | Scala dati | 100GB - PB | 1GB - 100GB | 100GB - PB | | Utenti concorrenti | Dozzine a centinaia | Singolo utente | Centinaia a migliaia | | Ingest | Batch (alto throughput) | Batch/file | Streaming real-time | | Latenza query (10B righe) | 200ms - 5s | N/A (solo locale) | 100ms - 2s | | Complessità operativa | Media | Nessuna (embedded) | Alta | | Supporto SQL | SQL analitico completo | SQL completo + estensioni | SQL limitato | | Migliore per | API backend analytics | Data science, analisi locale | Dashboard real-time |

DuckDB è notevole per quello che fa — query analitiche su file locali con zero infrastruttura. È eccellente per prototipare query prima di deployare su ClickHouse e per analisi ad-hoc su export CSV/Parquet. Ma non è un server. Non puoi puntare 50 utenti dashboard a un'istanza DuckDB.

Druid è costruito appositamente per analytics real-time con query sub-secondo su dimensioni ad altissima cardinalità. Gestisce query concorrenti meglio di ClickHouse a scala estrema. Ma la complessità operativa di Druid è significativamente più alta — ha sei tipi di nodo, ognuno che richiede capacity planning separato. ClickHouse ha un tipo di nodo.

L'Architettura di Produzione

Kafka ──► Buffer Table ──► Events Table ──► Viste Materializzate
 │
 ┌───────┼───────┐
 │ │ │
 Query Query Query
 Dashboard API Ad-hoc

Cluster ClickHouse: 3 nodi, ognuno con 32 core, 128GB RAM, 4TB NVMe SSD. ReplicatedMergeTree con 2 repliche per fault tolerance.

CREATE TABLE events_buffer AS events
ENGINE = Buffer(currentDatabase(), events, 16, 10, 100, 10000, 100000, 1000000, 10000000);

La Buffer table accumula insert in memoria e flusha alla tabella events sottostante in batch. Questo converte gli insert per-messaggio del consumer Kafka in batch insert efficienti da 10.000-100.000 righe.

Tasso di ingest: ~120.000 eventi/secondo sostenuti. Carico query: ~200 query/secondo tra tutti gli utenti dashboard. Latenza P99 per query dashboard (che colpiscono viste materializzate): 180ms. P99 per query ad-hoc su eventi grezzi (mese intero, singolo tenant): 3,2 secondi.

Il tutto gira su tre server che costano circa $2.400/mese. Il setup equivalente PostgreSQL (che non riusciva nemmeno a servire le query in tempo) girava su un r6g.8xlarge ($2.800/mese) e continuava ad andare in timeout.

ClickHouse non è magia. Sono trade-off ingegneristici resi espliciti: rinunci a transazioni, update a livello riga, e garanzie ACID. In cambio, ottieni performance di query analitiche che sono genuinamente 100-1000x più veloci dei database general-purpose. Per workload analytics, quel trade-off è ovvio una volta che hai visto i numeri.