ClickHouse Zero Data Loss: 4 Proven Strategies for Reliable Disaster Recovery on 26.8 LTS

ClickHouse zero data loss is a commitment that most analytics estates make implicitly and very few can evidence. The moment a ClickHouse cluster starts carrying payments telemetry, fraud features, regulatory reporting or customer-facing usage metering, it is no longer a reporting replica that can be rebuilt over a weekend. It is a system of record, and the board-level question changes from “is it fast” to “what exactly do we lose, and how quickly are we back, when a site goes dark”.

This guide sets out how we engineer ClickHouse zero data loss on the current open-source release line: ClickHouse 26.8 LTS (released 27 August 2026) for production estates and 26.10 (released 22 October 2026) as the current stable release. It is written for the CTO, the head of data platform and the principal engineer who will sign the RPO line in the DR policy. Every control below names the setting, the system table that evidences it, and the failure class it does and does not close.

Why ClickHouse zero data loss needs a deliberate design

Out of the box, ReplicatedMergeTree replication is asynchronous. A replica writes a new part locally, registers it in ClickHouse Keeper, acknowledges the INSERT, and its peers fetch the part afterwards. That design is the reason ClickHouse ingests at the rates it does, and it is also the reason a default cluster has a non-zero RPO: if the receiving replica is lost between acknowledgement and the first fetch, the part is gone.

Three further defaults widen that window, and each one is a conscious choice rather than a defect:

None of these should be changed blindly. Each costs latency or throughput, and the right answer depends on which failure classes the business has decided to absorb. The rest of this guide is a framework for making that decision explicitly and evidencing it.

Figure 1. ClickHouse zero data loss is built from four control layers, each closing a different failure class.

A failure-model first approach to ClickHouse disaster recovery strategy

In our advisory work, the most common gap is not a missing feature. It is a DR policy that states “RPO zero” without naming the failures it is protecting against. We start every ClickHouse disaster recovery strategy with a short failure register and map each line to a control and a measurement.

A practical consequence of this register: zero data loss and continuous write availability cannot both be guaranteed when the majority of sites is gone. A ClickHouse zero data loss design chooses consistency at that point and stops accepting writes. We document that trade-off in the policy so it is a decision, not an incident finding.

Strategy 1: ClickHouse insert quorum across failure domains

The first and most important control is the ClickHouse insert quorum. With insert_quorum set to 2 or more, an INSERT returns success only after that many replicas hold the part. If the quorum is not reached within insert_quorum_timeout (default 600,000 ms), the INSERT fails and the block is removed from the replicas that did write it, so the client can safely retry.

Quorum only delivers ClickHouse zero data loss against site loss if the replicas are in different sites. A quorum of two replicas in the same availability zone gives ClickHouse zero data loss against a host failure and nothing more. The reference topology is three replicas in three failure domains, with one Keeper voter in each.

Figure 2. Three-site stretched ReplicatedMergeTree. The writer is acknowledged after the second site holds the part, which is what makes ClickHouse zero data loss hold through a site failure.

Cluster and Keeper configuration

The cluster definition uses internal_replication so that the replicated table, not the Distributed engine, owns replication. Keeper runs with its default force_sync = true, which fsyncs the Raft changelog on every change; we never disable it on a ClickHouse zero data loss estate.

<clickhouse>
    <remote_servers>
        <prod_zdl>
            <shard>
                <internal_replication>true</internal_replication>
                <replica><host>ch-a1.internal</host><port>9000</port></replica>
                <replica><host>ch-b1.internal</host><port>9000</port></replica>
                <replica><host>ch-c1.internal</host><port>9000</port></replica>
            </shard>
        </prod_zdl>
    </remote_servers>

    <!-- one Keeper voter per site; any two sites keep the ensemble writable -->
    <zookeeper>
        <node><host>keeper-a.internal</host><port>9181</port></node>
        <node><host>keeper-b.internal</host><port>9181</port></node>
        <node><host>keeper-c.internal</host><port>9181</port></node>
    </zookeeper>

    <macros>
        <cluster>prod_zdl</cluster>
        <shard>01</shard>
        <replica>ch-a1</replica>   <!-- unique per host -->
    </macros>
