Clickhouse Analytics

ClickHouse analytics

ClickHouse is a multi-user, always-on, replicated columnar server built to ingest continuous high-volume writes and answer aggregation queries over billions of rows in milliseconds. You reach for it when the workload is "append a firehose of events/logs/metrics, then GROUP BY them for dashboards." Target 26.3 LTS (v26.3.12.3, 2026-05-22) — several defaults below changed in the 26.x line, so version matters.

The fork before you write any DDL: files on a laptop, in-process, no server, no concurrent writers → ../duckdb/SKILL.md; app CRUD, point updates, foreign keys, row locks, RLS, migrations → ../postgresdb/SKILL.md; clickhouse-server, replication, concurrent writers, 100M+ rows/s ingest → this skill.

Instrumenting capture (GA4/PostHog) is ../analytics/SKILL.md; charting the result for humans is ../dashboard/SKILL.md; deciding which metrics matter is ../kpi-framework/SKILL.md. ClickHouse is the engine underneath all three.

Pick the engine first

The engine decides dedup and merge behavior, and you cannot change ORDER BY/PARTITION BY later without a rebuild — so choose before typing CREATE TABLE.

Default to MergeTree. Move to AggregatingMergeTree only when you are pre-aggregating through a materialized view. Full matrix and reasoning: references/schema-and-engines.md.

Schema rules

1. ORDER BY is your single biggest perf lever — a good one cuts query time ~100x. It defines the sparse primary index that prunes which granules get read. Get this right above everything else.
2. Order the key low-cardinality → high-cardinality, left to right, driven by WHERE/GROUP BY — never by join keys. 3–5 columns. The leftmost column should be the one you filter on most; cardinality rises as you go right. Timeseries: put the raw timestamp last, often (tenant_id, toStartOfDay(ts), event_type, ts).
3. Treat ORDER BY and PARTITION BY as immutable. Changing either almost always means a new table + INSERT ... SELECT migration. Decide deliberately now.
4. Partition coarsely — by month, or by day only at very high volume. Partitioning is for data lifecycle (TTL, DROP PARTITION), not query speed; the sparse index does speed. Per-hour or per-toYYYYMMDD on a high-cardinality stream creates thousands of partitions → too many parts → merge storms.
5. Right-size types and use codecs. LowCardinality(String) for columns under ~10k distinct values (enum-like: country, event_type, status). Smallest int that fits. CODEC(Delta, ZSTD) for monotonic timestamps/counters; CODEC(ALP) for float columns (26.3, beats Gorilla on many workloads); native JSON type (GA in 26.3) for semi-structured payloads instead of stringly-typed blobs.

sqlCopy code
CREATE TABLE events
(
    tenant_id    UInt32,
    ts           DateTime64(3) CODEC(Delta, ZSTD),
    event_type   LowCardinality(String),
    user_id      UInt64,
    country      LowCardinality(String),
    revenue      Float64 CODEC(ALP),
    props        JSON
)
ENGINE = MergeTree
PARTITION BY toYYYYMM(ts)             -- monthly: coarse, for TTL/drops
ORDER BY (tenant_id, toStartOfDay(ts), event_type, ts)
TTL toDateTime(ts) + INTERVAL 18 MONTH;

Depth (cardinality math, codec table, type mapping, partition-count budget): references/schema-and-engines.md.

Ingestion rules

sqlCopy code
-- Bad: row-at-a-time. Each statement becomes its own tiny part.
INSERT INTO events VALUES (1, now(), 'click', 42, 'ES', 0, '{}');
INSERT INTO events VALUES (1, now(), 'view',  42, 'ES', 0, '{}');
-- ... 10k more single inserts -> 10k parts -> merges can't keep up

sqlCopy code
-- Good: one batch of many rows (aim 10k–100k+ per INSERT).
INSERT INTO events VALUES
  (1, now(), 'click', 42, 'ES', 0, '{}'),
  (1, now(), 'view',  42, 'ES', 0, '{}'),
  /* ...thousands more... */ ;

-- Or load straight from object storage, no client batching at all:
INSERT INTO events
SELECT * FROM s3('https://bucket.s3.amazonaws.com/events/2026/*.parquet', 'Parquet');

