Designing a Blazing-Fast Alerting System with ClickHouse: A Deep Dive

How open-source ClickHouse Alert Manager evaluates SQL-based alert rules, tracks state in MongoDB, schedules via Redis, and sends Prometheus Alertmanager webhooks.

What ClickHouse Alert Manager Does

• Runs any ClickHouse SQL as an alert rule on a schedule.
• Puts rule definitions in MongoDB (apmAlertTypes) and tracks live alert state in apmClickhouseAlertManager.
• Uses two Redis sorted sets (apm_alert_type_scheduler_queue and apm_alert_firing_scheduler) to manage scheduling and notifications.
• Splits evaluation from notification — so you can control notification noise with forTime.
• Pushes firing and resolved alerts to Alertmanager’s webhook endpoint using standard Prometheus JSON.
• Can write every evaluated result to a ClickHouse alerts_history table for later audits.

Architecture and System Context

You get three main components:

• HTTP server: handles /health, /translateQuery, exposes metrics.
• Evaluation engine: loads rules and runs SQL against ClickHouse.
• Firing engine: manages notification queue and posts alerts to Alertmanager.

Dependencies:

• ClickHouse: runs queries, can store alert history if you want it.
• Redis: two ZSETs — one for rule evaluation cadence, one for notification delay.
• MongoDB: rules live in apmAlertTypes, live alert state in apmClickhouseAlertManager.
• Prometheus Alertmanager: standard webhook receiver for alerts.

Here’s how the pieces fit together:

System context diagram: clickhouse-alert-manager with HTTP, evaluation, and firing engines connected to ClickHouse, Redis (two ZSETs), MongoDB, and Alertmanager.

Evaluation Pipeline (Rules → ClickHouse → State)

On startup, the service grabs a Redis SETNX lock so only one instance loads active rules into the apm_alert_type_scheduler_queue ZSET. No split-brain here—just one node in charge.

Get Mrityunjay’s stories in your inbox

Join Medium for free to get updates from this writer.

Remember me for faster sign in

Workers (usually goroutines) pop due rule IDs from the evaluation queue using an atomic Lua script:

local id = redis.call('ZRANGEBYSCORE', KEYS[1], '-inf', ARGV[1], 'LIMIT', 0, 1)[1]
if id then redis.call('ZREM', KEYS[1], id) end
return id

For each popped rule:

1. Fetch the rule definition from MongoDB.
2. Run the rule’s SQL against ClickHouse, get result rows.
3. Build label sets and annotations from each row.
4. Hash the sorted labels with SHA-256 to generate a deduplication key (UniqShaKey).
5. Diff these against alert state in MongoDB:

• New alert: insert with isFiring=false, isNotifiable=true.
• Ongoing: update lastSeenAt.
• Resolved: flag for immediate notification (so people actually hear about recoveries).

1. Reschedule the rule in Redis by updating its ZSET score (next_run = now + evaluationInterval).
2. Optionally, batch-insert the results into ClickHouse’s alerts_history table for audit trails.

The atomic pop is what keeps things safe — no double-processing, no lost rules. Most teams mess this up by not using Lua scripts.

Firing Pipeline (forTime → Alertmanager)

When an alert is marked isNotifiable, it’s enqueued in the firing ZSET (apm_alert_firing_scheduler) with a due time of now + forTime. This controls how long an alert must be firing before you actually notify anyone. It’s the difference between a noisy Slack channel and a system you can trust.

Workflow:

1. Worker pops due alerts from the firing ZSET using the same atomic Lua script as above.
2. Fetch alert state from MongoDB.
3. If the alert is firing (isFiring=false), POST the standard Prometheus alert JSON to Alertmanager. If it’s resolved, include endsAt and skip the forTime delay—resolves are instant.
4. Update MongoDB: set isFiring=true, lastNotifiedAt=now, and isNotifiable=false. Resolved alerts get their state cleared or updated.

Why Two Queues

Evaluation cadence (how often you check the rule) is not the same as notification cadence (how often you notify). If your SQL query flips state every minute, you don’t want a flood of notifications. The evaluation ZSET controls polling. The firing ZSET enforces the forTime before notifications go out. This separation is what keeps your on-call from burning out.

PromQL / LokiQL → ClickHouse SQL (translateQuery)

Send a POST to /translateQuery with a batch of queries:

{ "queries": [
  {"type":"promql","expr":"rate(requests_total[5m]) > 100"},
  {"type":"loki","expr":"{app=\"db\"} |= \"ERROR\""}
]}

You’ll get back:

{"results":[
  {"sql":"SELECT _time, value FROM otel_gauge WHERE metric='requests_total'"},
  {"sql":"SELECT timestamp, message FROM otel_logs WHERE app='db' AND message LIKE '%ERROR%'"}
]}

• PromQL is parsed with the official parser, then planned and translated into ClickHouse SQL.
• LokiQL support is a hand-written subset parser — simple but effective.
• Metric type routes to the right OTel ClickHouse table (gauge, sum, histogram, summary, logs).
• Batch queries are supported; shape your request as needed.

The translation isn’t perfect, but it’s enough to get real work done — especially for standard OTel schemas.

Design Choices That Matter in Production

• Atomic Lua pop-and-remove on Redis ZSET means no duplicate rule execution.
• Distributed lock (SETNX) on bootstrap keeps rule loading single-sourced.
• SHA-256 of sorted labels as dedup key: stable identity, no accidental merges or splits.
• Singleton clients for MongoDB and Redis — keeps connection churn low and avoids weird pool starvation bugs.
• Generic alert CASE wrapping: lets you override severity and annotations per rule via IsGenericAlert and severityOptions.

It’s not glamorous, but these details are why it works reliably at scale.