DEV Community

Cover image for ๐Ÿ› ๏ธ Copying and Moving Data Between Tables in ClickHouse (with Different Engines)
Shahab Ranjbary
Shahab Ranjbary

Posted on

3 1 1 1

๐Ÿ› ๏ธ Copying and Moving Data Between Tables in ClickHouse (with Different Engines)

Sometimes in ClickHouse, you need to move or copy data between tables โ€” especially when you're changing the table engine, for example from MergeTree to ReplicatedMergeTree.

At first glance, it may seem like MOVE PARTITION TO TABLE should do the trick โ€” but in many cases, it doesn't work due to strict requirements. Letโ€™s go over why, and a better approach we used.

โŒ Why MOVE PARTITION TO TABLE Didnโ€™t Work for Us

The ALTER TABLE source MOVE PARTITION TO TABLE dest command physically moves partitions and deletes them from the source table. But it comes with strict requirements:

  • โœ… Same structure
  • โœ… Same partition key
  • โœ… Same primary key
  • โœ… Same order by
  • โœ… Same storage policy
  • โœ… Same engine family (e.g., both must be MergeTree or both ReplicatedMergeTree)
  • โœ… Same or superset of indices and projections

So when youโ€™re trying to move data from a MergeTree table to a ReplicatedMergeTree table, this query will fail because the engines are different.

โœ… Alternative: ATTACH PARTITION FROM

Instead, we used this command:

ALTER TABLE new_table ATTACH PARTITION partition_expr FROM old_table;
Enter fullscreen mode Exit fullscreen mode

This copies the data (without deleting it from the source) and supports different engine types, which makes it ideal for our use case.

โœ… Requirements:

  • Same structure
  • Same partition key, order by, and primary key
  • Same storage policy
  • Indices/projections must match (or be a superset if enforce_index_structure_match_on_partition_manipulation is disabled)

Itโ€™s a safe and clean way to transfer data between tables โ€” even when they use different engines.

๐Ÿงน Final Step: Drop the Old Partition

Once the data is successfully attached and validated, we clean up the original table:

ALTER TABLE old_table DROP PARTITION partition_expr;
Enter fullscreen mode Exit fullscreen mode

This removes the partition from the old table (and on all replicas, if any).

Data is physically deleted after ~10 minutes.

๐Ÿงพ What About ATTACH TABLE AS REPLICATED?

ClickHouse also supports converting a table like this:

DETACH TABLE my_table;
ATTACH TABLE my_table AS REPLICATED;
SYSTEM RESTORE REPLICA my_table;
Enter fullscreen mode Exit fullscreen mode

But we chose not to use this option.

Why?

  • Our goal wasnโ€™t to convert the existing table but to control the replication and migration process manually.

So while ATTACH TABLE AS REPLICATED is valid and useful in some scenarios, it wasnโ€™t the right fit for us.

๐Ÿงช Summary: Best Way to Copy Between Tables with Different Engines

  • ๐Ÿ”ง Create a new table with the same schema, keys, and partitioning.
  • ๐Ÿ“ฅ Use ATTACH PARTITION FROM to copy data.
  • โœ… Validate.
  • ๐Ÿ—‘๏ธ Drop the original partition if needed.

This approach is clean, safe, and works even when the engines are different.

๐Ÿ“š Resources


๐Ÿงก Thanks for reading!
If youโ€™ve used other approaches or faced gotchas while doing engine migrations in ClickHouse, letโ€™s discuss in the comments!

Heroku

Deploy with ease. Manage efficiently. Scale faster.

Leave the infrastructure headaches to us, while you focus on pushing boundaries, realizing your vision, and making a lasting impression on your users.

Get Started

Top comments (0)

ACI image

ACI.dev: Fully Open-source AI Agent Tool-Use Infra (Composio Alternative)

100% open-source tool-use platform (backend, dev portal, integration library, SDK/MCP) that connects your AI agents to 600+ tools with multi-tenant auth, granular permissions, and access through direct function calling or a unified MCP server.

Check out our GitHub!