Monitoring systems operate on a simple loop: collect signals, detect anomalies, alert operators, investigate root cause. The bottleneck in most monitoring stacks is not collection but query performance. When an on-call engineer filters logs by service, time range, and error level, they need results in seconds, not minutes. When an alerting rule evaluates a threshold over the last 5 minutes of metrics, it needs sub-second query latency to fire before the incident escalates.
ClickHouse® handles monitoring data at the volume and latency that operational teams require. This post covers the event schemas, metric aggregation patterns, log search queries, anomaly detection, and alerting architectures that monitoring teams build with ClickHouse.
Monitoring event schema
Monitoring data spans three signal types, each with different schema requirements:
CREATE TABLE monitoring_events
(
ts DateTime64(3),
signal_type LowCardinality(String), -- 'metric', 'log', 'trace'
service LowCardinality(String),
host LowCardinality(String),
environment LowCardinality(String), -- 'production', 'staging'
severity LowCardinality(String), -- 'debug', 'info', 'warn', 'error', 'critical'
metric_name LowCardinality(Nullable(String)),
metric_value Nullable(Float64),
log_message Nullable(String),
trace_id Nullable(String),
span_id Nullable(String),
labels String -- JSON map of additional dimensions
)
ENGINE = MergeTree
PARTITION BY toYYYYMMDD(ts)
ORDER BY (service, signal_type, ts);
service and signal_type first in the sort key reflect the primary query pattern: filter by service, then by signal type, then by time range. Daily partitioning keeps partition count manageable while enabling efficient retention drops.
For real-time anomaly detection, storing raw metric values alongside computed baselines in the same table enables Z-score queries without joining separate tables.
Infrastructure metrics aggregation
Service-level metrics computed over sliding windows:
SELECT
service,
metric_name,
toStartOfMinute(ts) AS minute,
avg(metric_value) AS avg_value,
max(metric_value) AS max_value,
quantile(0.95)(metric_value) AS p95_value,
quantile(0.99)(metric_value) AS p99_value,
count() AS sample_count
FROM monitoring_events
WHERE signal_type = 'metric'
AND ts >= now() - INTERVAL 1 HOUR
GROUP BY service, metric_name, minute
ORDER BY minute DESC, service;
Current state per host using argMax:
SELECT
host,
service,
metric_name,
argMax(metric_value, ts) AS current_value,
max(ts) AS last_reported
FROM monitoring_events
WHERE signal_type = 'metric'
AND ts >= now() - INTERVAL 5 MINUTE
GROUP BY host, service, metric_name
HAVING current_value > 90
ORDER BY current_value DESC;
Hosts reporting CPU above 90% in the last 5 minutes surface immediately, without polling an external metrics store.
Log search and error correlation
For real-time error monitoring, log queries filter by service, severity, and time range:
SELECT
ts,
service,
host,
severity,
log_message,
trace_id
FROM monitoring_events
WHERE signal_type = 'log'
AND severity IN ('error', 'critical')
AND service = {{ String(service, required=True) }}
AND ts >= now() - INTERVAL 15 MINUTE
ORDER BY ts DESC
LIMIT 500;
Error rate by service over time, for alerting thresholds:
SELECT
service,
toStartOfMinute(ts) AS minute,
countIf(severity = 'error') AS errors,
countIf(severity = 'critical') AS criticals,
count() AS total_logs,
round(
countIf(severity IN ('error', 'critical')) / count() * 100, 2
) AS error_rate_pct
FROM monitoring_events
WHERE signal_type = 'log'
AND ts >= now() - INTERVAL 1 HOUR
GROUP BY service, minute
HAVING error_rate_pct > 1.0
ORDER BY minute DESC, error_rate_pct DESC;
Services with error rates above 1% in any minute trigger investigation before customers report the issue.
SLO tracking and burn rate alerts
Service Level Objective tracking computes error budget consumption over rolling windows:
SELECT
service,
toStartOfHour(ts) AS hour,
countIf(severity IN ('error', 'critical')) AS errors,
count() AS total_requests,
round(
(1 - countIf(severity IN ('error', 'critical')) / count()) * 100, 3
) AS availability_pct,
round(
countIf(severity IN ('error', 'critical')) / count() * 100, 3
) AS error_budget_burn_pct
FROM monitoring_events
WHERE signal_type = 'log'
AND ts >= now() - INTERVAL 24 HOUR
GROUP BY service, hour
HAVING availability_pct < 99.9
ORDER BY hour DESC, error_budget_burn_pct DESC;
Services below 99.9% availability in any hour have consumed error budget faster than their SLO allows. Burn rate alerts fire before the monthly SLO is exhausted, giving teams time to investigate before customer impact.
Anomaly detection on metrics
Z-score anomaly detection compares current values against a rolling baseline:
WITH baseline AS (
SELECT
service,
metric_name,
avg(metric_value) AS mean_value,
stddevPop(metric_value) AS stddev_value
FROM monitoring_events
WHERE signal_type = 'metric'
AND ts >= now() - INTERVAL 7 DAY
AND ts < now() - INTERVAL 1 HOUR
GROUP BY service, metric_name
),
current AS (
SELECT
service,
metric_name,
avg(metric_value) AS current_avg
FROM monitoring_events
WHERE signal_type = 'metric'
AND ts >= now() - INTERVAL 5 MINUTE
GROUP BY service, metric_name
)
SELECT
c.service,
c.metric_name,
c.current_avg,
b.mean_value,
b.stddev_value,
(c.current_avg - b.mean_value) / nullIf(b.stddev_value, 0) AS z_score
FROM current AS c
INNER JOIN baseline AS b USING (service, metric_name)
WHERE abs(z_score) > 3
ORDER BY abs(z_score) DESC;
Metrics with Z-scores above 3 standard deviations from their 7-day baseline are anomalies. This query runs in under a second over billions of metric data points.
Security event correlation
For ClickHouse cybersecurity logs, monitoring systems correlate security events across sources:
SELECT
JSONExtractString(labels, 'source_ip') AS source_ip,
count() AS event_count,
uniq(service) AS targeted_services,
countIf(severity = 'critical') AS critical_events,
min(ts) AS first_seen,
max(ts) AS last_seen
FROM monitoring_events
WHERE signal_type = 'log'
AND JSONExtractString(labels, 'event_category') = 'security'
AND ts >= now() - INTERVAL 1 HOUR
GROUP BY source_ip
HAVING event_count > 100
ORDER BY critical_events DESC, event_count DESC;
IP addresses generating more than 100 security events in an hour across multiple services indicate coordinated activity worth investigating.
Trace analysis and latency percentiles
Distributed trace monitoring requires percentile computation over span durations:
SELECT
service,
JSONExtractString(labels, 'operation') AS operation,
count() AS span_count,
quantile(0.50)(metric_value) AS p50_ms,
quantile(0.95)(metric_value) AS p95_ms,
quantile(0.99)(metric_value) AS p99_ms,
max(metric_value) AS max_ms
FROM monitoring_events
WHERE signal_type = 'trace'
AND ts >= now() - INTERVAL 1 HOUR
GROUP BY service, operation
HAVING span_count > 100
ORDER BY p99_ms DESC
LIMIT 50;
Operations with p99 latency above SLA thresholds surface for investigation. quantile() uses T-Digest approximation, making percentile computation feasible over billions of span records without full sorts.
Alerting rule evaluation
Alerting rules evaluate thresholds against pre-aggregated metrics on a schedule:
SELECT
service,
metric_name,
avg_value,
p99_value,
CASE
WHEN metric_name = 'cpu_usage' AND avg_value > 85 THEN 'critical'
WHEN metric_name = 'error_rate' AND avg_value > 5 THEN 'warning'
WHEN metric_name = 'memory_usage' AND avg_value > 90 THEN 'critical'
ELSE 'ok'
END AS alert_status
FROM minute_metrics
WHERE minute >= now() - INTERVAL 5 MINUTE
HAVING alert_status != 'ok'
ORDER BY alert_status DESC, avg_value DESC;
This query reads from the pre-aggregated minute_metrics table, evaluating all services in under 100ms. Each result triggers a notification through your alerting integration.
Pre-aggregated monitoring rollups
Alerting rules that evaluate every minute should read from pre-aggregated tables:
CREATE MATERIALIZED VIEW minute_metrics_mv TO minute_metrics AS
SELECT
toStartOfMinute(ts) AS minute,
service,
metric_name,
avg(metric_value) AS avg_value,
max(metric_value) AS max_value,
quantile(0.99)(metric_value) AS p99_value,
count() AS samples
FROM monitoring_events
WHERE signal_type = 'metric'
GROUP BY minute, service, metric_name;
For real-time logs analytics architectures, the same rollup pattern applies to log severity counts, error rates, and trace span durations.
Tinybird for monitoring systems
Building a monitoring analytics stack on ClickHouse requires managing ingestion from metrics agents, log shippers, and trace collectors, plus the query infrastructure for dashboards and alerting. Tinybird is managed ClickHouse that handles ingestion, storage, and query serving as parameterized HTTP endpoints.
The monitoring queries in this post become Tinybird Pipes: a service health endpoint called every 30 seconds by a Grafana dashboard, an error rate endpoint called by an alerting system, an anomaly detection endpoint called on a schedule. Build dashboards with real-time data interaction describes the pattern for ops dashboards powered by Tinybird endpoints.
For building a real-time fraud detection system, the same ClickHouse patterns apply: continuous event ingestion, sliding window aggregation, threshold-based alerting, and sub-second query response for investigation workflows.
Resend, processing 100TB per month on Tinybird, measured 62ms p90 query latency in production without caching. Monitoring queries over billions of events run inside that latency envelope. Tinybird is SOC 2 Type II certified.
The Kafka connector handles high-throughput log and metric streams without requiring custom consumer applications. Branch environments let monitoring teams iterate on schema changes and query definitions without affecting production datasources. SQL Pipes version alongside your monitoring rules, making it straightforward to add new alert dimensions or dashboard panels as your infrastructure grows.
What changes when monitoring is fast
Slow monitoring creates blind spots. Engineers filter logs with row limits because full scans time out. Alerting rules use coarse thresholds because fine-grained evaluation is too expensive. Incident investigation starts with guessing because correlating events across services takes too long.
When monitoring queries return in milliseconds, the workflow changes. Engineers filter freely across time ranges and dimensions. Alerting rules evaluate precise thresholds on fresh data. Incident correlation across services, hosts, and trace IDs happens in a single query instead of a multi-tool investigation.
ClickHouse makes that query performance the default, not an exception requiring dedicated hardware and custom indexing.