ClickHouse Replication

ClickHouse replicates per table, per shard, using ReplicatedMergeTree. Coordination runs through ClickHouse Keeper (or ZooKeeper), which stores the replication log and part metadata.

Creating a replicated table

CREATE TABLE events ON CLUSTER main
(
    event_time DateTime,
    tenant_id  UInt64,
    event_type LowCardinality(String)
)
ENGINE = ReplicatedMergeTree('/clickhouse/tables/{shard}/events', '{replica}')
PARTITION BY toYYYYMM(event_time)
ORDER BY (tenant_id, event_time);

The first argument is the Keeper path, which must be identical on all replicas of one shard and distinct between shards. {shard} and {replica} are macros defined per server:

<macros>
    <shard>01</shard>
    <replica>replica-a</replica>
</macros>

How it works

Replication is per table and asynchronous. An insert on one replica is written locally, registered in the Keeper log, and fetched by the others. Merges are also coordinated: one replica performs a merge and the rest fetch the result rather than repeating the work.

Replicas are equal — there is no primary. Any replica accepts inserts.

Keeper

ClickHouse Keeper is a Raft-based reimplementation of the ZooKeeper API, built into ClickHouse. It needs an odd number of nodes (three is standard) on separate hosts.

Insert durability

SET insert_quorum = 2;
SET insert_quorum_timeout = 60000;
SET select_sequential_consistency = 1;

Without insert_quorum, an insert is acknowledged when one replica has it. If that replica is lost before the others fetch the part, the data is gone. With a quorum, the insert waits for confirmation from the given number of replicas.

select_sequential_consistency = 1 makes reads see only quorum-confirmed data, which prevents reading rows that could still disappear.

Monitoring

SELECT database, table, is_readonly, is_session_expired,
       future_parts, parts_to_check, queue_size,
       absolute_delay, total_replicas, active_replicas
FROM system.replicas
WHERE is_readonly OR queue_size > 100 OR absolute_delay > 60;
 
-- What is stuck in the replication queue and why.
SELECT database, table, type, create_time, num_tries, last_exception
FROM system.replication_queue
WHERE num_tries > 5
ORDER BY create_time;

Fields worth alerting on:

• is_readonly = 1 — the replica has lost its Keeper session and accepts no writes.
• absolute_delay — seconds behind the rest of the shard.
• queue_size growing — this replica cannot keep up with fetches and merges.
• num_tries climbing in system.replication_queue — a task is failing repeatedly; read last_exception.

Recovering a replica

-- Ask the replica to re-check parts it is unsure about.
SYSTEM RESTART REPLICA events;
 
-- Rebuild this replica's metadata from Keeper and refetch missing parts.
SYSTEM RESTORE REPLICA events;