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