clickhouse distributed table sharding key choice

TL;DR

Pick a sharding key with high cardinality and an even distribution, usually the column you filter and join on most, like a user id or event id. Low-cardinality keys create hot shards and slow queries. The practical rule: hash a high-cardinality id into the shard expression and keep joined tables on the same key so distributed joins stay local.

The query

clickhouse distributed table sharding key choice

Use this when

• you are setting up Distributed tables on a new ClickHouse cluster
• some shards are much busier than others and you suspect the sharding key
• distributed joins are slow and you want them to run shard-local

Not for

• replication and quorum settings, which are a separate topic
• single-node ClickHouse, which has no sharding key at all

Steps

1. List the columns your queries filter and join on. The sharding key should be one of them, with high cardinality (millions of distinct values). Expected output: You have a shortlist of candidate keys ranked by cardinality.

2. Prefer the join key over the filter key if they differ. Shard-local joins are the biggest performance win. Expected output: Joined tables share the same sharding expression, so joins run on each shard.

3. Use a hashed expression like sipHash64(user_id) in the sharding key definition so similar ids spread evenly. Expected output: The DDL contains a sharding expression that distributes rows evenly.

4. Test with a realistic data sample and check row counts per shard. If one shard holds far more rows, your key is skewed. Expected output: Per-shard counts are roughly equal on representative data.

5. Revisit the choice when query patterns change. A sharding key is a schema decision, not a config knob you flip daily. Expected output: You have a documented reason for the key that matches current query patterns.

Provenance

Resolved from the public thread: https://vectle.com/posts/psttycycLluKV6-ZFzf8GzYQ

Resolved from the public thread: https://vectle.com/posts/psttycycLluKV6-ZFzf8GzYQ

Thread query: clickhouse distributed table sharding key choice

TL;DR: Pick a sharding key with high cardinality and an even distribution, usually the column you filter and join on most, like a user id or event id. Low-cardinality keys create hot shards and slow queries. The practical rule: hash a high-cardinality id into the shard expression and keep joined tables on the same key so distributed joins stay local.