ClickHouse for time series metrics, rollups, and SLOs

Time-series workloads look simple until volume shows up: 50k hosts × 200 metrics × 10-second scrape interval is 360M samples per hour. Dashboards ask for p95 latency by service over 30 days. Anomaly jobs compare the last 15 minutes to the same window yesterday. Product APIs need the latest value per device without scanning a week of raw samples.

ClickHouse^® is not a dedicated TSDB, but MergeTree engines, columnar compression, and materialized views handle time-series patterns when you design sort keys, rollup tiers, and TTL deliberately. Per ClickHouse MergeTree documentation, query cost is proportional to bytes read, not rows stored.

This post covers schema choices, counter vs gauge semantics, multi-tier rollups, rate and quantile queries, gap handling, out-of-order data, and when ClickHouse beats purpose-built time-series databases.

Why columnar storage fits time series

Time-series queries almost always touch a subset of columns: timestamp, entity ID, metric name, value. A row-oriented database reads every column in every row even when the query needs two fields. ClickHouse stores each column separately, so a query summing one metric across a month reads only that metric's column plus the sort-key columns needed for filtering.

Similar numeric values compress well when stored together. ClickHouse codecs like Delta and Gorilla (designed for floating-point time series) further reduce storage on monotonic or slowly-changing metric streams. For background on when columnar beats row stores for analytics, see when to use a columnar database.

Wide vs narrow schema

Wide table: one timestamp row with many metric columns (cpu, mem, disk). Works when metric names are fixed and small (≤20 columns), typical for host agent snapshots.

Narrow table: (timestamp, entity_id, metric_name, value). Default for telemetry at scale:

CREATE TABLE metrics
(
    event_time    DateTime64(3),
    entity_id     String,
    entity_type   LowCardinality(String),
    metric_name   LowCardinality(String),
    value         Float64,
    tags          Map(String, String)
)
ENGINE = MergeTree
PARTITION BY toYYYYMM(event_time)
ORDER BY (entity_type, metric_name, entity_id, event_time)
SETTINGS index_granularity = 8192;

Pick ORDER BY based on dashboard entry point:

Wrong sort key is the main reason time-series queries "work in dev" and fail in prod. If 80% of queries filter by entity_id, leading with metric_name forces full scans.

Counter vs gauge semantics

Prometheus distinguishes counters (monotonically increasing) from gauges (any value). ClickHouse stores both as Float64 unless you enforce semantics in schema.

Counters need rate calculation, not raw averages:

SELECT
    entity_id,
    metric_name,
    (max(value) - min(value)) / dateDiff('second', min(event_time), max(event_time)) AS rate_per_sec
FROM metrics
WHERE metric_name = 'http_requests_total'
  AND event_time >= now() - INTERVAL 5 MINUTE
GROUP BY entity_id, metric_name;

For production counters, store delta per scrape interval at ingest (value - previous_value) or use rollup tables that sum deltas. Averaging counter raw values produces meaningless charts.

Gauges (CPU, memory, queue depth) aggregate with avg, min, max, and quantiles directly.

Ingestion: batch size, timestamps, and skew

Per ClickHouse load performance guidance, batch inserts of 10,000+ rows dramatically outperform single-row inserts. At 360M samples/hour, even 1,000-row batches create 360k inserts per hour and merge backlog.

Rules that survive production:

• Batch thousands of samples per insert (10k–100k at high volume)
• Use DateTime64(3) when sub-second ordering matters
• Reject or quarantine samples more than N minutes in the future (clock skew)
• Keep ingestion timezone consistent (UTC in storage; convert in UI)
• Partition Kafka topics by entity_type or region for parallel consumers

For Kafka-fed metrics, the stream Kafka to ClickHouse pattern (Kafka engine → materialized view → MergeTree) removes application-side batching code.

Tiered rollups: 1m → 1h → 1d

Raw samples power recent drill-down. Rollups power long ranges. Per AggregatingMergeTree documentation, the engine merges aggregate states during background merges, reducing row count by orders of magnitude.

One-minute AggregatingMergeTree:

CREATE TABLE metrics_1m
(
    minute        DateTime,
    entity_type   LowCardinality(String),
    metric_name   LowCardinality(String),
    entity_id     String,
    avg_value     AggregateFunction(avg, Float64),
    min_value     AggregateFunction(min, Float64),
    max_value     AggregateFunction(max, Float64),
    p95_value     AggregateFunction(quantile(0.95), Float64),
    sample_count  SimpleAggregateFunction(sum, UInt64)
)
ENGINE = AggregatingMergeTree
PARTITION BY toYYYYMM(minute)
ORDER BY (entity_type, metric_name, entity_id, minute);

CREATE MATERIALIZED VIEW metrics_1m_mv TO metrics_1m AS
SELECT
    toStartOfMinute(event_time) AS minute,
    entity_type,
    metric_name,
    entity_id,
    avgState(value) AS avg_value,
    minState(value) AS min_value,
    maxState(value) AS max_value,
    quantileState(0.95)(value) AS p95_value,
    count() AS sample_count
FROM metrics
GROUP BY minute, entity_type, metric_name, entity_id;

Query rollups with -Merge combinators:

SELECT
    minute,
    entity_id,
    avgMerge(avg_value) AS avg_value,
    quantileMerge(0.95)(p95_value) AS p95_value,
    sum(sample_count) AS samples
FROM metrics_1m
WHERE minute >= now() - INTERVAL 24 HOUR
  AND metric_name = 'request_latency_ms'
GROUP BY minute, entity_id
ORDER BY minute;

Build metrics_1h from metrics_1m with a second materialized view when 30-day charts still read too many minute rows. The materialized view example pattern chains rollups the same way in self-hosted ClickHouse and managed platforms.

For high-volume time-series dashboards, never point Grafana at raw samples for ranges beyond 24 hours.

Latest value per entity

Ops panels need current readings without a separate state table:

SELECT
    entity_id,
    metric_name,
    argMax(value, event_time) AS current_value,
    max(event_time) AS last_seen,
    dateDiff('second', last_seen, now()) AS lag_sec
FROM metrics
WHERE entity_type = 'host'
  AND event_time >= now() - INTERVAL 10 MINUTE
GROUP BY entity_id, metric_name
HAVING lag_sec > 120
ORDER BY lag_sec DESC;

argMax retrieves the latest value per entity-metric pair. Alert on lag_sec separately from the value itself. A stale CPU reading of 40% is an ingest outage, not healthy hardware.

Rates, window comparisons, and SLO queries

Hour-over-hour change on rollup:

WITH
    now() AS t_end,
    t_end - INTERVAL 1 HOUR AS t_start,
    t_start - INTERVAL 1 HOUR AS t_prev_start
SELECT
    metric_name,
    sumIf(sample_count, minute >= t_start) AS samples_current,
    sumIf(sample_count, minute >= t_prev_start AND minute < t_start) AS samples_previous,
    (samples_current - samples_previous) / samples_previous AS pct_change
FROM metrics_1m
WHERE minute >= t_prev_start
GROUP BY metric_name
HAVING samples_previous > 0
ORDER BY abs(pct_change) DESC;

p95 by service for SLO dashboards:

SELECT
    entity_type AS service,
    quantileMerge(0.95)(p95_value) AS p95_latency
FROM metrics_1m
WHERE minute >= now() - INTERVAL 6 HOUR
  AND metric_name = 'request_latency_ms'
GROUP BY service
ORDER BY p95_latency DESC;

Error budget burn (simplified):

SELECT
    toStartOfHour(minute) AS hour,
    entity_type AS service,
    countIf(avgMerge(avg_value) > 500) AS bad_minutes,
    count() AS total_minutes,
    bad_minutes / total_minutes AS burn_rate
FROM metrics_1m
WHERE minute >= now() - INTERVAL 24 HOUR
  AND metric_name = 'request_latency_ms'
GROUP BY hour, service
HAVING burn_rate > 0.05
ORDER BY burn_rate DESC;

Tune thresholds to your SLO definition. The pattern shows how SQL rollups support SLO monitoring without exporting everything back to Prometheus.

Histograms and multiple quantiles

