This source did not publish a separate summary. Review SKILL.md before using the skill.
SKILL.md
ClickHouse Schema Design (Core Workflow A)
Overview
Design ClickHouse tables with correct engine selection, ORDER BY keys,
partitioning, and codec choices for analytical workloads. This skill covers the
four schema decisions that determine query speed and storage cost — engine,
sort key, partition expression, and column codecs — then points to
references/ for full DDL and the programmatic apply path.
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:
— sparse index over sort-key granules (8192 rows default)
Primary index
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 ranges
ORDER BY (tenant_id, event_type, created_at)
-- Bad: UUID first means every query scans the full index
ORDER BY (event_id, created_at) -- event_id is random UUID
Step 3: Write the Table DDL
Start from the append-only event skeleton below, then adapt the engine and sort
key to your access pattern. Full DDL for the three canonical shapes — event
analytics (MergeTree), user profiles (ReplacingMergeTree), and daily
aggregation (AggregatingMergeTree) — plus column codec choices is in
schema examples.
CREATE TABLE analytics.events (
event_id UUID DEFAULT generateUUIDv4(),
tenant_id UInt32,
event_type LowCardinality(String),
user_id UInt64,
properties String CODEC(ZSTD(3)), -- JSON blob, compress well
created_at DateTime64(3) DEFAULT now64(3)
)
ENGINE = MergeTree()
ORDER BY (tenant_id, event_type, toDate(created_at), user_id)
PARTITION BY toYYYYMM(created_at)
TTL created_at + INTERVAL 1 YEAR
SETTINGS index_granularity = 8192;
Step 4: Choose a Partition Expression
toYYYYMM(date) (monthly) is the right default for most time-series tables —
target 10-1000 parts per partition. Each partition creates separate parts on
disk, so over-partitioning (e.g., by user_id) creates millions of tiny parts
and kills performance. Full partition matrix and the Node.js apply path are in
partitioning and applying schema.
Output
Applying this skill produces:
Table DDL — a CREATE TABLE statement with an engine, ORDER BY sort key,
PARTITION BY expression, per-column codecs, and (optionally) a TTL clause.
A rationale for each decision — why this engine, why this sort-key order,
why this partition granularity — so the schema is reviewable, not cargo-culted.
Optional apply script — a @clickhouse/clientcommand() call that runs
the DDL from application code (see the reference), keeping schema in version
control alongside the service.
Full DDL for all three shapes plus column codec choices:
schema examples. The Node.js apply path
(client.command() with @clickhouse/client) and the full partition matrix:
partitioning and applying schema.
