ClickHouse MergeTree Table Generator

A ClickHouse table lives or dies by its sorting key, and the CREATE TABLE syntax packs the engine, partitioning, TTL and codecs into one statement that is easy to get subtly wrong. Describe your columns and this generator writes the DDL, then flags the mistakes that cost you scan performance. It all runs in your browser - nothing is uploaded.

DatabaseTableEngineReplicated

Columns (one 'name Type' per line)

ORDER BYPRIMARY KEY

Partitionon column

TTLafterdaysindex_granularitySuggest per-column codecs

CREATE TABLE statement

CREATE TABLE IF NOT EXISTS analytics.events
(
    event_time   DateTime CODEC(Delta, ZSTD(1)),
    event_date   Date DEFAULT toDate(event_time) CODEC(Delta, ZSTD(1)),
    user_id      UInt64 CODEC(Delta, ZSTD(1)),
    event_type   LowCardinality(String),
    country      LowCardinality(String),
    url          String CODEC(ZSTD(3)),
    duration_ms  UInt32 CODEC(Delta, ZSTD(1)),
    revenue      Decimal(18, 4) CODEC(ZSTD(1))
)
ENGINE = MergeTree
PARTITION BY toYYYYMM(event_date)
ORDER BY (event_type, user_id, event_time)
TTL event_date + INTERVAL 90 DAY DELETE
SETTINGS index_granularity = 8192
;

Sanity checks

Sanity checks
-------------------------------------------------------------
OK  Every ORDER BY column exists in the column list.
i  Consider LowCardinality(String) for: url
   It pays off below roughly 10,000 distinct values per column.

How MergeTree actually works

ORDER BY is the single most important choice
-------------------------------------------------------------
MergeTree stores rows sorted by the sorting key and keeps a SPARSE
index - one entry per index_granularity rows (8192 by default).
A query can skip a granule only when the filter lines up with the
leading columns of ORDER BY. Rule of thumb: order columns by
ascending cardinality, and put the columns you filter on first.

PRIMARY KEY vs ORDER BY
-------------------------------------------------------------
In ClickHouse the PRIMARY KEY is not unique - it only names the
prefix of ORDER BY that goes into the sparse index. Set it when you
want a long sorting key (for dedupe or locality) but a short index:
  ORDER BY (tenant_id, user_id, event_time)
  PRIMARY KEY (tenant_id, user_id)
It must be a prefix of ORDER BY; anything else is rejected.

Partitioning is for data lifecycle, not for speed
-------------------------------------------------------------
Partitions make DROP PARTITION and TTL cheap, and they let the
planner prune whole months. They do NOT replace a good ORDER BY.
Over-partitioning is a classic cause of slow inserts and 'too many
parts' errors, because every insert touches every partition it spans.

Verify the result after loading data
-------------------------------------------------------------
SELECT partition, count() AS parts, sum(rows) AS rows,
       formatReadableSize(sum(bytes_on_disk)) AS on_disk
FROM system.parts
WHERE table = 'events' AND active
GROUP BY partition ORDER BY partition;

-- Compression won per column, to check your CODEC choices:
SELECT name, formatReadableSize(sum(data_compressed_bytes)) AS compressed,
       formatReadableSize(sum(data_uncompressed_bytes)) AS raw,
       round(sum(data_uncompressed_bytes) / sum(data_compressed_bytes), 2) AS ratio
FROM system.columns
WHERE table = 'events'
GROUP BY name ORDER BY sum(data_compressed_bytes) DESC;

Do more than clickhouse mergetree table generator — meet Chat2DB

Chat2DB is an AI-powered SQL client for Windows, macOS and Linux. Write SQL in natural language, format and optimize queries automatically, and manage MySQL, PostgreSQL, Oracle and 20+ other databases in one workspace.

How to use

Paste your columns, one 'name Type' pair per line. LowCardinality, Nullable, Decimal and DEFAULT expressions all pass through unchanged.
Pick a MergeTree engine, set ORDER BY from lowest to highest cardinality, and choose a partition granularity and TTL for the data lifecycle.
Read the sanity checks for sorting-key and partitioning warnings, then copy the DDL into clickhouse-client or your SQL editor.

Frequently asked questions

How do I choose the ORDER BY key for a ClickHouse table?

Order the columns from lowest to highest cardinality and lead with the columns your queries filter on most. ClickHouse keeps only a sparse primary index - one mark per index_granularity rows, 8192 by default - so a query can skip data only when the WHERE clause matches a prefix of the sorting key. A key of (event_type, user_id, event_time) lets a filter on event_type alone skip most granules, while (user_id, event_time) forces a near-full scan for the same query because user_id is close to unique. Put a timestamp last: it is almost always the highest-cardinality column, and it still gives you good locality within each granule.

What is the difference between PARTITION BY and ORDER BY in ClickHouse?

They solve different problems. PARTITION BY physically splits the table into directories on disk, which makes DROP PARTITION and TTL expiry instant and lets the planner skip entire months; it is a data-lifecycle tool. ORDER BY sorts rows inside each part and drives the sparse index, which is what actually makes queries fast. Beginners often partition by day hoping for speed and end up with thousands of small parts, slow merges and 'too many parts' insert errors. Partition by month (toYYYYMM) unless you genuinely drop data daily, and invest the effort in the sorting key instead.

Does ReplacingMergeTree guarantee my rows are deduplicated?

No, and this trips up nearly everyone. ReplacingMergeTree removes duplicates of the sorting key only when parts are merged, which happens in the background at an unpredictable time - so a SELECT run right after an insert can still return both rows. To get deduplicated results at query time you must either add FINAL to the query, which is correct but costs performance, or aggregate around it with argMax on a version column. Supplying a version column is also what makes the winner deterministic: without one, ClickHouse keeps an arbitrary row from each duplicate group. Chat2DB connects to ClickHouse and shows the merged and unmerged row counts side by side, which makes this easy to reason about: download it at https://chat2db.ai/download or use the web version at https://app.chat2db.ai.