Sharding vs. Scaling Up: The Decision, Defended

The trade-off

Vertical scaling — a bigger machine, or bigger replicas of the same full dataset — keeps a simple mental model: there's one logical copy of your data (or several full copies, each independently able to answer any query). Sharding trades that simplicity for a hard ceiling removed: splitting different rows of the same table across different nodes, so total data volume and total ingestion throughput can grow past what any single machine, however large, could hold or absorb. What it costs isn't just more servers — it's a new operational shape: a sharding key that determines where every row physically lives, a Distributed table layer coordinating queries across nodes that don't share data, and correctness rules (GLOBAL JOIN, covered below) that don't exist at all on a single node or a simple replica set.

The alternatives, steelmanned — as an escalation ladder, not a coin flip

ClickHouse's own sizing guidance doesn't present scaling up and sharding as a coin flip between two equally-reasonable defaults — it's explicit that vertical scaling comes first: "we generally recommend using the largest server available to prevent having to re-shard your data in the future," and separately, "we suggest vertically scaling all replicas prior to adding additional replicas." That ordering is worth taking seriously as three distinct rungs, each with its own real case and its own real ceiling.

Rung 1 — scale the box(es) you have. The case for it: zero new operational surface. No sharding key to choose (and no way to choose it badly), no Distributed table to configure, no distributed-JOIN correctness rules to learn. Every query already sees the whole dataset, exactly as every lesson before this one in the course has assumed. The real ceiling: eventually a single machine's disk, CPU, or RAM is exhausted, or the workload's ingestion rate outpaces what one node's merge process can keep up with — no amount of "get a bigger box" fixes a workload that has genuinely outgrown what any single machine, at any realistic price point, can hold or serve.

Rung 2 — more replicas of the full dataset, no sharding key involved. This is the rung teams skip past too quickly, straight from "one node is struggling" to "let's shard." A ReplicatedMergeTree table, as replicated-engines covered, can have any number of replicas, and any replica can serve any query, because every replica holds the complete table. Adding replicas is a clean way to add read concurrency — more machines able to answer queries in parallel — without touching a sharding key at all. The real ceiling: it does nothing for total data volume (every replica still has to hold the entire dataset) or ingestion throughput (writes still ultimately propagate to, and get merged on, every replica). A workload where the bottleneck is "too many concurrent readers," not "too much data" or "too much write volume," is often fully solved here, at rung 2, without ever reaching rung 3.

Rung 3 — sharding, via the Distributed table engine. The case for it: this is the only rung that actually removes the ceiling rungs 1 and 2 both have — total data volume and total ingestion throughput that has outgrown what any single node (however large) or any set of full replicas (however many) can hold or absorb. A Distributed table "does not store any data of [its] own" — it's a routing layer over ordinary local tables that live on each shard, sending each INSERT's rows to a specific shard based on a sharding key, and fanning every SELECT out to all shards, merging partial results back together. The cost is everything rungs 1 and 2 never had to deal with: a sharding key decision that's expensive to revisit, a new query-correctness surface (GLOBAL JOIN), and cluster configuration to set up and maintain. Real, and warranted exactly when rungs 1 and 2 are both genuinely exhausted — not before.

-- On every shard node, an ordinary local table (same shape this course has used throughout):
CREATE TABLE events_local
(
    tenant_id  UInt32,
    event_type LowCardinality(String),
    event_time DateTime
)
ENGINE = ReplicatedMergeTree('/clickhouse/tables/{shard}/events', '{replica}')
ORDER BY (tenant_id, event_type, event_time);

