ClickHouse Table Engines, Keys & Ingestion Design

ClickHouse Table Engines and Ingestion Design

ClickHouse is a column-oriented database built for analytical queries over very large data: scanning billions of rows per second, aggregating events, logs, metrics, and clickstreams. Its speed comes from storing columns separately, compressing them heavily, skipping irrelevant data using sorted order, and processing in vectorized batches. But those properties depend on table design: the wrong sorting key reads everything, tiny frequent inserts create thousands of parts and errors, and treating ClickHouse like a transactional database — updating rows one at a time — fights its architecture.

Good ClickHouse design starts from the queries: choose an engine that matches how data changes, an ORDER BY key that matches common filters, a coarse partition key for data management, materialized views for rollups, and an ingestion pattern that inserts in batches.

TL;DR

MergeTree tables store data in immutable parts that background merges combine; nearly all production tables use this family.
ORDER BY defines physical order and the sparse primary index — put common, lower-cardinality filter columns first.
PARTITION BY coarsely (often by month) for retention and management, not for query speed; add TTL for expiry.
Use ReplacingMergeTree for latest-state rows, AggregatingMergeTree with materialized views for rollups.
Insert in batches (thousands to hundreds of thousands of rows) or use async inserts; avoid many tiny inserts.
Prefer append-only designs; mutations are heavy. Diagnose with EXPLAIN indexes = 1, system.query_log, and system.parts.

Quick Example

An events table, a rollup table, and a materialized view that maintains daily counts on insert:

A latest-state table for user profiles that are updated by inserting new versions:

Core Concepts

Parts and Merges

Every insert writes a new immutable part — a directory of compressed column files sorted by the table's ORDER BY key. Background merges combine parts into larger ones, and engine-specific logic (replacing, summing, aggregating, collapsing) is applied during merges, not at insert time. Two consequences follow: many small inserts create many parts and can trigger "too many parts" errors, and deduplication or aggregation by special engines is eventual until merges complete or queries use FINAL or explicit aggregation.

The MergeTree Family

Use the Distributed engine (or Cloud's architecture) to spread data across shards.

ORDER BY and the Primary Index

The ORDER BY key sorts data within each part; the primary key (by default the same columns) builds a sparse index with one entry per granule (8,192 rows by default). Queries filtering on a prefix of the key skip most granules. Guidelines:

Put columns used in most filters first — often a tenant or category, then type, then time.
Order earlier columns from lower to higher cardinality to improve both skipping and compression.
Keep the key short; more columns add little after the first few.
Use data skipping indexes (minmax, set, bloom filter) or projections for secondary access patterns.

Check index use with EXPLAIN indexes = 1.

Partitioning and TTL

Partitions group parts for management: dropping old months, moving data between storage tiers, and limiting merge scope. Keep partitions coarse (monthly is common; daily for very high volume). Too many partitions — for example by user ID — create too many parts and slow everything down. TTL clauses delete or move expired data automatically.

Types and Compression

Choose narrow types (UInt32 instead of Int64 where valid), LowCardinality(String) for repeated strings, DateTime64 with explicit time zones, and codecs such as Delta or DoubleDelta plus ZSTD for time series and large text. Avoid Nullable unless needed; it adds storage and processing overhead.

Materialized Views

ClickHouse materialized views are insert triggers: each insert block into the source table is transformed and written to a target table. They power real-time rollups, alternative orderings, and denormalized tables. They only see new inserts — backfill existing data separately — and they process each block independently, so aggregation must use mergeable states (as with AggregatingMergeTree).

Ingestion Design

Updates and Deletes

ClickHouse is optimized for appends. ALTER TABLE ... UPDATE/DELETE mutations rewrite parts asynchronously and are expensive; lightweight deletes mark rows deleted more cheaply. Model changing data as new versions (ReplacingMergeTree) or sign rows (Collapsing engines) instead of frequent mutations, and use partition drops for bulk retention.

Diagnosing Performance

EXPLAIN indexes = 1 shows how many parts and granules a query reads.
system.query_log records duration, rows and bytes read, and memory per query.
system.parts shows part counts and sizes per partition — rising counts signal ingestion problems.
system.merges and system.mutations show background work.

See Query Optimization and Database Indexing.

Best Practices

Design Keys From Queries

List the most common filters and group-bys, then choose ORDER BY to match them.

Batch Every Insert

Accumulate rows in the producer or use async inserts; never insert one row per request.

Roll Up With Materialized Views

Precompute common aggregations into AggregatingMergeTree tables and query the rollups.

Prefer Appends Over Mutations

Represent changes as new rows with versions, and manage retention with partitions and TTL.

Common Mistakes

Row-by-Row Inserts

Time-First Sorting Key

Expecting Immediate Deduplication

FAQ

What is the MergeTree engine in ClickHouse?

The main family of table engines, which stores data in sorted, immutable parts and merges them in the background, supporting primary indexes, partitioning, TTL, and replication.

How do I choose an ORDER BY key?

Start from the most common query filters, place frequently filtered and lower-cardinality columns first, and end with time or another column for range scans. Validate with EXPLAIN indexes = 1.

How does ReplacingMergeTree deduplicate?

It keeps one row per sorting key — the one with the highest version, if a version column is given — when parts merge. Until then, queries may see duplicates unless they use FINAL or equivalent aggregation.

Why does ClickHouse complain about too many parts?

Because each insert creates a part, and frequent small inserts produce parts faster than merges combine them. Batch inserts, use async inserts, and avoid overly fine partitions.

Can ClickHouse update and delete rows?

Yes, through mutations and lightweight deletes, but they are costlier than in transactional databases. Prefer append-based patterns with versions and partition-level retention.