</clickhouse>

For two-region estates, which are common under data-residency rules, the equivalent is two replicas per region plus a Keeper witness in a third location and insert_quorum = 3. Losing a region then leaves two replicas, the quorum cannot be met, and writes stop until an operator lowers the quorum through a gated runbook step. That is the honest cost of ClickHouse zero data loss across two regions.

Table definition with explicit engine parameters

We declare every engine parameter explicitly in customer-facing work rather than relying on defaults. The Kafka coordinates are carried on each row; they make reconciliation and replay checkpoints possible later.

CREATE TABLE analytics.payment_events_local ON CLUSTER prod_zdl
(
    event_date        Date,
    event_time        DateTime64(3, 'UTC'),
    merchant_id       UInt64,
    payment_id        UUID,
    amount_minor      Int64,
    currency          LowCardinality(String),
    status            LowCardinality(String),
    kafka_partition   UInt16,
    kafka_offset      UInt64
)
ENGINE = ReplicatedMergeTree(
    '/clickhouse/tables/{cluster}/{shard}/analytics/payment_events_local',
    '{replica}'
)
PARTITION BY toYYYYMM(event_date)
ORDER BY (merchant_id, event_date, event_time, payment_id)
SETTINGS
    index_granularity = 8192,
    replicated_deduplication_window = 10000,
    replicated_deduplication_window_seconds = 3600,
    fsync_after_insert = 0;

CREATE TABLE analytics.payment_events ON CLUSTER prod_zdl
AS analytics.payment_events_local
ENGINE = Distributed('prod_zdl', 'analytics', 'payment_events_local', cityHash64(merchant_id));

We leave fsync_after_insert at 0 when quorum spans independent sites, because two sites losing power before their page caches flush is a correlated failure we model separately. Inside a single data centre where all replicas share a power domain, we set it to 1 and accept the insert-latency cost, measured in a staging soak before production.

A dedicated settings profile for ClickHouse zero data loss writers

Quorum settings belong in a role-bound profile, not in application code, so that no writer can silently opt out.

CREATE SETTINGS PROFILE IF NOT EXISTS dr_quorum_writer
SETTINGS
    insert_quorum = 2,
    insert_quorum_timeout = 60000,
    insert_quorum_parallel = 1,
    distributed_foreground_insert = 1
TO etl_writer;

Two notes on that profile. insert_quorum_parallel = 1 (the default) keeps throughput high; the durability guarantee is identical. Set it to 0 only where readers need select_sequential_consistency, which requires parallel quorum inserts to be disabled and serialises writes per table. And distributed_foreground_insert = 1 removes the local background queue of the Distributed engine, which is an RPO leak in its own right.

Confirm in staging that the quorum settings reach the shard-local INSERT by checking the Settings map in system.query_log on each replica. We treat that query as evidence, not as a formality.

Strategy 2: anchor ingestion on a durable log

Quorum protects data after ClickHouse acknowledges it. It says nothing about rows that were in a loader’s memory when the loader crashed. The second control for ClickHouse zero data loss makes a replicated log, typically Kafka, the source of truth for RPO: producers write with acks=all to topics with replication factor 3 and min.insync.replicas = 2, and loaders commit consumer offsets only after ClickHouse has acknowledged the INSERT.

Commit-after-acknowledge gives at-least-once delivery. Deduplication turns it into effectively-once. Since 26.2 the unified deduplicate_insert setting defaults to enable for both synchronous and asynchronous inserts, and a caller-supplied insert_deduplication_token makes a retry of the same batch idempotent within the deduplication window.

Figure 3. Log-anchored ingestion. Kafka holds the data until both clusters acknowledge, which extends ClickHouse zero data loss to loader crashes and regional failover.

A loader that commits only after quorum acknowledgement

The loader below is the pattern we deploy, reduced to its essentials. It batches per Kafka partition, derives the deduplication token from the offset range, inserts under the quorum profile, and commits only after success. On partition assignment it syncs the replica and seeks past the highest offset already stored, which closes the narrow window between an acknowledged INSERT and a consumer commit that never happened.

import os
from collections import defaultdict