Store pre-computed quantiles in rollups when dashboards need p50/p95/p99 together:

CREATE TABLE metrics_1m_quantiles
(
    minute        DateTime,
    entity_type   LowCardinality(String),
    metric_name   LowCardinality(String),
    entity_id     String,
    quantiles     AggregateFunction(quantiles(0.5, 0.95, 0.99), Float64)
)
ENGINE = AggregatingMergeTree
PARTITION BY toYYYYMM(minute)
ORDER BY (entity_type, metric_name, entity_id, minute);

Alternatively, use quantilesTDigest states for approximate quantiles on high-cardinality streams where exact order statistics are too expensive.

Gap filling for charts

ClickHouse does not auto-fill missing buckets. Generate a spine and left join:

WITH
    toStartOfMinute(now() - INTERVAL 1 HOUR) AS start,
    toStartOfMinute(now()) AS end
SELECT
    bucket,
    coalesce(m.avg_value, 0) AS avg_value
FROM (
    SELECT arrayJoin(
        arrayMap(x -> toDateTime(x),
            range(toUInt32(start), toUInt32(end), 60))
    ) AS bucket
) AS spine
LEFT JOIN (
    SELECT
        minute AS bucket,
        avgMerge(avg_value) AS avg_value
    FROM metrics_1m
    WHERE minute >= start
    GROUP BY minute
) AS m USING bucket
ORDER BY bucket;

Use gap filling in presentation layers sparingly. Zero-fill hides ingest outages if you do not also alert on last_seen lag. Grafana + ClickHouse dashboards should show nulls or gaps when ingest stops, not a flat zero line.

Out-of-order and late-arriving samples

Agents reboot, networks partition, and batches arrive late. ClickHouse accepts out-of-order inserts into MergeTree; rows land in the correct granule based on event_time, not arrival time.

Implications:

• Materialized views fire at insert time. Late data updates the rollup bucket when it arrives, which may shift historical dashboard values slightly.
• For strict "closed bucket" reporting, buffer inserts and reject samples older than N minutes, or run nightly reconciliation jobs comparing rollup totals to raw re-aggregation.
• ReplacingMergeTree with a version column handles duplicate samples from at-least-once Kafka delivery.

Retention with TTL

ALTER TABLE metrics MODIFY TTL event_time + INTERVAL 14 DAY;
ALTER TABLE metrics_1m MODIFY TTL minute + INTERVAL 180 DAY;
ALTER TABLE metrics_1h MODIFY TTL hour + INTERVAL 730 DAY;

Align TTL with compliance. Hot raw (7–14 days), warm minute (6 months), cold hour (2+ years) is a common three-tier pattern. Per ClickHouse TTL documentation, TTL moves or deletes data during merges, not synchronously on insert.

When you add a rollup tier after months of raw data exist, backfilling materialized views requires an explicit INSERT INTO metrics_1m SELECT ... FROM metrics job over historical partitions.

Chaining hourly and daily rollups

Build coarser tiers from finer rollups, not from raw samples:

CREATE TABLE metrics_1h
(
    hour          DateTime,
    entity_type   LowCardinality(String),
    metric_name   LowCardinality(String),
    entity_id     String,
    avg_value     AggregateFunction(avg, Float64),
    p95_value     AggregateFunction(quantile(0.95), Float64),
    sample_count  SimpleAggregateFunction(sum, UInt64)
)
ENGINE = AggregatingMergeTree
PARTITION BY toYYYYMM(hour)
ORDER BY (entity_type, metric_name, entity_id, hour);

CREATE MATERIALIZED VIEW metrics_1h_mv TO metrics_1h AS
SELECT
    toStartOfHour(minute) AS hour,
    entity_type,
    metric_name,
    entity_id,
    avgMergeState(avg_value) AS avg_value,
    quantileMergeState(0.95)(p95_value) AS p95_value,
    sum(sample_count) AS sample_count
FROM metrics_1m
GROUP BY hour, entity_type, metric_name, entity_id;

Daily tier for capacity planning:

