ClickHouse High Availability on 26.9: 8 Brutal Failure Drills, Measured

How it works

What ClickHouse high availability is made of

ClickHouse high availability is built from three separate mechanisms, and most outages we handle come from treating them as one. Replication copies data between servers. ClickHouse Keeper coordinates that copying. The Distributed table and client settings decide which replica a query goes to. Each mechanism fails differently, so each needs its own monitoring and its own runbook.

ClickHouse replication works per table. A ReplicatedMergeTree table on each server registers itself under a path in Keeper. An INSERT writes a part on the replica that received it, then adds an entry to the shared replication log in Keeper. Every other replica reads the log, puts a GET_PART entry in its own queue, and fetches the part directly from a peer over the interserver HTTP port. Merges are recorded in the same log, so replicas converge on the same set of parts. The replication documentation describes the model.

Keeper stores coordination state, not data. It holds the log, each replica’s queue, the list of active replicas and the block hashes used for deduplication. It runs Raft, so a three-node ensemble keeps working with one node down and a five-node ensemble with two. Losing Keeper does not lose data, but it stops the cluster from agreeing on new data. The ClickHouse Keeper guide covers configuration and sizing.

That split explains most of what follows. A replica can be lost and rebuilt from its peers. The Keeper leader can be lost and replaced in about a second. Keeper quorum can be lost for a while: reads keep working from local parts, and writes wait. Even Keeper data can be lost and rebuilt from the parts on disk. Each of those statements is backed by a drill below.

The lab

A ClickHouse high availability cluster you can rebuild in ten minutes

The lab runs on one 2 vCPU host with 8 GiB of RAM: three ClickHouse Keeper processes and three ClickHouse servers, all version 26.9.2.8. Running everything on one host makes the timings pessimistic and removes real network failures, which we come back to at the end. It does exercise the same code paths as a production cluster. This is the Keeper configuration for node 1; nodes 2 and 3 differ only in server_id, ports and paths.

<!-- keeper1.xml: one member of a three-node ClickHouse Keeper ensemble (ClickHouse high availability lab) -->
<clickhouse>
    <listen_host>127.0.0.1</listen_host>
    <keeper_server>
        <tcp_port>9181</tcp_port>                      <!-- clients (ClickHouse servers) connect here -->
        <server_id>1</server_id>                       <!-- unique per member, must match raft_configuration -->
        <log_storage_path>/var/lib/clickhouse-keeper/log</log_storage_path>
        <snapshot_storage_path>/var/lib/clickhouse-keeper/snap</snapshot_storage_path>
        <coordination_settings>
            <operation_timeout_ms>10000</operation_timeout_ms>
            <session_timeout_ms>30000</session_timeout_ms>   <!-- how long a dead client stays "active" -->
            <raft_logs_level>information</raft_logs_level>
        </coordination_settings>
        <raft_configuration>                                   <!-- identical on all three members -->
            <server><id>1</id><hostname>keeper1</hostname><port>9234</port></server>
            <server><id>2</id><hostname>keeper2</hostname><port>9234</port></server>
            <server><id>3</id><hostname>keeper3</hostname><port>9234</port></server>
        </raft_configuration>
    </keeper_server>
</clickhouse>

Each ClickHouse server needs three things: where Keeper is, which replica it is, and which servers make up the cluster. Macros keep the table DDL identical on every replica, which matters later when a replica has to be rebuilt from scratch.