import clickhouse_connect
from confluent_kafka import Consumer, TopicPartition

TOPIC = "payment-events"
TABLE = "analytics.payment_events_local"
BATCH_ROWS = 50_000

ch = clickhouse_connect.get_client(
    host=os.environ["CH_HOST"],            # the local replica in this site
    username=os.environ["CH_USER"],
    password=os.environ["CH_PASSWORD"],
    secure=True,
)

consumer = Consumer({
    "bootstrap.servers": os.environ["KAFKA_BOOTSTRAP"],
    "group.id": os.environ["LOADER_GROUP"],   # one group per target cluster
    "enable.auto.commit": False,
    "auto.offset.reset": "earliest",
    "isolation.level": "read_committed",
})


def on_assign(c, partitions):
    """Seek past rows already stored, so a crash between ACK and commit is not replayed."""
    ch.command(f"SYSTEM SYNC REPLICA {TABLE}")
    for tp in partitions:
        stored = ch.query(
            f"SELECT max(kafka_offset) FROM {TABLE} "
            f"WHERE kafka_partition = {tp.partition} "
            f"AND event_date >= today() - 2"
        ).result_rows[0][0]
        if stored:
            tp.offset = stored + 1
    c.assign(partitions)


def flush(partition, rows):
    first, last = rows[0][-1], rows[-1][-1]
    token = f"{TOPIC}:{partition}:{first}-{last}"
    ch.insert(
        TABLE,
        rows,
        column_names=["event_date", "event_time", "merchant_id", "payment_id",
                      "amount_minor", "currency", "status",
                      "kafka_partition", "kafka_offset"],
        settings={
            "insert_quorum": 2,
            "insert_quorum_timeout": 60000,
            "insert_deduplication_token": token,
        },
    )
    # only now is the data durable on two sites; commit synchronously
    consumer.commit(offsets=[TopicPartition(TOPIC, partition, last + 1)],
                    asynchronous=False)


consumer.subscribe([TOPIC], on_assign=on_assign)
buffers = defaultdict(list)

while True:
    msg = consumer.poll(1.0)
    if msg is None or msg.error():
        continue
    row = decode(msg.value()) + [msg.partition(), msg.offset()]  # decode() is schema-specific
    buffers[msg.partition()].append(row)
    if len(buffers[msg.partition()]) >= BATCH_ROWS:
        flush(msg.partition(), buffers.pop(msg.partition()))

Two production details matter more than the code. First, a retry must reuse the same token and the same rows; if the loader rebuilds the batch differently, the token no longer matches. Second, the replay horizon must sit inside replicated_deduplication_window_seconds (3600 seconds by default). Replays older than that are not recognised as duplicates, which is why the assignment-time checkpoint above exists.

Strategy 3: an independent standby cluster for ClickHouse cross-region replication

A stretched cluster shares one Keeper ensemble and one set of replicated metadata. That coupling is what makes quorum writes possible, and it is also a shared failure domain: a Keeper corruption, a faulty schema change pushed ON CLUSTER, or a bad upgrade reaches every site at once.

For regulated estates our default ClickHouse cross-region replication pattern is therefore a second, fully independent cluster in another region, with its own Keeper ensemble, fed by its own consumer group from the same durable log (Figure 3). The standby is not a replica of the primary in the ReplicatedMergeTree sense. It is a peer that consumes the same immutable history.

The ClickHouse zero data loss argument for this pattern is simple: if the primary region disappears, every acknowledged event is still in the log, and the standby’s consumer lag is exactly the data it has yet to load. RPO is zero provided log retention exceeds the longest plausible lag, and RTO is the time to drain that lag and move client routing.

Most estates we design run both: Strategy 1 inside the primary region across availability zones, and Strategy 3 to a second region. That combination gives ClickHouse zero data loss for zone failures with no human action, and for regional failures with a measured, rehearsed cut-over.

Failover gate with explicit confirmation

Cut-over to the standby is a destructive-adjacent change: it redirects writers. We script it with a verification query before, a confirmation gate, and a validation query after. Credentials are always placeholders.

#!/usr/bin/env bash
# CD runbook step: promote standby cluster B to primary write target
set -euo pipefail

