Why a new ClickHouse shard stays empty until you move data onto it
ClickHouse does not rebalance existing data on its own. When a cluster fills up or slows down and you add a shard, the rows already stored on the old shards stay where they are. Only new writes can reach the new shard, and only if the write path sends them there. Teams that want existing data on the new shard choose between three approaches: weighted writes that favor the new shard for future inserts, moving whole partitions between shards, or copying rows with INSERT SELECT and then removing the originals. Each fits a different situation, and the choice depends on the sharding key, the table engine and the retention policy on the affected tables.
A Distributed table routes new inserts across the shards defined in its cluster, and it does not relocate parts that already exist on other shards. This is the point teams most often miss. A Distributed table can show the new shard as part of the cluster while the new shard holds almost nothing, and queries stay correct but cannot shift load onto the new hardware until data moves. Before picking a method, confirm three things in your own environment: how the sharding key distributes rows today, whether old partitions age out through TTL soon enough to make waiting worthwhile, and whether any table uses a sharding key that would send copied rows back to the wrong shard.
Which rebalancing method fits which situation
This table compares weighted writes, moving partitions and copying with INSERT SELECT across what moves, downtime, cluster load, correctness checks, sharding key impact and retention impact. Read each row as a question to answer before choosing a method, and use the last row to decide when the choice is driven by your data layout rather than by preference.
| Consideration | Weighted writes | Moving partitions | Copying with INSERT SELECT |
|---|---|---|---|
| What moves | Only new data written after the change; existing rows stay where they are | Whole partitions detached from one shard and attached to another, if the table layout allows it | Rows you select from the old shard and insert through a Distributed table or directly to the new shard |
| Downtime | Usually none, since it changes where future inserts route | Brief per-partition lock during detach and attach; confirm exact behavior on your version | None for reads on the source during the copy, but you must manage writes that arrive mid-copy |
| Load on the cluster | Low; adds routing change but no bulk reads or writes | Low to moderate; file-level operations on the source and target hosts | High; full read on source, network transfer and merges on target |
| Correctness checks | Check that new rows land on the intended shards and that the weight configuration matches the plan | Compare row counts and checksums per partition before and after the move, and confirm no duplicates remain | Compare counts and aggregates per partition and key range, and reconcile late writes before removing source rows |
| Sharding key impact | Old data keeps its original placement, so queries that rely on key locality may stay uneven | Works only if the partition key matches how you want data distributed; a poorly chosen partition key limits what can move | Lets you re-hash rows under a new sharding key, the only option here that can change the key itself |
| Retention impact | Old partitions age out on the source as scheduled, so the new shard fills only with new data | Moved partitions carry their TTL state; confirm that retention rules still apply on the target | Retention must be enforced on both sides during the copy or expired rows may be copied unintentionally |
| When it fits | Growing datasets where skew is acceptable and historical balance is not required | Time-partitioned tables where whole periods can be relocated and the partition key already matches your retention cycle | Re-sharding tables, changing the sharding key, or when partition boundaries do not align with the data you need to move |
Using shard weights to steer new writes before moving old data
Weighted writes are the lightest way to put a new shard to work, because they change where new inserts land and leave existing parts alone. The trade-off is time: the cluster only becomes balanced as old data ages out or is removed, so the cards below cover what the approach does, where it fits and what to verify before relying on it.
How weights steer new inserts
A Distributed table can carry a sharding expression and per-shard weights in its cluster definition, so inserts are split across shards in proportion to those weights. Raising the weight of the new shard sends more fresh rows there while history stays where it is. Confirm in the docs for your version how the weight is set in remote_servers and how the sharding expression is applied, since the exact behavior is version-specific.
Old data stays unbalanced
Weights affect new writes only. Parts already on the original shards are not redistributed by changing a weight, so the imbalance shrinks gradually as new data accumulates. Ask the team what share of stored data is still on the old shards at each milestone, and whether the cluster can tolerate that skew for the period it will last.
Fits append-heavy event tables
The approach suits append-only event, log or telemetry tables partitioned by time, where old partitions are dropped by TTL or retention and new data is the main load. Once the old partitions expire, the layout converges on its own. It fits poorly with tables that receive frequent updates to historical rows or that are queried mostly by old data.
Sharding key determines spread
The sharding key decides which shard each row goes to, so a poorly chosen key can keep traffic uneven even with correct weights. For example, a key with few distinct values concentrates rows on a few shards regardless of weight. Confirm the key distribution on your own data before changing weights, and document which key each Distributed table uses.
Verify weights before production writes
Test the weight and key settings on a pre-production cluster with representative insert volume, then count rows per shard after a known batch of inserts. Check the version's docs for how weights are read at runtime and whether a change needs a restart or a configuration reload. Record the chosen weights and the expected convergence period in the change plan so operators can tell normal skew from a fault.
Planning and running a partition move or INSERT SELECT copy
This sequence moves data onto a new ClickHouse shard in verified, reversible stages, so that source data is removed only after the target has been proven complete and reads and writes have been switched.
Inventory partitions and row counts per shard
The engineer responsible for the cluster lists every table that will move, its partitioning key, and the partitions on each existing shard, along with row counts and on-disk size per partition. Queries against the system tables for parts and partitions give these figures, and the new shard's empty state is confirmed. The output is a signed-off inventory that records the expected row total per table and per partition, which becomes the baseline for every later check.
Pick the method for each table
The team chooses between moving whole partitions and copying rows with INSERT SELECT for each table, based on the inventory. Partition moves suit tables partitioned by a clean time key and keep data in whole parts, while INSERT SELECT suits tables whose rows must be re-routed by a new sharding key. Confirm the exact partition-move and copy behavior against the current ClickHouse documentation for your version, because restrictions on table engines and schemas can differ. The output is a written method per table with its rollback approach.
Rehearse the procedure on a pre-production cluster
The same steps run on a pre-production cluster that holds representative data volumes, query patterns and schema versions. The team records elapsed time, errors, resource use during the copy, and any query results that change during the switch. Pre-production should match production's ClickHouse version, as the production FAQ recommends testing with representative data and queries before a change. The output is a tested runbook with observed row counts that match the baseline.
Pause or throttle ingestion before the move
The team decides, with the owners of each producer such as Kafka consumers or batch loaders, whether writes to the affected tables are paused, throttled or buffered during the move. Writes that land on the source after the copy starts would be missing from the target unless they are replayed. The check is that producer metrics show no new rows arriving for the affected tables, and that the row count on the source is stable across two readings. The output is a documented ingestion window and a replay plan for any buffered data.
Move or copy the data in batches
The engineer runs the partition move or INSERT SELECT copy one partition at a time, logging each partition's start and completion. Batches keep memory, merge pressure and replication traffic within limits and make a failed partition easy to retry without repeating the whole table. Each completed partition is checked for errors in the server logs and for replication lag on the target. The output is a log showing every partition finished with no unresolved errors.
Verify row counts and checksums per partition
For every partition, the team compares row counts between source and target, then compares a checksum computed over the same columns and ordering on both sides. Partition-level comparison makes a mismatch traceable to a single batch. Mismatches stop the process and trigger a retry or investigation before any further batch runs. The output is a verification report that shows matching counts and checksums for each partition, or a documented exception with its resolution.
Switch reads and writes to the new layout
The team updates the Distributed tables, cluster definitions or application connection settings so reads and writes use the new shard layout, then resumes ingestion with the replayed data. The old source copies stay in place during this period so that a switch back is possible. Checks include a sample of queries that compare results against the baseline, and confirmation that new inserts land on the intended shards. The output is a live layout with query results matching the verification report.
Drop source data only after a rollback point
Source partitions are dropped only after the team agrees a rollback point, which should be a tested backup of the affected tables taken after verification and before any drop. The team confirms the backup can be restored on a spare cluster, since an untested backup may not restore when needed. Source partitions are then removed in the same per-partition order used for the copy, with the check repeated after each drop. The output is a closed change record with the verification report, the backup identifier and the list of dropped partitions.
How partition moves and INSERT SELECT differ in practice
Partition operations move whole partitions between tables instead of selecting and rewriting rows. In MergeTree tables, a partition is the set of data parts that share one partition key value. The ALTER TABLE partition clauses let you move, attach or replace those parts from one table to another when the two tables are compatible. Because the parts already exist in sorted, compressed form, the server handles the move mostly at the file and metadata level, so the work depends on the number of parts and their size more than on the number of rows.
- Whole partitions only: you select data by partition key value, so every row in a chosen partition moves together.
- Server and host scope: many partition moves run between tables on the same server. Confirm whether your version supports the clause between hosts before you plan a cross-shard move.
- Source loses the data: a moved partition leaves the source table, so the source no longer holds it once the operation completes.
- Small batches: moving partitions in groups by time range keeps each step easy to verify and to repeat.
- Pre-staged data: the method fits historical partitions already loaded into a staging table on the cluster.
Before planning a batch, run the exact clause on a staging copy in your own version and confirm the row counts on both sides.
INSERT SELECT reads rows from a source table through a query and writes them into a target table. The rows pass through the query engine and the normal insert path, so the target builds new parts from them. The method works when the target is a table on the new shard or a Distributed table that routes writes to it. It does not require the physical partition layout to match, which makes it the more flexible option. The cost is that every row is decoded, optionally re-sorted and re-compressed on the way in, so the work grows with the volume of data copied.
- Transformation and filtering: the SELECT can filter by date, drop columns, reshape values or change the ordering to match the target sorting key.
- Batching and resume: split the copy by partition or date range so a failed batch can be retried without redoing the whole move.
- Duplicate risk: rerunning a batch without a precise filter can insert the same rows twice, so each batch needs a range that can be verified and repeated.
- Source stays in place: the source keeps its rows until you remove them, so storage must hold both copies during the copy.
- Heavier processing: the copy uses more CPU, memory and network than a partition move.
Test one batch on a staging copy, compare row counts for the same range on both sides, and confirm how your version writes to a Distributed target.
Both methods assume that the target can hold the data in a compatible logical shape, but the requirements differ. Partition moves need the destination table to match the source closely, including column names, data types, partition key expression and sorting key. INSERT SELECT needs the column list to map correctly, while the physical layout can differ. The sharding key matters for both methods. Once rows land on the new shard, queries that depend on which shard holds a given key must return the same results, so new writes and migrated data must use the same routing expression.
- Matching table structure: compare the output of
SHOW CREATE TABLEon both tables before any partition operation. - Partition key compatibility: the partition expression must produce the same key values on the target, or moved partitions will not line up with the target's partitioning.
- Sorting key differences: partition moves keep the existing ordering, while INSERT SELECT writes rows in the target's sorting key.
- Sharding key consistency: new writes and migrated rows should route through the same sharding expression, and the Distributed table definition should reflect it.
- Replicated targets: if the target uses ReplicatedMergeTree, confirm that moved or inserted data reaches every replica before you count the copy as complete.
Record the partition expression, sorting key and sharding key for both tables and compare them before you choose a method.
The two methods load the source and target in different ways. Partition moves read little from the source and write little to the target, because existing parts are reused. Their main effects are disk I/O and brief contention on the affected partitions. INSERT SELECT scans the full source range, decodes the rows and writes new parts on the target, so both nodes carry CPU, memory and network cost while the copy runs. After the copy, background merges on the target combine the many small parts that batch inserts produce, and that work can continue for some time.
- Source read pressure: an INSERT SELECT scan competes with live queries, so run it off-peak or limit its concurrency.
- Target merge backlog: frequent small inserts create parts that merges must consolidate, so watch the merge queue on the target.
- Network transfer: a cross-host copy sends every row over the network, which can saturate links shared with replication traffic.
- Replica fan-out: on a replicated target, each inserted block is also replicated to the other replicas, multiplying the write load.
- Resource caps: settings profiles and query limits can cap memory and threads for the copy. Confirm the setting names and their defaults on your version.
Measure CPU, disk, network and merge activity on both nodes during a trial batch, then size later batches from those measurements.
The right method depends on what the data looks like on the source and what the target must hold afterward. If the data already sits in a table with a matching structure, the partition key aligns and no transformation is needed, a partition move usually involves less processing. If the copy must filter rows, change types, reorder data for the new sorting key or move data across hosts that the partition operation does not reach, INSERT SELECT is the better fit. Many migrations use both: partition moves for clean historical ranges and INSERT SELECT for the remainder.
- Clean historical ranges: partition moves suit whole periods that already match the target layout.
- Mixed or transformed data: INSERT SELECT suits data that needs filtering, reshaping or a new ordering.
- Verification plan: whichever method you choose, define how you will compare counts and checksums for each batch.
- Recovery point: take a backup of the source and target before large moves, so an error has a restore path.
- Version review: confirm each clause and setting against the documentation for the exact ClickHouse version you run.
Before approving a plan, ask the team to show the exact statements, the batch boundaries and the verification query they will run after each step.
Security and data-integrity risks during a shard move
A shard move copies data between servers while writes and access continue, so each risk below can corrupt results, expose data or leave no recovery point. The cards cover duplication, cutover, access drift and backups, and they connect through one rule: confirm a verified backup exists before any source data is dropped.
Duplicate rows on retried copies
An INSERT SELECT or partition copy that fails partway and is rerun can write the same rows twice, and ClickHouse will not detect that on its own. Before the copy starts, record row counts and checksums per partition on both source and target, and define which partitions are safe to rerun. After each run, compare those figures so a retry never inflates totals silently.
Lost writes during cutover
If producers keep sending inserts to the old layout while the copy runs, rows written after the copy snapshot can be missing from the new shard. Decide how writes are paused, buffered or redirected during the switch, and confirm which component owns that decision. Check the final row counts against the source after writes have been redirected, not before.
Users and row policies that differ by shard
Access control in ClickHouse is defined per server, so users, roles, row policies and quotas created on one shard do not appear on another unless they were deployed there too. A new shard can therefore expose rows or allow queries that the original shard blocked. Compare access objects on every node before and after the move and treat any mismatch as a blocker.
Access to the copy path
The account that runs the copy typically has read on the source and write on the target, which makes it a high-value credential during the move. Give it a dedicated user with only the grants the copy needs, restrict its network path, and remove it once the move closes. Review the default user, which starts with all permissions, so it cannot be used to bypass the copy controls.
Backups before and after the move
Replication keeps copies of data in sync, so a bad delete or a faulty copy spreads to every replica. Take a backup of the affected tables before the move and another after validation, and keep both until the new layout has run cleanly. The backup documentation warns that a backup never test-restored will probably fail when needed, so restore the backup to a spare cluster and compare counts before dropping any source partition.
Verification before any drop
Dropping source partitions is the one step that cannot be undone from the cluster itself. Make it a gated action that requires matching row counts, matching checksums on sampled partitions, a successful test restore and a named approver. Write the gate into the change plan so the decision does not depend on whoever is running the move that night.
Questions teams ask before rebalancing a ClickHouse cluster
Does ClickHouse rebalance data automatically when I add a shard?
No. A new shard receives only the data written to it after it joins the cluster unless you move existing data yourself. Existing parts stay on the original shards, so reads spread unevenly until you act. The effort of closing that gap depends on how the table was designed, which is why the first step is an inventory of table engines, sharding keys, partitions and write paths rather than a copy command. Confirm the current behavior for your release in the ClickHouse docs and in your own test cluster before you plan the move.
What drives the effort of moving data onto a new ClickHouse shard?
Four factors shape most of the work:
- Data volume and part count, which set copy time and the load on existing nodes.
- Sharding key and partitioning, which decide whether whole partitions can move or every row must be re-routed.
- Downtime tolerance, which decides whether writes can pause or must keep flowing during the move.
- Verification depth, meaning how thoroughly row counts, checksums and query results are compared before cutover.
Each factor raises or lowers effort independently. Ask any partner to show how each one affects the proposed method and the test plan.
Which rebalancing method should we choose for our cluster?
The right method depends on the workload. Weighted writes change where new data lands, which suits append-heavy tables where historical skew is acceptable. Moving partitions suits tables partitioned by time, where whole partitions can be detached and attached elsewhere. Copying with INSERT SELECT suits cases where rows must be re-sharded by a new key, but it costs the most compute and needs careful verification. Some clusters combine methods. Use the sibling topic on Distributed tables to confirm how your sharding key routes inserts before you choose.
What does the work need from our own team?
Your team needs to supply access and context that no outside party can infer:
- Named owners for the cluster, the data sources that write to it and the dashboards that read from it.
- Agreed windows for maintenance, plus the write and read patterns that must continue during the move.
- A pre-production cluster with representative data and queries, because the production FAQ recommends testing before changes.
- Current backup coverage and a tested restore path, so a failed move can be rolled back.
Without these inputs, the plan will rest on assumptions that surface late in the project.
Can our own engineers rebalance a ClickHouse cluster without a partner?
Yes, if the team has hands-on ClickHouse operations experience, a staging cluster that matches production closely enough to test the method, and time to verify results before cutover. Teams with that profile usually handle simple partition moves in-house. Plan for an engineer to read the changelog for the target release first, because minor backward-incompatible changes can occur. Bring in a partner when the sharding key must change, when downtime is tightly limited, or when the team lacks capacity to run a restore test alongside the move.
When should we bring in a partner for a shard rebalance?
Bring in a partner when the decision carries more risk than your team can absorb alone. Common triggers include a required change of sharding key, large tables with strict read and write continuity, regulated data that needs documented verification, or a cluster that already shows replica or Keeper problems. DeployOpen plans, implements and verifies this kind of work inside your infrastructure under a written, fixed scope, and the quote follows from that scope. Ask for the verification steps, rollback plan and acceptance criteria in writing before any work begins.
What should a quote for rebalancing include?
A useful quote separates one-time work from anything that recurs. One-time items usually cover the inventory, method design, staging rehearsal, the cutover itself and verification. Recurring items may include monitoring changes or follow-up support if you choose a retainer. Ask for each deliverable to name the tables in scope, the method per table, the verification checks and who signs off. Ask how the quote changes if data volume or downtime limits change during planning. Compare quotes on that basis rather than on headline totals.
How do we verify that data moved correctly?
Verification compares the source and destination at several levels: row counts per partition, checksums or aggregate comparisons on key columns, and the results of representative queries before and after the move. The depth you need depends on how the data is used and how much risk a discrepancy carries. Keep a restore-tested backup before any move, since the backup documentation warns that an untested backup will probably not restore properly when needed. Confirm the exact checks, tolerances and sign-off steps with whoever owns the data.
What DeployOpen needs from your team to plan a rebalance
Start by giving us your current topology: the cluster definition from remote_servers, which hosts hold each shard and replica, how the new shard was added, and whether Distributed tables already route inserts to it. Alongside that, send the table definitions for every ReplicatedMergeTree and Distributed table involved, including the sharding key, the partition key and the ordering key. The partition layout matters most. Share row counts and on-disk sizes per partition for the last several months, so we can see which partitions are large enough to move and which are small enough to copy in a single pass.
Next, tell us your retention rules and your ingestion sources. Retention determines which partitions will be dropped while the work runs, and a TTL that fires during a copy can change the result. List every producer that writes to the cluster, such as Kafka consumers, batch loaders or application inserts, and say how each one handles failures and retries. Your backup status is equally important: share the date and location of the latest full backup, whether incremental backups run, and when a restore was last tested. A rebalance changes data placement, so a verified restore point should exist before anything moves. Confirm these facts with your own team rather than assuming them.
A pre-production environment with representative data is the last input and the one most often missing. Ask whether a staging cluster with a comparable shard layout exists and how much of your production data it can hold, even in anonymized or sampled form. We will use it to test the chosen method, measure duration and check query results before production is touched. Scope and deliverables are agreed in writing before work starts, and a quote follows from that written scope. Expect the quote to reflect the number of tables, the volume of data to move, and whether the staging work is included. Ask us to state those assumptions explicitly in the document.
How DeployOpen scopes and delivers a ClickHouse rebalance
DeployOpen approaches rebalancing data after adding a ClickHouse shard as a written, fixed-scope engagement inside your own infrastructure. We start by mapping the existing table layout, sharding key and data volumes, then choose among weighted writes, partition moves or INSERT SELECT copies based on what your cluster can tolerate. Each step is tested on representative data before production work begins.
How we scope
- Inventory the cluster, shard layout, sharding key and Distributed table definitions before choosing a method.
- Record which tables need data moved, how much, and whether queries must stay available during the move.
- Agree in writing on the method, the rollback point, the verification checks and the change window.
How we build
- Run the chosen method first on a copy of representative tables in a pre-production cluster.
- Compare row counts and checksums between source and target partitions before and after each move.
- Test that Distributed table reads and inserts behave correctly during and after the rebalance.
How we hand over
- Deliver the runbook with each command, its expected output, and the step at which to stop.
- Document the final data distribution, the routing settings left in place, and the cleanup steps taken.
- Walk your operators through the verification queries so they can repeat the checks themselves.
How we support
- Ongoing support, upgrades and continuous testing are available as a retainer after handover.
- Support can cover future shard additions, so the same runbook and checks are reused each time.
- Confirm with us during scoping which post-handover support, if any, your team wants to retain.
