Skip to content
Tutorials Center
My learning

Topics

Lesson 27 of 35 ยท Replication and Clusters

Sharding and Distributed Tables

Scale ClickHouse horizontally with shards: the Distributed engine, sharding keys, cluster configuration, distributed vs local queries, joins and resharding trade-offs.

Replication gives you copies of the same data. Sharding splits one dataset across several servers so that each holds only a part. Queries then run on all shards in parallel and the results are combined. Use sharding when the data or the query load no longer fits on one machine, not before: a single modern ClickHouse server handles many terabytes and billions of rows per second.

The cluster cannot run on this site, so ClickHouse statements are read-only. A SQL stand-in demonstrates how a sharding key spreads rows. Make sure you understand replication, because a typical cluster combines both.

Shards and replicas together

A cluster is a grid. Each shard holds a slice of the data; each shard has one or more replicas holding identical copies of that slice.

flowchart TB
    Q["Query on any node"] --> D["Distributed table"]
    D --> S1["Shard 1: replica A, replica B"]
    D --> S2["Shard 2: replica A, replica B"]
    D --> S3["Shard 3: replica A, replica B"]

The Distributed table sends the query to one replica of each shard and merges the partial results.

The cluster is declared in the server configuration under remote_servers, with a name such as main, listing every shard and replica host. SELECT * FROM system.clusters shows what each server knows.

Two tables: local and Distributed

A sharded setup always has two layers:

  1. A local table with a ReplicatedMergeTree engine on every node. It stores that shard's data.
  2. A Distributed table, a thin virtual table that stores nothing. It knows the cluster, the database and local table name, and a sharding key.
SQL
CREATE TABLE events_local ON CLUSTER main
(
    ts       DateTime,
    site     LowCardinality(String),
    user_id  UInt64
)
ENGINE = ReplicatedMergeTree('/clickhouse/tables/{shard}/events_local', '{replica}')
ORDER BY (site, ts);

CREATE TABLE events ON CLUSTER main AS events_local
ENGINE = Distributed(main, currentDatabase(), events_local, cityHash64(user_id));

The last argument is the sharding key expression. Each inserted row is assigned to shard hash % total_weight (shards can have a weight). Choose a key that is:

  • Evenly distributed, to avoid hot shards (cityHash64(user_id) is good; a country code is not).
  • Aligned with your heavy queries. If most queries group by user_id, sharding by user_id puts all of a user's rows on one shard, so GROUP BY user_id and uniq(user_id) can be finished locally.

Writing to a Distributed table

You can insert in two ways:

  • Into the Distributed table. The receiving node splits rows by the sharding key and forwards them to the shards. By default it writes them asynchronously to a local queue directory first (distributed_foreground_insert = 1, named insert_distributed_sync in older versions, makes it synchronous). If a shard is down, data waits in the queue. This is convenient for small loads but adds work to the receiving node.
  • Directly into local tables. Your loader (or a load balancer) chooses the shard and inserts there. This is faster and a common choice for high volume, but the loader must follow the same key logic if you want key alignment.

Querying

SQL
SELECT site, uniq(user_id) AS visitors
FROM events
WHERE ts >= today() - 7
GROUP BY site;

ClickHouse rewrites this into one query per shard (against events_local), each shard returns partial aggregation states, and the initiator node merges them. Because states merge correctly (see combinators), uniq and quantile work across shards.

Setting distributed_group_by_no_merge or optimize_distributed_group_by_sharding_key lets ClickHouse skip the final merge when the GROUP BY includes the sharding key.

The trouble with joins

A join between two Distributed tables is the classic trap. By default, the initiator sends the right table query to every shard and each shard joins its local left rows with the whole right table fetched from everywhere, or, worse, with only its own local right slice, giving wrong results. Safe patterns:

Pattern When
GLOBAL JOIN / GLOBAL IN the right side is small; it is collected on the initiator, then shipped to all shards
Co-located join: both tables sharded by the same key and distributed_product_mode = 'local' the join key equals the sharding key
Replicate the small table everywhere (or use a dictionary) dimension tables
SQL
SELECT e.site, u.plan, count()
FROM events AS e
GLOBAL INNER JOIN users AS u ON e.user_id = u.id
GROUP BY e.site, u.plan;

Resharding is hard

ClickHouse does not rebalance data automatically when you add a shard. New rows may use the new layout, but old rows stay. Typical approaches: add shards early, plan for growth by using a hash with spare weights, or copy data into a new cluster with INSERT INTO ... SELECT FROM remote(...) or clickhouse-copier. Newer managed setups with shared storage (ClickHouse Cloud) avoid this by separating compute from data.

Stand-in: how a sharding key spreads rows

SQLite has no hash functions like cityHash64, so the stand-in uses modulo of a number. The goal is to feel how the key decides placement and why a skewed key causes a hot shard.

SQL Stand-in: even key versus skewed key

Press Run (or Ctrl + Enter) to see the result here.

The user key balances the three shards almost equally, while the country key sends about 70 percent of the data to one shard.

SQL Stand-in: a distributed query = local partials, then a merge

Press Run (or Ctrl + Enter) to see the result here.

Common mistakes

  • Sharding too early; vertical scaling and replication often suffice.
  • Choosing a low-cardinality or skewed sharding key.
  • Joining Distributed tables without GLOBAL or co-location and getting wrong counts.
  • Inserting into the Distributed table at very high rates through one node.
  • Dropping the local table but forgetting the Distributed one (or the reverse).

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. What does a Distributed table store?

    It is a virtual table that knows the cluster and local table name and applies the sharding key.
  2. 2. Which sharding key is likely to cause a hot shard?

    Low-cardinality or skewed keys put most rows on one shard.
  3. 3. When should you use GLOBAL JOIN in a distributed query?

    GLOBAL collects the right side once on the initiator and sends it to all shards, avoiding incorrect local-only joins.
  4. 4. What happens to existing data when you add a shard?

    There is no automatic rebalancing in the open-source engine; copy or reinsert data to redistribute it.

Summary

  • Sharding splits data across servers; each shard usually has replicas.
  • A Distributed table routes writes with a sharding key and fans out reads.
  • Choose an even key aligned with your main GROUP BY/join key.
  • Use GLOBAL JOIN or co-location for joins; resharding is manual.

Next: Cluster Failures and Consistency