• Async inserts are enabled by default starting 26.3 LTS. The server buffers small inserts in memory and flushes on a size/time threshold, so many client-side batchers become unnecessary. Flush fires on the first threshold hit: async_insert_max_query_number (default 450) or the adaptive busy timeout, between async_insert_busy_timeout_min_ms (default 50ms) and a data-rate-driven max (adaptive since 24.2).
• Insert deduplication is on by default for all inserts as of 26.2 (previously sync-only), and works end-to-end across async inserts and dependent materialized views since 26.1. Net effect: retrying a failed insert is safe — an identical block won't double-count. Pass insert_deduplication_token when you want explicit control over what counts as identical.
• Keep inserts synchronous when you must read-your-write immediately, or when you already batch large blocks yourself and want no buffering latency.

S3/Kafka/file recipes, async-insert tuning knobs, dedup tokens: references/ingestion-and-mvs.md.

Materialized views and pre-aggregation

For anything beyond raw sum/count (uniq, quantiles, argMax), pre-aggregate incrementally with AggregatingMergeTree + a materialized view storing -State partials, queried back with -Merge.

sqlCopy code
CREATE TABLE events_hourly
(
    tenant_id  UInt32,
    hour       DateTime,
    users      AggregateFunction(uniq, UInt64),
    revenue    AggregateFunction(sum, Float64)
)
ENGINE = AggregatingMergeTree
PARTITION BY toYYYYMM(hour)
ORDER BY (tenant_id, hour);            -- MV GROUP BY MUST match this

CREATE MATERIALIZED VIEW events_hourly_mv TO events_hourly AS
SELECT tenant_id,
       toStartOfHour(ts) AS hour,
       uniqState(user_id) AS users,
       sumState(revenue)  AS revenue
FROM events
GROUP BY tenant_id, hour;              -- no POPULATE on a big base table

sqlCopy code
-- Read it back: -Merge collapses the partial states.
SELECT tenant_id, hour, uniqMerge(users) AS uniq_users, sumMerge(revenue) AS rev
FROM events_hourly
GROUP BY tenant_id, hour;

• The MV's GROUP BY must match the target table's ORDER BY so merges stay efficient.
• Never POPULATE a billion-row base table — it blocks the MV and can OOM. Create the MV empty (it captures new rows immediately), then backfill history in time-bounded INSERT ... SELECT windows. Full backfill walkthrough: references/ingestion-and-mvs.md.

Query optimization

The sparse index only prunes on ORDER BY prefix columns. When a hot query filters on a column the primary key doesn't cover, in order of reach for:

1. PREWHERE — ClickHouse auto-applies it, but an explicit PREWHERE on a cheap, highly selective column reads that column first and skips other columns for non-matching rows. Cuts I/O.
2. Projection — an alternate ORDER BY/pre-aggregation stored with the table; ClickHouse picks it transparently. Best when one secondary access pattern is common and worth the storage.
3. Data-skipping index — minmax (correlated-with-PK ranges), set (low distinct count), bloom_filter (high-cardinality equality/IN). Cheaper than a projection, coarser pruning.

Decision: PK can't prune and you query one alternate sort order a lot → projection. You just need to skip granules on a side column → skip index (bloom_filter for high-cardinality =/IN, minmax for ranges). Inspect with EXPLAIN indexes = 1 and SET send_logs_level = 'trace' to see granules read. Walkthrough + slow-query recipes: references/query-optimization.md.

sqlCopy code
SELECT event_type, count() FROM events
PREWHERE country = 'ES'                 -- cheap, selective: filter before reading the rest
WHERE ts >= now() - INTERVAL 7 DAY
GROUP BY event_type;

Operations

• Watch part count. SELECT table, count() FROM system.parts WHERE active GROUP BY table — a growing number means inserts are too small/frequent or partitioning is too fine. Fix the insert pattern, not the merge settings.
• Retention via TTL, not DELETE. TTL on the table drops expired data during merges automatically.
• ALTER TABLE ... DROP PARTITION is instant and free; row-level DELETE/ALTER DELETE is a mutation that rewrites parts — avoid it for bulk cleanup. This is the payoff of coarse partitioning.
• ReplacingMergeTree reads can see un-merged duplicates. Use FINAL only on cold/admin queries, never in dashboards — it merges at query time.

Anti-patterns

Verification

scripts/verify.sh <file.sql> is a static linter over candidate ClickHouse DDL/queries: flags MergeTree without ORDER BY, over-fine PARTITION BY, single-row INSERT ... VALUES, POPULATE on materialized views, SELECT *, and FINAL. Read-only, no live cluster needed, exits 0 on clean input.