Monitoring Replication Lag with system.replicas

Replication lag in ClickHouse is not a single number you read — it is the arithmetic between a replica’s log_pointer and the shared log’s log_max_index, cross-checked against absolute_delay and the pending entries in system.replication_queue. This how-to walks through computing lag correctly, setting alert thresholds that distinguish a draining backlog from a stuck one, and remediating queue entries that refuse to advance.

The trap is treating absolute_delay alone as the health signal. It is a good staleness proxy, but it says nothing about direction: a replica at 30 seconds of delay that is catching up needs no action, while the same 30 seconds on a stalled queue is an incident in progress. Reading the log-index gap and the queue composition together is what tells the two apart.

Prerequisites

• A ReplicatedMergeTree table with at least two replicas registered in system.replicas
• SELECT access to system.replicas, system.replication_queue, and system.replicated_fetches
• The SYSTEM SYNC REPLICA and SYSTEM RESTART REPLICA grants for remediation (an infrastructure role)
• A metrics sink (Prometheus, VictoriaMetrics, or a ClickHouse history table) that samples per node
• The deployment’s replication factor and your freshness budget in seconds (the delay you can tolerate on reads)

How lag decomposes across the system tables

Every insert into a replicated table appends an entry to the shared replication log held in Keeper. log_max_index is the newest index in that log; log_pointer is the last index this replica has enqueued locally. The difference is the backlog still to apply, and it materializes as rows in system.replication_queue. absolute_delay is the wall-clock age of the oldest unprocessed entry — the staleness a reader sees.

Step 1 — Read the raw lag signals

Start with one snapshot row per local replica. These five columns are the whole picture; everything downstream is interpretation.

SELECT
    database,
    table,
    absolute_delay,                      -- staleness in seconds
    queue_size,                          -- total pending entries
    inserts_in_queue,                    -- pending GET_PART (fetch) entries
    log_max_index - log_pointer AS log_gap,  -- entries not yet enqueued locally
    is_readonly,
    is_session_expired
FROM system.replicas
ORDER BY absolute_delay DESC;

Expected healthy output — delay near zero, a small gap that does not grow between samples:

┌─database──┬─table──────┬─absolute_delay─┬─queue_size─┬─inserts_in_queue─┬─log_gap─┬─is_readonly─┬─is_session_expired─┐
│ analytics │ events_raw │              0 │          2 │                0 │       1 │           0 │                  0 │
└───────────┴────────────┴────────────────┴────────────┴──────────────────┴─────────┴─────────────┴────────────────────┘

The key discipline: sample this on an interval and compare consecutive readings. A stable or shrinking queue_size and log_gap mean the replica is keeping up; a monotonic climb across three or more samples is real lag, not a transient merge.

Step 2 — Distinguish a draining backlog from a stuck one

queue_size cannot tell you whether entries are moving. system.replication_queue can, because it exposes retry counts and errors per entry.

SELECT
    type,                       -- GET_PART, MERGE_PARTS, MUTATE_PART, ...
    count()          AS entries,
    max(num_tries)   AS max_tries,
    max(num_postponed) AS max_postponed,
    any(last_exception) AS sample_exception
FROM system.replication_queue
WHERE database = 'analytics' AND table = 'events_raw'
GROUP BY type
ORDER BY entries DESC;

Interpretation:

• max_tries in the low single digits with an empty sample_exception is a healthy backlog draining in order.
• max_tries in the dozens or hundreds on the same type is a stuck entry — the queue is retrying and failing, so nothing behind it advances.
• A large GET_PART count points at fetch/network problems; a large MERGE_PARTS count points at merge-thread saturation.

Step 3 — Confirm active fetches are progressing

When inserts_in_queue is high, the replica should be pulling parts. Verify the transfers are moving rather than wedged.

SELECT
    table,
    source_replica_hostname,
    round(progress, 3) AS progress,
    round(elapsed, 1)  AS elapsed_s,
    formatReadableSize(total_size_bytes_compressed) AS part_size
FROM system.replicated_fetches
ORDER BY elapsed DESC;

Sample this twice a few seconds apart. If progress advances between samples, catch-up is healthy and you should wait it out. If progress is frozen — or the table is empty while inserts_in_queue stays high — fetches are not being scheduled, which is your signal to check bandwidth throttling and Keeper before touching the queue.

