Using the Replicated database engine to replicate schema changes without ON CLUSTER
If every CREATE, ALTER or DROP on your self-managed ClickHouse cluster must be repeated with ON CLUSTER, the Replicated database engine can replicate those statements for you. It stores a database's metadata changes in a DDL log kept in ClickHouse Keeper (or ZooKeeper), and every replica of that database executes the logged statements. The engine is based on the Atomic engine, and the server documentation describes one replica of a given replicated database per server. Plan the host layout before you create the database. Teams usually adopt it when the cluster is large enough that a missed ON CLUSTER clause becomes a recurring problem. Check the Replicated database engine documentation for syntax and current limits before you commit.
Metadata replication and data replication are separate, and that difference decides what you must verify. The Replicated engine carries schema and DDL order across replicas through Keeper. Rows move through the tables themselves, and only ReplicatedMergeTree tables replicate their data through their own Keeper paths, so confirm the engine of each table with a query against system.tables after you create it. The Replicated engine does not change the table engine you choose. Replication also does not protect against mistakes: a DROP TABLE or DROP DATABASE runs on every replica, which is why the backup documentation treats backups as a separate control.
Before adoption, run a pre-production test on a topology that matches yours. Create a database with the engine, add a table on one host, and confirm it appears on the others. Then stop one replica, apply an ALTER, and bring the replica back to confirm the change arrives. Ask your team to confirm how DDL behaves for a replica that is down for a long period, how the engine interacts with existing ON CLUSTER scripts, and which statements are excluded from replication. Also confirm the migration path for databases that already exist, because converting live databases carries its own risk and needs its own test.
How the Replicated engine uses Keeper for metadata
The Replicated database engine keeps every replica's schema in step by writing each database-level DDL statement to a DDL log stored in ClickHouse Keeper (or ZooKeeper), then executing that log entry on all replicas of the database. The engine is built on the Atomic engine, so the database itself is the unit of replication. Each server that hosts a replica runs the entries from the log in order, which means a CREATE TABLE issued on one node becomes visible on the others without a cluster-wide statement. The practical consequence is that the Keeper ensemble is on the critical path for schema changes: if Keeper loses quorum, DDL against the replicated database cannot be coordinated until quorum returns. Confirm the exact behavior of your server version in the Replicated database engine documentation before you rely on it for production change windows.
This differs from ON CLUSTER, which sends the statement to each host from the client and records it in the distributed DDL queue in Keeper. With ON CLUSTER, the initiating server tracks completion per host, so a failed or slow host shows up as a queue error that someone must find and resolve. With the Replicated engine, the replicas pull the same log, and the question shifts from "did every host accept this statement" to "is every replica connected to a healthy Keeper and applying the log." Check the Keeper health of each replica, and rehearse a schema change in a pre-production cluster to see how a lagging replica reports itself on your version. Do not assume the two mechanisms share the same error surface during an incident.
The engine replicates metadata and table definitions only. Row data in ReplicatedMergeTree tables is a separate concern, configured per table with its own Keeper paths, shard and replica macros, and insert settings. A replicated database with an unhealthy table replication setup still gives you matching schemas, and a healthy data replication setup does not guarantee that new databases were created everywhere. Plan the two layers separately in your runbook, test a table creation followed by a fresh insert on every replica, and ask your ClickHouse support path to confirm how the DDL log handles a replica that was offline during a long series of changes.
Configuration options and when each fits
This table compares the Replicated database engine with ON CLUSTER DDL, one database per replica group with shared databases, and the Keeper path layout choices. Read each row against your cluster size, how often schema changes ship, and how much operational work your team can carry. Confirm parameter names and defaults in the engine documentation before you write any configuration.
| Decision | What it gives you | Operational cost | When it fits |
|---|---|---|---|
| Replicated engine for schema changes | Metadata is written to a DDL log in Keeper and executed on every replica of the database, so replicas are not left behind by a missed host | Every replica must be a member of the database. You manage Keeper health as a hard dependency for schema work | Teams that want schema replication to follow the database itself, without listing every host in each statement |
| ON CLUSTER DDL for schema changes | Each statement names the cluster and runs across listed hosts through the distributed DDL queue | Each statement must include ON CLUSTER. Failed or offline hosts need manual follow-up, and the team must check the queue | Small clusters with stable membership and a team that already runs ON CLUSTER routinely |
| One database per replica group | Replicated databases map cleanly to one group of replicas, which keeps the replica set easy to reason about | More databases to create, back up and monitor. Cross-group queries need distributed tables or explicit planning | Workloads that already separate by domain, tenant or retention policy |
| Shared database across groups | Fewer databases and a single namespace for applications and tooling | Replica membership and failure impact span more tables. Schema changes affect more of the cluster at once | Small estates where one namespace simplifies access control and migrations |
| Keeper path layout by cluster | Each database or cluster has its own Keeper paths, so ownership and cleanup are explicit | More paths to document, back up and clean up when a database is dropped | Multi-cluster Keeper ensembles where several teams share one ensemble |
| Keeper path layout shared | Fewer paths and a simpler ensemble to inspect | Paths from different teams sit side by side, which raises the risk of accidental overlap and complicates cleanup | Single-team deployments with one Keeper namespace and strict naming rules |
Setting up a Replicated database across replicas
This sequence creates one Replicated database that every replica shares, so a schema change made on one server reaches all of them through the Keeper DDL log. Each step ends with a check, and the team should not move on until that check passes. The result is a database with matching metadata on every replica and a verified record of the change.
Confirm the Keeper ensemble is healthy
Confirm the Keeper ensemble is healthy before any DDL runs. The replication guide recommends at least three Keeper nodes on dedicated hosts in production, with about 4 GB of RAM each, so compare your host list against that guidance. The platform or database team checks that each Keeper node is up, that a quorum exists and that no node logs errors. Done when every node reports healthy and the team has written down the Keeper hostnames, which the Replicated engine depends on.
Verify each replica runs on its own host
Confirm that every replica sits on its own host. One server cannot host multiple replicas of the same replicated database, so a second replica on the same machine will fail to join. Check the inventory for shared hosts, including containers or virtual machines scheduled onto one physical node, and give each replica a unique name. Done when you have a written replica map with one host per replica and the names match what each server will use in its configuration.
Create the Replicated database on the first server
Run CREATE DATABASE with the Replicated engine, which is based on the Atomic engine and stores its DDL log in Keeper. Supply the Keeper path, shard and replica arguments exactly as the Replicated database engine reference documents them. Check the result with SHOW CREATE DATABASE and confirm the database appears in
system.databases. Rollback: if the statement fails partway, check the Keeper path it wrote, remove any partial database on this server, and confirm the path is clean before retrying.Create the database on each remaining replica
On every other replica, run the same CREATE DATABASE statement with the same Keeper path and database name, changing only the replica argument to that server's name. Each server joins the shared DDL log at that point. Check that
system.databaseslists the database with the Replicated engine on every host. Rollback: if a replica fails to join, drop only its local copy, fix the argument or host issue, and retry. Do not remove the Keeper path that other replicas are using.Run a test table across all replicas
Create a throwaway table inside the replicated database on one server, using a name nobody else uses. Run SHOW TABLES on every replica, then insert one row on one server and query it from another to confirm the data path works too. Done when the table and the row appear on all replicas. Rollback: once the checks pass, drop the test table through the replicated database so the production schema stays clean.
Review the DDL log and Keeper health
Check that the DDL statement for the test table is recorded in the DDL log in Keeper and that no entry is stuck or waiting on a host. Recheck Keeper quorum and the replica status in the system tables, looking for replicas that report errors or lag behind. Done when the log shows the statement completed on every host and Keeper still has quorum. If an entry stays pending, stop further schema changes and investigate before continuing.
Choosing between a new cluster and an existing one
A greenfield cluster has no databases yet, so the Replicated engine can be part of the design from the first CREATE DATABASE. Every replica is created with the same database definition, and later schema changes flow through the database's DDL log instead of being repeated on each host. Because no data is at stake at this point, the main work is getting the Keeper ensemble, host names and macros right before any application depends on them.
- Settle Keeper and naming first: Confirm the Keeper ensemble is healthy and that every server has a unique replica identity before creating the first replicated database.
- Create the database once: Plan the engine arguments and the shard and replica names so new servers can join the same database without edits.
- Build the schema through the database: Route table creation through the replicated database so the DDL log records every change.
- Test failure and rejoin: On a non-production cluster, stop one replica, apply schema changes, then bring it back and confirm it catches up.
- Confirm limits with vendor docs: Check current restrictions on server layout, such as the rule that one server cannot host multiple replicas of the same replicated database.
Before production, check the current ClickHouse documentation for the Replicated engine and the exact version you plan to run.
Many teams already apply schema changes with ON CLUSTER and have built runbooks, scripts and habits around it. Moving to the Replicated engine does not require discarding that work, but it changes where the source of truth for schema changes lives. The migration question is which databases to move, and in what order, while keeping the existing ON CLUSTER process working for databases that stay on it.
- Inventory databases and scripts: List every database, every ON CLUSTER statement in deployment tooling, and every host that applies changes.
- Pause schema changes during the switch: Agree a freeze window so no ON CLUSTER DDL runs against a database while its definition is being moved.
- Choose a pilot database: Start with one low-risk database to confirm the workflow end to end.
- Update automation deliberately: Change pipelines so that a database is either managed through the replicated engine or through ON CLUSTER, never both.
- Confirm behavior with vendor docs: Check how the current release handles in-flight distributed DDL and the distributed DDL queue before combining the two methods.
Test each step on a non-production copy of the cluster with the same schema and version.
A cluster that already holds data needs its databases converted without losing tables, parts or replication state. The work centers on the existing database definitions, the replicas that already hold data, and how the new replicated database maps onto them. Treat the conversion as a change with a tested rollback, because a mistake here affects live data rather than just schema.
- Capture the current state: Record table definitions, row counts, replication health and a verified backup before any conversion.
- Map databases to replicas: Confirm which servers will host each replica and that no server would hold two replicas of the same database.
- Rehearse on a copy: Restore the backup to a spare cluster and run the conversion there, timing it and checking every table.
- Verify after conversion: Compare schema, row counts and query results on each replica, then confirm new DDL reaches all hosts.
- Confirm procedure with vendor docs: Check the documented conversion steps and any restrictions for your exact release, since procedures can change between versions.
A restore test on spare hardware is the only reliable evidence that the conversion can be reversed.
Limits to confirm before adopting the Replicated engine
The Replicated database engine moves schema changes from per-statement cluster coordination into a shared DDL log. That shift changes where failures appear, what existing scripts do and how each version behaves, so each limit below is a check to run before the engine goes into production.
One server, one replica
A single server cannot host more than one replica of the same replicated database. Layouts that put two copies of a shard on one host will not work with this engine. Map each database to replicas on separate hosts in your topology plan, and confirm that your host count and replica placement follow that rule before you create anything.
Keeper must be available for DDL
Schema changes are written to a DDL log in Keeper and then executed on every replica. When Keeper loses quorum, DDL statements against replicated databases cannot be recorded or applied. Confirm how your team will detect Keeper outages, and whether your change process can wait for Keeper to recover before deploying schema updates.
Existing ON CLUSTER scripts
Deployment scripts that already append ON CLUSTER to each statement need review before they run against a replicated database. Mixing the two approaches can apply the same change more than once or produce unexpected results. Inventory every migration job, decide which path each one uses, and test the combined behavior on a copy of your cluster.
Tables created outside the engine
Tables that already exist in a database created with a different engine are not converted automatically. Replicated behavior applies to what the engine manages, so older tables may still need their own replication setup. List every existing database and table, record its engine and replication status, and plan the conversion steps as a separate change.
Version-specific behavior
Behavior of the Replicated engine can change between ClickHouse releases, so the version you run in production determines what you can rely on. Read the changelog for each release in your upgrade path and note any change that affects DDL handling, metadata or the engine itself. Pin the version you tested and repeat the check before each upgrade.
Test on a representative cluster
Behavior under failure, replica addition and large schema changes is hard to predict from documentation alone. Build a pre-production cluster with the same replica count, Keeper layout and schema shape as production. Run representative DDL, simulate a Keeper or replica failure and confirm recovery before you move production databases to the engine.
Questions teams ask before adopting the Replicated engine
How much effort does adopting the Replicated database engine take?
Effort depends mostly on how many databases and DDL scripts you already run, and how many of those scripts use ON CLUSTER today. The engine itself is a configuration and migration change: you create databases with the Replicated engine, move existing schema into them, and then change how schema scripts are applied. The larger share of work is usually inventory and testing, not the switch itself. Ask any partner to break the quote into schema inventory, pilot build, cutover steps, rollback planning and documentation, so you can see what drives the total.
Can our own team adopt the Replicated engine without outside help?
Yes, if your team already runs ReplicatedMergeTree tables on a Keeper ensemble and is comfortable reading system tables and server logs. The engine is documented, and the work is mostly SQL and configuration. Outside help tends to pay off when you have many databases, strict change control, or no one who has done a cutover before. Confirm the skills on your team against the official Replicated database engine documentation before deciding, and agree on who owns the Keeper paths afterward.
How long does a pilot take to validate the Replicated engine?
The pilot length is not fixed. It depends on how long your team needs to run representative DDL, watch replica consistency and rehearse a restore or replica rebuild. Set the pilot exit criteria first, such as a set of schema changes applied on every replica without drift, and then time the work against your change calendar. Confirm the expected duration with your own engineers and with any partner you consider, using your real database count as the input.
What does the work need from our environment?
Three things matter most. You need Keeper hosts, because the Replicated engine stores its DDL log in ZooKeeper or Keeper, so a working ensemble must exist before you start. You need a representative schema, meaning real database and table definitions, dictionaries and views, not a toy copy. You also need a spare cluster where you can rehearse changes and restores without touching production. Check the Keeper sizing in the official replication guide, and confirm how much capacity your spare environment needs with your platform team.
Does the Replicated engine replace ReplicatedMergeTree or Keeper?
No. The Replicated database engine does not replace ReplicatedMergeTree, and it does not replace Keeper. ReplicatedMergeTree still replicates table data, and Keeper still coordinates that replication. The Replicated engine only handles database metadata, so that schema changes made through it are applied on every replica of the database. You keep the same Keeper dependency, and you still need replicated tables for data durability. Treat the engine as an addition to your existing replication design, not a replacement for it.
How does the Replicated engine change our ON CLUSTER scripts?
For databases created with the Replicated engine, many DDL statements no longer need an ON CLUSTER clause, because the schema change is replicated through the DDL log to every replica of that database. Existing scripts that still use ON CLUSTER need review, since mixing the two approaches can cause confusion about where a change ran. Plan a script inventory, mark which databases move to the new engine, and test each changed script on the spare cluster before production. Confirm the exact behavior for your ClickHouse version in the official documentation.
What limits should we know before adopting the Replicated engine?
The main documented limit is that one server cannot host multiple replicas of the same replicated database. That affects how you place replicas across hosts, and it should be checked against any cluster layout you plan, including test and single-node setups. The engine is based on the Atomic engine, so review your current database engine choices as part of the plan. Ask your team to read the limits section of the official Replicated database engine documentation and record any that conflict with your topology.
Will adopting the Replicated engine affect our security or compliance evidence?
It can, because schema changes will be recorded and applied through a different path. Your change records should show who approved each DDL change, where it ran, and which replicas confirmed it. Keeper access also becomes more important, since the DDL log lives there. Confirm with your auditors which change and access evidence they expect, and review Keeper access controls before the cutover. DeployOpen prepares systems, controls and evidence for compliance assessments, but independent auditors decide whether the evidence is sufficient.
How DeployOpen scopes and delivers Replicated database adoption
DeployOpen plans, builds, documents and supports self-hosted ClickHouse inside your own infrastructure, including the decision on whether the Replicated database engine fits your cluster. Engagements start with a written scope that states which schema changes move to replicated DDL, which hosts and Keeper nodes are involved, and how the cutover is verified.
How we scope
- We inventory existing databases, ON CLUSTER DDL scripts and deployment tooling that would change under the Replicated engine.
- We confirm Keeper availability, replica placement and the rule that one server hosts only one replica of a given database.
- We agree in writing which databases, clusters, environments and rollback steps are in scope before any work starts.
How we build
- We create test databases on a non-production cluster that matches your topology and run representative DDL through the new engine.
- We script database creation, adding replicas and schema changes so they can be reviewed, version controlled and repeated.
- We test failure paths, including a replica restarting during a schema change, and record the observed behavior for your team.
How we hand over
- We deliver runbooks for creating replicated databases, adding a replica and checking DDL queue health in your environment.
- We document the limits we confirmed for your version, so operators know what the engine will and will not replicate.
- We walk your platform and database teams through the handover and agree the acceptance checks in the written scope.
How we support
- Ongoing support, upgrades and continuous testing are available as retainers after the initial handover.
- We can review changes to the ClickHouse version and the release notes before upgrades that affect database DDL behavior.
- We can extend the runbooks and tests as your cluster grows, including new shards, replicas and Keeper membership changes.
