Alerting on Part Count Thresholds
A part-count alert that fires on a fixed number — “page me at 250 active parts” — is wrong on every table but the one it was tuned for, because the number that actually rejects inserts, parts_to_throw_insert, is a per-table MergeTree setting. The robust design alerts on active parts as a fraction of each table’s own delay and throw thresholds, so one rule covers every table and every partition and fires with enough lead time to act before ClickHouse starts rejecting writes with TOO_MANY_PARTS. This walkthrough builds that alert end to end: the system.parts query, the threshold math, a Prometheus rule (and a dependency-free scheduled-check alternative), and the remediation runbook the page attaches to.
Prerequisites
What to alert on, and why it is a ratio
The insert path has two per-partition guardrails: at parts_to_delay_insert ClickHouse artificially slows inserts, and at parts_to_throw_insert it rejects them. Both count active parts in a single partition, so the alert must group by partition and compare each partition’s active-part count to the table’s own thresholds. Expressing the alert as active_parts / parts_to_delay_insert gives a unitless ratio that means the same thing on every table: 1.0 is the point where inserts begin to slow, and the throw threshold is typically another 2x beyond that. Page on the delay-threshold ratio, not the throw-threshold ratio, because by the time you are near throw you have already lost the lead time to fix it gracefully.
Step 1 — Read the effective thresholds per table
Never hard-code 150/300. Read the live settings, and account for tables that override them in their DDL. The server-level defaults come from system.merge_tree_settings; per-table overrides live in the CREATE TABLE statement.
-- Server defaults for the guardrails.
SELECT name, value
FROM system.merge_tree_settings
WHERE name IN ('parts_to_delay_insert', 'parts_to_throw_insert');
-- Tables that override the guardrails in their own SETTINGS clause.
SELECT database, table,
extract(create_table_query, 'parts_to_throw_insert\s*=\s*(\d+)') AS throw_override,
extract(create_table_query, 'parts_to_delay_insert\s*=\s*(\d+)') AS delay_override
FROM system.tables
WHERE create_table_query LIKE '%parts_to_%insert%';
Expected output on a default install with no overrides:
┌─name──────────────────────┬─value─┐
│ parts_to_delay_insert │ 150 │
│ parts_to_throw_insert │ 300 │
└────────────────────────────┴───────┘
Fold any overrides into the collector so each table’s ratio uses its own denominator.
Step 2 — Query active parts per partition
The alert signal is the worst partition per table, because rejection is per-partition — one hot partition at 300 parts throws inserts even if every other partition is empty.
SELECT
database,
table,
max(active_parts) AS worst_partition_parts,
argMax(partition, active_parts) AS worst_partition,
sum(active_parts) AS total_active_parts
FROM (
SELECT database, table, partition, count() AS active_parts
FROM system.parts
WHERE active
GROUP BY database, table, partition
)
GROUP BY database, table
ORDER BY worst_partition_parts DESC;
Expected shape — the top row is the table closest to trouble:
┌─database──┬─table───────┬─worst_partition_parts─┬─worst_partition─┬─total_active_parts─┐
│ analytics │ events_raw │ 118 │ 20260717 │ 204 │
│ analytics │ sessions │ 22 │ 20260717 │ 61 │
└───────────┴─────────────┴───────────────────────┴─────────────────┴────────────────────┘
Here events_raw sits at 118 active parts against a delay threshold of 150 — a ratio of 0.79, already inside the warning band.
Step 3 — Compute the ratio and emit gauges
Push both the raw count and the denominator so the alert rule can compute the ratio in the alerting system (which keeps the thresholds visible in the rule, not buried in the collector). A clickhouse-connect collector:
import clickhouse_connect
client = clickhouse_connect.get_client(host="clickhouse", port=8123)
# Denominators, read once per scrape (cheap; cache if you scrape sub-second).
settings = {
row[0]: int(row[1])
for row in client.query(
"SELECT name, value FROM system.merge_tree_settings "
"WHERE name IN ('parts_to_delay_insert','parts_to_throw_insert')"
).result_rows
}
delay_default = settings["parts_to_delay_insert"]
throw_default = settings["parts_to_throw_insert"]
rows = client.query("""
SELECT database, table, max(active_parts) AS worst
FROM (
SELECT database, table, partition, count() AS active_parts
FROM system.parts WHERE active
GROUP BY database, table, partition
)
GROUP BY database, table
""").result_rows
for db, table, worst in rows:
# emit three series per table for the alerting system to combine
print(f'clickhouse_part_count_worst{{db="{db}",table="{table}"}} {worst}')
print(f'clickhouse_parts_to_delay_insert{{db="{db}",table="{table}"}} {delay_default}')
print(f'clickhouse_parts_to_throw_insert{{db="{db}",table="{table}"}} {throw_default}')
Expose the printed lines from an HTTP endpoint your Prometheus scrapes, or adapt them to your metrics client. Scrape every 15–30 seconds — part counts ramp fast during a burst, and a slow scrape interval will miss the climb.
Step 4 — Wire the Prometheus alert rule
With the count and its denominator as separate series, the rule expresses the ratio directly and stays readable. Two severities: a warning ticket at 0.6 of the delay threshold, a page at 1.0.
groups:
- name: clickhouse-part-health
rules:
- alert: ClickHousePartCountWarning
expr: |
clickhouse_part_count_worst
/ clickhouse_parts_to_delay_insert >= 0.6
for: 5m
labels:
severity: warning
annotations:
summary: "Active parts high on {{ $labels.db }}.{{ $labels.table }}"
description: "Worst partition at {{ $value | humanize }}x the delay threshold; merges are lagging inserts."
- alert: ClickHousePartCountCritical
expr: |
clickhouse_part_count_worst
/ clickhouse_parts_to_delay_insert >= 1.0
for: 2m
labels:
severity: critical
annotations:
summary: "Inserts slowing on {{ $labels.db }}.{{ $labels.table }} — TOO_MANY_PARTS approaching"
description: "Worst partition past parts_to_delay_insert; act before parts_to_throw_insert rejects writes."
The for: clauses matter: a 2-minute sustain on the critical rule filters the brief part spikes that a large insert followed by a fast merge produces, so you page on a real backlog, not a transient.
Step 5 — The dependency-free scheduled check
Where you have no Prometheus, a scheduled script does the whole job: query, compute ratio, post on breach. Run it from cron or a systemd timer every 30 seconds.
import sys, clickhouse_connect
client = clickhouse_connect.get_client(host="clickhouse", port=8123)
delay = int(client.query(
"SELECT value FROM system.merge_tree_settings WHERE name='parts_to_delay_insert'"
).result_rows[0][0])
rows = client.query("""
SELECT database, table, max(active_parts) AS worst
FROM (SELECT database, table, partition, count() AS active_parts
FROM system.parts WHERE active GROUP BY database, table, partition)
GROUP BY database, table
""").result_rows
breaches = [(db, t, w, round(w / delay, 2)) for db, t, w in rows if w / delay >= 0.6]
for db, table, worst, ratio in breaches:
sev = "CRITICAL" if ratio >= 1.0 else "WARNING"
# replace print with your Slack/PagerDuty webhook call
print(f"[{sev}] {db}.{table}: {worst} parts = {ratio}x delay threshold")
sys.exit(1 if any(r >= 1.0 for *_, r in breaches) else 0)
The non-zero exit on a critical breach lets a wrapping systemd timer or CI job surface the failure to whatever already watches exit codes.
Verification
Prove the alert fires without waiting for a real incident by manufacturing parts. Insert many tiny batches into a scratch table so its part count climbs, then confirm the collector sees it:
-- Create a scratch table and force small parts (one part per insert).
CREATE TABLE analytics.part_alert_test (id UInt64, ts DateTime64(3))
ENGINE = MergeTree PARTITION BY toYYYYMMDD(ts) ORDER BY id;
-- Run this 200 times (each is its own part) to cross the warning band.
INSERT INTO analytics.part_alert_test SELECT rand(), now64(3);
SELECT partition, count() AS active_parts
FROM system.parts
WHERE table = 'part_alert_test' AND active
GROUP BY partition;
-- Expect active_parts climbing toward parts_to_delay_insert; the alert should trip.
Confirm the ratio your rule would compute, then clean up so background merges do not have to:
SELECT max(active_parts) / 150.0 AS ratio_vs_delay
FROM (SELECT count() AS active_parts FROM system.parts
WHERE table = 'part_alert_test' AND active GROUP BY partition);
DROP TABLE analytics.part_alert_test;
Gotchas and edge cases
- Per-table overrides silently change the denominator. A table that sets
parts_to_throw_insert = 600in its DDL will reject much later than the server default suggests, and a fixed-number alert tuned to 300 will page constantly for no reason. Always read the effective per-table value — the override query in Step 1 — and let each table’s ratio use its own denominator. - Inactive parts are not counted by the guardrails, but they trip a different one.
parts_to_delay_insertcounts onlyactiveparts; the just-merged source parts (active = 0) do not push you towardTOO_MANY_PARTS. But ifinactive_parts_to_throw_inserthas been enabled, a cleanup thread that falls behind can reject inserts on inactive parts alone — alert on both populations if you have enabled that setting. - The worst partition, not the total, is what rejects. Summing active parts across all partitions hides the one hot partition that actually throws. A table with 40 partitions averaging 10 parts each looks fine on a total but is safe; a table with one partition at 290 looks fine on an average but is about to reject. Always alert on the per-partition maximum.
- A brief spike is normal after a large insert. One big
INSERTcan momentarily create several parts that a fast merge immediately coalesces. Thefor:duration on the alert rule exists precisely to ignore this — without it you will page on healthy bursts. When the count stays high, the situation is real, and the remediation belongs to diagnosing background merge lag. - Remediation is insert-side first. When the page fires, the durable fix is almost never raising the thresholds — it is enlarging insert batches so fewer parts are created per second, as laid out in batch insert optimization. Raising
parts_to_throw_insertwithout more merge capacity just moves the wall.
Related
- Part & Merge Health Monitoring — the parent pattern covering parts, merges, and the guardrails end to end.
- Diagnosing Background Merge Lag — what to do once an alert fires and merges are the bottleneck.
- Batch Insert Optimization — the root-cause fix that keeps part creation below merge throughput.