ReplicatedMergeTree replication setup

Configuring ClickHouse ReplicatedMergeTree replication for your cluster

ClickHouse replicates data through ReplicatedMergeTree tables, coordinated by ClickHouse Keeper, which replaces ZooKeeper as the coordination service. The server itself is open source under the Apache License 2.0, so the replication design is something your team builds and owns. The work sits in three places: the table definitions, the Keeper paths that identify each replica, and the quorum and health settings that decide what a write means and whether a lagging replica is noticed. A team needs this setup once a single server is a business-level point of failure, meaning an outage would stop reports, ingestion or customer-facing queries that the organization cannot tolerate. For a development environment, a test dataset or a workload where rebuilding from source data is acceptable, a single node is usually the honest choice.

Each replicated table takes a Keeper path and a replica name. In production these are normally built from the {shard} and {replica} macros defined in each server's configuration, so the same DDL runs unchanged on every node. Keeper paths must be unique per table and per shard, and a mistake here is hard to undo once data is written. Insert quorum is the setting that trades write latency for durability: with insert_quorum set, an insert is acknowledged only after the chosen number of replicas confirm it. Digest ACLs control who can read or modify the Keeper nodes that hold this metadata, which matters because anyone who can change those paths can disturb replication. Confirm the exact setting names and defaults for your server version in the ClickHouse replication documentation before you write production DDL.

Replication also needs an operating check. Query the replication system tables on each node, watch replica queue length, absolute delay and read-only status, and alert on them before a user reports stale data. A replica that restarts is expected to resync missing data from a healthy peer, but that is not a backup: replication copies mistakes, deletions and corruption to every replica. Plan a tested backup alongside the cluster, and confirm how your chosen version reports replica health before you rely on any alert threshold.

Replication options and when each fits

This table compares the main table-level choices for a ReplicatedMergeTree table: the Keeper path, the replica name, insert quorum and digest ACLs. Read each row for what it protects, what it costs in insert latency or operations, and the situation where a team should choose it. Confirm defaults for each setting against the replication guide for your ClickHouse version before you rely on them.

Option	What it protects	Cost in latency or effort	Choose it when
Keeper path with shard and replica macros	Keeps each shard's replicas in one Keeper namespace, so replicas find each other and data never mixes between shards	Low once set; a wrong path is hard to change later and needs a recreated table	Every replicated table. Use the shard and replica macros from each host's config so one DDL statement works across nodes
Explicit, hand-written Keeper path	Same grouping as macros, but the path is fixed in the statement and does not depend on host config	Low, though every table definition repeats the full path, which invites copy errors	Few tables, or when a team wants the path visible in every CREATE statement
Replica name	Identifies each replica inside a shard so Keeper can track its log position and parts	Low; must be unique per replica, and a reused name can confuse recovery	Always set through the replica macro so names follow the host automatically
insert_quorum off (default behavior, confirm for your version)	Inserts return once one replica accepts the data, so a single failure right after the write can lose that batch before other replicas copy it	No added latency; the lowest write cost	Logs, telemetry or events where losing a small recent batch is tolerable and ingest speed matters most
insert_quorum set to a replica count	An insert returns only after the stated number of replicas confirm it, so the write survives the loss of a replica that acknowledged it	Each insert waits for more replicas, which adds latency; with fewer live replicas, inserts can fail	Data that must not be lost once acknowledged, and where the team accepts slower or failing writes during replica outages
Digest ACLs on Keeper paths	Restricts who can read or change replication metadata in Keeper, which limits damage from a compromised client or misconfigured tool	Moderate; ACL setup, credential rotation and Keeper config must be kept in sync across all nodes	Keeper is shared with other systems or reachable from wider networks, or an assessor expects access control on coordination data
Digest ACLs left unset	Keeper paths rely on network isolation and Keeper's own access settings alone	Lowest setup effort and fewer moving parts during upgrades	Keeper runs on a dedicated, tightly firewalled network and the team has no shared-tenant concern

Creating a replicated table step by step