<!-- config.d/replication.xml: ClickHouse replication settings on replica r1 (r2 and r3 change only the replica macro) -->
<clickhouse>
    <zookeeper>                                         <!-- the section name is historical; it points at Keeper -->
        <node><host>keeper1</host><port>9181</port></node>
        <node><host>keeper2</host><port>9181</port></node>
        <node><host>keeper3</host><port>9181</port></node>
        <session_timeout_ms>30000</session_timeout_ms>
        <operation_timeout_ms>10000</operation_timeout_ms>
    </zookeeper>
    <macros>
        <cluster>ha</cluster>
        <shard>01</shard>
        <replica>r1</replica>                        <!-- unique per server -->
    </macros>
    <interserver_http_port>9009</interserver_http_port>  <!-- part fetches between replicas -->
    <remote_servers>
        <ha>
            <shard>
                <internal_replication>true</internal_replication>  <!-- let ReplicatedMergeTree copy, not Distributed -->
                <replica><host>ch-r1</host><port>9000</port></replica>
                <replica><host>ch-r2</host><port>9000</port></replica>
                <replica><host>ch-r3</host><port>9000</port></replica>
            </shard>
        </ha>
    </remote_servers>
</clickhouse>

The table and its Distributed front end are created once, with ON CLUSTER, so every replica gets the same definition.

-- ClickHouse replication: one logical table, three physical replicas
CREATE DATABASE IF NOT EXISTS ha ON CLUSTER ha;

CREATE TABLE IF NOT EXISTS ha.orders ON CLUSTER ha
(
    order_id    UInt64,
    tenant_id   UInt32,
    created_at  DateTime64(3),
    amount      Decimal(18, 2),
    status      LowCardinality(String)
)
ENGINE = ReplicatedMergeTree(
    '/clickhouse/tables/{cluster}/{shard}/ha/orders',   -- Keeper path, shared by all replicas of the shard
    '{replica}'                                         -- this server's name under that path
)
PARTITION BY toYYYYMM(created_at)
ORDER BY (tenant_id, created_at, order_id)
SETTINGS index_granularity = 8192;

-- Reads go through the Distributed table, which knows all three replicas
CREATE TABLE IF NOT EXISTS ha.orders_all ON CLUSTER ha AS ha.orders
ENGINE = Distributed('ha', 'ha', 'orders', rand());

Drills 1 and 2 · a replica dies

Losing a replica: what insert_quorum really promises

By default an INSERT is acknowledged as soon as the replica that received it has written the part and registered it in Keeper. The other replicas copy it a moment later. If the receiving replica’s disk dies inside that window, the acknowledged rows are gone. insert_quorum closes the window: the INSERT returns only after the given number of replicas hold the part. 'auto' means a majority, two of three here.

First we measured what that costs on a healthy cluster: 100 INSERTs of 10,000 rows per mode, interleaved so every mode saw the same background noise.

Quorum roughly tripled median latency, from 50.7 ms to between 158 and 183 ms. The three quorum modes were within noise of each other because all replicas shared one host. On a real network, waiting for every replica costs more than waiting for a majority, because you always wait for the slowest one.

Then we killed replica r3 with SIGKILL and sent one INSERT in each mode two seconds later. Quorum 0, 2 and 'auto' all succeeded, in 104 to 158 ms. Quorum 3 did not return. Our client gave up after 60 seconds. The server’s query log showed what had really happened:

-- ClickHouse support check: what happened to an INSERT the client gave up on?
SELECT
    event_time,
    type,
    query_duration_ms,
    written_rows,
    exception_code
FROM system.query_log
WHERE query_kind = 'Insert'
  AND Settings['insert_quorum'] = '3'
  AND query_duration_ms > 2000
ORDER BY event_time;

-- event_time            type         query_duration_ms  written_rows  exception_code
-- 2026-10-05 12:42:04   QueryFinish  75250              10000         0

The INSERT waited 75.25 seconds and then succeeded, once r3 came back and fetched the part. The client had already reported it as a failure. At the moment of the INSERT, Keeper still listed r3 as active, because its session had not timed out yet. So ClickHouse accepted the quorum and waited for a replica that was dead.

That is the most useful finding in this post for anyone writing ingestion code. A client timeout does not mean the write failed. The only safe response is to retry the identical block and let deduplication discard the copy, which we test further down.

In drill 2 we waited for the dead replica’s Keeper session to expire. Its is_active node was still present 15 seconds after the kill and gone by 25 seconds. From then on, quorum 3 failed in 19 ms with a clear error:

