devLap

Day 77 of #100DaysOfClickHouse: Resolving Replication Conflicts in ClickHouse®

dev.to

Resolving Replication Conflicts in ClickHouse®

Introduction

ClickHouse® is designed for high-performance analytics and can scale horizontally by replicating data across multiple servers. The ReplicatedMergeTree table engine provides automatic data replication, ensuring high availability, fault tolerance, and resilience against server failures.

Unlike traditional transactional databases that replicate individual row-level changes, ClickHouse® replicates immutable data parts. Every replica stores the same data parts and coordinates replication through ClickHouse Keeper. This architecture enables efficient synchronization while minimizing replication overhead.

Although ClickHouse replication is highly reliable, operational issues such as network interruptions, ClickHouse Keeper outages, replica failures, disk problems, or configuration mismatches can occasionally cause replicas to fall out of sync. These situations are commonly referred to as replication conflicts, even though they are not conflicts in the traditional database sense.

Knowing how to identify, troubleshoot, and resolve replication issues is an essential skill for anyone operating ClickHouse in production.

In this guide, you'll learn how ClickHouse replication works, how to monitor replica health, diagnose common replication problems, and recover your cluster using the built-in administrative tools.

Understanding ClickHouse Replication

When data is inserted into a ReplicatedMergeTree table, ClickHouse performs several coordinated steps to ensure every replica eventually contains the same data.

The replication workflow is as follows:

  1. A client sends an INSERT request.
  2. The receiving replica creates a new immutable data part.
  3. Metadata describing the new part is written into ClickHouse Keeper.
  4. Other replicas detect the new metadata.
  5. Each replica downloads the missing data part.
  6. Background replication verifies consistency until all replicas are synchronized. 
Client
   │
   ▼
Distributed Table
   │
   ▼
Replica A
   │
Creates Data Part
   │
   ▼
ClickHouse Keeper
   │
Metadata Notification
   │
 ┌───────────────┐
 ▼               ▼
Replica B     Replica C
Download      Download
Missing Part  Missing Part
Enter fullscreen mode Exit fullscreen mode

Because ClickHouse replicates immutable parts instead of row-by-row updates, synchronization is both fast and reliable, even for very large datasets.

Prerequisites

Before following the examples in this guide, ensure you have the following environment:

  • A ClickHouse cluster using ReplicatedMergeTree
  • ClickHouse Keeper installed and operational
  • Two or more replicas participating in the cluster
  • Basic familiarity with distributed ClickHouse deployments

For simplicity, all examples use the default database and an orders table.

Common Replication Issues

The most frequently encountered replication problems include:

  • Replica enters read-only mode
  • Growing replication queue
  • Missing data parts
  • Lost replica after hardware or server failure
  • ClickHouse Keeper connection failures
  • Incorrect replication paths
  • Replication lag
  • Inactive replicas
  • Schema mismatches between replicas

Fortunately, ClickHouse provides comprehensive system tables for identifying each of these issues.

Monitoring Replication Health

The first step in troubleshooting replication is checking replica status.

SELECT
    database,
    table,
    replica_name,
    is_leader,
    is_readonly,
    active_replicas,
    total_replicas,
    queue_size
FROM system.replicas;
Enter fullscreen mode Exit fullscreen mode

A healthy replica generally reports:

  • is_readonly = 0
  • queue_size = 0
  • active_replicas = total_replicas

If these values differ, additional investigation is required.

Checking Pending Replication Tasks

The replication queue contains every task waiting to be processed.

SELECT
    database,
    table,
    type,
    create_time
FROM system.replication_queue
ORDER BY create_time;
Enter fullscreen mode Exit fullscreen mode

A continuously growing queue usually indicates replication delays caused by slow networking, unavailable replicas, or resource constraints.

Common Replication Problems and Solutions

1. Replica Becomes Read-Only

One of the most common operational issues is a replica entering read-only mode.

When this occurs, the replica cannot accept inserts or execute replication tasks.

Symptoms

  • INSERT statements fail
  • Replication stops
  • Replica falls behind the rest of the cluster

Check the replica status:

SELECT
    replica_name,
    is_readonly
FROM system.replicas;
Enter fullscreen mode Exit fullscreen mode