This sequence takes a self-managed ClickHouse cluster from a healthy Keeper ensemble to a ReplicatedMergeTree table that accepts writes on one replica and shows the same rows on every other replica. Each step ends with a check you can run before moving on.

Confirm the Keeper ensemble is healthy
Before any table is created, check the ClickHouse Keeper ensemble that will coordinate replication. The replication guide recommends at least three Keeper nodes, on dedicated hosts in production, with about 4 GB of RAM each. Confirm that every Keeper node answers its four-letter status commands, reports a leader or follower role, and that the ensemble has exactly one leader. Also confirm that each ClickHouse server can open a connection to every Keeper node on the client port defined in its configuration. The step is complete when all nodes agree on the leader and a test connection from each ClickHouse host succeeds. If any node is missing or disagrees, stop here, because replicated tables will not be created or coordinated correctly until quorum is restored.
Define shard and replica macros on each server
Each ClickHouse server needs a macros section in its configuration that sets the shard and replica identifiers used in Keeper paths. Typically the shard value is the same across replicas of one shard, and the replica value is unique per server within that shard. Choose names that are stable across restarts and reflect the hostname or role, because changing them later means changing the coordination paths. Apply the configuration on every node, then query the system.macros table on each server. The check passes when each node returns the expected shard and replica values, and the two values on any pair of replicas in the same shard differ only in the replica name. Do not proceed if a macro is missing, since the create statement in the next step will fail or write to the wrong path.
Create the ReplicatedMergeTree table with macro paths
Run a CREATE TABLE statement that uses the ReplicatedMergeTree engine with two arguments: a Keeper path and a replica name. Use the macros in the path, for example a path built from the table name and {shard}, and use {replica} as the replica name. Keep the Keeper path identical in every replica's statement so that all copies join the same replication group. Choose the table's ORDER BY key and partitioning with your query and retention needs in mind, since these are hard to change after data arrives. Run the statement on the first replica only at this point. The check is that the table appears in system.tables on that server, with the engine shown as ReplicatedMergeTree and the expected Keeper path, and that the table is registered under the Keeper path when you list the child nodes in Keeper.
Create the same table on every replica
Run the identical CREATE TABLE statement on each remaining replica in the shard, changing nothing except the local host context. The replicas will register under the same Keeper path with their own replica names and begin coordinating. This step is where configuration mistakes most often appear, such as a typo in the path, a missing macro, or a different column definition. Compare the output of SHOW CREATE TABLE across all replicas to confirm the schema text matches. Then check system.replicas on each server: the row for the table should show the replica as active, the expected number of replicas in the ensemble, and no read-only flag. If a replica reports read-only or is absent from the Keeper path, pause here, read its error in the server log, and fix the configuration before any data is written.
Run a test insert and confirm replication
Insert a small, clearly labeled set of test rows on one replica, for example a few rows with a marker value that identifies this test. Wait for the insert to complete, then query the table on every replica and compare row counts and the marker values. Using SYSTEM SYNC REPLICA on the other replicas before querying gives a deterministic answer for the test. Check system.replication_queue on each replica to confirm no entries are stuck, and check system.replicas for a small or zero queue size and a stable absolute delay. The step is complete when every replica returns the same rows. If a replica does not catch up after a reasonable wait, pause and inspect its replication queue and log entries before loading real data, since a replica that cannot fetch parts is usually a network, permission, or Keeper connectivity issue.
Check replication health in system tables
Finish with a full health review using system tables rather than a single query result. Inspect system.replicas for is_readonly, is_session_expired, absolute_delay, queue_size, and total_replicas versus active_replicas on every replica. Review system.replication_queue for failed or repeatedly retried entries, and system.replicated_fetches if a resync is in progress. Confirm that the Keeper ensemble still reports its expected members. Record the output in your runbook as the baseline for this table, so later comparisons show whether behavior changed after upgrades or configuration edits. The step is complete when all replicas report active, no read-only flags, and no stuck queue entries. Keep the test rows in place only until the baseline is recorded, then remove them with a labeled delete or a partition drop so that test data does not reach production reports.