Code: 285. DB::Exception: Number of alive replicas (2) is less than requested quorum (3/3).
(TOO_FEW_LIVE_REPLICAS) (version 26.9.2.8 (official build))

With three replicas, quorum 3 turns any single replica failure into a write outage. That is the opposite of ClickHouse high availability. Use 'auto' when acknowledged rows must exist on more than one server, and quorum 0 with retries for everything else.

Drill 4 · the Keeper leader dies

ClickHouse high availability when the Keeper leader fails: 1.27 seconds

With writes and reads running, we found the Keeper leader with the mntr four-letter command and killed it.

# ClickHouse high availability check: Keeper role of each member (four-letter commands are on the Keeper client port)
for port in 9181 9182 9183; do
    printf 'mntr' | nc -w 1 127.0.0.1 "${port}" | grep -E 'zk_server_state|zk_version'
done
# zk_server_state   follower
# zk_server_state   follower
# zk_server_state   leader      <- keeper3, killed with SIGKILL

A surviving member reported itself as leader 1.27 seconds after the kill. Across the 40-second drill, none of 146 writes and none of 498 reads failed. One INSERT, already in flight when the leader died, took 6.52 seconds instead of the usual 170 ms. The client library reconnected to another Keeper member without any help from us. For a three-node ensemble, losing the leader costs seconds of write latency and nothing else.

Drill 7 · Keeper data is gone

Keeper metadata loss and SYSTEM RESTORE REPLICA

This is the drill most teams never rehearse. We stopped all three Keeper members, deleted their logs and snapshots, and started them empty. That is what happens after a bad restore of Keeper, or when someone recreates the ensemble with fresh volumes.

The first surprise came from Keeper. It refused the ClickHouse servers’ sessions, and its log said why:

Refusing session as the client has seen zxid 18709 while our last processed zxid is 0.

Keeper detected that its clients had seen newer state than it now held, and refused them. That safety check is deliberate. The ClickHouse servers stayed read-only until we restarted them, 11.1 seconds for all three. After the restart the tables were still read-only, because their metadata no longer existed:

Code: 242. DB::Exception: Table is in readonly mode since table metadata was not found in zookeeper:
replica_path=/clickhouse/tables/ha/01/ha/orders/replicas/r1. (TABLE_IS_READ_ONLY)

SELECTs kept working throughout and returned all 6,649,002 rows. SYSTEM RESTORE REPLICA rebuilds the metadata from the parts each replica has on disk, as described in the SYSTEM statements reference. Parts that were already present are attached and registered, not fetched again over the network. This is the runbook our ClickHouse support team uses, with a check before and after each step.

-- ClickHouse support runbook: rebuild replication metadata after Keeper data loss
-- Blast radius: one table at a time. Rollback: none needed; RESTORE only registers local parts.
-- Gate: run ONLY when is_readonly = 1 because metadata is missing, never on a healthy table.

-- 1. Verify: which replicated tables are read-only, and is Keeper reachable?
SELECT database, table, replica_name, is_readonly, is_session_expired, zookeeper_exception
FROM clusterAllReplicas('ha', system.replicas)
WHERE is_readonly;

-- 2. ClickHouse replication baseline: row counts and a checksum per replica before touching anything
SELECT hostName(), count(), sum(cityHash64(order_id, amount))
FROM clusterAllReplicas('ha', ha.orders)
GROUP BY hostName();

-- 3. ClickHouse support step: restore one replica first; it recreates the shared table path in Keeper
SYSTEM RESTORE REPLICA ha.orders;            -- on r1: 14.98 s in our lab

-- 4. Then each remaining replica, one at a time
SYSTEM RESTORE REPLICA ha.orders;            -- on r2: 0.22 s
SYSTEM RESTORE REPLICA ha.orders;            -- on r3: 0.82 s

-- 5. ClickHouse support validation: writable again, all replicas active, counts and checksums identical
SELECT replica_name, is_readonly, queue_size, active_replicas, total_replicas, lost_part_count
FROM clusterAllReplicas('ha', system.replicas)
WHERE database = 'ha' AND table = 'orders';

