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.
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:
pathis bounded (~400 routes).tenant_idis 50,000.user_idis 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
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 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:
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):
{
"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 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 pattern (connector or Kafka engine into MergeTree) is the production ingest. Exactly-once is a different post: 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.
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.
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;
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 and incremental MV docs are the mechanics.
Tinybird equivalent: a target datasource plus a materialized pipe.
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)"
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:
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 in the companion post cover 1m to 1h to 1d if a year of history is added later.
Serve: merge states, not raw
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.
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. 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:
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 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:
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:
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 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 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 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 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.