PULSE Check: Detecting Read-Only Replicas Before Users Notice

A read-only ClickHouse replica is the worst kind of failure: it looks healthy, responds to every query, and silently serves data frozen at the moment the ZooKeeper session died. Load balancers keep routing traffic to it. Health checks pass. Users get wrong answers and never know.

This PULSE Check covers the queries that catch read-only state before it corrupts analytics. Each maps to the U (Uptime) dimension of the PULSE Framework.

Why Read-Only Is Dangerous

When a replica loses its ZooKeeper session:

• It can no longer receive new data via replication
• It can no longer coordinate merges with other replicas
• It can no longer process writes (inserts fail on this replica)
• It continues serving SELECT queries with whatever data it had

The replica doesn't crash. It doesn't throw errors on reads. It responds to TCP health checks. From the outside — from Grafana, from your load balancer, from application health endpoints — it looks perfectly fine.

But every query that hits this replica returns data frozen at the moment the session died. If the session died 6 hours ago, the replica is missing 6 hours of data. If the session died 3 days ago, it's missing 3 days. This is silent replication drift at its most extreme.

The Detection Queries

Query 1: Immediate Read-Only Check

-- Run this on every node. Any result = critical.
SELECT
    database,
    table,
    replica_name,
    is_readonly,
    is_session_expired,
    absolute_delay,
    queue_size,
    active_replicas,
    total_replicas
FROM system.replicas
WHERE is_readonly = 1
ORDER BY database, table;

Pass criteria: Zero rows. Any read-only replica requires immediate investigation.

What the columns tell you:

• is_session_expired = 1 → ZooKeeper session is dead. The replica will recover automatically when ZK comes back. If ZK is healthy but session expired, check network between ClickHouse and ZK nodes.
• absolute_delay → How far behind this replica is. If the session died recently, delay may still be low. If it's been hours, the delay shows the full gap.
• active_replicas < total_replicas → Confirms other replicas also see this one as down.

Query 2: Near-Read-Only Detection

Before a replica goes fully read-only, there are warning signs:

-- Replicas showing signs of ZooKeeper stress
SELECT
    database,
    table,
    replica_name,
    is_readonly,
    is_session_expired,
    zookeeper_exception,
    absolute_delay,
    queue_size,
    future_parts
FROM system.replicas
WHERE is_session_expired = 1
   OR zookeeper_exception != ''
   OR (queue_size > 100 AND absolute_delay > 60)
ORDER BY absolute_delay DESC;

A nonzero zookeeper_exception or is_session_expired = 1 with is_readonly = 0 means the replica is about to go read-only. It's still processing queries and replication but the ZooKeeper connection is degraded.

Query 3: ZooKeeper Health From ClickHouse's Perspective

-- Can ClickHouse reach ZooKeeper?
SELECT *
FROM system.zookeeper
WHERE path = '/'
LIMIT 1;

If this query fails or times out, ZooKeeper is unreachable from this node. All replicated tables on this node will go read-only shortly.

-- Check ZooKeeper response time
SELECT
    name,
    value
FROM system.zookeeper
WHERE path = '/clickhouse'
LIMIT 10;

Slow response (> 1 second) from this query indicates ZooKeeper latency that can cause session timeouts.

For deeper ZooKeeper monitoring, see the ZooKeeper dependency guide.

Query 4: Stale Data Age Estimation

When you find a read-only replica, estimate how stale the data is:

-- How old is the newest data on this replica?
SELECT
    database,
    table,
    max(modification_time) AS latest_data,
    dateDiff('minute', max(modification_time), now()) AS staleness_minutes
FROM system.parts
WHERE active = 1
GROUP BY database, table
ORDER BY staleness_minutes DESC
LIMIT 20;

Run this on the read-only replica. staleness_minutes shows how long this replica has been frozen. Any table with staleness over your ingestion interval is serving incomplete data.

