Clickhouse Analytics

---
name: clickhouse-analytics
description: "Use when running a ClickHouse server for high-volume OLAP: choosing a MergeTree engine and ORDER BY/PARTITION BY keys, ingesting billions of event/log/metric rows, pre-aggregating with materialized views, or fixing a query that scans instead of pruning. NOT in-process file analytics (that is `duckdb`), NOT OLTP CRUD indexing (that is `postgresdb`)."
tags: [clickhouse, olap, columnar, mergetree, analytics, materialized-views, data-ingestion, sql]
recommends: [duckdb, postgresdb, dashboard, kpi-framework, reporting, business-intelligence]
origin: risco
---

# 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`.

| Engine | Use it for | Dedup / merge behavior | Gotcha |
|---|---|---|---|
| `MergeTree` | Append-only events, logs, metrics | No dedup of logical rows; inserts dedup'd by block since 26.2 | The default and 90% of tables |
| `ReplacingMergeTree(ver)` | Upserts / keep latest version per key | Collapses duplicate `ORDER BY` keys *eventually* during merges | Reads see dupes until merged; need `FINAL` to force — slow, keep off hot path |
| `AggregatingMergeTree` | Pre-aggregated rollups fed by a materialized view | Merges `-State` partials per `ORDER BY` key | Only useful behind an MV; query with `-Merge` |
| `SummingMergeTree` | Simple additive rollups (sum only) | Sums numeric columns per `ORDER BY` key on merge | Can't do uniq/quantile — use AggregatingMergeTree for those |
| `Replicated*` prefix | High availability / multi-replica | Same as base engine + ZooKeeper/Keeper replication | Production HA wrapper; combine with any of the above |

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.

```sql
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

```sql
-- 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
```

```sql
-- 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`.

```sql
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
```

```sql
-- 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`.

```sql
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

| Anti-pattern | Why it hurts | Do instead |
|---|---|---|
| `MergeTree` with no `ORDER BY` (or `ORDER BY tuple()`) on a queried table | No sparse index → every query full-scans | Pick a 3–5 col key, low→high cardinality, `WHERE`-driven |
| `PARTITION BY` a high-cardinality col / per-hour / per-day at low volume | Thousands of partitions → too many parts → merge storms | Partition by `toYYYYMM`; the sparse index does the speed |
| Single-row `INSERT ... VALUES` in a loop | Each becomes a tiny part; merges can't keep up | Batch 10k–100k+ rows, or rely on 26.3 async inserts |
| `POPULATE` on a billion-row base table's MV | Blocks the MV, can OOM | Create MV empty, backfill in time windows |
| `SELECT *` on a wide table | Reads every column, defeats columnar storage | Select only the columns you need |
| `FINAL` in a dashboard query | Forces merge at query time → slow | Keep `FINAL` off hot paths; accept eventual dedup |
| ClickHouse for OLTP point-updates / single-row reads by id | Wrong engine; no real updates, weak point lookups | Use `../postgresdb/SKILL.md` |
| MV `GROUP BY` not matching target `ORDER BY` | Inefficient merges, wrong rollups | Align them exactly |

## 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.