Design ClickHouse schemas with MergeTree engines, ORDER BY keys, and partitioning.
Use when creating new tables, choosing engines, designing sort keys,
or modeling data for analytical workloads.
Trigger: "clickhouse schema design", "clickhouse table design",
"clickhouse ORDER BY", "clickhouse partitioning", "MergeTree table".
Design ClickHouse schemas with MergeTree engines, ORDER BY keys, and partitioning.
Use when creating new tables, choosing engines, designing sort keys,
or modeling data for analytical workloads.
Trigger: "clickhouse schema design", "clickhouse table design",
"clickhouse ORDER BY", "clickhouse partitioning", "MergeTree table".
Design ClickHouse tables with correct engine selection, ORDER BY keys,
partitioning, and codec choices for analytical workloads.
Prerequisites
@clickhouse/client connected (see clickhouse-install-auth)
Understanding of your query patterns (what you filter and group on)
Instructions
Step 1: Choose the Right Engine
Engine
Best For
Dedup?
Example
MergeTree
General analytics, append-only logs
No
Clickstream, IoT
ReplacingMergeTree
Mutable rows (upserts)
Yes (on merge)
User profiles, state
SummingMergeTree
Pre-aggregated counters
Sums numerics
Page view counts
AggregatingMergeTree
Materialized view targets
Merges states
Dashboards
CollapsingMergeTree
Stateful row updates
Collapses +-1
Shopping carts
ClickHouse Cloud uses SharedMergeTree — it is a drop-in replacement for
MergeTree on Cloud. You do not need to change your DDL.
Step 2: Design the ORDER BY (Sort Key)
The ORDER BY clause is the single most important schema decision. It defines:
Primary index — sparse index over sort-key granules (8192 rows default)
Data layout on disk — rows sorted physically by these columns
Query speed — queries filtering on ORDER BY prefix columns hit fewer granules
Rules of thumb:
Put low-cardinality filter columns first (event_type, status)
Then high-cardinality columns you filter on (user_id, tenant_id)
End with a time column if you use range filters (created_at)
Do NOT put high-cardinality columns you never filter on in ORDER BY
-- Good: filter by tenant, then by time rangesORDERBY (tenant_id, event_type, created_at)
-- Bad: UUID first means every query scans the full indexORDERBY (event_id, created_at) -- event_id is random UUID