-- The routing layer, defined once per node, referencing a cluster config
-- that lists every shard (and each shard's replicas):
CREATE TABLE events AS events_local
ENGINE = Distributed(my_cluster, default, events_local, tenant_id);

-- Every INSERT and SELECT application code actually runs goes through `events`,
-- never `events_local` directly.
INSERT INTO events VALUES (42, 'purchase', now());

SELECT event_type, count() FROM events
WHERE tenant_id = 42 GROUP BY event_type;

The sharding key: the decision that's expensive to take back

tenant_id in the example above is the sharding key — the expression ClickHouse evaluates for each row to decide which shard it belongs on, computed as that expression's value modulo the shards' total weight, a per-shard configuration number (equal across shards by default) that controls what fraction of new rows each shard receives. A well-chosen key spreads both data volume and write load evenly across shards — tenant_id, for a workload with many similarly-sized tenants, is a reasonable choice for the same reason it led ORDER BY in designing-events-tables: most queries already filter on it. A poorly chosen key — a handful of huge tenants dominating a tenant_id-keyed cluster, or a low-cardinality key that collapses most rows onto one shard — produces exactly the skew a single well-scaled node never has to think about, because a single node doesn't have a "which fraction of my data lives here" question at all.

Revisiting that choice later is real, deliberate work, not a config change: "when you add a new shard, you do not have to transfer old data into it. Instead, you can write new data to it by using a heavier weight" — meaning a newly added shard starts empty and only gradually accumulates a fair share of new writes; existing data doesn't automatically redistribute itself. Official guidance is blunt about the underlying reason to avoid this in the first place: ClickHouse "doesn't automatically shard, and re-sharding your dataset will require significant compute resources." A sharding key chosen carelessly at rung 3 isn't a quick fix later — it's closer to a one-way door.

The correctness trap sharding introduces: GLOBAL JOIN

A JOIN against a Distributed table's right-hand side, without the GLOBAL keyword, doesn't run once — "the query is sent to remote servers, and each of them runs the subqueries in the IN or JOIN clause" independently, against only that shard's own local slice of the joined table. If the right-hand table isn't itself sharded the same way as the left-hand one, each shard silently joins against an incomplete view of it — not an error, just a quietly wrong answer built from partial data. GLOBAL JOIN fixes this by executing the subquery once, on the node that received the original query, then broadcasting that full result to every shard to join against locally. This isn't a hypothetical footgun — a currently open ClickHouse GitHub issue documents GLOBAL RIGHT OUTER JOIN on Distributed tables duplicating unmatched rows across shards with no diagnostic warning, which is exactly the shape of failure this section is warning about: sharding doesn't just add operational cost, it adds an entirely new category of query that can silently return the wrong answer if written the way single-node habits would write it.

Decision framework

1. Name the specific resource that's actually constrained — disk capacity, ingestion write/merge throughput, or read query concurrency. "We're getting big" is not a diagnosis; each of those three points to a different rung.
2. If the constraint is read concurrency alone — more simultaneous queries than one node's replica set can serve, with total data volume and ingestion still comfortably within a single node's capacity — rung 2 (more replicas of the full dataset) solves it without ever touching a sharding key.
3. If the constraint is total data volume or ingestion throughput that has genuinely outgrown the largest single node you're willing to provision, rung 3 is warranted — and the sharding key deserves real scrutiny before commitment, given how expensive it is to revisit.
4. Once sharded, does every query joining one Distributed table against another correctly use GLOBAL? This isn't a performance-tuning nicety to get to later — an omitted GLOBAL produces silently wrong results now, the kind of bug that passes code review because the query runs fine and returns a number, just not the right one.
5. Are you sharding because rungs 1 and 2 are genuinely exhausted, or because sharding is what "real" ClickHouse clusters are assumed to look like? The second reason is replicated-engines' reflexive-infrastructure trap, one rung further out — the cost of guessing wrong here is a sharding key baked into your data's physical layout indefinitely, not just an idle server.

Case studies

A good fit, continuing this course's Cloudflare thread. Cloudflare's HTTP analytics pipeline — already covered in designing-events-tables and summing-and-aggregating-mergetree for its schema and pre-aggregation choices — ran on "36 nodes with 3x replication," which works out to roughly 12 distinct shards, each replicated three times: 36 total nodes divided by a replication factor of 3. At "6M HTTP requests per second, with peaks of up to 8M requests per second," this is squarely a rung-3 workload — ingestion volume that no realistic single node, however large, was ever going to absorb — and the same source's own account of needing to think carefully about index_granularity and storage economics at this scale (both covered in earlier lessons) reflects a team that had clearly exhausted rungs 1 and 2 well before reaching for this many shards.

A composite bad fit, representative of a common mistake rather than a single named incident. A team stands up a 4-shard, 2-replica-per-shard cluster — 8 nodes — for a table sitting at 200 GB, comfortably fitting on a single modern server with room to spare, because a blog post's reference architecture diagram showed multiple shards and the team wanted to "build it right the first time." Every query written against this table now has to account for GLOBAL JOIN correctness even though the whole dataset would fit in memory on one box; every schema change has to be applied consistently across 8 nodes instead of one; and the sharding key, chosen without a real ingestion-throughput or data-volume constraint to design around, was picked somewhat arbitrarily and would be expensive to revisit if it turns out to distribute load unevenly. Nothing about this table's actual size or growth rate — the trigger conditions this lesson's decision framework names — justified rung 3 at all.

Anti-pattern gallery

Sharding before rungs 1 and 2 are exhausted. The single most common mistake this lesson addresses: reaching for the Distributed table engine because it's the most-discussed ClickHouse scaling topic, not because vertical scaling and read replicas have actually been tried and found insufficient for the specific resource that's constrained.

A sharding key chosen for convenience rather than distribution. A key that happens to exist on every row (an auto-incrementing ID, an ingestion timestamp) isn't automatically a good sharding key — what matters is whether it distributes both data volume and write load evenly, which requires actually knowing the data's real cardinality and skew, not just picking whatever column is at hand.

Assuming a JOIN against a Distributed table is automatically correct. Omitting GLOBAL doesn't produce an error — it produces a query that runs, returns a plausible-looking number, and is wrong, because each shard silently joined against only its own local slice of the other side. This is the kind of bug that survives code review.

Assuming a newly added shard immediately rebalances existing data. ClickHouse's own documented behavior is that a new shard starts by receiving a heavier share of new writes — old data already on the existing shards doesn't move there on its own. A team that adds a shard expecting instant, even rebalancing across all data (old and new) will be surprised by how long actual rebalancing takes, if it happens at all without manual data movement.