Typical causes include:

  • ClickHouse Keeper unavailable
  • Network interruption
  • Incorrect configuration
  • Replica losing communication with Keeper

Resolution

First, verify ClickHouse Keeper is reachable.

echo "ruok" | nc keeper-node 9181
Enter fullscreen mode Exit fullscreen mode

Expected response:

imok
Enter fullscreen mode Exit fullscreen mode

If Keeper is healthy:

  • Verify network connectivity between replicas.
  • Check firewall rules.
  • Review ClickHouse server logs.
  • Restart the ClickHouse server after resolving the underlying issue. 
sudo systemctl restart clickhouse-server
Enter fullscreen mode Exit fullscreen mode

Once communication with Keeper is restored, the replica typically exits read-only mode automatically.

2. Replication Queue Continues Growing

A steadily increasing replication queue usually indicates that a replica cannot process incoming replication tasks quickly enough.

Example:

queue_size = 250
Enter fullscreen mode Exit fullscreen mode

Possible causes include:

  • Slow network links
  • Large data transfers
  • Replica temporarily offline
  • Heavy background merge activity

Inspect the queue:

SELECT *
FROM system.replication_queue;
Enter fullscreen mode Exit fullscreen mode

Resolution

Depending on the cause:

  • Restore network connectivity
  • Bring offline replicas back online
  • Allow background synchronization to complete
  • Avoid frequent tiny inserts
  • Batch inserts whenever possible

Large queues often resolve automatically once connectivity improves.

3. Missing Data Parts

Sometimes one replica contains fewer data parts than the others.

This usually happens when a server was offline during an INSERT operation.

Symptoms

Queries executed against one replica return fewer rows than expected.

Compare active parts across replicas:

SELECT
    partition,
    count() AS part_count,
    sum(rows) AS total_rows
FROM system.parts
WHERE database = 'default'
  AND table = 'orders'
  AND active = 1
GROUP BY partition
ORDER BY partition;
Enter fullscreen mode Exit fullscreen mode

If the counts differ between replicas, some parts are missing.

Resolution

Synchronize the affected replica.

SYSTEM SYNC REPLICA default.orders;
Enter fullscreen mode Exit fullscreen mode

ClickHouse automatically downloads missing data parts from healthy replicas.

4. Schema Mismatch Between Replicas

Schema inconsistencies usually occur when DDL statements are executed on individual nodes instead of using cluster-wide operations.

Compare table definitions:

SELECT
    name,
    type
FROM system.columns
WHERE database = 'default'
  AND table = 'orders'
ORDER BY position;
Enter fullscreen mode Exit fullscreen mode

If the schema differs between replicas, synchronization problems may occur.

Resolution

Always execute schema changes with ON CLUSTER.

Example:

ALTER TABLE default.orders
ON CLUSTER cluster_1S_3R
ADD COLUMN IF NOT EXISTS discount Float64 DEFAULT 0.0;
Enter fullscreen mode Exit fullscreen mode

Using ON CLUSTER guarantees every replica receives the same schema modification.

5. Lost Replica

After rebuilding a server or replacing storage, the replica metadata may no longer exist locally.

Instead of recreating the table manually, restore the replica.

SYSTEM RESTORE REPLICA default.orders;
Enter fullscreen mode Exit fullscreen mode

ClickHouse reconstructs replica metadata using ClickHouse Keeper and downloads missing parts from healthy replicas.

6. ClickHouse Keeper Connection Failure

Replication depends entirely on ClickHouse Keeper.

When Keeper becomes unavailable:

  • Replicas stop synchronizing
  • Read-only mode may be enabled
  • Replication queues continue growing

Verify Keeper health.

echo "ruok" | nc keeper-node 9181
Enter fullscreen mode Exit fullscreen mode

Expected output:

imok
Enter fullscreen mode Exit fullscreen mode

If Keeper is unavailable:

  • Restore Keeper quorum
  • Resolve network issues
  • Restart affected replicas

Replication resumes automatically after Keeper becomes available.

Recovering a Severely Out-of-Sync Replica

In rare cases, a replica becomes too inconsistent to recover normally.

You may need to rebuild it.

Detach the table:

DETACH TABLE default.orders;
Enter fullscreen mode Exit fullscreen mode

Remove the replica registration from Keeper.

