ClickHouseintermediate

ClickHouse Table Engines

Which engine to use for which purpose — MergeTree variants, integration engines and the special-purpose ones.

2 min readIntermediateUpdated 31 Jul 2026Edit this page

The table engine determines how data is stored, merged and replicated. Most tables should be a MergeTree variant; the rest are integration points.

MergeTree family

Engine	Use for
`MergeTree`	Immutable events — the default choice
`ReplacingMergeTree`	Rows superseded by a newer version of the same key
`SummingMergeTree`	Pre-aggregated counters summed by key
`AggregatingMergeTree`	Materialized views holding aggregate function states
`CollapsingMergeTree`	Change streams expressed as +1 / −1 rows
`VersionedCollapsingMergeTree`	Collapsing when events can arrive out of order

Each has a Replicated prefix variant (ReplicatedMergeTree) that adds replication through ClickHouse Keeper. In a replicated cluster, every MergeTree table should use it — see Replication.

CREATE TABLE user_state
(
    user_id    UInt64,
    updated_at DateTime,
    status     LowCardinality(String),
    plan       LowCardinality(String)
)
ENGINE = ReplacingMergeTree(updated_at)
ORDER BY user_id;

ReplacingMergeTree(updated_at) keeps the row with the highest updated_at per ORDER BY key — but only once the rows meet in a merge.

Deduplicating engines are eventually consistent

Until a merge combines them, both the old and the new row are visible, so a plain SELECT returns duplicates. Use FINAL (which is expensive), or write queries that aggregate explicitly:

SELECT user_id, argMax(status, updated_at) AS status
FROM user_state GROUP BY user_id;

Special-purpose engines

Distributed — no data of its own; fans queries out to shards and merges results. See Distributed Tables.
Null — discards everything written to it. Genuinely useful as the insert target of a materialized view when you want the aggregate but not the raw rows.
Memory — in RAM, lost on restart. For small temporary sets.
Dictionary — exposes a dictionary as a table for joins and lookups.
MaterializedView — the storage behind a materialized view.

Integration engines

These read from or write to external systems, letting ClickHouse query them directly:

Kafka — consumes a topic; normally paired with a materialized view that writes into a MergeTree table. The Kafka table itself is a stream, not storage: reading from it consumes.
S3, HDFS — query files in object storage, or export to them.
MySQL, PostgreSQL, MongoDB — query a remote database as a table. Convenient for small dimension tables; every query is a live remote query, so it is unsuitable for hot paths.
URL, File — read from an HTTP endpoint or a local file.

-- Ingest from Kafka into a MergeTree table via a materialized view.
CREATE TABLE events_queue (raw String)
ENGINE = Kafka
SETTINGS kafka_broker_list = 'kafka:9092',
         kafka_topic_list = 'events',
         kafka_group_name = 'clickhouse',
         kafka_format = 'JSONAsString';
 
CREATE MATERIALIZED VIEW events_consumer TO events AS
SELECT
    JSONExtractUInt(raw, 'tenant_id')      AS tenant_id,
    JSONExtractString(raw, 'event_type')   AS event_type,
    parseDateTimeBestEffort(JSONExtractString(raw, 'ts')) AS event_time
FROM events_queue;