Query 5: Load Balancer Exposure

Estimate how many queries are hitting the stale replica:

-- Recent queries served by this node (run on the read-only replica)
SELECT
    toStartOfMinute(event_time) AS minute,
    count() AS queries,
    countIf(type = 'QueryFinish') AS completed,
    countIf(type = 'ExceptionWhileProcessing') AS errors
FROM system.query_log
WHERE event_time > now() - INTERVAL 30 MINUTE
  AND is_initial_query = 1
GROUP BY minute
ORDER BY minute DESC;

If this replica is serving hundreds of queries per minute while read-only, every one of those queries is returning stale data. The impact scales with traffic.

Fixing Read-Only State

Step 1: Check ZooKeeper

Is ZooKeeper itself healthy? If all nodes report session issues, the problem is ZooKeeper, not ClickHouse.

# Check ZooKeeper status (run on ZK node)
echo ruok | nc localhost 2181
# Should return: imok
 
# Check ZooKeeper connections
echo stat | nc localhost 2181

Step 2: Restart ZooKeeper Session

If ZooKeeper is healthy but ClickHouse lost its session:

-- Force reconnection to ZooKeeper
SYSTEM RESTART REPLICA database.table_name;

This restarts the replication subsystem for one table. For all tables:

-- Restart all replicated tables' ZK sessions
SYSTEM RESTART REPLICAS;

Step 3: Verify Recovery

After restarting, verify the replica is catching up:

SELECT database, table, replica_name,
    is_readonly,
    absolute_delay,
    queue_size
FROM system.replicas
WHERE is_readonly = 1
   OR absolute_delay > 30
   OR queue_size > 0
ORDER BY absolute_delay DESC;

If is_readonly is back to 0 and queue_size is decreasing, the replica is recovering. Wait for absolute_delay to reach 0 and queue_size to drain before considering the incident resolved.

Step 4: Check for Data Gaps

Even after recovery, verify the replica's data matches other replicas. See Your Replicas Are Lying for the cross-replica consistency check queries.

Prevention: Don't Wait for Read-Only

Monitor ZooKeeper health proactively:

-- Monitor ZK session health as part of PULSE U (Uptime)
SELECT
    database,
    table,
    replica_name,
    CASE
        WHEN is_readonly = 1 THEN 'CRITICAL: Read-only'
        WHEN is_session_expired = 1 THEN 'WARNING: Session expired'
        WHEN active_replicas < total_replicas THEN 'WARNING: Missing replicas'
        WHEN absolute_delay > 30 THEN 'WARNING: Replication lag'
        ELSE 'OK'
    END AS status,
    absolute_delay,
    queue_size
FROM system.replicas
ORDER BY status ASC, absolute_delay DESC;

Add this to a 60-second monitoring loop. The WARNING states precede read-only — catching them gives you minutes to hours of lead time.

How ClusterSight Detects Read-Only State

ClusterSight monitors is_readonly on every replica every 60 seconds as part of the U (Uptime) PULSE dimension:

• Instant detection — alerts within 60 seconds of a replica going read-only
• Stale data estimation — calculates how much data the read-only replica is missing
• Impact assessment — estimates query volume hitting the stale replica
• Recovery tracking — monitors queue drain and delay reduction after ZK reconnection
• ZooKeeper health trending — catches session degradation before read-only state occurs

A read-only replica drops the U score to 40 or below. The health score immediately reflects the severity.

Check your cluster's PULSE.

This post is part of the PULSE Check series — tactical health checks for ClickHouse operators.

Read next:

• Your Replicas Are Lying: How Silent Replication Drift Corrupts Analytics
• The Mutation That Ran for 72 Hours
• The Complete Guide to system.replicas
• ClickHouse ZooKeeper Dependency: What Breaks When Keeper Fails
• PULSE Check: 5 Queries Before Every ClickHouse Upgrade
• How to Monitor ClickHouse in Production