The first replica took longest because it recreated the table’s path in Keeper and registered every part. The others only had to join. A quorum INSERT straight after the restore succeeded in 768 ms. All three replicas then reported 6,650,002 rows with identical checksums. Run this per table; on a cluster with hundreds of replicated tables, script it and run it per database. For databases using the Replicated engine, SYSTEM RESTORE DATABASE REPLICA is the equivalent.

The safety net

Retries are safe because ClickHouse deduplicates blocks

Several drills ended with the same advice: retry the identical block. That is only safe if a retry cannot insert the same rows twice. ReplicatedMergeTree stores a hash of each inserted block in Keeper and drops a block it has already seen. On 26.9 the window is the last replicated_deduplication_window = 10000 blocks or replicated_deduplication_window_seconds = 3600. The switch is now deduplicate_insert, which defaults to enable and supersedes the older insert_deduplicate. We tested it directly:

-- ClickHouse replication deduplication: send the same block three times
INSERT INTO ha.orders VALUES
    (990000001, 7, '2026-10-05 10:00:00.000', 125.50, 'paid'),
    (990000002, 7, '2026-10-05 10:00:01.000',  75.25, 'paid');   -- attempt 1 on r1
-- attempt 2: the identical statement again on r1
-- attempt 3: the identical statement on r2, a different replica

-- result: 2 rows added in total, not 6

The third attempt went to a different replica and was still dropped, because the block hashes live in Keeper and every replica shares them. Two conditions apply. The retry must contain the same rows in the same order, because the hash covers the block contents. And it has to arrive inside the window. Clients that rebuild batches from a queue on retry should set insert_deduplication_token so the identity survives a reshuffle.

INSERT ... SELECT follows a separate rule on 26.9. deduplicate_insert_select defaults to enable_when_possible, which deduplicates only when the SELECT is stable: it ends in ORDER BY ALL with a single output stream, or the INSERT carries an insert_deduplication_token. Our generated INSERT ... SELECT FROM numbers() batches were not stable, and they were not deduplicated. When we reran a drill script that regenerated the same order IDs, the rows were stored again. If your ingestion uses INSERT SELECT, give each batch a token.

What ClickHouse support watches

Monitoring ClickHouse high availability: the queries our support team runs

Every drill above showed up in system.replicas before anything else noticed. This is the first query our ClickHouse support engineers run on a cluster, across every replica at once. It returns only replicas that need attention. Pass --param_show_all=1 to list all of them.

-- ClickHouse support health check: replicated tables that need attention, cluster-wide
SELECT
    database,
    table,
    replica_name,
    is_readonly,                 -- 1: no Keeper session or no metadata, writes rejected
    is_session_expired,
    queue_size,                  -- replication entries waiting on this replica
    inserts_in_queue,
    merges_in_queue,
    absolute_delay,              -- seconds behind the freshest replica
    active_replicas,
    total_replicas,
    lost_part_count,             -- parts no replica has any more: escalate immediately
    last_queue_update_exception
FROM clusterAllReplicas('ha', system.replicas)
WHERE is_readonly
   OR is_session_expired
   OR absolute_delay > 30
   OR queue_size > 100
   OR active_replicas < total_replicas
   OR lost_part_count > 0
   OR 1 = {show_all:UInt8}
ORDER BY absolute_delay DESC, queue_size DESC;

A growing queue on one replica is the second thing we look for. system.replication_queue shows the entries that keep failing and why. A fetch postponed hundreds of times usually means a missing part, a full disk, or an interserver port blocked by a firewall change.

-- ClickHouse replication entries that are stuck, with the reason ClickHouse gives
SELECT
    database,
    table,
    type,                                    -- GET_PART, MERGE_PARTS, MUTATE_PART, ALTER_METADATA ...
    new_part_name,
    num_tries,
    num_postponed,
    substring(postpone_reason, 1, 60)  AS postpone_reason,
    substring(last_exception, 1, 80)   AS last_exception,
    create_time
