ClickHouse: Zero to Hero

The blazing-fast columnar database built for analytics — billions of rows scanned in milliseconds. You'll run it locally, load data, understand why columnar storage is so fast, design tables with the right engine and sort key, and learn the patterns (and traps) of real-time analytics at scale. Hands-on throughout. Work top to bottom, or jump to a part.

The journey:

1. What ClickHouse is and why it's fast
2. Run it and query in one command
3. Columnar storage — the core idea
4. Tables, MergeTree, and the sort key
5. Loading data efficiently
6. Analytical queries and materialized views
7. Partitioning and TTL
8. When ClickHouse is the wrong tool
9. Production practices

---

Prerequisites

ClickHouse is an OLAP (analytics) database, not a transactional one — keep that framing as you go. Everything runs locally.

---

Part 1 — What ClickHouse is and why it's fast

ClickHouse is a column-oriented OLAP database designed to run analytical queries over huge datasets — aggregations, filters, and scans across billions of rows — in a fraction of a second. It powers observability backends, product analytics, and real-time dashboards.

Its speed comes from a stack of choices: columnar storage (read only the columns a query needs), aggressive compression (similar values sit together and compress ~10×), vectorized execution (process columns in batches using SIMD), and sparse primary indexes. The trade-off: it's built for reads and bulk inserts, not for row-by-row updates or transactions.

---

Part 2 — Run it and query in one command

docker run --rm -it clickhouse/clickhouse-server:24.8
# in another terminal, open the client:
docker exec -it $(docker ps -q --filter ancestor=clickhouse/clickhouse-server:24.8) clickhouse-client

Or query the built-in demo without any data:

SELECT number, number * number AS square
FROM system.numbers
LIMIT 5;

-- ClickHouse ships enormous public datasets and functions:
SELECT version();

The system.numbers table is an infinite generator — handy for testing. ClickHouse's SQL adds analytical superpowers (arrays, higher-order functions, approximate aggregates) on top of standard SQL.

---

Part 3 — Columnar storage — the core idea

A row store (Postgres, MySQL) keeps each row's fields together on disk — great for "fetch this one order." ClickHouse stores each column together instead:

Row store:    [id,ts,url,status][id,ts,url,status]...   ← whole rows
Column store: [id,id,id...][ts,ts,ts...][status,status...]  ← whole columns

Why it's fast for analytics: a query like SELECT avg(latency) WHERE status = 500 only reads the latency and status columns — it never touches url, user_agent, or the dozens of other columns. Add per-column compression (a status column of mostly 200s compresses to almost nothing) and vectorized processing, and you scan billions of rows without reading most of the data. The cost: assembling a full row (all columns) is comparatively expensive — which is why row-by-row lookups and updates are the wrong pattern.

---

Part 4 — Tables, MergeTree, and the sort key

ClickHouse tables need an engine. The workhorse family is MergeTree, and its ORDER BY (sort key) is the single most important design decision:

CREATE TABLE events (
    ts       DateTime,
    service  LowCardinality(String),
    status   UInt16,
    latency  UInt32,
    url      String
)
ENGINE = MergeTree
ORDER BY (service, ts);      -- the sort/primary key

The ORDER BY determines how data is physically sorted and the sparse primary index — so queries that filter or group by its leading columns (here service, then ts) are dramatically faster because ClickHouse skips whole blocks (granules) that can't match. Put your most common filter columns first, roughly lowest-cardinality to highest. LowCardinality(String) dictionary-encodes columns with few distinct values (service names, statuses) for big speed/space wins.

---

Part 5 — Loading data efficiently

ClickHouse loves large batch inserts and hates many tiny ones (each insert creates a data "part" that must later be merged):

-- Batch insert (thousands to millions of rows per statement)
INSERT INTO events VALUES
  ('2026-07-16 10:00:00', 'api', 200, 12, '/health'),
  ('2026-07-16 10:00:01', 'api', 500, 800, '/pay');

-- Load from a file
INSERT INTO events FORMAT CSV INFILE 'events.csv';

# From the shell, stream a file in:
clickhouse-client --query "INSERT INTO events FORMAT CSVWithNames" < events.csv

The rule: batch inserts big (tens of thousands of rows minimum, ideally in a single insert per second per table). Streaming from Kafka/many producers? Use a buffer, the Buffer engine, or async inserts so ClickHouse isn't flooded with tiny parts.

---

Part 6 — Analytical queries and materialized views

This is where ClickHouse shines — aggregations over the sort key are near-instant:

-- Error rate per service, last hour
SELECT service,
       countIf(status >= 500) / count() AS error_rate,
       quantile(0.99)(latency)          AS p99
FROM events
WHERE ts > now() - INTERVAL 1 HOUR
GROUP BY service;

For dashboards you query constantly, a materialized view pre-aggregates on insert, so reads hit a tiny summary table:

CREATE MATERIALIZED VIEW events_per_min
ENGINE = SummingMergeTree
ORDER BY (service, minute) AS
SELECT service, toStartOfMinute(ts) AS minute, count() AS n
FROM events GROUP BY service, minute;

Materialized views in ClickHouse are insert triggers — they transform rows as they arrive into a target table, turning expensive repeated aggregations into cheap lookups.

---

Part 7 — Partitioning and TTL

Partitioning splits a table into independent parts (usually by month) for efficient data management, and TTL expires old data automatically:

CREATE TABLE logs (
    ts      DateTime,
    service LowCardinality(String),
    message String
)
ENGINE = MergeTree
PARTITION BY toYYYYMM(ts)          -- one partition per month
ORDER BY (service, ts)
TTL ts + INTERVAL 90 DAY;          -- auto-delete after 90 days

Partitioning lets you drop whole months instantly (ALTER TABLE ... DROP PARTITION) instead of slow row deletes, and TTL enforces retention without cron jobs. Don't over-partition (partition by month, not by hour) — too many partitions hurts, just like too many shards in a search engine.

---

Part 8 — When ClickHouse is the wrong tool

Reaching for ClickHouse where it doesn't fit is a classic mistake. It is not:

• A transactional (OLTP) database — no real transactions, no efficient single-row updates/deletes (they rewrite parts). Use Postgres/MySQL for app state.
• A key-value store — point lookups by id aren't its strength.
• A queue or a search engine — use Kafka / OpenSearch for those.

It is the right tool for: append-mostly, high-volume, time-series and event data you query with aggregations — logs, metrics, traces, clickstreams, product analytics. If your workload is "insert a lot, update rarely, aggregate constantly," it's a great fit.

---

Part 9 — Production practices

• Design the ORDER BY around your queries — leading columns = your common filters, low→high cardinality.
• Batch inserts large; avoid many tiny inserts (part explosion). Use async/Buffer for streams.
• LowCardinality and the smallest int types (UInt16 not Int64) — storage and speed matter at scale.
• Partition by month, TTL for retention — never delete rows in a loop.
• Materialized views for hot dashboard queries.
• Don't use it for OLTP — pair it with a transactional DB for app state.
• Monitor merges and parts (system.parts, system.merges); watch for "too many parts" errors.
• Replicate with ReplicatedMergeTree + ClickHouse Keeper for HA.

---

Common mistakes to avoid

• Many small inserts. Each creates a part; the merge system falls behind ("too many parts"). Batch big.
• A poor ORDER BY. Queries that don't align with the sort key scan everything — the whole point is lost.
• Using it as an OLTP database. Single-row updates/deletes are expensive rewrites; it has no real transactions.
• Over-partitioning. Partition by month, not hour/day — too many partitions degrade performance.
• Int64/String for everything. Wastes space and speed; use UInt16, LowCardinality, Enum.
• Expecting instant consistency across replicas. Replication is eventually consistent.
• Row-by-row deletes for cleanup. Use DROP PARTITION and TTL instead.

---

Practice: try it yourself

1. First query. Run ClickHouse in Docker and SELECT from system.numbers to feel the speed.
2. Design a table. Create a MergeTree events table with a sensible ORDER BY (service, ts) and LowCardinality where it fits.
3. Bulk load. Insert a few thousand rows in one statement (or from CSV) and confirm the part count in system.parts.
4. Analyze. Write an aggregation for error rate and p99 latency per service over a time window.
5. Retention. Add PARTITION BY toYYYYMM(ts) and a TTL, then DROP PARTITION an old month and watch it vanish instantly.

Self-check:

• Why is columnar storage fast for analytics but poor for single-row lookups?
• What makes the ORDER BY (sort key) the most important table decision?
• Why must inserts be batched large?
• Name two workloads ClickHouse is the wrong tool for.

---

Where to go next

• Compare with search — the OpenSearch tutorial; both do analytics, with different engines and trade-offs.
• Feed it a stream — pair with Kafka for high-volume event ingestion; visualize with the Grafana tutorial (ClickHouse data source).
• Practice with data/observability interview questions in the question bank.

You now have the loop: columnar model → MergeTree + sort key → batch load → aggregate → partition + TTL → know its limits. That's ClickHouse for real-time analytics.