---
title: "ClickHouse for time series metrics, rollups, and SLOs"
excerpt: "Time-series data in ClickHouse® uses MergeTree, rollups, and TTL. Schema and query patterns for metrics, IoT, and event streams at scale."
authors: "Tinybird"
categories: "AI Resources"
createdOn: "2026-08-11 00:00:00"
publishedOn: "2026-08-11 00:00:00"
updatedOn: "2026-08-11 00:00:00"
status: "published"
---

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{% sup %}®{% /sup %} is not a dedicated TSDB, but MergeTree engines, [columnar compression](https://www.tinybird.co/blog/what-is-a-columnar-database), and materialized views handle time-series patterns when you design sort keys, rollup tiers, and TTL deliberately. Per [ClickHouse MergeTree documentation](https://clickhouse.com/docs/en/engines/table-engines/mergetree-family/mergetree), 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](https://www.tinybird.co/blog/when-to-use-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:

```sql
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:

| Dashboard opens with | Sort key lead columns |
| --- | --- |
| Metric across fleet | `entity_type, metric_name, entity_id, event_time` |
| Single host/device | `entity_id, metric_name, event_time` |
| Global service SLO | `metric_name, entity_type, event_time` |

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:

```sql
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](https://clickhouse.com/blog/supercharge-your-clickhouse-data-loads-part2), 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](https://www.tinybird.co/blog/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](https://clickhouse.com/docs/en/engines/table-engines/mergetree-family/aggregatingmergetree), the engine merges aggregate **states** during background merges, reducing row count by orders of magnitude.

**One-minute AggregatingMergeTree:**

```sql
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:

```sql
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](https://www.tinybird.co/blog/clickhouse-create-materialized-view-example) pattern chains rollups the same way in self-hosted ClickHouse and managed platforms.

For [high-volume time-series dashboards](https://www.tinybird.co/blog/real-time-dashboards-high-volume-time-series-data), 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:

```sql
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:**

```sql
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:**

```sql
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):**

```sql
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:

```sql
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:

```sql
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](https://www.tinybird.co/blog/clickhouse-grafana-example) 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

```sql
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](https://clickhouse.com/docs/en/engines/table-engines/mergetree-family/mergetree#table_engine-mergetree-ttl), 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](https://www.tinybird.co/blog/backfilling-materialized-views-diy-vs-tinybird-and-best-practices) 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:

```sql
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:

```sql
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:

```sql
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](https://www.tinybird.co/blog/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:

```sql
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](https://www.tinybird.co/blog/roll-up-data-with-materialized-views) before scaling cluster size.

## ClickHouse vs dedicated TSDBs

| Concern | Purpose-built TSDB (Prometheus, Influx, etc.) | ClickHouse |
| --- | --- | --- |
| PromQL / native TS query language | Strong | SQL only |
| Mixed workloads (logs + metrics + events) | Weak | Strong |
| Arbitrary joins and dimensions | Limited | Strong |
| Cardinality control | Varies | Your schema discipline |
| Long-retention cost | Often expensive at high card | Columnar compression + rollups |
| Downsampling | Built-in retention policies | Materialized views you design |

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](https://www.tinybird.co/blog/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](https://www.tinybird.co/) is a [managed ClickHouse platform](https://www.tinybird.co/product/managed-clickhouse) 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](https://www.tinybird.co/docs/ingest/events-api), 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](https://www.tinybird.co/docs/forward/ingest-data/connectors/kafka). 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](https://www.tinybird.co/docs/publish/api-endpoints/guides/consume-api-endpoints-in-prometheus-format) by appending `.prometheus` to the Pipe endpoint URL.

**Schema iteration and Branches:**

Tinybird lists [schema iteration](https://www.tinybird.co/product/managed-clickhouse) (safe migrations with zero downtime) and [Branches](https://www.tinybird.co/product/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](https://www.tinybird.co/product/managed-clickhouse). Resend reports 100 TB processed per month and 62 ms p90 query latency without relying on cache, per [Tinybird's Resend customer story](https://www.tinybird.co/customer-stories/resend). 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:

| Range | Recommended table | Target query time |
| --- | --- | --- |
| Last 1 hour | Raw `metrics` | &lt; 500 ms |
| Last 7 days | `metrics_1m` | &lt; 1 s |
| Last 90 days | `metrics_1h` | &lt; 2 s |
| 1+ years | `metrics_1d` | &lt; 3 s |

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](https://clickhouse.com/docs/en/engines/table-engines/mergetree-family/aggregatingmergetree), 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](https://www.tinybird.co/product/managed-clickhouse) instead of self-managing ClickHouse infrastructure. Self-hosted ClickHouse fits teams that need direct control over cluster topology and operational tooling.

{% cta
  title="Time-series analytics without the ops tax"
  text="Tinybird is managed ClickHouse with streaming ingestion and sub-second SQL APIs. Query metrics and events from one platform."
  button={href: "https://cloud.tinybird.co/signup", target: "_blank", text: "Try Tinybird free"}
/%}