ClickHouse: schema modeling, queries, materialized views

← Back to the section

In PostgreSQL you design tables around the entities of your domain and then add indexes to serve queries. In ClickHouse it is the other way around: first you list the analytical questions, and from them you derive the ORDER BY, the engine, and the pre-aggregates. A mistake in these decisions cannot be cured by "one more index" — only by recreating the table.

Sort order — the main decision

When ClickHouse writes data, it sorts the rows within each part by the columns from ORDER BY and builds a sparse index. At query time it reads not rows one by one, but blocks of 8,192 rows (granules). If the leading columns of ORDER BY match the WHERE condition, the engine skips whole unneeded granules.

The rule: put columns with a small number of unique values that you always filter by at the start of ORDER BY; put more unique columns and time at the end.

CREATE TABLE order_events (
    event_time   DateTime,
    event_type   LowCardinality(String),
    region       LowCardinality(String),
    customer_id  UInt64,
    order_id     UUID,
    amount       Decimal(18, 2)
)
ENGINE = MergeTree
PARTITION BY toYYYYMM(event_time)
ORDER BY (region, event_type, event_time);

A "by region over a period" query uses the key prefix and reads the minimum number of granules. If you set ORDER BY (event_time, region, event_type), filtering by event type stops cutting off granules: within each time range all types are mixed together.

An extra bonus: identical values that end up next to each other after sorting compress better with codecs. A good key both speeds up reads and reduces on-disk size.

Data types: where you save space and time

Every column in ClickHouse is stored as a separate file, and its size directly affects scan speed. So the type is not a formality.

• LowCardinality(String) — dictionary encoding for columns with hundreds or thousands of unique values: statuses, event types, regions, currencies. Less space, faster GROUP BY. With millions of unique values the dictionary bloats and becomes harmful.
• Decimal(18, 2) — for money. Float64 introduces rounding errors in arithmetic, just as in any other database.
• DateTime vs DateTime64(3) — second precision versus millisecond precision. DateTime64 takes twice as much space; use it only when milliseconds truly matter.
• UInt8/16/32/64 — unsigned integers instead of bigint for everything. The smaller the type, the faster the scan.
• Enum8('created' = 1, 'paid' = 2, ...) — more compact and stricter than LowCardinality, but adding a new value means an ALTER. Suitable for closed sets that change rarely.
• Nullable(T) — creates a separate mask file for each column and forbids use in an ORDER BY prefix. By default it is better to rely on defaults (0, ''); use Nullable only when "no value" is semantically different from zero or an empty string.
• Array(T), Map(K, V) — legitimate denormalization: tags, arbitrary event attributes. Together with the functions arrayJoin, has, mapKeys they cover most "flexible schema" cases without separate tables.

Aggregate queries: idioms that PostgreSQL does not have

ClickHouse is built for aggregation. A few built-in functions make queries shorter and faster compared to plain SQL:

SELECT
    toStartOfMonth(event_time)            AS month,
    count()                               AS events,
    countIf(event_type = 'order_paid')    AS paid_orders,
    sumIf(amount, event_type = 'order_paid') AS revenue,
    uniq(customer_id)                     AS customers,
    quantile(0.95)(amount)                AS p95_check
FROM order_events
WHERE event_time >= '2026-01-01'
GROUP BY month
ORDER BY month;

• countIf / sumIf / avgIf — conditional aggregates instead of CASE WHEN inside an aggregate. They read better and run faster.
• uniq vs uniqExact — uniq counts approximately (HyperLogLog, about 1% error) and is orders of magnitude cheaper in memory and CPU. uniqExact gives an exact answer, but at a high cost. For dashboards uniq is almost always enough.
• quantile / quantileExact — the same pair for percentiles.
• argMax(col, ts) — returns the value of col from the row with the maximum ts. Handy for the "last order status" without window functions and self-joins.

Materialized views: pre-aggregation on the fly

In PostgreSQL a materialized view is a snapshot of a query result that is refreshed on command. In ClickHouse it is different: a materialized view works as an insert trigger. Every INSERT into the source table is run through the view's query and appended to the target table right at write time.

The standard pairing is with AggregatingMergeTree:

CREATE TABLE revenue_by_region_daily (
    day      Date,
    region   LowCardinality(String),
    revenue  AggregateFunction(sum, Decimal(18, 2)),
    orders   AggregateFunction(uniq, UUID)
)
ENGINE = AggregatingMergeTree
ORDER BY (region, day);

CREATE MATERIALIZED VIEW revenue_by_region_daily_mv
TO revenue_by_region_daily AS
SELECT
    toDate(event_time)        AS day,
    region,
    sumState(amount)          AS revenue,
    uniqState(order_id)       AS orders
FROM order_events
WHERE event_type = 'order_paid'
GROUP BY day, region;

Reading is done through -Merge functions:

SELECT day, region, sumMerge(revenue) AS revenue, uniqMerge(orders) AS orders
FROM revenue_by_region_daily
GROUP BY day, region;

Why sumState/uniqState and not just sum/uniq? They store the intermediate state of the aggregate rather than the finished number. This means a per-day pre-aggregate correctly rolls up into months and years. For uniq this is essential: you cannot add two HyperLogLog numbers — you need the original structures. The dashboard reads a table of thousands of rows instead of raw billions.

Two important limitations. First, the MV fires only on new inserts — data inserted before the view was created will not make it into the aggregate. Backfilling is done with a separate INSERT ... SELECT. Second, an error in a cascade of views can break the INSERT into the source table. Cascades deeper than one or two levels quickly become undebuggable.

ReplacingMergeTree: how to store "current state"

ClickHouse is an append-only system. To update a row, you insert a new version. When merging parts, ReplacingMergeTree keeps only the row with the maximum value of the specified field:

CREATE TABLE orders_latest (
    order_id    UUID,
    status      LowCardinality(String),
    amount      Decimal(18, 2),
    updated_at  DateTime64(3)
)
ENGINE = ReplacingMergeTree(updated_at)
ORDER BY order_id;

Every change to an order is a new insert with the same order_id. When merged, the row with the maximum updated_at remains. The problem is that the merge happens "someday", and until it completes the duplicates are visible in queries. An honest read is one of two options:

-- Short option: merges versions right at read time
SELECT * FROM orders_latest FINAL WHERE status = 'paid';

-- Alternative: predictable cost on large tables
SELECT order_id, argMax(status, updated_at) AS status, argMax(amount, updated_at) AS amount
FROM orders_latest
GROUP BY order_id;

FINAL is convenient, but expensive on large tables. The argMax option is more verbose, but more predictable under load. In both cases this is a deliberate price for "updatability" in an append-only world.

JOIN and dictionaries

ClickHouse does have JOIN, but with an important caveat: the right-hand table is loaded entirely into memory. Joining a facts table with a regions reference is fine. Joining two billion-row event tables leads to out-of-memory.

How people work around it:

• Denormalization at write time — the main technique. The category name, region, tariff are placed directly into the event row at the pipeline stage. Extra disk space is cheaper than expensive JOINs at read time.
• Dictionaries — reference data that ClickHouse loads itself from PostgreSQL, a file, or an HTTP source and refreshes on a schedule. Access is through the function dictGet('regions_dict', 'name', region_id) with no JOIN at all.
• If a JOIN is unavoidable — keep the small table on the right and apply filters before the JOIN, not after.

Common mistakes

In short

• The schema is designed from queries: first the list of analytical questions, then the ORDER BY.
• In ORDER BY, put low-cardinality columns first (region, type), and time at the end.
• LowCardinality(String) saves space and speeds up GROUP BY for columns with hundreds of unique values.
• Nullable creates extra files — use defaults by default.
• countIf/sumIf/argMax are the core idioms of aggregate queries in ClickHouse.
• A materialized view is an insert trigger, not a snapshot; it fires only on new data.
• sumState/uniqState store the intermediate state — the pre-aggregate rolls up correctly further on.
• ReplacingMergeTree defers deduplication until the merge; an honest read is via FINAL or argMax.
• JOIN loads the right-hand table into memory; the alternatives are denormalization at write time and dictionaries.

What to read next

• Fundamentals — how parts and the sparse index work under the hood.
• Integration with a service — how to populate these tables from Java/Spring.
• Operations — TTL, replication, monitoring merges and mutations.