Why ON CLUSTER DDL matters when you run ClickHouse across several nodes
Consider a team running a sharded ClickHouse cluster with replicated tables and Distributed tables on top. They add a column to an events table and run the ALTER on one node. Every other node keeps the old definition, inserts start failing on some hosts, and queries through the Distributed table return inconsistent results. CREATE, DROP, ALTER and RENAME affect only the local server unless `ON CLUSTER` is used. With the clause, the statement is written to a distributed DDL queue in ClickHouse Keeper, and each host picks it up and applies it, which is what keeps schema changes from drifting across nodes.
Teams need ON CLUSTER whenever one logical object exists on several hosts: Distributed tables and their underlying local tables on every shard, ReplicatedMergeTree tables where each replica must hold the same definition, and any database or table that a cluster-wide query path depends on. It also matters for consistency checks. Before you run a change, confirm that every host in the cluster definition is reachable and that each one has the same remote_servers entry and a working Keeper connection, because the queue depends on both. Ask your team to document which cluster name each change targets, so a later engineer does not run an unqualified statement on a single node by mistake.
ON CLUSTER is not the right tool for every change. It does not replace a data migration, a rebalance of rows between shards, or a version upgrade of the server binaries. It also gives you no protection when a host is down for a long time, since the queue holds the statement until that host returns. For a schema that must be kept identical by the database itself, teams often evaluate the Replicated database engine instead, covered in the sibling topic on that engine. Before you adopt either approach, read the ON CLUSTER documentation for the exact behavior of your server version and test the pattern on a non-production cluster.
Which DDL execution settings fit which cluster shape
This table compares the main choices for running schema changes across a ClickHouse cluster: ON CLUSTER with a named cluster, ordered execution with distributed_ddl.pool_size set to 1, higher concurrency, and the Replicated database engine. Read each row against your cluster size, replica layout and tolerance for partial failure, then confirm defaults and names against your ClickHouse version.
| Option | When it fits | Trade-off | Confirm before relying on it |
|---|---|---|---|
| ON CLUSTER with a named cluster | Small clusters where each schema change is applied per statement and the team runs every DDL step by hand | Each statement must be repeated on every change; a host that is down receives the query from the queue when it returns, so its state can lag | The cluster name matches the remote_servers definition on every host, and the Keeper connection works from each node |
| distributed_ddl.pool_size set to 1 | Clusters where statement order on each host matters, such as a sequence of ALTERs that depend on each other | Throughput of queued DDL drops, and a slow statement delays everything behind it on that host | The setting name and default in your server configuration profile, and whether it is applied per host |
| Higher DDL concurrency | Large clusters with many independent tables where statements do not depend on each other and queue delay is the bottleneck | Statements can finish in a different order on different hosts, which makes dependent changes harder to reason about | That each statement in the batch is independent, and how the queue reports partial completion |
| Replicated database engine | Clusters where schema should follow a database automatically as replicas are added, and where teams want fewer per-host DDL steps | Adoption changes how databases are created and has limits that must be checked before migration | Engine limits, support for your table types, and the steps to convert existing databases on your version |
| Monitoring the DDL queue | Every cluster shape, since the queue is where a failed or stuck host shows up | Checking the queue adds an operational step that teams often skip after a change | Which system table or query your version uses to list distributed DDL tasks and their host status |
Setting up ON CLUSTER DDL across your nodes
This sequence prepares a ClickHouse cluster so that a schema change written once with ON CLUSTER reaches every host, and it confirms each prerequisite before the first production change.
Define the cluster identically on every host
The platform or database team adds the same cluster definition under remote_servers on every ClickHouse node, listing each shard and replica by a hostname that all other nodes can resolve. Identical files matter because distributed DDL runs against the cluster name, so a host with a different definition can be missed or addressed incorrectly. Check the result by running a query against system.clusters on each node and confirming that every host returns the same cluster name, shard layout and replica count. Done means the output matches across all nodes, not just the one you are logged into.
Confirm each host reaches the Keeper ensemble
Distributed DDL depends on a ZooKeeper or ClickHouse Keeper connection on every host, because the query queue lives in Keeper. The operator confirms that each node's Keeper or ZooKeeper configuration points to the ensemble and that the connection is healthy, then checks the Keeper-related system tables such as system.zookeeper on each node. Keeper health is a separate concern from DDL, so verify it before any DDL test rather than assuming a working replicated table proves it. Done means every host reads the same Keeper data without errors.
Set distributed_ddl pool size where ordering matters
The platform team sets distributed_ddl.pool_size to 1 in the server or user settings when statements must run in the order they were submitted on each host. The documentation states that ordering on a host is guaranteed only at this setting, so leave the default in place only if your changes are independent of each other. Record the value you chose in the change repository, because a later change to this setting alters the ordering guarantee for every DDL statement. Confirm the active value on each host by querying system.settings or the server configuration, and confirm that it is the same everywhere.
Test with a harmless CREATE using ON CLUSTER
The engineer creates a throwaway database or table, such as a small MergeTree table in a dedicated test database, using CREATE with ON CLUSTER and the cluster name from remote_servers. Use an object that carries no data and that nobody queries, so a failure has no business impact. The statement returns once it is queued and applied, so do not treat the return as proof of success. Note the query_id from the response if your version returns one, because you will need it to look up the statement's status. Done means the statement completes without an error on the initiating host.
Verify the object exists on every host
The engineer checks the test object on each node, either by querying system.tables or system.databases on every host or by using a cluster-wide read such as clusterAllReplicas where it fits the object type. Each host should report the table with the same engine and definition. Also check system.distributed_ddl_queue, which records the state of queued DDL, to confirm the statement finished on each host and that no host shows an exception. Done means every host reports the object and the queue shows completion for all hosts.
Test the failure path with one host down
Before relying on the cluster for production changes, the operations team stops one non-critical node in a maintenance window, runs another harmless ON CLUSTER statement, and then starts the node again. The documentation says queries wait in Keeper for unavailable hosts, so the statement should apply on the returning node after it rejoins. Watch system.distributed_ddl_queue during the test and confirm the change appears on the restarted host. Record what you observed, including any time the queue held the statement, so your runbook reflects your own environment rather than a generic expectation. Done means the restarted host catches up and the test object matches on all nodes.
Document the baseline and the rollback path
The team writes down the cluster definition, Keeper endpoints, the pool size value, and the queries used to verify each host, and stores them with the schema change process. For each production change, the reviewer should ask for the statement, the queue check to run afterward, and the reverse statement to use if a host shows an error. Confirm with your own engineers which of your changes need a reverse DDL and which need a restore from backup instead. Done means the next schema change can be run and checked by someone who did not perform this setup.
Timeouts, failed hosts and partial applies to plan for
An ON CLUSTER statement touches every host in a cluster definition, so one unhealthy host, a slow node or a Keeper outage changes what the statement does. Each card below names a failure pattern, the symptom it produces and the first check to run before retrying, so the team can separate a delayed change from a broken one.
Host down during the statement
When a host is unavailable, the distributed DDL queue keeps the statement entry for that host and applies it once the host comes back. The statement may report success for the hosts that were reachable while the offline host still has the change pending. Check the distributed DDL queue entries in system tables and confirm the host is catching up before you run the same statement again. Confirm the exact queue behavior in the docs for the version you run.
Timeout waiting for slow hosts
The coordinating server waits a bounded time for each host to report completion. A host that is slow to apply a change, often because of a long merge or a heavy query, can make the statement return a timeout error even though the change still applies later. Check the query log and the queue status before retrying, because a repeated CREATE or ALTER may fail with an already-exists or conflict error. Confirm the timeout setting and its behavior in the docs for your version.
Statement succeeds on some hosts only
A statement can apply on some hosts and fail on others, for example when a column type conflicts with local data or a local object already exists. The schema then differs between replicas and shards, and queries can return different results depending on the host they reach. Compare table definitions across all hosts with system tables after every ON CLUSTER change, and treat any mismatch as an incident to resolve before the next change.
Mismatched cluster definitions between hosts
ON CLUSTER resolves the cluster name from each host's own configuration. If one host lists different members, ports or shard layout in its remote_servers section, the same statement can reach a different set of hosts depending on where it runs. Check that the cluster definition is identical on every node, and confirm with a query against the cluster before any schema change. Verify this after each configuration rollout, not only at initial setup.
Keeper unavailable blocking all DDL
Distributed DDL depends on a ZooKeeper or ClickHouse Keeper connection on every host. When the Keeper ensemble loses quorum or a host cannot reach it, new ON CLUSTER statements cannot be queued, so schema changes stop across the cluster even though queries may still run. Check Keeper health and the connection state on each server first, and resolve the ensemble before attempting any DDL. Confirm the exact error text and Keeper requirements in the docs for your version.
Safe practices for schema changes in production
Treat every schema change on a ClickHouse cluster as a change to all replicas and Distributed tables at once, and plan it that way from the start. Before anything runs in production, rehearse the exact statement on a pre-production cluster that has the same cluster definition, the same replica layout and a representative sample of real data, then run the same ON CLUSTER statement there and confirm that every host reports success. The ClickHouse production guidance recommends a pre-production environment with representative data and queries for the same reason. Read the release notes for the version you run before the change, particularly if you are upgrading at the same time, because DDL behavior can shift between releases. Confirm in the ClickHouse changelog for your target version whether any statement you depend on has changed.
Apply one change at a time, and wait for the distributed DDL queue to drain before starting the next. Check the queue through the system.distributed_ddl_queue table and confirm that no host is lagging or failing. Keep distributed_ddl.pool_size at 1 wherever the order of changes matters, because ordering on a host is only guaranteed at that setting. Verify the result on each host after each step, and compare the replicated table definitions with the Distributed table definitions that point at them. A column added to a replicated table but missing from its Distributed wrapper, or a sharding key that no longer matches the table, produces errors that appear only when queries or inserts reach the affected layer. Review both definitions together in the same change record before you approve it.
Do not run a destructive DROP on a table, partition or database in production until a backup of that object exists and a restore of it has been tested on a spare cluster. Replication does not protect against this kind of mistake: a dropped table is removed from every replica by design, so the replicas will faithfully delete the data as well. The ClickHouse backup documentation also warns that a backup never test-restored will probably not restore properly when you need it. Ask your team to confirm the backup schedule, the storage location and the restore runbook for the tables you change, and to schedule restore tests on a spare cluster. A common control is to require a fresh backup as a gate in the change ticket, and to rename a table to a holding name for a defined period before dropping it, so that a mistake can be reversed while the data still exists.
Access and operational controls around cluster-wide DDL
A cluster-wide DDL statement reaches every node in one command, so one mistaken or unauthorized change spreads everywhere at once. The four controls below limit who can issue these statements, keep the cluster definition consistent, leave a record of each change, and protect the Keeper path that carries the DDL queue.
Restrict the default user
The default user starts with all permissions, so any client that connects as default can run ON CLUSTER DDL on every host. Disable or restrict default, create named users, and grant DDL rights through roles so each person or service can run only the statements its job requires. Check that the DDL role cannot alter access control on the cluster, and confirm the workflow in the access control documentation before you change anything in production.
Version the cluster definition
ON CLUSTER only works when every host shares the same cluster definition in its remote_servers configuration. If one host has a stale or edited copy, a statement can skip it or fail on it in ways that are hard to see. Keep the cluster definition in version control, deploy it through the same pipeline as other configuration, and compare the file on each host after a change. Record which definition each release used so a failed DDL can be traced to a configuration difference.
Audit who runs cluster statements
A DDL statement run through ON CLUSTER leaves its effect on every host, so the audit trail has to name the person or service that issued it. Log query activity on the initiating host, tie each statement to a named user and a change ticket, and review the record after each schema change. Ask your team which audit and query log settings are enabled and where they are shipped, because the answer determines whether you can reconstruct a bad change later.
Protect Keeper connections
Distributed DDL depends on a ZooKeeper or Keeper connection on every host. Anyone who can write to that path can change the DDL queue, and a host that loses its Keeper session will queue statements it cannot yet apply. Restrict network access to Keeper ports, use encrypted and authenticated connections where your version supports them, and monitor session health so a partitioned host is found before the next schema change. Confirm the exact Keeper settings for your release in the replication documentation.
Questions teams ask before running schema changes across a cluster
Can our own team run ON CLUSTER DDL without a partner?
Yes, many teams do. ON CLUSTER is standard SQL run from any node, and the work is manageable when the cluster definition is identical on every host and a Keeper connection is healthy everywhere. The question is whether your team has someone who can read the distributed DDL queue, know which hosts are unavailable, and reverse a change that half-applied. If that skill is spread across people who rarely touch the cluster, a short review of your first few changes is often worth more than a long engagement. Confirm your current cluster definition and Keeper health before deciding.
What drives the effort of a schema change rollout?
Effort grows with the number of hosts, the number of replicated and Distributed tables a change touches, and how much review and rehearsal each change needs. A change to one local table on a small cluster is a small task. A change that alters a replicated table, its Distributed counterpart and dependent materialized views across several shards takes longer to write, check and rehearse. Other drivers are how many teams write to the affected tables, whether queries depend on the old column definitions, and how much of the change must be scripted for repeatable runs. Ask any provider for a written scope that lists tables, hosts and the rehearsal steps.
How long does a schema change rollout take?
The duration depends on cluster size, table size, the kind of ALTER involved and how long each host takes to apply it, plus the time spent on review and rollback preparation. Some metadata-only changes finish quickly, while changes that rewrite data on large tables take much longer and may compete with inserts and merges. No fixed duration applies to every cluster, so ask for a timeline that separates review, rehearsal on a pre-production cluster and the production window. Also confirm how the team will handle a host that is down during the change, since the queued statement may run later.
What do we need to provide for a schema change engagement?
The work needs several things from your side before it starts. Access to the environment should be scoped to the cluster, with read access to system tables and the ability to run DDL under a named account. Keeper access should include read visibility into the distributed DDL queue so the team can see pending and failed entries. A pre-production cluster with representative table definitions and a sample of realistic data is needed to rehearse changes. Finally, confirm that recent backups exist and that a restore has been tested on a spare cluster, because the backup documentation warns that untested backups often fail when they are needed.
Which parts of the schema change process cause the most trouble?
Most incidents come from a few predictable causes. A host can be unreachable, so the statement waits in the queue and applies later, which can surprise teams that expected an immediate result. Hosts with different cluster definitions, or with a broken Keeper connection, can end up with mismatched schemas. Running a multi-step change without ordering controls can also leave a table half-migrated. Setting distributed_ddl.pool_size to 1 is the documented way to guarantee order on a host, but it slows work down, so the trade-off should be decided in advance. Check queue status and per-host results after each change rather than assuming success from the first response.
When does an outside partner add value for schema changes?
A partner adds the most value when the change is high risk, the cluster is large, or your team is short of experience with distributed DDL. Typical contributions include reviewing change scripts, building a rehearsal plan on a pre-production cluster, writing rollback steps, checking backup restore tests and staffing the production window. DeployOpen plans, implements, documents and supports self-hosted open-source platforms inside your own infrastructure, and most engagements are fixed-scope with agreed deliverables in writing before work starts. Ask for a written scope that names the tables, the hosts and the evidence you will receive afterward.
What should we ask for in a written scope for schema changes?
Ask for a scope that lists the clusters, the number of replicated and Distributed tables in each change, the rehearsal environment, the rollback steps and the operations that will be monitored during the window. The document should state who runs each step, what evidence is delivered, such as queue records and per-host results, and what happens if a host fails mid-change. Ask how the provider will verify backups before any destructive change. Confirm whether support after the window is included or offered as a retainer, and ask for any figures in writing rather than relying on verbal estimates.
Official ClickHouse documentation on distributed DDL and shards
Before you write your first ON CLUSTER statement in production, read the official ClickHouse reference on distributed DDL. It describes how CREATE, ALTER, DROP and RENAME behave when they run across a cluster, and it is the source to check against when your team's habits and the server's actual behavior disagree. Pay particular attention to the requirement that every host carries the same cluster definition and a working Keeper connection, because a statement that looks correct on one node can still leave another node behind if that requirement is not met.
Then read the ClickHouse guide on table shards and replicas, which explains how a cluster's shards and replicas map onto the tables you create. Use it to confirm that the cluster name you pass to ON CLUSTER matches the layout you have deployed, and to check how Distributed tables and their underlying local tables should be named and created on each host. Ask your platform team to write down which cluster names exist, which hosts belong to each, and who owns changes to that definition, since a schema change is only as consistent as the topology it targets.
Treat both pages as the reference for your own runbook rather than a substitute for testing. Versions change, and the behavior around queue handling, timeouts and the distributed_ddl.pool_size setting should be confirmed against the release you actually run, using a staging cluster with the same topology. Record the version, the settings you changed and the results of your test statements so that an operator who inherits the cluster can see what was verified and when.
How DeployOpen scopes and delivers cluster schema work
DeployOpen treats ON CLUSTER schema changes as a controlled release, not a one-off command. We scope the cluster definition, Keeper dependency and change process in writing, then build, test and document the procedure your team will run against your own ClickHouse nodes.
How we scope
- Inventory every host in the cluster definition and confirm each one reaches the same Keeper connection.
- Agree in writing which DDL statements the procedure covers and which stay local to a single server.
- Define timeout, failed-host and rollback expectations for each class of schema change before work starts.
How we build
- Write and test ON CLUSTER change templates on a pre-production cluster with representative table layouts.
- Verify the distributed DDL queue behavior, including queued statements for unavailable hosts, in your environment.
- Set distributed_ddl.pool_size to 1 where ordering on a host matters, and record the reason in the runbook.
How we hand over
- Deliver a written runbook covering pre-checks, execution, queue inspection, failed-host recovery and rollback steps.
- Walk your operators through a supervised schema change on a non-production cluster, using the delivered runbook.
- Document the Keeper, cluster and user settings the procedure depends on, so changes to them are reviewed first.
How we support
- Ongoing support, upgrades and continuous testing are available as retainers, scoped separately from the build.
- Review schema change procedures again when ClickHouse versions change or the cluster topology grows.
- Confirm with your team which changes need a documented restore test before they reach production.