FROM clusterAllReplicas('ha', system.replication_queue)
WHERE num_tries > 10
   OR create_time < now() - INTERVAL 10 MINUTE
ORDER BY num_tries DESC
LIMIT 20;

The Keeper side gets its own check. system.zookeeper_connection shows which Keeper member each server is connected to and for how long. If every server is connected to the same member, that member has become a single point of load, even though the ensemble looks healthy.

-- ClickHouse high availability: Keeper sessions as seen from each ClickHouse server
SELECT
    hostName()                         AS server,
    host                               AS keeper_host,
    port,
    connected_time,
    session_uptime_elapsed_seconds,
    is_expired
FROM clusterAllReplicas('ha', system.zookeeper_connection);

For load balancers and orchestrators, every ClickHouse server answers /replicas_status on its HTTP port. It returns 200 with Ok. when replicas are current and 503 when one lags beyond max_replica_delay_for_distributed_queries, as described in the monitoring guide. With ?verbose=1 it lists the delay per table.

# ClickHouse high availability health endpoint for a load balancer: 200 when current, 503 when lagging
curl -s -w ' [%{http_code}]\n' "http://ch-r1:8123/replicas_status"
# Ok. [200]

curl -s "http://ch-r1:8123/replicas_status?verbose=1"
# ha.orders:	Absolute delay: 0. Relative delay: 0.

For alerting, these are the ClickHouse high availability rules we put on every cluster under managed ClickHouse operations. They use series from the built-in Prometheus endpoint. Every metric named here exists on our 26.9 lab server. The thresholds are starting points to tune against each cluster’s own baseline.

# prometheus/rules/clickhouse-ha.yml: ClickHouse high availability alerts (starting thresholds)
groups:
  - name: clickhouse-high-availability
    rules:
      # ClickHouse support page: a replicated table is read-only: Keeper session lost or metadata missing (drills 5 to 7)
      - alert: ClickHouseReadonlyReplica
        expr: ClickHouseMetrics_ReadonlyReplica > 0
        for: 1m
        labels: { severity: page }

      # ClickHouse replication: a replica is falling behind its peers (drill 3)
      - alert: ClickHouseReplicaDelay
        expr: ClickHouseAsyncMetrics_ReplicasMaxAbsoluteDelay > 60
        for: 5m
        labels: { severity: ticket }

      # ClickHouse replication queue growing: fetches or merges not keeping up
      - alert: ClickHouseReplicationQueue
        expr: ClickHouseAsyncMetrics_ReplicasMaxQueueSize > 200
        for: 10m
        labels: { severity: ticket }

      # No Keeper session at all on this server
      - alert: ClickHouseNoKeeperSession
        expr: ClickHouseMetrics_ZooKeeperSession == 0
        for: 1m
        labels: { severity: page }

Readiness review

The ClickHouse high availability checklist we use in support onboarding

The last row matters because replication is not a backup. A bad mutation, a lightweight DELETE or a TRUNCATE replicates to every replica in seconds, exactly as designed. High availability keeps the service up; backups and restore drills keep the data.

Further reading

Related ChistaDATA guides and sources

From our blog: ClickHouse performance observability on 26.9, advanced ClickHouse troubleshooting and how ClickHouse query execution works.

ClickHouse documentation used for this guide: data replication, ClickHouse Keeper, SYSTEM statements, session settings, monitoring and the changelog.

Test every setting, query and runbook in this guide on a staging copy of your own cluster before applying it to production. Keep tested backups and a restore path outside the cluster, because high availability is not a substitute for disaster recovery.

Measurements: ChistaDATA lab, one 2 vCPU host with 8 GiB of RAM, three ClickHouse 26.9.2.8 servers and three ClickHouse Keeper 26.9.2.8 members, one shard with three replicas, October 2026. Failures were injected with SIGKILL; network faults were not tested. Timings vary between runs. Release facts are from clickhouse.com.