How insert quorum trades write latency for durability

An insert with quorum is acknowledged only after the configured number of replicas hold the data, so a single node failure right after the write does not lose it. In ReplicatedMergeTree this is controlled by the insert_quorum setting, which can be set per session or per query. Without it, the server acknowledges the insert once one replica has accepted the data, and the other replicas fetch it asynchronously. That default is faster, but a crash in the window before replication completes can leave the newest batch on one node only. Confirm the exact semantics of insert_quorum and its companion settings for your server version in the replication documentation before you rely on them in a runbook.

The cost is paid in two places. Each insert waits for acknowledgments from more replicas, which adds latency to every batch and matters most for small, frequent inserts. The second cost is availability: when enough replicas are down that the quorum cannot be reached, quorum inserts fail rather than succeed on fewer copies. Teams running event or telemetry ingestion often split the decision by table. Raw event tables that feed a buffer or a message queue can usually be replayed from the source, so asynchronous replication may be acceptable there. Tables holding records that cannot be re-created, such as billing or audit events, are better candidates for quorum. Write the choice down per table, with the owner and the replay path for each asynchronous table.

Test the setting before production with write load that resembles your real traffic: batch sizes, insert frequency, number of concurrent writers and the replica count you plan to run. Measure p95 and p99 insert latency with quorum on and off, then simulate a replica outage and confirm which writers see errors, how your ingestion client retries, and whether any batch is duplicated or dropped. The ClickHouse production FAQ recommends a pre-production environment with representative data and queries for this reason. Ask your team or partner to record the test results alongside the chosen quorum value so the trade-off can be revisited when hardware or volumes change.

Securing Keeper paths with digest ACLs and network controls

Replicated tables depend on metadata stored in ClickHouse Keeper, and anyone who can write to those paths or reach the Keeper ports can disrupt replication. The controls below work together: they limit who can change coordination state, where traffic can come from, what hardware Keeper runs on, and how credentials and DDL are handled. Confirm each one against your own network and access model before you rely on it.

Digest ACLs on Keeper paths

Digest ACLs restrict which clients can read or modify the znodes that hold a replicated table's metadata, such as the replica list, log and queue entries. Without them, any client that can reach Keeper may be able to alter replication state for tables it should not manage. Check that the ACL is set on the paths your ReplicatedMergeTree definitions use, that the credential is scoped to ClickHouse servers, and that your ops tooling does not run Keeper commands with broader access. Confirm that no other application shares the same Keeper ensemble without its own paths and credentials.

Keeper network access restriction

Keeper listens on a client port for ClickHouse servers and a separate port for Raft traffic between ensemble members. Allowing either port from general application or user networks widens the set of hosts that can talk to the coordination layer. Limit client access to the ClickHouse server hosts and limit Raft access to the other Keeper nodes, using firewall rules or security groups. Confirm the actual port numbers in your configuration and test that a host outside the allowed list is refused.

Dedicated hosts and separate disks

The replication guide recommends dedicated hosts for Keeper in production, with about 4 GB of RAM each. Sharing hosts with heavy ClickHouse merges or other workloads can starve Keeper of memory or disk I/O, and slow Keeper writes can stall inserts into replicated tables. Keep Keeper log and snapshot data on a disk that is not shared with ClickHouse data parts. Confirm the RAM, CPU and disk headroom of each Keeper host under peak insert load before go-live.

Protecting replication credentials

Replication settings and the digest credentials used for Keeper paths are secrets. If they appear in server logs, in system table output, or in configuration files exported to a shared repository or ticket, anyone with that access can authenticate to Keeper. Store the values in a secrets manager or restricted files with tight permissions, mask them in log pipelines, and exclude them from config backups that leave your control. Ask who can read `SHOW CREATE TABLE` output and the server configuration directory in your environment.

TLS for inter-node traffic