Step 4 — Set alert thresholds on trend, not instant value

Alert on sustained conditions so a single merge does not page anyone. Encode the rules against your history table; expressed as a query over sampled rows:

-- Fire when lag is BOTH high AND not improving over the last 3 samples.
SELECT host, database, table,
       max(absolute_delay) AS worst_delay,
       argMax(queue_size, sampled_at) - argMin(queue_size, sampled_at) AS queue_trend
FROM monitoring.replica_health_history
WHERE sampled_at > now() - INTERVAL 2 MINUTE
GROUP BY host, database, table
HAVING worst_delay > 60      -- staleness beyond a 60 s budget
   AND queue_trend >= 0      -- queue flat or growing, i.e. not draining
ORDER BY worst_delay DESC;

Practical thresholds for a healthy cluster: warn at absolute_delay > 30 s sustained over 90 seconds, page at absolute_delay > 120 s or any is_session_expired = 1, and page immediately when max(num_tries) > 20 on any queue entry regardless of delay — a poison entry only gets worse.

Anchor those thresholds to a freshness budget rather than a round number. If dashboards tolerate 60-second-old data, a warn at half the budget and a page at the budget gives you a full window to react before readers notice. And always gate the delay alert on queue_trend >= 0: a 90-second delay that is shrinking by 20 entries per sample is a merge finishing, not an incident, and paging on it trains the on-call to ignore the alert.

Step 5 — Remediate stuck queue entries

Once you have identified a stuck entry, choose the least disruptive fix that clears it.

-- Re-reconcile this replica's parts against its peers. Clears most GET_PART stalls.
SYSTEM SYNC REPLICA analytics.events_raw;

-- If the queue is wedged on corrupt local metadata, restart just the replica's
-- Keeper interaction without dropping data:
SYSTEM RESTART REPLICA analytics.events_raw;

SYSTEM SYNC REPLICA blocks until the local replica has pulled everything its peers hold, which both clears fetch backlogs and confirms the fix. For an entry that names a genuinely missing or corrupt part on the source, detach that part on the source replica so the queue can advance; for a replica whose Keeper metadata is unrecoverable, SYSTEM RESTORE REPLICA analytics.events_raw rebuilds it from the surviving replicas. Escalating replica-level failover beyond this belongs to the replication & availability monitoring runbook.

Verification

After remediation, confirm the queue is draining and the delay is collapsing toward zero:

SELECT database, table, absolute_delay, queue_size, inserts_in_queue,
       log_max_index - log_pointer AS log_gap
FROM system.replicas
WHERE table = 'events_raw';
-- Expect queue_size and log_gap shrinking on each successive read, absolute_delay → 0.

Take two or three readings a few seconds apart. Success is not a single low number but a clear downward trajectory across samples, ending at queue_size in the low single digits and absolute_delay near zero.

Gotchas and edge cases

• absolute_delay is zero on an idle table even when Keeper is unreachable. With no new inserts there is nothing to fall behind on, so a quiet table can mask a lost session. Always pair the delay check with is_session_expired rather than trusting delay in isolation.
• system.replicas is node-local. Querying it through a load balancer reports only whichever replica you were routed to. Scrape every node by its own address, or a lagging replica will be invisible precisely when it matters.
• log_pointer can briefly exceed what has been applied. It marks the last entry enqueued locally, not executed, so a small nonzero log_gap with queue_size draining is normal steady state, not lag.
• SYSTEM SYNC REPLICA can block for a long time on a large backlog. It waits for full catch-up, so wrap it with a timeout in automation (RECEIVE_TIMEOUT) and never call it inline on a request path.
• A growing queue_size with an empty system.replication_queue error column is often upstream. The replica is healthy but inserts are arriving faster than parts replicate; fix the write path (larger blocks, buffering) rather than the replica.

Related

• Replication & availability monitoring — the parent runbook covering Keeper health, read-only flips, and failover routing.
• Tracking memory pressure with system.metrics — the memory ceiling a fast catch-up must not breach.
• Fallback routing & high availability — routing reads away from a lagging replica while it recovers.

Up: Replication & Availability Monitoring