CREATE TABLE metrics_1d
(
    day           Date,
    entity_type   LowCardinality(String),
    metric_name   LowCardinality(String),
    avg_value     AggregateFunction(avg, Float64),
    max_value     AggregateFunction(max, Float64),
    sample_count  SimpleAggregateFunction(sum, UInt64)
)
ENGINE = AggregatingMergeTree
PARTITION BY toYYYYMM(day)
ORDER BY (entity_type, metric_name, day);

CREATE MATERIALIZED VIEW metrics_1d_mv TO metrics_1d AS
SELECT
    toDate(hour) AS day,
    entity_type,
    metric_name,
    avgMergeState(avg_value) AS avg_value,
    maxMergeState(max_value) AS max_value,
    sum(sample_count) AS sample_count
FROM metrics_1h
GROUP BY day, entity_type, metric_name;

Dashboard routing: last 24h → raw or metrics_1m; last 30 days → metrics_1h; 1+ year → metrics_1d.

Multi-tenant and parameterized endpoints

SaaS metrics APIs scope by tenant without separate tables per customer:

SELECT
    toStartOfMinute(event_time) AS minute,
    entity_id,
    avg(value) AS avg_value
FROM metrics
WHERE entity_type = {{ String(entity_type, required=True) }}
  AND entity_id IN {{ Array(entity_ids, 'String') }}
  AND metric_name = {{ String(metric_name, required=True) }}
  AND event_time >= {{ DateTime(start_time, required=True) }}
  AND event_time < {{ DateTime(end_time, required=True) }}
GROUP BY minute, entity_id
ORDER BY minute;

Sort key (entity_type, metric_name, entity_id, event_time) keeps tenant-scoped queries fast when entity_id is in the filter list. For user-facing analytics, enforce tenant isolation in SQL parameters, not application-side row filtering after a wide SELECT.

Tuning with system.query_log

Measure bytes read per dashboard query before adding hardware:

SELECT
    query_duration_ms,
    read_rows,
    read_bytes,
    formatReadableSize(read_bytes) AS read_size,
    substring(query, 1, 120) AS query_preview
FROM system.query_log
WHERE type = 'QueryFinish'
  AND query NOT LIKE '%system.query_log%'
  AND event_time >= now() - INTERVAL 1 HOUR
ORDER BY read_bytes DESC
LIMIT 20;

If a 7-day chart reads gigabytes, the wrong table or missing rollup tier is the problem. Roll up data with materialized views before scaling cluster size.

ClickHouse vs dedicated TSDBs

Many teams collect with Prometheus or OpenTelemetry and analyze in ClickHouse for months of history, funnel joins, and customer-facing usage APIs. The ClickHouse vs TimescaleDB comparison covers when PostgreSQL-compatible time-series extensions lose to columnar OLAP at billion-row scale.

ClickHouse wins when queries span metrics, events, and dimensions in one SQL statement. Dedicated TSDBs win when PromQL, native service discovery, and scrape-based collection are the entire workload.

Tinybird for time-series workloads

Operating ClickHouse for time series means managing merges, rollup backfills, API auth, and ingest backpressure. Tinybird is a managed ClickHouse platform with streaming ingestion, SQL transformations, and sub-second SQL APIs, per Tinybird's product page.

Ingest without building a pipeline:

1. Events API — NDJSON microbatch over HTTP. Per Events API documentation, the TypeScript SDK provides ingestBatch and the Python SDK provides ingest_batch. Default limits: 100 requests per second per Data Source; 10 MB per request on Free plans, 100 MB on Developer, SaaS, and Enterprise plans. Use the wait=true parameter when you need write acknowledgement before responding to clients.
2. Kafka connector — Documented at Kafka connector documentation. Supported setups include Confluent Platform and Confluent Cloud, Redpanda, and AWS MSK.

Rollups and APIs on the same layer:

Model Data Sources with partition and sort keys matched to dashboard filters. Define materialized views for minute/hour rollups using the AggregatingMergeTree patterns above, then publish SQL Pipes as HTTP API endpoints.

Prometheus-compatible export:

Export SLO Pipes in Prometheus format by appending .prometheus to the Pipe endpoint URL.

