---
title: "Time Series Database Example in SQL"
excerpt: "A per-tenant API latency workload in SQL: event-time rollups, late data, cardinality math, and where series-based engines break the same schema."
authors: "Tinybird"
categories: "AI Resources"
createdOn: "2026-09-23 00:00:00"
publishedOn: "2026-09-23 00:00:00"
updatedOn: "2026-09-23 00:00:00"
status: "published"
---

This example is one workload, fully specified: **per-tenant API latency and error rate, 1-minute grain, last hour on the product page, 90 days retained as rollups, raw kept 14 days.** The SQL is ClickHouse®, which is what Tinybird runs. The datafiles below are the Tinybird form of that SQL: a datasource, a materialized pipe for the 1-minute rollup, and an endpoint the product can call with a tenant token. The same constraints are then run through series-oriented engines. Failures are modeling issues, not benchmark issues.

Host CPU scrapes belong on Prometheus. This example is **wide events**, not a fixed series catalog. The engine-level companion is [ClickHouse for time series](https://www.tinybird.co/blog/clickhouse-time-series-data).

## The product question, written as an SLO

- **Freshness:** a request that finished is visible in the chart within 5 seconds.
- **Latency:** the tile query p95 < 100 ms at 50 concurrent tenants.
- **Correctness:** a request that arrives 8 minutes late still belongs to the minute it happened, not the minute it landed.
- **Cardinality:** `path` is bounded (~400 routes). `tenant_id` is 50,000. `user_id` is millions and **must not** be a series key.

That last line is the whole post. Time series engines that allocate an in-memory series per label set cannot take `user_id` as a tag. Columnar event stores can store it as a column and refuse to group by it on the hot path.

## Raw table: store events, not series

```sql
CREATE TABLE api_requests
(
    ts DateTime64(3),
    tenant_id String,
    path LowCardinality(String),
    status UInt16,
    duration_ms UInt32,
    user_id String,
    release LowCardinality(String)
)
ENGINE = MergeTree
PARTITION BY toDate(ts)
ORDER BY (tenant_id, path, ts)
TTL toDate(ts) + INTERVAL 14 DAY
SETTINGS index_granularity = 8192;
```

**Why this `ORDER BY`.** The tile always filters `tenant_id` and usually `path`, then a time range. Putting `ts` first would force a scan of every tenant in the partition. Leading columns must match equality filters.

**Why `LowCardinality(path)`.** Routes repeat. Dictionary encoding cuts I/O. Do not mark `user_id` LowCardinality. It will not stay low.

**Why TTL 14 days on raw.** Debug drill-down needs individual rows for 2 weeks. Longer raw is a storage habit, not a product requirement. Rollups keep the 90 days.

**What we are not doing.** We are not creating 50,000 × 400 Prometheus series at write time. We are appending rows. [ClickHouse vs Prometheus on cardinality](https://clickhouse.com/blog/clickhouse-vs-promethous-high-cardinality-part-2-cardinality-in-clickhouse) is the reference: a new label combination is another row, not another series object.

On Tinybird the same table is a datasource. JSON paths are how Events API ingest maps fields:

```tinybird
DESCRIPTION >
    Raw API request events. 14-day TTL. Drill-down only.

SCHEMA >
    `ts` DateTime64(3) `json:$.ts`,
    `tenant_id` String `json:$.tenant_id`,
    `path` LowCardinality(String) `json:$.path`,
    `status` UInt16 `json:$.status`,
    `duration_ms` UInt32 `json:$.duration_ms`,
    `user_id` String `json:$.user_id`,
    `release` LowCardinality(String) `json:$.release`

ENGINE "MergeTree"
ENGINE_PARTITION_KEY "toDate(ts)"
ENGINE_SORTING_KEY "tenant_id, path, ts"
ENGINE_TTL "toDate(ts) + toIntervalDay(14)"
```

## Ingest: append-only, idempotent enough

HTTP path (Tinybird Events API or ClickHouse HTTP):

```json
{
  "ts": "2026-09-23 08:01:04.221",
  "tenant_id": "t_9f3a",
  "path": "/v1/invoices",
  "status": 200,
  "duration_ms": 47,
  "user_id": "u_441",
  "release": "2026.09.22"
}
```

Batch. Single-row inserts at 5,000 events/sec create merge backlog. 10k-row batches are the default starting point. [ClickHouse load guidance](https://clickhouse.com/blog/supercharge-your-clickhouse-data-loads-part2) is blunt about this.

Kafka path: same JSON, one topic, `tenant_id` as the partition key so a single tenant's events stay ordered enough for debugging. You do not need exactly-once to draw a p95. You need **at-least-once plus a way to ignore dupes** if the producer retries. The [stream Kafka to ClickHouse](https://www.tinybird.co/blog/stream-kafka-to-clickhouse) pattern (connector or Kafka engine into MergeTree) is the production ingest. Exactly-once is a different post: [Kafka to ClickHouse exactly-once](https://www.tinybird.co/blog/kafka-to-clickhouse-exactly-once).

If duplicates matter (billing, not charts), add `request_id UUID` and use `ReplacingMergeTree(ts)` with `ORDER BY (tenant_id, request_id)` on a **raw-unique** table. Do not put that merge key on the rollup. Charts want `countState()`, not last-write-wins on the minute.

Store UTC. Convert in the UI. Reject samples more than 15 minutes in the future. Clock skew from a mobile client will otherwise create partitions that TTL cannot drop cleanly.

Target ingest for this example: **5,000 events/sec** peak, ~400 million raw rows in the 14-day window. That is a laptop-sized ClickHouse if the sort key is right, and a pager if it is wrong.

## Late events: the minute is the event time

A worker retries at 08:09. The request happened at 08:01. The chart for 08:01 must move.

```sql
SELECT
    toStartOfMinute(ts) AS minute,
    path,
    count() AS requests
FROM api_requests
WHERE tenant_id = 't_9f3a'
  AND ts >= now() - INTERVAL 1 HOUR
GROUP BY minute, path;
```

`toStartOfMinute(ts)` uses **event time**. Ingest time never appears. If you bucket on `now()` at insert, late data lands in the wrong bar and you will not notice until a customer argues.

Watermarks (Flink-style) are optional here. For a 1-minute product tile, allowing the bar to **update for 15 minutes** after the minute closes is enough. Close the bar in the UI after that if you need a frozen export. Do not freeze the table.

Out-of-order inside the same minute is free. MergeTree does not require inserts to arrive sorted. Parts merge in the background. A sort key of `(tenant_id, path, ts)` keeps the sparse index useful even when a retry lands late.

## Rollup: move work to insert time

The hot query should not read 400 million raw rows. It should read **aggregate states**.

```sql
CREATE TABLE api_requests_1m
(
    minute DateTime,
    tenant_id String,
    path LowCardinality(String),
    requests AggregateFunction(count),
    errors AggregateFunction(sum, UInt64),
    dur_q AggregateFunction(quantilesTDigest(0.5, 0.95), UInt32)
)
ENGINE = AggregatingMergeTree
PARTITION BY toYYYYMM(minute)
ORDER BY (tenant_id, path, minute)
TTL minute + INTERVAL 90 DAY;
```

```sql
CREATE MATERIALIZED VIEW api_requests_1m_mv
TO api_requests_1m
AS
SELECT
    toStartOfMinute(ts) AS minute,
    tenant_id,
    path,
    countState() AS requests,
    sumState(if(status >= 500, 1, 0)) AS errors,
    quantilesTDigestState(0.5, 0.95)(duration_ms) AS dur_q
FROM api_requests
GROUP BY minute, tenant_id, path;
```

A ClickHouse materialized view is a **trigger on insert blocks**, not a Postgres `REFRESH MATERIALIZED VIEW`. New parts write new states. Background merges combine states for the same `(tenant_id, path, minute)`. Late events just insert another state for that key. [Official rollup tutorial](https://clickhouse.com/docs/use-cases/time-series) and [incremental MV docs](https://clickhouse.com/docs/concepts/features/materialized-views/incremental-materialized-view) are the mechanics.

Tinybird equivalent: a target datasource plus a materialized pipe.

```tinybird
DESCRIPTION >
    1-minute aggregate states per tenant and path. 90-day TTL.

SCHEMA >
    `minute` DateTime,
    `tenant_id` String,
    `path` LowCardinality(String),
    `requests` AggregateFunction(count),
    `errors` AggregateFunction(sum, UInt64),
    `dur_q` AggregateFunction(quantilesTDigest(0.5, 0.95), UInt32)

ENGINE "AggregatingMergeTree"
ENGINE_PARTITION_KEY "toYYYYMM(minute)"
ENGINE_SORTING_KEY "tenant_id, path, minute"
ENGINE_TTL "minute + toIntervalDay(90)"
```

```tinybird
NODE minute_states
SQL >
    SELECT
        toStartOfMinute(ts) AS minute,
        tenant_id,
        path,
        countState() AS requests,
        sumState(if(status >= 500, 1, 0)) AS errors,
        quantilesTDigestState(0.5, 0.95)(duration_ms) AS dur_q
    FROM api_requests
    GROUP BY minute, tenant_id, path

TYPE MATERIALIZED
DATASOURCE api_requests_1m
```

**Cardinality of the rollup.** 50,000 tenants × 400 paths × 1,440 minutes/day × 90 days is an upper bound of ~2.6e12 if every tenant hits every path every minute. They do not. Realistic: 5% of pairs are hot. Still: **do not add `user_id` to this `ORDER BY`.** That would recreate Prometheus in a MergeTree.

## Hourly cascade: keep 90 days cheap

The 1-minute table is the product tile. The weekly exec view should not read 90 days of minutes. Cascade:

```sql
CREATE TABLE api_requests_1h
(
    hour DateTime,
    tenant_id String,
    path LowCardinality(String),
    requests AggregateFunction(count),
    errors AggregateFunction(sum, UInt64),
    dur_q AggregateFunction(quantilesTDigest(0.5, 0.95), UInt32)
)
ENGINE = AggregatingMergeTree
PARTITION BY toYYYYMM(hour)
ORDER BY (tenant_id, path, hour)
TTL hour + INTERVAL 90 DAY;
```

Populate from the 1-minute table with `countMergeState`, `sumMergeState`, and `quantilesTDigestMergeState`. Merging states is lossless for these functions. Do not re-read raw for the hour table. A daily table is optional. For this product, hour is enough. [Tiered rollups](https://www.tinybird.co/blog/clickhouse-time-series-data) in the companion post cover 1m to 1h to 1d if a year of history is added later.

## Serve: merge states, not raw

```sql
SELECT
    minute,
    path,
    countMerge(requests) AS requests,
    sumMerge(errors) / countMerge(requests) AS error_rate,
    quantilesTDigestMerge(0.5, 0.95)(dur_q).2 AS p95_ms
FROM api_requests_1m
WHERE tenant_id = {tenant:String}
  AND minute >= now() - INTERVAL 1 HOUR
GROUP BY minute, path
ORDER BY minute, path;
```

On Tinybird this is a pipe with a typed parameter, published as an endpoint. Auth is a resource token scoped to that pipe. The app never opens a SQL port.

```tinybird
NODE tile
SQL >
    SELECT
        minute,
        path,
        countMerge(requests) AS requests,
        sumMerge(errors) / countMerge(requests) AS error_rate,
        quantilesTDigestMerge(0.5, 0.95)(dur_q).2 AS p95_ms
    FROM api_requests_1m
    WHERE tenant_id = {{ String(tenant, required=True) }}
      AND minute >= now() - INTERVAL 1 HOUR
    GROUP BY minute, path
    ORDER BY minute, path

TYPE ENDPOINT
```

If p95 of this query is over 100 ms, you usually have the wrong `ORDER BY`, not a "need a bigger TSDB" problem. Check `system.query_log` for rows read vs rows in the hour.

A second `ORDER BY` on raw without a second table is a [projection](https://www.tinybird.co/blog/projections). Use that when a small set of queries filter by `release` instead of `path`. Do not project `user_id`.

## Ship the example on Tinybird

The SQL above is the engine work. Tinybird is how that SQL becomes a product endpoint without a SQL port, a connection pooler, or a Prometheus remote-write sidecar.

Create the raw datasource (or let the Events API infer types, then lock them). Add the `AggregatingMergeTree` target and the materialized pipe. Publish the `countMerge` pipe as an endpoint. Scope a resource token to that pipe so `?tenant=t_9f3a` cannot read another tenant. Backfill the 14 days already on disk with an `INSERT SELECT` or a copy pipe, 1 day at a time.

Ingest from the app is an HTTP POST, not a scrape:

```bash
curl \
  -H "Authorization: Bearer $TINYBIRD_TOKEN" \
  -d '{"ts":"2026-09-23 08:01:04.221","tenant_id":"t_9f3a","path":"/v1/invoices","status":200,"duration_ms":47,"user_id":"u_441","release":"2026.09.22"}' \
  https://api.tinybird.co/v0/events?name=api_requests
```

At 5,000 events/sec, batch on the client or use the [Kafka connector](https://www.tinybird.co/docs/ingest/kafka) with `tenant_id` as the partition key. Local `npx tinybird dev` runs the same pipes. `npx tinybird preview` gives the PR its own data. Production is `npx tinybird deploy`. Schema changes (add `release`, widen `status`) go through a `FORWARD_QUERY` on the datasource instead of a `DELETE` plus reload.

The tile the product calls is the endpoint, not `clickhouse-client`. Resend-style product analytics (p90 in the 60 ms range on similar rollups, 99.9% SLA, SOC 2 / HIPAA / GDPR on paid plans) is this pattern. A warehouse JDBC loop that resumes a Small warehouse every 30 seconds is the other pattern, and it fails the 100 ms SLO in the header.

Pricing is the monthly plan plus $0.0002 per vCPU-second over the baseline. The 1-minute rollup is what keeps that overage small. Querying 400 million raw rows from the endpoint is how the bill and the p95 both blow up.

## Drill-down: raw is allowed, once

When someone clicks a spike, query raw for **that tenant, that path, that minute**:

```sql
SELECT ts, status, duration_ms, user_id, release
FROM api_requests
WHERE tenant_id = {tenant:String}
  AND path = {path:String}
  AND ts >= {minute:DateTime}
  AND ts < {minute:DateTime} + INTERVAL 1 MINUTE
ORDER BY duration_ms DESC
LIMIT 50;
```

The sort key makes this a point lookup. A "top slow users last 90 days" query without `tenant_id` is a different product. Put it on a batch job, not the tile.

## Backfill when the MV did not exist yet

Materialized views only fire on **new** inserts. Historical raw needs a one-shot `INSERT SELECT` into `api_requests_1m` with the same `countState` / `sumState` / `quantilesTDigestState` expressions. Run it per day partition so a failure is retryable. After backfill, `OPTIMIZE TABLE api_requests_1m FINAL` is optional and expensive. Prefer letting merges happen. If a bad hour must be corrected, delete the partition and re-insert. Mutations on `AggregatingMergeTree` are how people create 2 competing states for 1 key.

## Cardinality math you should write down

Before picking an engine, compute:

```text
hot_series ≈ active_tenants × active_paths_per_tenant × extra_tags
```

For this example: `12,000 active/hour × 30 paths × 1 = 360,000` rollup keys touching the last hour. ClickHouse treats that as 360k group keys in a small table. Prometheus treats a naive export as 360k **active series**, plus history.

Add `user_id` (~80 per tenant per hour): 360k × 80 = 28.8M. That is a Prometheus incident. It is still just rows in `api_requests`.

Write the number in the design doc. If the engine's marketing site does not talk about that number, do not use the engine for this example.

## What 7 other engines do with this exact schema

This is not a ranked list of TSDBs. It is a list of failure modes for these constraints. 7 engines, because those are the ones that show up when this schema is pasted into a buying thread.

**Prometheus.** If you export `duration_ms` with labels `{tenant, path, user}`, you create tens of millions of series. Memory and compaction fall over. Prometheus is correct for `http_server_ok{route,code}` on **your** service with a few hundred label sets. It is incorrect as the store for customer-facing per-tenant charts. PromQL `histogram_quantile` is excellent for *your* SLO burn. It is not a tenant-filtered product API. [ClickHouse integration with Prometheus](https://www.tinybird.co/blog/clickhouse-integration-prometheus) is the remote-write pattern if you want both: Prom for scrape, ClickHouse for this example.

**InfluxDB (classic TSM).** `tenant_id` and `path` as tags is fine until someone adds `user_id` or `request_id` as a tag "for debugging." Series explosion is the same physics. Fields are cheap. Tags are not. The example only works if tag policy is enforced in the writer, which application teams routinely skip.

**TimescaleDB.** A hypertable on `ts` with a btree on `(tenant_id, path, ts)` can serve this at moderate ingest. Continuous aggregates map to the 1-minute rollup. You still run Postgres: autovacuum, WAL volume at 5k inserts/sec, connection pooling for 50 concurrent tiles. [ClickHouse vs TimescaleDB](https://www.tinybird.co/blog/clickhouse-vs-timescaledb) is the comparison when the tile count and ingest climb together. Timescale is the right call when the rest of the app already lives in Postgres and this chart is internal. [TimescaleDB alternatives](https://www.tinybird.co/blog/TimescaleDB-TigerData-Alternatives) if you already know you are leaving.

**QuestDB.** SQL is close. Influx line protocol ingest is fast. You will still invent the API, TTL policy, and multi-tenant tokens. Fine as an engine in a lab. Incomplete as this example's production path.

**Amazon Timestream / CloudWatch Metrics.** Useful if the only tenant is you and the cardinality limits are documented. Magnetic store vs memory store is a retention SKU, not a rollup design. Not a 50k-tenant product database.

**VictoriaMetrics.** Handles higher cardinality than Prometheus. It still thinks in series. Wide events with unbounded `user_id` do not belong there. Remote-write from Prom is a good **metrics** path. It is a bad **request-log** path.

**OpenTSDB.** HBase-backed, tag-oriented, operationally heavier than the problem. Same series physics, older ops story. Nobody greenfields this example on OpenTSDB in 2026. It still appears in RFPs written in 2018.

Postgres will also be suggested. [OLTP vs OLAP](https://www.tinybird.co/blog/oltp-vs-olap) is that argument. At 5k inserts/sec plus 50 concurrent tiles, the OLTP engine is being asked to be the OLAP engine.

## What this example must satisfy in production

The example is finished when a tenant token can only read that tenant's rollup, late events correct the bar, and `user_id` never appears in a group-by on the hot path. Tinybird is the path that publishes that rollup as an HTTP endpoint with the same ClickHouse SQL. Everything else is vendor preference.

## Frequently Asked Questions (FAQs)

### Why not store `user_id` as a Prometheus label?

`user_id` is millions of values. A label combination is an in-memory series. 12,000 active tenants × 30 paths × 80 users/hour is 28.8M series. Prometheus and classic Influx tags fail that math. ClickHouse stores `user_id` as a column and omits it from the hot `GROUP BY`.

### Event time or ingest time?

Event time. `toStartOfMinute(ts)` must use the timestamp on the request, not `now()` at insert. An 8-minute-late retry still belongs to the minute it happened.

### Do I need a dedicated time series database?

Not for this schema. A dedicated TSDB is correct for a closed series catalog (host CPU scrapes). Wide events with unbounded `user_id` and a tenant-filtered product API are a columnar OLAP workload. Tinybird is managed ClickHouse for that job.