Move ClickHouse Table to Another Disk: Storage Policy Guide

Moving an existing ClickHouse table to another disk without downtime requires a storage policy that includes both the old and new disks. Once the table is bound to that policy, parts can be migrated either automatically (via move_factor) or manually with ALTER TABLE ... MOVE PART. This guide walks through the full procedure.

1. Prepare the New Device

Mount the new device, create the data directory, and grant ownership to the clickhouse user:

mkdir -p /mnt/disk_1/clickhouse
chown -R clickhouse:clickhouse /mnt/disk_1/clickhouse

Confirm the disk has enough free space for the table and ongoing inserts.

2. Declare the Disk and a Migration Policy

Edit /etc/clickhouse-server/config.d/storage.xml to register the new disk and create a policy that contains both the default disk (as the source) and the new disk (as the target):

<clickhouse>
  <storage_configuration>
    <disks>
      <disk_1>
        <path>/mnt/disk_1/clickhouse/</path>
      </disk_1>
    </disks>
    <policies>
      <move_from_default_to_disk_1>
        <volumes>
          <default>
            <disk>default</disk>
          </default>
          <target>
            <disk>disk_1</disk>
          </target>
        </volumes>
        <move_factor>0.99</move_factor>
      </move_from_default_to_disk_1>
    </policies>
  </storage_configuration>
</clickhouse>

Reload the configuration. ClickHouse picks up storage configuration changes without a restart for adding disks and policies, though removals require one.

3. Switch the Table to the New Policy

ALTER TABLE table_4 MODIFY SETTING storage_policy='move_from_default_to_disk_1';

At this point new parts still land on the default volume (because it is listed first). Existing parts can stay where they are.

4. Trigger the Migration

You have two options.

Manual migration of an existing partition to the target volume:

ALTER TABLE table_4 MOVE PARTITION '202401' TO VOLUME 'target';

ClickHouse does not support a single statement to move all partitions at once. Iterate over the partition list and issue one MOVE PARTITION per entry, or, for finer control, move individual parts:

ALTER TABLE table_4 MOVE PART '202401_1_1_0' TO VOLUME 'target';

You can also rely on move_factor, which automatically migrates parts to the next volume when the current volume passes the configured fill ratio. With <move_factor>0.99</move_factor> set above, parts move only when the default volume is nearly full, so manual moves are usually the better approach for a planned migration.

5. Monitor Progress

Track which parts live on which disk:

SELECT
    disk_name,
    count() AS parts,
    formatReadableSize(sum(bytes_on_disk)) AS size
FROM system.parts
WHERE database = 'default' AND table = 'table_4' AND active
GROUP BY disk_name;

Watch in-flight moves:

SELECT * FROM system.moves;

6. Retire the Old Disk

When system.parts shows zero active parts on the source disk, update the storage policy to drop the default volume so the table no longer references it:

<move_from_default_to_disk_1>
    <volumes>
        <target>
            <disk>disk_1</disk>
        </target>
    </volumes>
</move_from_default_to_disk_1>

Removing a disk from a live policy requires a server restart. Plan a maintenance window or perform a rolling restart of replicas.

After the restart, confirm the layout:

SELECT policy_name, volume_name, disks
FROM system.storage_policies
WHERE policy_name = 'move_from_default_to_disk_1';

Common Pitfalls

• Forgetting to chown the new mount point. ClickHouse silently fails to write parts.
• Setting move_factor too low, which moves parts back and forth as the source disk fills and drains.
• Removing the source disk from the policy while parts still live there. ClickHouse refuses to start and you have to roll back the config.
• Trying to move a table whose storage_policy was never explicitly set. The default policy is implicit, so you must define a named policy first.

Frequently Asked Questions

Q: Can I move a table to another disk without downtime? A: Yes. The new disk and policy can be added live, the ALTER TABLE ... MODIFY SETTING storage_policy is online, and part moves run in the background. Only retiring the source disk requires a restart.

Q: How long does the migration take? A: Roughly the size of the table divided by the lower of the two disks' write throughput. Inserts continue normally during the move.

Q: Is part migration safe against crashes? A: Yes, moves are atomic at the part level. A killed server leaves either the original or the moved part in place, never both.

Q: Can I throttle the migration? A: Yes, use the background_move_pool_size server setting and max_data_part_size_bytes on the volume to limit concurrency and per-move size.

Q: What about replicated tables? A: Run the same procedure on each replica. The storage policy is local to each node, so moves do not propagate through ZooKeeper.