CH="clickhouse-client --host ${CH_STANDBY_HOST} --user ${CH_USER} --password ${CH_PASSWORD} --secure"

echo "== Pre-check: standby replication health =="
$CH --query "
SELECT database, table, is_readonly, absolute_delay, queue_size
FROM system.replicas
WHERE database = 'analytics'
FORMAT PrettyCompact"

echo "== Pre-check: consumer lag on loader-region-b must be 0 =="
kafka-consumer-groups.sh --bootstrap-server "${KAFKA_BOOTSTRAP}" \
    --describe --group loader-region-b | awk 'NR>1 {lag+=$6} END {print "total lag:", lag}'

read -r -p "Type PROMOTE-REGION-B to redirect all writers to cluster B: " answer
[[ "${answer}" == "PROMOTE-REGION-B" ]] || { echo "Aborted, no change made."; exit 1; }

# routing change is environment-specific (DNS, service mesh or load balancer)
./switch_write_endpoint.sh --target "${CH_STANDBY_HOST}"

echo "== Post-check: writes landing on cluster B =="
$CH --query "
SELECT count() AS rows_last_5m
FROM analytics.payment_events_local
WHERE event_time >= now() - INTERVAL 5 MINUTE"

For ClickHouse zero data loss, the rollback path matters as much as the failover: it is the same script pointed back at the original primary, run only after the original region has re-consumed the log and its row reconciliation matches. Failback is the half of DR that estates most often leave untested, and it is where we spend most drill time.

Strategy 4: immutable incremental backups and the logical-error gap

Replication and log replay cannot undo a logical error. A mutation that deletes the wrong rows is replicated to every site, and an accidental DROP TABLE is executed on every replica in the cluster. The fourth control is a backup chain that is independent of the running cluster.

Native BACKUP and RESTORE support incremental chains through base_backup. We write them to an S3 disk declared in the storage configuration and permitted under <backups><allowed_disk>, so no credentials ever appear in SQL. The bucket has versioning and a retention lock, verified during the restore drill.

-- weekly full
BACKUP DATABASE analytics ON CLUSTER prod_zdl
TO Disk('dr_s3', 'analytics/full-2026-11-08/')
SETTINGS async = 1;

-- daily incremental chained to the full
BACKUP DATABASE analytics ON CLUSTER prod_zdl
TO Disk('dr_s3', 'analytics/incr-2026-11-12/')
SETTINGS
    base_backup = Disk('dr_s3', 'analytics/full-2026-11-08/'),
    async = 1;

-- evidence: every backup run, its status and its size
SELECT
    name,
    status,
    error,
    start_time,
    end_time,
    formatReadableSize(total_size) AS total_size,
    num_files
FROM system.backups
ORDER BY start_time DESC
LIMIT 10;

Backups give a point-in-time restore target, not RPO zero on their own. Combined with the durable log, they do: restore the last good backup into an isolated cluster, then replay the log from the backup’s high-water Kafka offsets up to the moment before the logical error. That is the only way we know to deliver ClickHouse zero data loss against a bad mutation, and it only works if log retention covers the backup interval.

For the fastest class of mistake, the Atomic database engine keeps dropped tables recoverable with UNDROP TABLE for database_atomic_delay_before_drop_table_sec (480 seconds by default), unless the DROP ran with SYNC. We raise that window on production servers and forbid SYNC drops for application roles.

Evidencing ClickHouse zero data loss: the verification spine

A ClickHouse zero data loss control that is not measured continuously is an assumption. These are the queries we schedule and alert on; each maps to a line in the failure register.

-- 1. Replica health: any replica read-only, lagging or with a deep queue
SELECT
    database,
    table,
    replica_name,
    is_readonly,
    is_session_expired,
    absolute_delay,
    queue_size,
    inserts_in_queue,
    active_replicas,
    total_replicas
FROM system.replicas
WHERE is_readonly
   OR is_session_expired
   OR absolute_delay > 30
   OR active_replicas < total_replicas;

-- 2. Stuck replication work, with the error that blocks it
SELECT
    database,
    table,
    type,
    create_time,
    num_tries,
    last_exception