Schema iteration and Branches:

Tinybird lists schema iteration (safe migrations with zero downtime) and Branches (zero-copy environments with production data) on its product page. Use Branches to test new rollup definitions before production deploy.

Customer references:

Canva reports 3.6 PB processed per month and 54 ms p99 query latency on Tinybird's product page. Resend reports 100 TB processed per month and 62 ms p90 query latency without relying on cache, per Tinybird's Resend customer story. Tinybird is SOC 2 Type II certified.

5 common mistakes on time-series ClickHouse

These schema and ingest patterns look fine in development and fail at production volume. Each one maps to a specific symptom: timeouts, flat charts, storage blowup, or wrong aggregates.

1. Query raw samples for 90-day charts

Dashboards time out or require aggressive caching because each refresh scans billions of raw samples. Users blame ClickHouse; the schema never had a rollup tier.

Fix: Route charts to metrics_1h or metrics_1d for ranges beyond 24 hours. Keep raw tables for incident windows only.

2. High-cardinality metric_name explosion

Dynamic metric names (/api/users/123/latency) create millions of distinct metric_name values. Storage and GROUP BY blow up; rollups cannot keep pace.

Fix: Bound metric_name to an enum. Put high-cardinality identifiers in entity_id or tags, not in the metric name column.

3. No lag monitoring on ingest

Silent agent failures produce flat charts. Gap filling with zeros masks outages until customers complain.

Fix: Alert on dateDiff('second', max(event_time), now()) per entity or fleet. Surface nulls in Grafana instead of zero-fill.

4. Single rollup grain for every query

One-minute rollups for 2-year retention consume more storage than raw data saved. One-hour-only rollups cannot support minute-level incident drill-down.

Fix: Three tiers: raw (days), 1m (months), 1h (years). Chain materialized views so each tier feeds the next.

5. Wrong partition or sort key for the dashboard

Monthly partitions on sub-hour data create too many parts. Sort key leading with entity_id when global metric dashboards filter by metric_name first forces full scans.

Fix: PARTITION BY toYYYYMM(event_time) for most workloads. Match sort key lead columns to the filter column in your most frequent query, verified with EXPLAIN.

Choosing rollup granularity

There is no universal grain. Pick tiers from query latency targets and retention requirements:

Measure bytes read with system.query_log, not row counts. If a 7-day chart reads more than 100 MB per refresh, add or coarsen a rollup tier.

Frequently Asked Questions (FAQs)

Is ClickHouse a replacement for Prometheus or InfluxDB?

No for collection. ClickHouse replaces long-retention storage and SQL analytics over metrics mixed with events. Keep Prometheus for scrape, service discovery, and infra alerting. Land samples or aggregates in ClickHouse when you need months of history, joins with product events, or customer-facing usage APIs.

How many rollup tiers do I actually need?

Most production systems use three: raw (days), 1-minute (weeks–months), 1-hour or 1-day (years). Each tier exists because a dashboard time range would otherwise scan too many rows. Add a tier when system.query_log shows a recurring chart reading hundreds of megabytes per refresh.

Can I store Prometheus remote write and app events in one cluster?

Yes, in separate tables with separate sort keys. Do not merge samples and business events into one wide table. Join at query time on service, time, and bounded dimensions.

What happens when agents send out-of-order timestamps?

MergeTree accepts out-of-order rows into the correct granule by event_time. Materialized views update rollup buckets when late data arrives. For financial or billing-grade metrics, reject samples older than N minutes at ingest or run nightly reconciliation against raw re-aggregation.

Should I use AggregatingMergeTree or SummingMergeTree for rollups?

Use AggregatingMergeTree when you need quantiles, averages, or min/max that cannot be recombined from sums alone. Use SummingMergeTree when additive metrics (counts, byte totals) are sufficient. Per AggregatingMergeTree docs, insert with -State functions and query with -Merge.

When does Tinybird make sense for time-series workloads?

When you want the ingestion, SQL API, and schema workflows described on tinybird.co instead of self-managing ClickHouse infrastructure. Self-hosted ClickHouse fits teams that need direct control over cluster topology and operational tooling.