ClickHouse for analytics

01The idea: columns, not rows

Imagine a spreadsheet of a billion page views. To answer “views per country last week”, a row store must read every row with all its twenty columns. A column store keeps each column in its own file: it reads only date and country, which is a tiny fraction of the data. Values in one column are similar (many repeated countries), so they compress extremely well, and the CPU can process them in batches with vectorized instructions. The trade-off: fetching or updating a single complete row is slower, and ClickHouse is not designed for many small transactional updates. OLTP (orders, users) stays in your relational database; OLAP (questions over history) goes here.

• OLTP: many small reads and writes of single records, with transactions.
• OLAP: few large queries that scan and aggregate many records.
• Vectorized execution: processing a block of values per CPU operation instead of one at a time.

02MergeTree tables and the sorting key

Almost every table uses an engine from the MergeTree family. Each insert writes an immutable part sorted by the ORDER BY key, and background merges combine small parts into bigger ones. The primary index is sparse: it stores one entry per granule (8,192 rows by default), so it is small enough to stay in memory. When a query filters on the leading columns of ORDER BY, ClickHouse skips granules that cannot match. Choosing ORDER BY is therefore the most important design decision: put low-cardinality columns you always filter by first (tenant, event type), then time. PARTITION BY is for data management (dropping old months), not for speed; keep partitions coarse.

1CREATE TABLE events
2(
3    tenant_id   UInt32,
4    event_type  LowCardinality(String),
5    event_time  DateTime64(3, 'UTC'),
6    user_id     UInt64,
7    url         String,
8    country     LowCardinality(String),
9    properties  JSON
10)
11ENGINE = MergeTree
12PARTITION BY toYYYYMM(event_time)
13ORDER BY (tenant_id, event_type, event_time)
14TTL toDateTime(event_time) + INTERVAL 13 MONTH DELETE;

03Inserting data the right way

ClickHouse loves big batches and hates tiny inserts. Every INSERT creates at least one part, and thousands of single-row inserts per second create thousands of parts, which triggers the famous “too many parts” error. Batch on the client (thousands to hundreds of thousands of rows per insert, roughly once per second) or enable asynchronous inserts so the server buffers small writes for you. Data typically arrives through a queue (Kafka, a Redis-backed worker, HTTP collectors) and is flushed in batches. Formats such as JSONEachRow, CSV, Parquet and the native format are supported over HTTP (port 8123) or the native protocol (9000).

1# Batch insert over HTTP from a file of JSON lines
2curl -u your_user:your_password \
3  'http://localhost:8123/?query=INSERT%20INTO%20events%20FORMAT%20JSONEachRow' \
4  --data-binary @events.ndjson
5 
6-- Or let the server batch small inserts
7INSERT INTO events SETTINGS async_insert = 1, wait_for_async_insert = 1
8FORMAT JSONEachRow {"tenant_id":1,"event_type":"page_view","event_time":"2026-11-05 10:00:00","user_id":7,"url":"/","country":"AM","properties":{}}

04Analytical SQL

ClickHouse speaks SQL with many analytical extras. uniq() and uniqExact() count distinct users (uniq is approximate and much faster), quantile() computes percentiles, countIf() and sumIf() filter inside aggregates, and windowFunnel() and retention() answer product questions directly. Arrays and maps are first-class, and the JSON type stores semi-structured properties as typed sub-columns. Always filter on the leading ORDER BY columns and the time range, select only the columns you need (never SELECT * on a wide table), and use LIMIT while exploring.

1SELECT
2    toStartOfDay(event_time)                      AS day,
3    uniq(user_id)                                 AS visitors,
4    countIf(event_type = 'purchase')              AS purchases,
5    round(purchases / visitors * 100, 2)          AS conversion_pct
6FROM events
7WHERE tenant_id = 1
8  AND event_time >= now() - INTERVAL 30 DAY
9GROUP BY day
10ORDER BY day;

05Materialized views for fast dashboards

A materialized view in ClickHouse is an insert trigger: when rows arrive in the source table, the view runs its SELECT on just that new batch and writes the result into a target table. Combined with SummingMergeTree or AggregatingMergeTree, this keeps pre-aggregated rollups (per hour, per tenant) always up to date, so dashboards read thousands of rows instead of billions. Use the -State combinators when writing and -Merge when reading, because partial aggregates from different parts must be merged. Remember that a view only sees new inserts; backfill historical data separately with INSERT INTO ... SELECT.

