Horizontal scaling (sharding & replication)

Last updated: Aug 09, 2022

Sharding is a horizontal cluster scaling strategy that puts parts of one ClickHouse database on different shards. This can help you to:

Improve fault tolerance.
Sharding lets you isolate individual host or replica set malfunctions. If you don't use sharding, then when one host or a set of replicas fails, the entire data they contain may become inaccessible. But if 1 shard out of 5 fails, for example, then 80% of the table data is still available.
Improve the query performance.
Requests compete with each other for the computing resources of cluster hosts, which can reduce the rate of request processing. This drop in the rate usually becomes obvious as the number of read operations or CPU time per query grows. In a sharded cluster where queries to the same table can be executed in parallel, competition for shared resources is eliminated and query processing time is reduced.

How to set up sharding and replication

Sharding PostHog ClickHouse is a new experimental feature only supported from PostHog 1.34.0.

To use sharding, first upgrade to version >= 1.34.0 and run the 0004_replicated_schema async migration

Using PostHog Helm charts

Update values.yaml with the appropriate sharding settings.

Example:

YAML

clickhouse:
  layout:
    shardsCount: 3
    replicasCount: 2

PostHog helm charts implement sharding by leveraging clickhouse-operator configuration. Full documentation for this can be found in clickhouse-operator documentation

With external ClickHouse

If you're using an external ClickHouse provider like Altinity.Cloud, you can change sharding and replication settings within that platform.

Note that to propagate all the schemas to the new ClickHouse nodes, you should also do an helm upgrade which creates the right schema on the new nodes.

Rebalancing data

When adding new shards to an existing cluster, data between shards is not automatically rebalanced. This rebalancing is the job of the PostHog operator to do.

Tools like clickhouse-copier or clickhouse-backup can help with this rebalancing.

Questions?

Was this page useful?

Kafka Engine

ClickHouse provides a table engine called Kafka Engine to consume Kafka messages, convert the messages into table rows, and persist the table rows into the destination table. Although a Kafka engine can be configured with multiple topics, a Kafka engine can only have one table schema defined. PostHog uses the Kafka engine type for several tables: Note: SELECT is not particularly useful for reading messages (except for debugging), because each message can be read only once. You will lose…