How do you design a sharded ClickHouse cluster, and what are the tradeoffs of sharding vs replication for concurrency?

Think of a restaurant chain. Sharding is like opening multiple locations (each serves different neighborhoods and handles its own orders independently, increasing total throughput). Replication is like staffing each location with multiple cashiers (each handles a different customer simultaneously, increasing concurrent service at one location). You need both: multiple locations for capacity and multiple cashiers per location for responsiveness.

ClickHouse sharding works through the Distributed table engine, which acts as a transparent query router. A Distributed table knows the topology of your cluster (which shards exist and where replicas live). When you INSERT into a Distributed table, it routes rows to the appropriate shard based on the sharding key (a hash or modulo function applied to a column). When you SELECT from it, the query is fanned out to all shards, each processes its local data in parallel, and results are aggregated on the initiator node.

Internally, the shard key determines data locality. With shardNum() = cityHash64(user_id) % num_shards, all data for a given user_id lives on one shard. This enables local GROUP BY operations for user-scoped queries without cross-shard communication. For queries that filter by the shard key, only one shard needs to be contacted. However, queries that aggregate across all users must touch all shards and merge results on the initiator, which becomes a bottleneck for complex aggregations with high intermediate result cardinality.

At production scale, the key tradeoffs are: more shards = higher write throughput (each shard handles 1/N of inserts) and faster full-table scans (N-way parallelism), but worse performance for cross-shard JOINs (requires shuffling data between nodes) and higher operational complexity (more nodes to manage, coordinate schema changes, backup). Replication within each shard provides read concurrency (multiple replicas serve different SELECT queries simultaneously) and high availability (one replica can go down without data loss). The standard architecture for a 1TB/day analytics platform might be 4 shards x 2 replicas = 8 nodes.

The non-obvious gotcha is resharding. Unlike Kafka where you can add partitions, adding a shard to a ClickHouse cluster requires redistributing existing data because the shard key hash now maps to different servers. ClickHouse does not natively support online resharding (some experimental features exist but are not production-ready). Teams must plan shard count for peak capacity from the start or use a resharding strategy that involves creating a new cluster with more shards and backfilling data. Also, the Distributed table's INSERT does not guarantee atomicity across shards: a partial failure can leave data on some shards but not others.