Sharding in Managed Service for ClickHouse®
Sharding in ClickHouse® is a horizontal cluster scaling strategy that splits data into a number of logical parts called shards. Each shard can consist of a single host or multiple replica hosts.
Advantages of sharding:
- Horizontal scaling enables you to bypass the limits of a single server in terms of CPU, RAM, disk space, and I/O throughput.
- Parallel query execution, where you run queries against the same logical table across different shards, allows you to balance the load, reduce resource contention, and accelerate read and write operations.
Sharding specifics in Managed Service for ClickHouse®
To implement distributed storage of table data in Managed Service for ClickHouse®, you need to create:
- Identical local tables on the MergeTree family engine across multiple cluster shards.
- Distributed table on the Distributed engine that uses these shards.
Each local table stores some of the rows from the distributed dataset. The distributed table routes queries to the local tables but stores no data.
The system distributes data across shards using a sharding key. During an INSERT query, the distributed table calculates the sharding key value and routes the query to the relevant local table. The sharding key is defined when creating the distributed table. Examples of sharding keys include table fields, calculated expressions, and random distribution functions.
Choosing the right sharding key is important because it controls:
- How evenly your data is distributed across shards.
- How efficient queries are.
Note
Unlike INSERT, SELECT queries target all cluster shards regardless of the sharding key. To change this behavior, use the optimize_skip_unused_shards setting.
You can distribute table data across all cluster shards or their subset, i.e., a shard group. You can also create a distributed table either for all cluster shards or a specific shard group.
This enables you to flexibly manage sharding in a Managed Service for ClickHouse® cluster.
For examples of different sharding methods, see Table sharding in ClickHouse®.
Sharding management in Managed Service for ClickHouse®
In Managed Service for ClickHouse®, you can create a sharded cluster or enable sharding for an existing cluster.
When you create a non-sharded cluster, the system automatically adds a single shard containing all cluster hosts. To enable sharding for an existing cluster, you need to add at least one more shard.
New sharded clusters can have two or more shards from the start. You can also add more shards to an existing sharded cluster.
Note
You cannot delete a host from a shard if the limit for the minimum number of hosts has been reached.
Replication in a sharded Managed Service for ClickHouse® cluster
In a sharded Managed Service for ClickHouse® cluster, replication is restricted to individual shards, not the entire cluster:
- Each shard stores its own portion of the data.
- All replicas within a single shard hold identical copies of that data portion.
- There is no cross-replication of data between different shards.
In clusters with the coordination service enabled, replication is activated automatically when you create a shard with two or more hosts.
Note
You cannot create a shard with two or more hosts in clusters where the coordination service is disabled.
ClickHouse® is a registered trademark of ClickHouse, Inc.