Table Engines & MergeTree

The engine you choose determines HOW your data is stored, sorted, merged, and queried. MergeTree is the king — let's master it and its variants.

🏗️ What is a Table Engine?

🧒 ELI5: The Car Engine Analogy

A car's body might look the same, but the engine determines everything: speed, fuel efficiency, power. Similarly, a ClickHouse table looks like a normal SQL table, but the engine determines how data is stored, sorted, compressed, and queried.

Choose the wrong engine? Your queries will be slow. Choose the right one? Blazing fast!

🌲 MergeTree — The King of Engines

95% of the time, you'll use MergeTree or one of its variants. It's the default, the most powerful, and the most optimized.

CREATE TABLE events (
    event_date Date,
    event_time DateTime,
    user_id UInt64,
    event_type String,
    revenue Float64
) ENGINE = MergeTree()
PARTITION BY toYYYYMM(event_date)
ORDER BY (event_date, user_id)
SETTINGS index_granularity = 8192;

Key Clauses Explained

Clause	What It Does	Analogy
`ENGINE = MergeTree()`	Selects the storage engine	Choosing your car's engine type
`PARTITION BY`	Splits data into folders by value	Filing cabinet drawers by month
`ORDER BY`	Sorts data within each partition	Alphabetical order within each drawer
`index_granularity`	How many rows per index entry	How many pages between bookmarks

⚠️ ORDER BY is CRITICAL

The ORDER BY clause is the most important decision you'll make. It determines the physical sort order on disk and directly affects query speed. Put your most-filtered columns first!

🔄 MergeTree Variants

ReplacingMergeTree

Deduplicates rows with the same ORDER BY key during merges. Keeps the latest version.

CREATE TABLE user_profiles (
    user_id UInt64,
    name String,
    email String,
    updated_at DateTime
) ENGINE = ReplacingMergeTree(updated_at)
ORDER BY user_id;

🧒 ELI5

Like a phone contacts list — if you save "John" twice with different phone numbers, it keeps only the latest one. But it doesn't happen instantly; it happens during background "cleaning" (merges).

SummingMergeTree

Automatically sums numeric columns for rows with the same key during merges.

CREATE TABLE daily_stats (
    date Date,
    country String,
    views UInt64,
    clicks UInt64,
    revenue Float64
) ENGINE = SummingMergeTree()
ORDER BY (date, country);

AggregatingMergeTree

Stores pre-aggregated states (like partial AVG, COUNT DISTINCT). Used with materialized views for real-time aggregation.

CollapsingMergeTree

Uses a "sign" column (+1/-1) to handle updates and deletes by canceling out rows during merges.

💡 Which Engine Should I Use?

MergeTree — Default choice, works for 90% of cases
ReplacingMergeTree — When you need deduplication (CDC, upserts)
SummingMergeTree — Pre-aggregated counters/metrics
AggregatingMergeTree — Complex real-time aggregations
CollapsingMergeTree — When you need mutable data

📂 Partitioning Strategy

🧒 ELI5: The Filing Cabinet

Partitioning is like having separate drawers in a filing cabinet. If your data is partitioned by month, and you query "show me January data," ClickHouse opens ONLY the January drawer. It completely ignores the other 11 drawers. This is called partition pruning.

-- Common partitioning strategies:

-- By month (most common)
PARTITION BY toYYYYMM(event_date)

-- By day (high-volume data)
PARTITION BY toYYYYMMDD(event_date)

-- By year (low-volume data)
PARTITION BY toYear(event_date)

-- By custom expression
PARTITION BY (toYYYYMM(event_date), country_code)

⚠️ Don't Over-Partition!

Having too many partitions (>1000) creates too many small files and actually SLOWS things down. Rule of thumb: aim for partitions with at least 1 million rows each.

🧠 Quick Quiz

Which MergeTree variant automatically deduplicates rows?

Architecture Data Types

October 2026 practice

Checked against the ClickHouse documentation in October 2026. The JSON type has been production-ready since version 25.3. ClickHouse Cloud rewrites a MergeTree create onto the matching SharedMergeTree engine.

ReplacingMergeTree(version) drops older rows with the same ORDER BY key only when a merge happens. Until then, duplicates are visible. FINAL forces that merge at read time and is expensive on large tables. Prefer argMax or GROUP BY for the latest row. OPTIMIZE is not an upsert strategy. On Cloud, ReplacingMergeTree is stored as the SharedReplacingMergeTree equivalent. Lightweight deletes exist. They are not a reason to use FINAL on every dashboard.

SELECT user_id, argMax(email, updated_at) AS email
FROM user_profiles
GROUP BY user_id