Denormalization and Its Trade-offs

Why a JOIN costs something real here

ClickHouse's default join algorithm is a hash join: build an in-memory hash table from one side of the join — as of ClickHouse 24.12, the query planner automatically reorders two-table joins to put the smaller table on that side — then stream the other side past it, probing the hash table row by row. The word to notice is build: the whole hash-table side has to be materialized in memory before the first probe even happens, every single time the query runs.

Compare that to the row-oriented OLTP databases most engineers learn JOINs on first. There, a JOIN can often walk a B-tree index on the join key and look up matching rows one at a time, touching only what it needs. sparse-primary-index already established why ClickHouse doesn't have that option for arbitrary columns: its primary index is a coarse, per-granule structure built for one specific sort order, not a general-purpose per-row lookup structure for whatever column happens to be the join key. So instead of "look up 40,000 individual matches," a JOIN here is closer to "read the smaller table, build a hash table out of the whole thing, then stream the larger table past it" — real memory, real CPU, paid on every execution.

CREATE TABLE orders
(
    order_id    UInt64,
    product_id  UInt64,
    quantity    UInt32,
    order_time  DateTime
)
ENGINE = MergeTree ORDER BY (order_time);

CREATE TABLE products
(
    product_id    UInt64,
    product_name  String,
    category      String
)
ENGINE = MergeTree ORDER BY (product_id);

SELECT o.order_id, p.product_name, o.quantity
FROM orders o
JOIN products p ON o.product_id = p.product_id
WHERE o.order_time > now() - INTERVAL 1 DAY;

For a products table with a few thousand rows this is a non-issue — the hash table is tiny. ClickHouse has non-memory-bound alternatives (full sorting merge, partial merge, and grace hash join) that spill to disk and trade join speed for a bounded memory footprint when the built side genuinely can't fit in memory. But reaching for those is treating the symptom. The more common real fix, when a dimension table is queried this way constantly, is to stop paying the JOIN's build cost on every single query in the first place.

Real-world case study: ClickHouse's own denormalization guide

ClickHouse's official data-modeling guide works through exactly this decision using the public Stack Overflow dataset, and its examples are concrete enough to double as a decision framework in their own right.

A good candidate: Posts and PostLinks (posts that link to other posts). PostLinks averages "54 new links per hour" against a bounded relationship — at most 125 links per post — and denormalizing produced "66 million rows in approximately 2 minutes." Low update rate, bounded cardinality per parent row: this is the shape denormalization is built for.

A bad candidate, same dataset: Posts and Votes. Votes arrive at roughly "40k votes per hour," and one single post had accumulated 35,123 votes by itself — denormalizing would mean rewriting that post's row (and every other popular post's row) continuously, forever. The guide's own recommendation: "aggregated vote statistics for each post would be sufficient for most analysis" — solve the actual query need (vote counts) with a rollup, not a full denormalized copy of every vote.

A worse candidate: Users and Badges. One user in the dataset had earned 19,334 badges. The guide's own assessment: "It's probably not realistic to denormalize 19k objects onto a single row" — past a certain per-parent cardinality, the physical shape of "one row per parent" simply stops fitting the data, independent of update frequency.

The pattern across all three: denormalization earns its keep exactly when the child data is low-cardinality per parent row and changes slowly enough that rewriting the parent row on every update isn't a real operational cost. Both properties have to hold — PostLinks had both, Votes failed the update-rate test, Badges failed the cardinality test outright.

A third option that isn't "copy it in" or "join it every time": dictionaries

Denormalization and JOINs are opposite ends of the same trade: pay once at write time, or pay every time at read time. An external dictionary is neither — it's a key -> attributes structure ClickHouse loads and holds in memory on the server itself, refreshed on a schedule, looked up with a function call instead of a JOIN clause.

CREATE DICTIONARY products_dict
(
    product_id    UInt64,
    product_name  String,
    category      String
)
PRIMARY KEY product_id
SOURCE(CLICKHOUSE(TABLE 'products'))
LAYOUT(HASHED())
LIFETIME(MIN 300 MAX 600);

SELECT
    order_id,
    dictGet('products_dict', 'product_name', product_id) AS product_name,
    quantity
FROM orders
WHERE order_time > now() - INTERVAL 1 DAY;