Traffic between ClickHouse servers and Keeper, and between Keeper members, can carry replication metadata and credentials in clear text if TLS is not configured. Enabling TLS where your ClickHouse and Keeper versions support it protects these exchanges from interception on shared networks. Plan certificate issuance, renewal and trust store updates on every node, since a missed renewal can take replication offline. Confirm which TLS options your target version supports for Keeper and cluster connections before you design the certificate chain.

Review of DDL on replicated tables

A DDL statement run against a replicated table, or through ON CLUSTER, can change schema on every replica at once, and a mistake spreads just as fast. Limit who holds ALTER, DROP and CREATE rights on replicated databases and tables, and require a reviewed change for each statement. Record which accounts run schema changes so that an unexpected replica divergence can be traced. Confirm which roles hold DDL rights today, since the default user starts with all permissions until you restrict it.

Checking ReplicatedMergeTree replication health in system tables

Start with whether each replica is readable and writable. The system.replicas table has one row per replicated table on each server, and its status flags show whether a replica can accept inserts, whether it is in read-only mode, and whether it has a live session with Keeper. Read these flags on every node, not only the one you query through a load balancer, because a single replica can be read-only while the rest of the cluster looks fine.

Read-only flag: is_readonly set to 1 means the replica cannot accept writes, often because its Keeper session is lost or its metadata is missing.
Session state: is_session_expired and zookeeper_exception show whether the replica can talk to Keeper and what error it last reported.
Replication delay: absolute_delay shows how far behind the replica is in seconds. A large or steadily growing value means the replica is not catching up.
Readiness of the table: is_leader and total_replicas versus active_replicas show whether the expected replicas are online.
Location: Record the host and database for each row so you can compare replicas consistently.

Confirm exact column names and flag semantics against your server version, since these tables change between releases.

Next check whether replication work is queued or stuck. Each replica keeps a queue of fetches, merges and mutations to apply, recorded in system.replication_queue. The system.replicas table also carries summary counts for the queue, so you can see the size of the backlog without scanning every entry.

Queue length: The queue count in system.replicas growing over time means the replica is receiving work faster than it applies it.
Stuck entries: Rows in system.replication_queue with a high num_tries and a populated last exception point to a specific failing operation, such as a fetch that cannot reach a peer or a merge that fails on disk space.
Age of the oldest entry: An old queued entry with no progress usually needs investigation before it blocks later work.
Mutations: Long-running ALTER mutations appear as queue work and can hold up other replication tasks.
Disk and network: Most queue backlogs trace back to disk space, network reachability between replicas or a long-running merge.

When the queue keeps growing, check the server logs for the same operation, because the table shows what is pending, not always why it fails. Confirm the column names for your version before you write alerts against them.

Status flags can look healthy while replicas hold different rows, so verify the data itself. Insert a small, known test batch through one replica, wait for replication to finish, then compare the result on every replica. Use a dedicated test table so production data is not affected, and remove the test rows afterward.

Row counts: Run count() on the same table on each replica, and compare the results after the queue for that table is empty.
Checksums: Compare an aggregate or hash of a stable column set on each replica, for example a hash over ordered key columns, to confirm the contents match and not only the row counts.
Expected values: Insert rows with known keys and values so you can confirm each one appears on every replica.
Timing: Compare only after system.replication_queue shows no pending work for the table, otherwise you will read a moving target.
Scope: Repeat the check for each replicated table, since one table can lag while others are current.

Ask your team which query and hash function to use for each table's key columns, and document the exact steps so the check can be repeated after upgrades or failovers.

What replication does not protect against

Replication protects availability, not data. A ReplicatedMergeTree table keeps copies of the same parts on every replica, coordinated through ClickHouse Keeper, so a node failure does not stop reads and writes. The trade-off is that a bad DROP TABLE, a faulty ALTER, a bulk DELETE run by mistake, or a corrupted part is also applied to every replica. The ClickHouse backup documentation states plainly that replication alone does not protect against human error or corruption. A separate backup and restore plan is therefore a requirement of this design, not an optional add-on.