1CREATE TABLE events_hourly
2(
3    tenant_id UInt32, hour DateTime, event_type LowCardinality(String),
4    events UInt64, users AggregateFunction(uniq, UInt64)
5)
6ENGINE = AggregatingMergeTree ORDER BY (tenant_id, event_type, hour);
7 
8CREATE MATERIALIZED VIEW events_hourly_mv TO events_hourly AS
9SELECT tenant_id, toStartOfHour(event_time) AS hour, event_type,
10       count() AS events, uniqState(user_id) AS users
11FROM events GROUP BY tenant_id, hour, event_type;
12 
13-- Read: SELECT hour, sum(events), uniqMerge(users) FROM events_hourly WHERE tenant_id = 1 GROUP BY hour;

06Running it: single node, cluster or cloud

A single ClickHouse server handles far more than most teams expect, often billions of rows on one machine with fast NVMe disks. For high availability, use ReplicatedMergeTree tables coordinated by ClickHouse Keeper (the built-in replacement for ZooKeeper); for more capacity, shard data across nodes with a Distributed table. ClickHouse Cloud separates storage (object storage) from compute and removes most of this work, at a usage-based price. Whatever you choose: create users with least privilege, keep ports 8123 and 9000 off the public internet, set query limits (max_memory_usage, max_execution_time) per user, and back up with BACKUP TO S3 or your provider's snapshots.

07Parts, granules and how a query skips data

Each part on disk is a directory with one file per column (wide format) plus primary.idx and mark files. Marks point to the start of each granule inside the compressed column files. At query time ClickHouse uses primary.idx to binary search the range of granules whose ORDER BY values can match, reads only the marks for those granules, and decompresses only those blocks of the requested columns. Data-skipping indexes (minmax, set, bloom_filter) add per-granule summaries for columns outside the sorting key. Use EXPLAIN indexes = 1 to see how many parts and granules were selected out of the total; a filter that selects 95% of granules is a full scan in disguise.

1EXPLAIN indexes = 1
2SELECT count() FROM events
3WHERE tenant_id = 1 AND event_time >= now() - INTERVAL 1 DAY;
4 
5-- A skipping index for a column outside ORDER BY
6ALTER TABLE events ADD INDEX idx_url url TYPE bloom_filter GRANULARITY 4;
7ALTER TABLE events MATERIALIZE INDEX idx_url;

08Deduplication and changing data: ReplacingMergeTree and friends

ClickHouse does not enforce unique keys. ReplacingMergeTree keeps the latest row per ORDER BY key, chosen by a version column, but only after merges, which happen at an unknown time. Queries that need exact results use FINAL (which merges at read time; it is much faster in recent versions but still costs) or aggregate with argMax(value, version). CollapsingMergeTree and VersionedCollapsingMergeTree handle updates by inserting a cancelling row with sign -1. Inserts into replicated tables are deduplicated by block hash for a window, so retrying the same batch after a timeout is safe, as long as the batch is identical.

1CREATE TABLE users_dim
2( user_id UInt64, plan LowCardinality(String), updated_at DateTime64(3) )
3ENGINE = ReplacingMergeTree(updated_at)
4ORDER BY user_id;
5 
6SELECT plan, count() FROM users_dim FINAL GROUP BY plan;

09Joins, dictionaries and projections

ClickHouse executes joins, and its join planner has improved a lot in recent releases, but the default hash join still builds the right-hand table in memory, so keep the smaller table on the right and filter both sides before joining. For small dimension tables that change slowly (countries, plans, product names), dictionaries are better: they are loaded into memory and queried with dictGet(), often far faster than a join. Projections store an extra, differently sorted or pre-aggregated copy of a table inside the same parts, and the optimizer uses them automatically when a query matches. They cost disk and insert time, so add them for proven hot queries only.

10Scaling out and protecting the cluster

Scale up first: more cores and fast NVMe give near-linear gains. When you need more, shard by a key that matches tenancy (for example cityHash64(tenant_id)) so most queries hit one shard, and replicate each shard with ReplicatedMergeTree and ClickHouse Keeper (three Keeper nodes for quorum). A Distributed table fans queries out and merges results. For multi-tenant analytics, protect the cluster with settings profiles and quotas: max_memory_usage, max_execution_time, max_rows_to_read and max_concurrent_queries per user, plus row policies so each tenant's user only sees its rows. Monitor system.query_log, system.parts (parts per partition) and system.merges, and alert on part counts and replication lag.