Create a MergeTree table

Every ClickHouse table declares an engine, and most general ClickHouse tables use the MergeTree family. Inserts create immutable parts, and background merges combine parts over time.

That sentence is the whole storage model, so let’s slow down on it. Each insert writes a new part: a self-contained directory of column files, sorted and never modified again. A background process later merges small parts into bigger ones. Reads see the current set of parts; writes never block them.

Here’s an events table for a service that tracks API requests:

CREATE TABLE events (
  ts          DateTime,
  service     LowCardinality(String),
  user_id     UInt64,
  event_type  LowCardinality(String),
  duration_ms UInt32,
  metadata    String
)
ENGINE = MergeTree
ORDER BY (service, ts);

ORDER BY defines how rows are sorted inside each part. It’s required, and choosing it well is the single biggest schema decision — big enough that it gets its own lesson next.

Types are a performance decision

Choose narrow accurate types, use dates and timestamps deliberately, and avoid nullable values when a clear default or separate state fits. Schema choices affect compression and query work.

UInt32 for a duration in milliseconds beats UInt64 — half the bytes to scan for a value that never needs the range. LowCardinality(String) is the right wrapper for columns like service or event_type that repeat a small set of values; ClickHouse dictionary-encodes them and both storage and GROUP BY get faster. And Nullable(String) adds a hidden mask column to every read — an empty string default is usually the better call.

Insert and verify

Insert five realistic rows:

INSERT INTO events VALUES
  ('2026-08-03 10:00:01', 'api',     101, 'request',  42, '{"path":"/v1/users"}'),
  ('2026-08-03 10:00:02', 'api',     102, 'request',  55, '{"path":"/v1/orders"}'),
  ('2026-08-03 10:00:02', 'checkout', 101, 'payment', 310, '{"amount":49}'),
  ('2026-08-03 10:00:03', 'api',     103, 'request',  38, '{"path":"/v1/users"}'),
  ('2026-08-03 10:00:05', 'checkout', 104, 'payment', 290, '{"amount":19}');

Now look at the storage that insert created:

SELECT name, rows, active FROM system.parts
WHERE table = 'events';
-- all_1_1_0 │ 5 │ 1

One insert, one part, five rows. Every part your table ever creates shows up in system.parts, and checking it becomes second nature when you debug ingestion later.

One expectation to correct early: there’s no cheap UPDATE here. Parts are immutable, so changing a row means rewriting parts. If you catch yourself designing a MergeTree table around modifying rows, the design is wrong — model events as append-only facts instead.