Recovered replicas resync from peers, not from backups. When a replica restarts after a failure, it automatically fetches missing data from a healthy replica without manual intervention. That behavior is useful for node loss, but it means a replica that comes back with the bad state will not undo it. Confirm with your own tests how your version handles a replica that has lost its data or was offline during a destructive change. Replica failure and rebuild steps, including the safety check that blocks startup and the force_restore_data flag, are covered on the page for recovering a failed or lost ClickHouse replica.

Plan the backup and test the restore before go-live. Use BACKUP and RESTORE to protect tables, partitions or databases, with storage on local disk, S3-compatible storage or Azure Blob Storage, and decide the schedule and retention with your recovery objectives in mind. The backup documentation warns that a backup never test-restored will probably not restore properly when needed, so restore into a spare cluster on a regular cadence and record the results. The backup and disaster recovery group covers how to build and rehearse these restores in detail.

Limits to confirm before you set replication in production

Replication behavior depends on the version you run, the release line you choose, and the Keeper and write patterns in your own environment. The cards below list the items to verify before you create replicated tables, so the design you test matches the one you operate.

Supported version line

Confirm that the version you plan to run is on the list of supported lines in the security policy. The policy lists 26.9, 26.8, 26.7 and 26.3 as supported with security updates and treats all other versions as unsupported. A cluster on an unlisted line will not receive security fixes, so pick the line before you write replication DDL and recheck it before go-live.

Stable or LTS release choice

Decide whether you will run a stable release or an LTS line. The production FAQ recommends stable releases for most production use, while LTS releases ship twice a year and are supported for a year after release. Record the release you test, and plan the next upgrade window, because replicated clusters should move between versions only after you have read the changelog.

Keeper sizing for your topology

Confirm the Keeper ensemble size for your node count and the network latency between replicas and Keeper nodes. The replication guide recommends at least three Keeper nodes on dedicated hosts in production, with about 4 GB of RAM each. Measure round-trip latency between sites before you place Keeper nodes, since a Keeper quorum that spans slow links will slow every replicated insert.

Quorum behavior under write volume

Test how insert_quorum and related settings behave at your real insert rate. A quorum write waits for acknowledgment from several replicas, so high concurrency or a slow replica can turn into failed or delayed inserts. Decide which tables need quorum durability and which can accept asynchronous replication, and confirm the current default and parameter names in the documentation for your version.

DDL behavior across replicas

Verify how schema changes propagate to every replica before you automate them. Replicated tables need ON CLUSTER or an equivalent mechanism for DDL, and a host that is down or lagging can leave the schema out of step with its peers. Test an ALTER on a staging cluster with one replica stopped, and confirm how the distributed DDL queue reports failed hosts in your version.

Backup and restore test cadence

Set a restore test schedule before you rely on replication for recovery. Replication copies human error and corruption to every replica, so it does not replace backups. The backup documentation warns that a backup never test-restored will probably not restore properly, and recommends regular restore tests on spare clusters. Agree the cadence with your recovery owners and record each test result.

Questions buyers ask about ReplicatedMergeTree setup and effort

How much effort does a ReplicatedMergeTree setup take?

Effort depends on how many clusters, shards, replicas and replicated tables you run, and on how much testing the durability targets require. Each replicated table needs a Keeper path, macros on every host, insert settings agreed with the application team, and checks that replicas catch up. The work scales with table count more than with server count, because every table has to be reviewed for its keys, quorum behavior and failure handling. Ask any provider to break the estimate into cluster design, table migration, Keeper hosting, test cycles and handover, and to state which items are one-time and which recur.

Can an in-house team set up ReplicatedMergeTree on its own?

A team with Linux, networking and ClickHouse experience can build it, provided it has time to test failures before production traffic arrives. The main risks are not the initial CREATE TABLE statements but the decisions around them: Keeper placement, quorum settings, digest ACLs, and how the application handles write errors when a replica is down. Teams without prior ClickHouse operations experience often underestimate the test cycle. Compare your staff's hours and on-call coverage against a written scope, and decide which parts you will keep in-house, such as day-two operations, and which you want reviewed or built by a partner.