SYSTEM DROP REPLICA 'replica_3'
FROM TABLE default.orders;
Enter fullscreen mode Exit fullscreen mode

Reattach the table.

ATTACH TABLE default.orders;
Enter fullscreen mode Exit fullscreen mode

The replica registers again with Keeper and downloads every missing data part from healthy replicas.

Important: SYSTEM DROP REPLICA permanently removes the replica registration from ClickHouse Keeper. Only use this procedure when standard synchronization methods fail.

Verifying the Recovery

After applying any fix, verify that replication has returned to a healthy state.

SELECT
    database,
    table,
    replica_name,
    is_readonly,
    active_replicas,
    total_replicas,
    queue_size,
    last_exception
FROM system.replicas
WHERE table = 'orders';
Enter fullscreen mode Exit fullscreen mode

Healthy replicas typically report:

  • is_readonly = 0
  • active_replicas = total_replicas
  • queue_size = 0
  • last_exception is empty

If all conditions are met, replication has successfully recovered.

Useful SYSTEM Commands

Synchronize a replica:

SYSTEM SYNC REPLICA default.orders;
Enter fullscreen mode Exit fullscreen mode

Restart replication:

SYSTEM RESTART REPLICA default.orders;
Enter fullscreen mode Exit fullscreen mode

Restore a lost replica:

SYSTEM RESTORE REPLICA default.orders;
Enter fullscreen mode Exit fullscreen mode

These administrative commands are among the most valuable tools for ClickHouse operators.

Best Practices

Following operational best practices significantly reduces replication issues.

  • Monitor system.replicas continuously.
  • Regularly inspect system.replication_queue.
  • Execute all DDL using ON CLUSTER.
  • Insert data through Distributed tables rather than local replicated tables.
  • Batch inserts instead of sending thousands of tiny INSERT statements.
  • Deploy an odd number of ClickHouse Keeper nodes to maintain quorum.
  • Ensure reliable network connectivity between replicas.
  • Keep replica configuration identical across every node.
  • Configure alerts for queue_size > 100.
  • Alert immediately when is_readonly = 1.
  • Restart one replica at a time during maintenance windows.
  • Never manually delete data parts from the filesystem unless following an official recovery procedure.

Common Operational Mistakes

Mistake Impact
Restarting all replicas simultaneously Entire cluster becomes unavailable
Frequent tiny inserts Large replication queues
Ignoring Keeper health Replication failures
Incorrect replication paths Replicas cannot synchronize
Different table schemas Replication inconsistencies
Running DDL without ON CLUSTER Schema mismatches across replicas

Avoiding these mistakes dramatically improves cluster reliability.

Recommended Troubleshooting Workflow

Whenever replication problems occur, follow this sequence:

Replication Issue
        │
        ▼
Check system.replicas
        │
        ▼
Check system.replication_queue
        │
        ▼
Verify ClickHouse Keeper
        │
        ▼
Check Network Connectivity
        │
        ▼
Run SYSTEM SYNC REPLICA
        │
        ▼
Verify Replica Health
Enter fullscreen mode Exit fullscreen mode

Following this workflow helps isolate problems quickly while minimizing downtime.

Conclusion

Replication conflicts in ClickHouse® are relatively uncommon in properly configured clusters, but they can still occur because of network interruptions, ClickHouse Keeper outages, hardware failures, replica rebuilds, or accidental administrative operations.

Fortunately, ClickHouse provides powerful built-in tools for monitoring and recovery. System tables such as system.replicas and system.replication_queue, together with commands like SYSTEM SYNC REPLICA, SYSTEM RESTART REPLICA, and SYSTEM RESTORE REPLICA, make diagnosing and resolving replication issues straightforward.

By continuously monitoring replica health, maintaining a reliable ClickHouse Keeper cluster, executing schema changes with ON CLUSTER, and following proven operational best practices, you can ensure your replicated ClickHouse deployment remains consistent, resilient, and highly available even when infrastructure problems occur.

References

  • ClickHouse® Documentation – Replication
  • ClickHouse® Documentation – system.replicas
  • ClickHouse® Documentation – system.replication_queue
  • ClickHouse® Documentation – SYSTEM Statements
  • ClickHouse® Documentation – ClickHouse Keeper

Source: dev.to

arrow_back Back to News