Typorithm · easy

MergeTree Table

mergetree-tableclickhousemergetreeorder-bypartition

Saved on this device

Full lesson · 8 learning steps Open any exercise for focused practice

Step 1 of 8 · intro card

Learn MergeTree Table

Columns & Engines

Create a MergeTree table: ORDER BY defines the on-disk sort and sparse index, PARTITION BY groups rows into monthly parts.

MergeTree is ClickHouse's primary engine, storing data column by column in sorted, immutable parts.

ORDER BY sets how rows are physically sorted and builds the sparse primary index used to skip data.

PARTITION BY splits data into parts, often by month, so queries and TTL can prune whole partitions.

ENGINE = MergeTree for analytics.

ORDER BY = sort order + sparse index.

PARTITION BY groups rows into parts.

CREATE TABLE events
(
    event_date  Date,
    user_id     UInt64,
    event_type  LowCardinality(String),
    value       Float64
)
ENGINE = MergeTree
PARTITION BY toYYYYMM(event_date)
ORDER BY (user_id, event_date);

Read the implementation

Connect each part of the code to the concept before you type.

1. Core engine

MergeTree is ClickHouse's primary engine for analytics tables.

ENGINE = MergeTree

2. Sort key + sparse index

ORDER BY sets the on-disk sort order and builds the sparse primary index.

ORDER BY (user_id, event_date)

3. Monthly partitions

Rows are grouped into parts per month for pruning and TTL.

PARTITION BY toYYYYMM(event_date)

4. Dictionary string

LowCardinality dictionary-encodes columns with few distinct values.

LowCardinality(String)

A complete learning loop

Understand

Read the concept map and follow the code breakdown.

Reconstruct

Type the implementation, repair mistakes and work through every recall exercise.

Verify

Use the final check without hints; revisit weak concepts in short practice sessions.