What does the setup need from our team?

The work needs named people and access at several points. Expect to provide:

Hosts or cloud accounts for ClickHouse and, in production, dedicated hosts for at least three Keeper nodes, as the replication guide recommends
Network rules between the replicas and Keeper nodes, plus an approved change window for each environment
A pre-production environment with representative data volumes and the queries that matter
A decision owner who signs off the durability targets, such as how many replicas must acknowledge a write before it succeeds

Without these inputs, the testing phase will stall, so confirm them before the scope is agreed.

How long does testing take, and what sets that length?

Testing length is set by the failure scenarios you need to prove, not by a standard duration. Typical cases include stopping a replica during writes, stopping a Keeper node, restarting a replica after a long outage, and confirming the application behaves correctly when quorum cannot be met. Each scenario needs a repeatable procedure, timing data and a recorded result. The number of tables, the size of data that must resync and the number of environments all extend the cycle. A written scope should list each scenario, the environment it runs in and the acceptance criteria, so you can judge the schedule before work starts.

Does replication replace backups for ClickHouse?

No. Replication keeps copies of data available when a node fails, but it also copies a bad DROP, a faulty ALTER or a corrupted part to every replica. The ClickHouse backup documentation makes this point directly and treats BACKUP and RESTORE as a separate control. Plan both: replicas for availability, and backups to local disk, S3-compatible storage or Azure Blob Storage for recovery from human error or corruption. A backup that has never been restored onto a spare cluster should be treated as unverified, so budget for restore tests as a recurring activity.

What should a quote for this work include?

A quote should follow directly from a written scope, and DeployOpen agrees scope, deliverables and timelines in writing before work starts. Ask that the quote lists the cluster and table inventory in scope, the Keeper topology and hosting, the insert and quorum decisions each table needs, the digest ACL and access setup, the failure tests to be run and their acceptance criteria, the documentation and handover, and any excluded items such as application changes or ongoing support. Ask separately whether ongoing support or testing is offered as a retainer, since those costs recur and should not be mixed into the one-time build.

Which ClickHouse version should we replicate on?

Choose a version that is still supported with security updates and confirm it against the current security policy before you build. The security policy lists which versions receive security updates, and the production FAQ recommends stable releases for most production use while reading the changelog before any upgrade. Because minor backward-incompatible changes can occur, build and test on the version you intend to run, not on a version you plan to move to later. Ask your provider to state the version in the scope and to say how the upgrade path will be tested.

How DeployOpen scopes and delivers ClickHouse replication

DeployOpen plans and builds ReplicatedMergeTree replication inside your own infrastructure, starting from your table list, shard layout and recovery targets. Each engagement produces a written scope, a tested Keeper and replica configuration, and documentation your operators can run without us.

How we scope

Inventory which tables need replicas, how many shards and replicas each needs, and where they run.
Agree in writing the Keeper ensemble layout, macro naming, insert quorum settings and monitoring expectations.
Confirm the ClickHouse version and changelog review path before any replicated table is created.
Engagements are fixed-scope, with deliverables and timelines agreed before work starts.

How we build

Define Keeper paths with shard and replica macros so every table follows one documented naming pattern.
Set insert quorum and digest ACL settings according to the durability targets agreed in scope.
Test replica failure, resync and read consistency on a pre-production cluster with representative data.
Check replication health through system tables and record the queries operators will use daily.

How we hand over

Deliver the final configuration files, table definitions and Keeper path conventions as documentation.
Walk your operators through the health checks, alert conditions and the recovery steps for a lost replica.
Document what remains outside replication scope, including backups, which need their own tested plan.
Record open items and the vendor or environment details your team must confirm before go-live.

How we support

Ongoing support, version upgrades and continuous testing are available as a retainer after handover.
Review replication health and Keeper behavior when you add shards, replicas or new hosts.
Plan rolling upgrades and changelog reviews against the ClickHouse support policy for your versions.
Re-run replication tests after configuration changes so the documented behavior stays accurate.

Configuring ReplicatedMergeTree replication