Data SRE for ClickHouse: 7 Proven Reliability Practices We Run in Production

Definition

What Data SRE means for a ClickHouse estate

Most ClickHouse clusters are monitored. Far fewer are operated against an explicit reliability target. The difference shows up during an incident: a monitored cluster produces forty alerts and no decision, while a cluster run under Data SRE produces one page that says which objective is at risk, how fast the error budget is burning, and which runbook applies.

Data SRE borrows its vocabulary from site reliability engineering and adapts it to stateful systems. A stateless service recovers by restarting. A database has to recover without losing acknowledged writes, without serving stale replicas as if they were current, and without a restore that has never been rehearsed. That is why the practice puts as much weight on replication state, ClickHouse Keeper quorum health and restore drills as it does on query latency.

The terms overlap, so it helps to fix them. Data reliability engineering is the broader discipline, ClickHouse SRE is what it looks like on one engine, and Data SRE is the name we use for the practice as a whole.

We treat three properties as the core of reliability for analytical databases:

• Availability: the share of valid queries and inserts that succeed.
• Latency: the share of queries that complete inside the threshold the product actually needs, measured at p95 and p99 rather than the average.
• Freshness and durability: how far replicas and downstream materialized views lag behind ingestion, and whether a backup can be restored inside the recovery time objective.

Release coverage: ChistaDATA delivers consulting, 24×7 support, managed services and remote DBA on ClickHouse 26.8 LTS (current long-term-support release, August 2026, supported to August 2027) and 26.3 LTS (supported to March 2027), plus every supported stable release between them, with upgrade engineering for 24.x and 25.x estates. Last verified September 2026.

Practice 1

Compute service level indicators from system.query_log

ClickHouse already records what a Data SRE practice needs. system.query_log holds one row per query event with duration, exception code, memory and the initiating user. We compute availability and latency indicators directly from it, across every replica, rather than inferring them from host metrics.

-- Availability and latency SLIs per hour, last 24 hours, whole cluster.
-- Replace cluster_name with the name from system.clusters.
SELECT
    toStartOfHour(event_time) AS hour,
    countIf(type = 'QueryFinish') AS ok_queries,
    countIf(type = 'ExceptionWhileProcessing') AS failed_queries,
    round(ok_queries / (ok_queries + failed_queries), 5) AS availability_sli,
    quantileIf(0.95)(query_duration_ms, type = 'QueryFinish') AS p95_ms,
    quantileIf(0.99)(query_duration_ms, type = 'QueryFinish') AS p99_ms,
    round(countIf(type = 'QueryFinish' AND query_duration_ms <= 1000)
          / countIf(type = 'QueryFinish'), 5) AS latency_sli_1s
FROM clusterAllReplicas('cluster_name', system.query_log)
WHERE event_time >= now() - INTERVAL 24 HOUR
  AND is_initial_query = 1
  AND query_kind = 'Select'
  AND type IN ('QueryFinish', 'ExceptionWhileProcessing')
GROUP BY hour
ORDER BY hour;

Two details decide whether this indicator is trustworthy. First, is_initial_query = 1 keeps distributed sub-queries from being counted as separate user requests. Second, not every exception is the database’s fault. A syntax error or an unknown table is a client defect, so we classify exception codes into “counts against the budget” and “does not” before the number is shown to anyone.

-- Which errors are actually spending the budget?
SELECT
    exception_code,
    errorCodeToName(exception_code) AS error_name,
    count() AS occurrences,
    uniqExact(user) AS affected_users,
    any(substring(exception, 1, 120)) AS sample
FROM clusterAllReplicas('cluster_name', system.query_log)
WHERE event_time >= now() - INTERVAL 24 HOUR
  AND type = 'ExceptionWhileProcessing'
  AND is_initial_query = 1
GROUP BY exception_code
ORDER BY occurrences DESC
LIMIT 20;

Codes such as MEMORY_LIMIT_EXCEEDED, TOO_MANY_SIMULTANEOUS_QUERIES, TIMEOUT_EXCEEDED and TABLE_IS_READ_ONLY are reliability signals. SYNTAX_ERROR and UNKNOWN_TABLE usually are not. Check the retention of system.query_log before relying on it: the table has a TTL and is flushed on an interval, so a long-window SLO needs the log copied into a retained telemetry table.

Practice 3

Alert on burn rate, not on thresholds

Threshold alerts (“CPU above 80%”) page people for conditions users never notice and stay silent during slow degradations. Burn-rate alerting asks a better question: at the current error rate, how quickly will the budget run out? We use the multi-window, multi-burn-rate pattern described in the Google SRE Workbook chapter on alerting on SLOs, fed by the Prometheus endpoint that ClickHouse exposes natively.

<!-- /etc/clickhouse-server/config.d/prometheus.xml
     New listener: requires a server restart. Restrict port 9363 to the
     monitoring network before enabling. -->
<clickhouse>
    <prometheus>
        <endpoint>/metrics</endpoint>
        <port>9363</port>
        <metrics>true</metrics>
        <events>true</events>
        <asynchronous_metrics>true</asynchronous_metrics>
    </prometheus>
</clickhouse>

