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®Sharding specifics in Managed Service for ClickHouse®

To implement distributed storage of table data in Managed Service for ClickHouse®, you need to create:

1. Identical local tables on the MergeTree family engine across multiple cluster shards.
2. 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.

You can distribute table data across all cluster shards or a shard group. A shard group may include both cluster shards and external shards. You can create a distributed table either for all cluster shards or a specific shard group.

For examples of different sharding methods, see Table sharding in ClickHouse®.

External shardsExternal shards

You can add new shards of external ClickHouse® clusters (so called external shards) to the shard groups of your Managed Service for ClickHouse® cluster. You can use such shards to solve the following tasks:

• Running distributed queries to an external cluster. To do this, create a shard group consisting only of external shards.
• Running distributed queries to a local cluster and to an external one simultaneously. To do this, create a shard group consisting of local and external shards.

When running INSERT and SELECT queries to a distributed table, external shards take part in query processing under the same rules as local cluster shards.

For a shard group with external shards, DDL queries are not supported.

You can add external shards when creating or updating a shard group.

Sharding management in Managed Service for 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.

Replication in a sharded Managed Service for ClickHouse® clusterReplication 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.

ClickHouse® is a registered trademark of ClickHouse, Inc.