Distributed Tables
A Distributed table stores nothing: it fans queries out to shards, merges results and routes inserts by a sharding key.
The problem
When data or compute outgrows one node, you shard it. Applications should not need to know which shard holds which rows. The Distributed engine presents a cluster of shards as one table.
How it works
- A Distributed table holds no data. It is defined over a cluster (from server config), a database and a local table that exists on every shard.
- SELECT: the initiator node sends the query to one replica of every shard, each shard runs it on its local table, and the initiator merges the partial results (aggregations are pushed down so only partial states travel).
- INSERT: each row is routed to a shard by the sharding key expression; without a key you can insert only when there is a single shard (or with `insert_distributed_one_random_shard`).
- Inserts through a Distributed table are asynchronous by default: data is written to a local queue on the initiator and forwarded in the background. `insert_distributed_sync = 1` waits for the shards. For throughput, many writers insert directly into the local tables.
- With `internal_replication = true` in the cluster config, a write goes to the first healthy replica of the shard and replication spreads it; with false the Distributed table writes to every replica itself (and can diverge).
- JOINs: joining two Distributed tables sends the right side to every shard; use GLOBAL JOIN to broadcast it once, or colocate both tables on the same sharding key so each shard joins locally.
- ON CLUSTER executes DDL on every node, so schemas stay in step.
Examples
The local table lives on every shard; the Distributed table fans out and routes by a hash of tenant_id.
sql
CREATE TABLE events_all ON CLUSTER main AS events
ENGINE = Distributed(main, currentDatabase(), events, cityHash64(tenant_id));
SELECT tenant_id, count() AS n
FROM events_all
WHERE ts >= now() - INTERVAL 1 DAY
GROUP BY tenant_id
ORDER BY n DESC
LIMIT 10;
Broadcast the small side of a join once instead of re-sending it per shard.
sql
SELECT e.event, t.plan, count() AS n
FROM events_all AS e
GLOBAL INNER JOIN tenants AS t ON t.id = e.tenant_id
GROUP BY e.event, t.plan;
In practice
- Large analytics clusters — Shards for capacity and parallelism, replicas inside each shard for availability; apps query only the Distributed table.
- clickhouse-operator — Generates the cluster definition (shards, replicas, internal_replication) the Distributed engine reads.
- chproxy / load balancers — Spread queries across initiator nodes so no single node merges everything.
- Pitfall — Writing everything through one initiator creates a bottleneck and a backlog in its async queue; check `system.distribution_queue`.
Trade-offs
Pros
- Transparent horizontal scale for storage and query CPU
- Partial aggregation pushdown keeps network traffic small
- One table name for applications
Cons
- A query is as slow as the slowest shard, and fails if a shard has no healthy replica
- Async inserts through the Distributed table can queue up and lose data if the initiator dies
- Cross-shard joins and high-cardinality GROUP BY are expensive