LAYOUT(HASHED()) picks how the dictionary's data is structured in memory once loaded — HASHED builds a straightforward in-memory hash table keyed on PRIMARY KEY, and it isn't the only option: FLAT trades that flexibility for even less overhead when keys are small, densely-numbered integers, and CACHE holds only a working set in memory, requesting whatever keys a query needs that aren't already cached (or have gone stale) directly from the source on demand, for a dimension too large to load in full. HASHED is the reasonable default for a dimension like products — comfortably sized, no special key-numbering to exploit.

LIFETIME(MIN 300 MAX 600) doesn't mean "reload every 300–600 seconds" as two fixed schedules — ClickHouse picks a random reload time inside that window specifically "to distribute the load on the dictionary source when updating on a large number of servers," so a cluster of nodes doesn't all hammer products with a reload query in the same instant. That randomized window is also the trade you're accepting: a dictionary lookup can be up to LIFETIME's max seconds stale relative to the source table. products_dict here can lag products by up to 10 minutes — fine for a product catalog that changes a few times a day, not fine for anything that needs to reflect an update within the same second it happened.

A public benchmark from Altinity (a ClickHouse consultancy) on the 1.3-billion-row NYC taxi dataset found dictionary lookups meaningfully more efficient than JOINs specifically for string attributes, because "dictionaries have much more compact representation of the data and do not require huge string columns to be read from the disk" on every query — but the same benchmark found "denormalized schema works faster than normalized in cases where grouping and ordering goes against numeric attributes." Neither dictionaries nor denormalization is a strictly dominant choice; which one wins depends on what the query actually does with the enriched column, not just how the dimension is shaped.

Idiom vs. anti-pattern

SELECT
    order_id,
    dictGet('products_dict', 'product_name', product_id) AS product_name
FROM orders
WHERE order_time > now() - INTERVAL 1 DAY;
-- Every query pays one hash lookup per row, not one hash-table build per query.

CREATE TABLE orders_wide
(
    order_id       UInt64,
    quantity       UInt32,
    order_time     DateTime,
    -- the entire products table's columns, copied onto every order row:
    product_name   String,
    category       String,
    supplier       String,
    unit_cost      Decimal(10, 2),
    weight_grams   UInt32
    -- ...and every column products ever grows, on every historical order row
)
ENGINE = MergeTree ORDER BY (order_time);
-- A supplier correction on one product now means rewriting every
-- historical order row that ever referenced it.

This isn't a strawman — it's the natural next move after "JOINs are expensive, so avoid them," taken past the point where it's warranted. It looks reasonable schema by schema: each individual column copied onto orders_wide seems harmless. What it actually does is turn every future change to products — a corrected unit_cost, a renamed category — into a decision about whether to mutate potentially years of historical order rows, or accept that orders_wide now permanently disagrees with products about products ordered before the change. A dictionary sidesteps that dilemma entirely: products stays the single source of truth, and products_dict just becomes accurate again on its next scheduled reload — no rewrite of orders required, at any table size.

A third alternative, briefly: projections

Both denormalization and dictionaries assume you're willing to reach for a second table (or table-shaped structure). A projection takes a different approach: it stores an alternate physical layout of the same table's data — a different sort order, or a pre-aggregation — and ClickHouse's query planner picks whichever layout has the least data to scan for a given query, automatically, without the query itself changing. It's still a form of denormalization in the broad sense — ClickHouse's own documentation warns that a projection "will create internally a new hidden table," costing "more IO and space on disk" — but the duplication is table-transparent: no second CREATE TABLE, no dictGet() in your query text, no separate object for an application to know about. Projections solve a different problem than the one this lesson has focused on (multiple physical layouts of one table, not enrichment from a second entity), and the mechanics of when ClickHouse selects one are covered in Module 7. Worth knowing it exists as a third lever — not yet worth reaching for.

Design exercise

An ad-tech company's impressions fact table logs several billion rows a day. Two enrichment needs come up constantly:

1. Campaigns: every impression needs to be enriched with its campaign's name and daily budget for a live spend dashboard, queried continuously by every advertiser's account team. The campaigns table has a few thousand rows and changes a handful of times a day — a campaign gets created, paused, or has its budget adjusted.
2. Advertiser accounts: analysts occasionally run deep, ad-hoc joins between impressions and an advertisers table — tens of millions of rows, updated continuously throughout the day by a separate, external billing system the ad-tech company doesn't control.