FROM system.replication_queue
WHERE num_tries > 10
ORDER BY create_time
LIMIT 20;

-- 3. Rows waiting in Distributed background queues (should be empty with foreground inserts)
SELECT
    database,
    table,
    data_files,
    formatReadableSize(data_compressed_bytes) AS pending,
    last_exception
FROM system.distribution_queue
WHERE data_files > 0;

-- 4. Quorum write failures in the last day
SELECT
    exception_code,
    count() AS failures,
    any(exception) AS sample
FROM system.query_log
WHERE event_date >= today() - 1
  AND type = 'ExceptionWhileProcessing'
  AND query_kind = 'Insert'
  AND exception ILIKE '%quorum%'
GROUP BY exception_code;

Alongside these, we monitor Keeper with the mntr four-letter command (outstanding requests, followers in sync, fsync latency) and reconcile row counts per Kafka partition and offset range between the primary and the standby every hour. A reconciliation mismatch is a Severity 2 event in our support model, because it means the RPO claim is no longer evidenced.

What changes in ClickHouse 26.8 LTS and 26.10 for DR

Version matters. The following release-level changes affect ClickHouse disaster recovery strategy and upgrade runbooks; we verified them against the upstream changelog and source on 12 November 2026.

On lifecycle: 26.3 LTS remains supported upstream until March 2027, and 25.8 LTS is already out of upstream security support. An estate still on 25.8 carries a DR risk that no topology fixes, and we treat the upgrade as part of the ClickHouse zero data loss programme rather than a separate project.

Governance: making the RPO line auditable

The engineering above is necessary but not sufficient. A defensible ClickHouse zero data loss position, the kind that survives an internal audit or a regulator’s question, has four governance elements:

A written failure model, approved by the data platform owner, that states which failures are covered at RPO zero and which are not. A quarterly drill calendar in which a site is actually removed and the restore-and-replay path is actually executed, with measured RPO and RTO recorded against the target. A change policy that blocks any modification of the quorum profile, Keeper membership or log retention without a second approver. And an owner for failback, named in the runbook, because failback is where estates most often lose data after surviving the original incident.

We publish our own approach to rehearsals in ClickHouse disaster recovery drills, and the zone-level design that complements this guide in ClickHouse high availability architecture. The upstream reference on replication semantics is the ClickHouse data replication documentation.

ClickHouse zero data loss: frequently asked questions

Can ClickHouse guarantee zero data loss out of the box?

No. Default ReplicatedMergeTree replication is asynchronous, so ClickHouse zero data loss requires insert_quorum of 2 or more with replicas in separate failure domains, plus a durable log in front of ingestion.

Does ClickHouse zero data loss work with asynchronous inserts?

Yes, provided the client waits for the flush. Keep wait_for_async_insert = 1 (the default) so the acknowledgement follows the write; with wait_for_async_insert = 0 the client is acknowledged before the data is durable.

Is ClickHouse zero data loss possible across two regions?

Yes, with two replicas per region, a Keeper witness in a third location and insert_quorum = 3. The cost is cross-region latency on every insert and a write stop when a region is lost.

Does ClickHouse zero data loss protect against an accidental DROP or a bad mutation?

Not through replication. Logical errors are copied to every replica. Immutable backups, a log retained beyond the backup interval and the UNDROP window are the controls for that failure class.

How often should a ClickHouse zero data loss design be tested?

At least quarterly, with a real site removal and a real restore-and-replay, and after every LTS upgrade. The RPO and RTO measured in the drill are the figures that belong in the DR policy.

How ChistaDATA delivers ClickHouse zero data loss programmes

ChistaDATA runs 24x7x365 ClickHouse operations from the San Francisco Bay Area plus eleven global offices, on 100% open-source ClickHouse with no vendor lock-in. A ClickHouse zero data loss engagement typically moves through a failure-model workshop, a topology and settings design, a staged rollout with per-node verification, and a first measured drill, after which the estate moves into our ClickHouse managed services with drills on a quarterly calendar.

If your current DR policy states RPO zero and you cannot yet point to the system table that proves it, that is the right moment to talk to our ClickHouse consulting team.