# prometheus/rules/clickhouse-slo.yml  (99.9% availability objective)
groups:
  - name: clickhouse-slo-burn
    rules:
      - record: clickhouse:query_error_ratio:rate5m
        expr: |
          sum(rate(ClickHouseProfileEvents_FailedQuery[5m]))
          / sum(rate(ClickHouseProfileEvents_Query[5m]))
      - record: clickhouse:query_error_ratio:rate1h
        expr: |
          sum(rate(ClickHouseProfileEvents_FailedQuery[1h]))
          / sum(rate(ClickHouseProfileEvents_Query[1h]))
      - record: clickhouse:query_error_ratio:rate6h
        expr: |
          sum(rate(ClickHouseProfileEvents_FailedQuery[6h]))
          / sum(rate(ClickHouseProfileEvents_Query[6h]))

      # Fast burn: 2% of a 30-day budget gone in one hour. Page.
      - alert: ClickHouseErrorBudgetFastBurn
        expr: |
          clickhouse:query_error_ratio:rate1h > (14.4 * 0.001)
          and clickhouse:query_error_ratio:rate5m > (14.4 * 0.001)
        for: 2m
        labels: {severity: page}
        annotations:
          summary: "ClickHouse availability budget burning at 14.4x"
          runbook: "https://runbooks.example.internal/clickhouse/error-budget-fast-burn"

      # Slow burn: 5% of the budget gone in six hours. Ticket.
      - alert: ClickHouseErrorBudgetSlowBurn
        expr: |
          clickhouse:query_error_ratio:rate6h > (6 * 0.001)
          and clickhouse:query_error_ratio:rate1h > (6 * 0.001)
        for: 15m
        labels: {severity: ticket}

ClickHouseProfileEvents_FailedQuery counts every failed query, including client mistakes, so the Prometheus rule is the fast detector and the classified system.query_log figure from Practice 2 remains the number of record. Every paging alert carries a runbook link. An alert without a runbook is removed or demoted during the first month of an engagement. A Data SRE rotation should be paged rarely, and always for a reason a user would recognise.

Practice 5

Keep merge and mutation pressure inside safe limits

The most common ClickHouse outage we are called into is not a crash. It is ingestion rejecting inserts with TOO_MANY_PARTS because parts are being created faster than merges can consolidate them. The cause is nearly always upstream: inserts that are too small, a partition key that is too fine, or a mutation that has monopolised the merge pool. Data SRE catches it while it is still a trend.

-- Active parts per partition against the server's own limits.
WITH
    (SELECT toUInt64(value) FROM system.merge_tree_settings
      WHERE name = 'parts_to_delay_insert') AS delay_limit,
    (SELECT toUInt64(value) FROM system.merge_tree_settings
      WHERE name = 'parts_to_throw_insert') AS throw_limit
SELECT
    database,
    table,
    partition,
    count() AS active_parts,
    delay_limit,
    throw_limit,
    round(100 * active_parts / throw_limit, 1) AS pct_of_throw_limit,
    formatReadableSize(sum(bytes_on_disk)) AS size_on_disk
FROM system.parts
WHERE active
GROUP BY database, table, partition
HAVING active_parts > delay_limit * 0.5
ORDER BY active_parts DESC
LIMIT 25;

Reading the limits from system.merge_tree_settings matters because the defaults have changed between releases and many estates override them per table. The same view of the world applies to mutations. An ALTER TABLE ... UPDATE or DELETE that cannot complete will retry indefinitely and hold merge capacity while it does.

-- Mutations that are stuck or failing.
SELECT
    database,
    table,
    mutation_id,
    substring(command, 1, 100) AS command,
    create_time,
    parts_to_do,
    latest_fail_time,
    substring(latest_fail_reason, 1, 160) AS latest_fail_reason
FROM clusterAllReplicas('cluster_name', system.mutations)
WHERE NOT is_done
  AND create_time < now() - INTERVAL 30 MINUTE
ORDER BY create_time;

The durable fix is an engineering change: larger insert batches or asynchronous inserts, a coarser partition key, or replacing row-level mutations with a ReplacingMergeTree or lightweight-delete design. Those changes come out of the reliability backlog, and the MergeTree storage engine guide explains the mechanics behind them.

Practice 7

Plan capacity from measured growth and make changes reversible

Capacity planning in Data SRE starts from telemetry rather than from a vendor sizing sheet. Disk growth per table, peak memory per query class and concurrent query counts are trended, and the forecast is stated with the date on which a limit will be reached if nothing changes.

-- New data written per table per day, last 30 days (inserts only, merges excluded).
-- Requires system.part_log, which is enabled in the default packaged configuration.
SELECT
    database,
    table,
    event_date,
    formatReadableSize(sum(size_in_bytes)) AS written,
    sum(rows) AS rows_written
FROM system.part_log
WHERE event_type = 'NewPart'
  AND event_date >= today() - 30
GROUP BY database, table, event_date
ORDER BY database, table, event_date;

-- Free space per disk, to set against the growth rate.
SELECT name, path,
       formatReadableSize(free_space) AS free,
       formatReadableSize(total_space) AS total,
       round(100 * free_space / total_space, 1) AS free_pct
FROM system.disks;

Change safety is the other half. Most reliability loss in a mature estate is self-inflicted: an upgrade, a schema change, a settings change. Every change we make follows the same shape, and the same shape is written into every runbook we hand over:

1. State the blast radius and the rollback path before the change is approved.
2. Run a verification query that captures the state before the change.
3. Apply the change to one replica or one shard first where the topology allows it.
4. Run a validation query and compare it with the verification result.
5. Hold for an agreed soak period while watching the burn rate, then proceed or roll back.

Upgrades follow the ClickHouse LTS calendar, one replica at a time, with the compatibility setting pinned until the whole cluster is on the new release. Schema changes on large tables are rehearsed on a restored copy from the most recent drill, which is one more reason the drills in Practice 6 pay for themselves.

FAQ

Data SRE questions we are asked most often