Create a MergeTree table

Every ClickHouse table declares an engine, and almost every real table uses one from the MergeTree family. Inserts create immutable parts. 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 folder with one file per column, 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 the events table we’ll use for the rest of the course. It tracks API requests for a small service:

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 picking it well is the single biggest schema decision you’ll make. Big enough that it gets the next lesson to itself.

Types are a performance decision

Choose narrow, accurate types. Use dates and timestamps on purpose. Avoid nullable columns when a clear default fits. All of this changes how well the data compresses and how much work every query does.

UInt32 for a duration in milliseconds beats UInt64. It’s half the bytes to scan, for a value that never needs the bigger range. LowCardinality(String) is the right wrapper for columns like service and event_type that repeat a small set of values. ClickHouse stores them as a dictionary, so both storage and GROUP BY get faster. And Nullable(String) adds a hidden mask column that every read has to consult. An empty string default is almost always the better call.

Insert and verify

Let’s 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 what that insert created on disk:

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. I check it constantly when I debug ingestion, and you will too.

One expectation to correct early: there is 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.