Skip to content
Tutorials Center
My learning

Topics

Lesson 28 of 35 ยท Replication and Clusters

Cluster Failures and Consistency

What happens when a ClickHouse replica, shard or Keeper node fails: insert deduplication, retries, read-only replicas, quorum, recovery steps and idempotent loading patterns.

A cluster is only as useful as its behaviour when things go wrong. Networks drop packets, disks fill, a node reboots in the middle of an insert. This lesson covers what ClickHouse guarantees, what it does not, and how to design your loaders so a failure never produces lost or doubled data. It builds on replication and sharding.

ClickHouse statements below are read-only because the server cannot run on this site. The SQL stand-ins model deduplication by block hash.

Failure scenarios and outcomes

Failure What happens What you do
One replica goes down other replicas keep serving; inserts to them continue restart it; it replays the log and fetches missing parts
Replica loses Keeper session it becomes read-only (is_readonly = 1) fix Keeper connectivity; it recovers on its own
One Keeper node fails (of 3) cluster continues, quorum still holds replace the node
Keeper quorum lost all replicated tables become read-only; reads work, inserts fail restore quorum; data in tables is safe
A whole shard is down queries touching it fail unless skip_unavailable_shards = 1, which returns partial data restore; be careful with partial results
Disk full / corrupted part replica detaches the part and fetches a good copy from another replica investigate the cause; check system.detached_parts
Client crashes mid-insert the insert may or may not have been committed retry safely (see below)

The key insight: a part is atomic. An insert either creates a complete part and registers it in Keeper, or it does not exist. There are no half-applied parts, but the client may not know which happened.

Retries and insert deduplication

If a client sends an insert, loses the connection and does not know the outcome, it retries. Without protection this could duplicate rows. Replicated tables therefore keep checksums of the last N inserted blocks (replicated_deduplication_window, default 1000 blocks, and a time window of 7 days in recent versions). When an identical block (same data, same order, same block boundaries) arrives again, it is silently ignored.

Consequences to design around:

  • Deterministic blocks. A retry only deduplicates if it sends exactly the same block. Retry the same batch, with the same rows in the same order.
  • Block size must be stable. max_insert_block_size and min_insert_block_size_rows can split a large insert into blocks differently on a retry. For file-based loads, rely on identical chunking or set insert_deduplication_token (see below).
  • Not for deliberate repeats. Inserting the same rows twice on purpose is also dropped, which surprises people in tests. Set insert_deduplicate = 0 to disable.
  • Non-replicated MergeTree does not deduplicate unless you enable non_replicated_deduplication_window.

For explicit control, give each logical batch a token:

SQL
INSERT INTO events
SETTINGS insert_deduplication_token = 'daily-2025-03-01-part-07'
VALUES (...);

Two inserts with the same token into the same table are treated as duplicates, regardless of content. Derive the token from your source (file name plus offset, Kafka partition plus offsets).

Materialized views complicate this: a deduplicated insert into the source also skips the views, but a retried insert after a partial failure of a view target may need deduplicate_blocks_in_dependent_materialized_views = 1.

Duplicates at the model level

Even with deduplication, the same business event can arrive twice from upstream. Solutions:

  1. ReplacingMergeTree keeps one row per sorting key after merges (the latest by a version column). Until merged, duplicates are visible, so queries use FINAL, LIMIT 1 BY key or argMax.
  2. Idempotent loading: write each batch into a staging table, check it, then ATTACH/REPLACE PARTITION into the main table, so a rerun replaces rather than appends:
SQL
ALTER TABLE events REPLACE PARTITION '2025-03-01' FROM events_staging;
  1. Exactly-once consumers for Kafka by committing offsets only after the insert and using tokens from offsets.

Read consistency under failure

With insert_quorum = 2 and three replicas, an insert succeeds only after two have the part; if only one is alive the insert fails rather than risking loss on a single copy. insert_quorum_parallel (default on) allows several quorum inserts at once. Reading your own writes needs select_sequential_consistency = 1. These increase safety but reduce availability when replicas are down, a classic trade-off.

For distributed queries, skip_unavailable_shards and max_replica_delay_for_distributed_queries (do not read from replicas lagging more than N seconds) tune what you get when part of the cluster is unhealthy.

Recovery checklist

  1. Check system.replicas for is_readonly, queue_size, absolute_delay and last_queue_update_exception.
  2. Inspect system.replication_queue for stuck tasks and error messages.
  3. Check Keeper health (mntr/stat four-letter commands, or system.zookeeper queries).
  4. For a broken table, SYSTEM RESTART REPLICA db.table, then SYSTEM SYNC REPLICA db.table.
  5. If Keeper metadata was lost but data is on disk, SYSTEM RESTORE REPLICA db.table.
  6. If a replica is hopeless, drop it and create it anew; it will fetch everything from the others.
  7. Look in system.detached_parts before deleting anything.

Stand-in: deduplication by block hash

The stand-in keeps a table of block hashes (here a text label). Inserting a block with a known hash is a no-op. This is a model of the idea; real ClickHouse hashes the block contents.

SQL Stand-in: retrying the same block is harmless

Press Run (or Ctrl + Enter) to see the result here.

The retried block adds nothing, so id = 3 appears once.

SQL Stand-in: keep the latest version per key (ReplacingMergeTree idea)

Press Run (or Ctrl + Enter) to see the result here.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. Why is it safe to retry an INSERT into a ReplicatedMergeTree table after a timeout?

    Replicated tables remember checksums of recent blocks and ignore a block that is identical to one already inserted.
  2. 2. A retry did not deduplicate because the batch was split into different blocks. What gives you explicit control?

    A deterministic token tied to the source batch makes retries recognizable regardless of block boundaries.
  3. 3. What state does a replica enter if it loses its Keeper session?

    The replica stays readable but rejects inserts until the session is restored.
  4. 4. What is the downside of insert_quorum = 2 on a three-replica shard?

    Quorum increases durability but reduces availability when replicas are down.

Summary

  • Parts are atomic; an insert either exists fully or not at all.
  • Replicated tables deduplicate identical recent blocks; use tokens for deterministic retries.
  • Use ReplacingMergeTree, partition replacement or staging tables for idempotent models.
  • Quorum settings trade availability for safety; check system.replicas and system.replication_queue first when debugging.

Next: Loading Data and Insert Patterns