When sharding ClickHouse with Distributed tables is the right next step
Sharding is the right next step when one self-managed ClickHouse server no longer holds the dataset or no longer meets query latency, and the options are to scale that server up or to split the data across several servers. A Distributed table spreads data across shards and forwards SELECT queries to them, and it routes INSERTs to the shard that should hold each row. The model is shared-nothing: each shard owns a separate slice of the data on its own server, so no node sees the full table. That gives you more aggregate storage and CPU, but every query now depends on several machines, and the sharding key you choose decides how evenly work and data land across them.
Sharding is a separate decision from replication. Sharding answers how much data and work the cluster can hold, while replication answers what happens when one node is lost. A shard with a single replica still loses its slice when its server fails, so the two decisions are usually made together, and the replication guide on this page covers how to add copies within each shard. Before you commit, confirm what your workload needs from a single larger host, including memory, disk throughput and the number of concurrent queries, and measure it on representative data. Ask whether the queries that are slow can be answered from one shard, because queries that must touch every shard pay the cost of the slowest node.
Sharding also adds operational work that scaling up does not. Schema changes, user grants and cluster definitions must be consistent on every host, and the documented shard and replica guide is the reference for how the cluster definition and Distributed tables fit together. Plan the sharding key and the cluster layout before loading production data, because changing them later means moving data. Write down the growth you expect for the next few years, the query patterns that must stay fast, and the restore path for each shard, then compare that plan with the cost and simplicity of a larger single server.
How to set up a sharded ClickHouse cluster step by step
This sequence takes a set of self-managed ClickHouse servers from separate standalone instances to one logical cluster where a Distributed table spreads data and queries across shards, with a check at each stage that proves the wiring works before real data arrives.
Define the cluster in remote_servers
Your platform engineer adds a cluster definition to the ClickHouse server configuration under remote_servers, listing each shard and each replica within it by hostname and port. Each shard is a group of nodes that hold a distinct slice of the data, and replicas within a shard hold identical copies of that slice. Use stable hostnames rather than IP addresses so the definition survives host replacement. Apply the same definition to every node. Done means every node starts cleanly and returns the cluster when you query the system.clusters table, with the expected shard and replica count on each host.
Prepare local tables on every node
Create the local table that stores each shard's slice of the data on every node, using the same schema, column types and ORDER BY key everywhere. Where you need replicas inside a shard, use ReplicatedMergeTree with Keeper paths that include shard and replica macros, so each shard's copies stay in sync. If you run a single replica per shard for now, a MergeTree engine is enough, but plan for replication before production. Done means the same table definition appears on every node, verified by comparing SHOW CREATE TABLE output across hosts and confirming each node reports an empty table.
Choose the sharding key
Decide which expression determines the shard for each row, such as a tenant identifier, a hash of a stable user or device key, or a time bucket. The key controls how evenly data spreads, which queries can be answered from a single shard, and how hard it becomes to change later. Test the candidate against a representative sample and count rows per resulting value to catch heavy skew. Record the decision in writing with the reason, because changing the key later means moving data. Done means you have a documented expression and a skew check showing no single shard would hold a disproportionate share of rows.
Create the Distributed table with ON CLUSTER
Run CREATE TABLE with ON CLUSTER and the Distributed engine on one node, naming the cluster, the database and the local table, plus the sharding key. ON CLUSTER sends the statement to every node in the cluster, so the Distributed table exists everywhere and each node can accept queries. Distributed tables hold no data themselves; they forward SELECT queries to all shards and route INSERTs according to the key. Done means the table appears on every node, confirmed by querying system.tables on each host for the same engine and cluster name.
Verify routing with test inserts
Insert a small, labelled set of test rows through the Distributed table, using values that cover several key values. Then query the local tables directly on each shard and count the rows, and confirm the total across shards matches what you inserted and that each row landed on the shard the sharding key predicts. Query the Distributed table to confirm it returns the full set. Check system.distributed_ddl_queue or the Distributed table's pending batches if any rows seem missing. Done means local row counts per shard add up to the test total and the distributed query returns the same count.
Confirm queries and clean up test data
Run representative queries through the Distributed table and compare the results with the same query run against a single-node copy of a sample, checking aggregates, joins and any distributed-only behavior your queries rely on. Then truncate the test rows on the local tables, using ON CLUSTER where appropriate, so no synthetic data reaches production. Done means results match the reference within the expected tolerance and the local tables are empty again, recorded in your change log.
Decide whether to shard or scale up first
Before production load, compare the sharded layout against a larger single server using your real query and ingest numbers. Sharding adds routing, a second failure domain per shard, and operational work for every schema change, so it pays off only when a single server cannot hold the data or sustain the load after tuning. Ask your team or partner to document the measured reason for each shard you add. Done means the decision and its evidence are on record before production traffic depends on the cluster.
Sharding key and table options compared
This table compares sharding key strategies and Distributed table settings against the query patterns and data distribution each one fits. Read each row against your ingest pattern and most common filters, then confirm the behavior of each setting on the ClickHouse version you run before you commit.
| Option | Best fit | Main trade-off | What to confirm |
|---|---|---|---|
| Random key (rand()) | Even spread for high-volume inserts with no dominant filter column | Rows for one entity land on many shards, so joins and per-entity lookups fan out to every shard | Shard row counts after a test load |
| Hash of a column (cityHash64 or intHash64 on an entity ID) | Queries that filter or group by one entity, such as a tenant, device or user | Skewed IDs (one large tenant) can overload one shard; adding shards changes the modulo mapping | Key distribution in your data and whether skew is acceptable |
| Expression-based weights | Uneven shard capacity, such as a new shard with more disks or a phased rollout | Weights are set in the cluster definition and need deliberate maintenance as shards change | Weight syntax and behavior for your release in the cluster configuration docs |
| Sharding key on time or date | Time-bounded queries and retention by partition on each shard | Recent data concentrates writes on the newest shard, creating hotspots | Write distribution during peak ingest windows |
| internal_replication = true | Replicated shards where each shard writes to one replica that Keeper then copies | Writes depend on replica coordination, so Keeper health affects ingest | Keeper health and replication lag during test loads |
| internal_replication = false | Cases where the Distributed table must write to every replica directly | Duplicate data risk and higher write load on the client path | Whether your setup needs this option at all |
| insert_distributed_sync and async insert settings | Choosing between waiting for shard acknowledgment and background forwarding | Async forwarding delays visibility and can leave pending batches on disk after failures | Defaults and behavior for your version, and how pending data is monitored |
| Distributed table reads (distributed_* query settings) | Controlling how queries fan out and aggregate across shards | Settings change result shape and load; defaults shift between releases | Query settings and default values for the version in production |
How inserts reach shards and why the sharding key matters
The sharding key decides where each row lands. When an INSERT goes to a Distributed table, the engine uses the sharding expression to pick a shard for every row, so the key you choose is the main lever over data skew. A key with few distinct values, or one dominated by a handful of tenants or devices, concentrates writes on one or two shards and leaves the others idle. Before you create the Distributed table, run a distribution check on a representative sample of production data and confirm that no shard would hold a disproportionate share. Ask your team to document the key, its expected cardinality and the weight of each shard in the cluster definition, because those choices are hard to change later without copying data.
The same key also shapes where joins and aggregations run. Queries that filter or group by the sharding key can often be answered shard by shard with less data crossing the network, while queries that join on other columns may need to move rows between nodes and cost more. Confirm the query patterns that matter most with the team that owns them, then test representative joins and aggregations against the planned layout on a pre-production cluster. Be aware that routing rules are fixed at write time: rows already stored follow the layout they were written under.
Adding a shard is a separate task from the initial design. ClickHouse does not move existing data onto a new shard automatically, so a freshly added shard receives only the rows written after it joins the routing configuration. Plan the rebalancing approach, whether that means weighted writes, moving partitions or copying with INSERT SELECT, before you add capacity, and keep it in its own change record with a restore point. If you cannot tolerate a period of uneven load, confirm with your platform team how long the copy will take on your hardware, since no duration can be assumed from the platform itself.
Trade-offs that come with sharding
Distributed tables spread data and queries across shards, which removes the single-server ceiling but adds coordination work. Each card below names one cost that appears in day-to-day operation, and the check to run before committing to a shard count and sharding key.
Query complexity across shards
A Distributed table fans each SELECT out to every shard and merges the partial results on the initiating node. Aggregations, joins and IN subqueries behave differently than on one server, and some queries move far more data across the network than they did before. Check your heaviest dashboards and joins against a cluster that mirrors your production layout, and confirm how the join and distributed query settings in your version handle them.
Skew from a poor sharding key
A sharding key that concentrates rows on a few values leaves some shards holding far more data than others, so they become the slow path for every query. Low-cardinality keys, or keys tied to a time period, are common causes. Measure row counts and disk use per shard after a realistic load, and test the key on a sample of your real event stream before you fix it in the table definition.
Operational overhead of more nodes
Each shard adds hosts to patch, monitor, back up and secure, and each replica inside a shard adds more. Capacity planning, certificates, disk alerts and upgrade windows all multiply with node count. Ask whether your team can staff this footprint across on-call rotations, and price the monitoring and backup work for every node, not only for the cluster as a whole.
Schema changes need coordinated DDL
A Distributed table is only a routing layer, so the local tables on every shard must match it. A column added on some hosts and missed on others causes failed inserts or silent mismatches. Plan each schema change as a cluster-wide operation, and confirm how ON CLUSTER reports partial failures before you run it against production.
One failed shard affects queries
Without replicas inside each shard, losing a node removes its share of the data from every query that touches that shard. Dashboards may return partial totals or errors depending on settings you chose. Decide in advance whether partial answers are acceptable for each workload, and pair shards with replicas if they are not.
Cost of rebalancing data
ClickHouse does not move existing data onto a new shard automatically. Adding a shard leaves older data where it is, so load stays uneven until you copy or move partitions, or accept that only new inserts spread out. Budget time for that migration as part of the expansion, and rehearse it on a copy of the cluster first.
Questions teams ask before sharding ClickHouse
How much more effort is sharding than scaling one ClickHouse server up?
Sharding takes more effort in almost every dimension, so it should be chosen only when a single server cannot meet capacity or speed needs. Scaling up keeps one node, one schema and one place to look when something goes wrong. Sharding adds cluster definitions, a sharding key, Distributed tables on every node, replicas per shard, and the operational tooling to manage all of them. The effort grows with the number of shards, the number of teams querying the data, and how often the data model changes. Ask your team to document the specific limit a single server hits (disk, memory, query latency or ingest rate) before committing to a design, because that limit determines whether sharding is the right fix at all.
What does sharding require from our internal team?
Your team needs working knowledge of ClickHouse table engines, especially the difference between local MergeTree tables and Distributed tables, and enough Linux and networking skill to run several nodes reliably. Someone must own the cluster definition in configuration, the sharding key choice, and the query patterns that the key supports. Operations staff need to understand how inserts are routed, how a failed shard shows up in queries, and how to read system tables for replication and cluster health. Plan for at least one person who can review schema changes before they are applied across all nodes, since mistakes in a shard key are expensive to reverse.
Can we design and build a sharded ClickHouse cluster in-house?
Yes, many teams do, provided they have the skills above and time to test the design against representative data. The work is well documented in the ClickHouse guides, so the main risk is not the mechanics but the design decisions: which key distributes data evenly, which queries will fan out across shards, and how the cluster will grow. Teams that build in-house usually underestimate the test effort. A partner such as DeployOpen can take on the planning, implementation, testing and handover under a written scope, while your team keeps ownership of the platform afterward. Either way, define who makes the final design decision before work starts.
How are schema changes applied across shards?
Schema changes are applied to every node in the cluster, usually with ON CLUSTER DDL statements that run the same CREATE, ALTER or DROP on each host through a distributed DDL queue coordinated by Keeper. Failed or slow hosts can leave the cluster in an inconsistent state, so the queue, timeouts and failed host handling need to be checked after each change. Alternatively, teams can use the Replicated database engine for schema replication, which has its own limits. Confirm the approach your version supports, test each DDL change on a non-production cluster first, and keep a rollback statement ready for every change.
What drives the cost and duration of a sharding project?
The main drivers are the number of shards and replicas, the complexity of the sharding key and data model, the number of Distributed tables and dependent materialized views, the volume of data that must be redistributed, and the amount of query and failover testing required. Cost components include infrastructure for the extra nodes and Keeper ensemble, engineering time for design and migration, and ongoing operations. One-time work covers design, build, data loading and testing. Recurring work covers monitoring, upgrades, capacity planning and adding shards later. When requesting a quote, ask for the written scope, the list of tables to be sharded, the expected test cases, and how each recurring activity will be handled. DeployOpen provides fixed-scope quotes based on that written scope.
How do we confirm the sharding design before production?
Test the design on a pre-production cluster with data and queries that resemble production, since the ClickHouse production guidance recommends a pre-production environment with representative data and queries. Check that inserts land on the intended shards, that the sharding key spreads rows evenly, that queries through Distributed tables return correct results, and that a query behaves as expected when one shard or replica is unavailable. Practice a schema change using ON CLUSTER and confirm the DDL queue completes. Record the results and sign off the design against the criteria agreed before the test started. Confirm the exact behavior of your chosen ClickHouse version against its documentation before relying on it.
Limits to confirm for your ClickHouse version and environment
Before you commit to a sharded layout, confirm how cluster definitions and Distributed tables behave on the exact ClickHouse version you will run in production. Behavior around remote_servers, sharding weights, distributed DDL and insert routing can differ between releases, and the reference for your build is the ClickHouse docs: Table shards and replicas guide. Read it against your version, not against the latest documentation you happen to find.
Next, check support status. The ClickHouse security policy lists the versions that receive security updates, and any other version is unsupported. Confirm that your target version is on that list before you build, because a shard topology that runs on an unsupported release turns every later upgrade into a forced move. Also read the changelog for each release between your current version and the target. The production FAQ notes that minor backward-incompatible changes can occur, and those changes can affect query results and schema behavior on a Distributed table.
Finally, run a pre-production test with representative data and representative queries. Load a realistic share of your data across the planned shards, confirm that the sharding key spreads rows as you expect, and time the queries that matter, including ones that aggregate across shards. Test a failed shard and an insert during a partial outage, since those paths are where assumptions break. Use the ClickHouse docs: Which ClickHouse version to use in production? page as the baseline for what a pre-production environment should contain, and record the results so the decision to shard rests on measured behavior.
How DeployOpen scopes and delivers a sharded ClickHouse cluster
DeployOpen plans, builds, documents and supports self-hosted ClickHouse inside your own infrastructure, including the shard layout, Distributed tables and insert routing this topic describes. Every engagement starts from a written scope, so the cluster design, the sharding key and the handover material are agreed before build work begins.
How we scope
- Confirm the data volume, query patterns and latency targets that justify sharding over scaling up one server.
- Agree the sharding key, shard and replica count, and which tables are local, replicated or Distributed.
- Record the written scope, deliverables and timeline before any cluster build starts, as fixed-scope work.
How we build
- Define cluster topology in configuration and test ON CLUSTER DDL against a pre-production environment with representative data.
- Create Distributed tables and insert routing, then measure skew across shards with your real key distribution.
- Test query fan-out, failure of a single shard and the path for adding a shard later in a non-production copy.
How we hand over
- Document the cluster definition, sharding key rationale, table layout and the exact DDL used to create each table.
- Provide runbooks for routing inserts, checking shard health and applying schema changes across all nodes.
- Walk your platform and data teams through the design so they can operate the cluster without DeployOpen.
How we support
- Offer ongoing support, upgrades and continuous testing as a retainer after handover, if your team wants it.
- Review changelogs and test each ClickHouse upgrade against the sharded layout before applying it to production.
- Confirm with your team which support hours, response expectations and escalation paths apply before signing.
