Choosing a TSDB¶

Slack thread, Monday. "Should we standardize on InfluxDB, Timescale, or just remote_write everything into VictoriaMetrics?" Forty replies in, nobody has mentioned what any of the three teams actually write or query — one pages on service SLOs, one joins device readings to a customer table, one wants user_id-level latency charts.

Predict before you read on: is there one engine that serves all three well, or does the right answer depend entirely on the access pattern each team actually has?

There is no one right logo — the market is a pile of them — and the job is an access pattern: scrape-and-alert, IoT {timestamp, device_id, sensor, value}, or high-cardinality events that only look like metrics because they have a timestamp. Pick the engine from how you write and read, not from who sponsored the conference.

Start with the situation¶

Three questions this academy actually asks:

1. SLO / paging: rate(http_requests_total{service="checkout"}[5m]) at 15 s scrape, 200 services, bounded labels.
2. IoT: 10 M devices, 30 s temperature, latest-value + 24 h chart + 1 y trend, SQL joins to a customers table.
3. Product analytics / observability events: hundreds of millions of events/day, user_id on the row, dashboard GROUP BY endpoint for one tenant.

One system that does all three well is a slide, not a deployment.

Why the obvious approach breaks¶

TSDBs share append-heavy writes and time predicates. They disagree on:

• identity (labels vs columns vs measurements);
• query language (PromQL vs SQL vs Flux);
• pull vs push;
• how badly high cardinality hurts;
• retention / downsampling story;
• joins.

ClickHouse is not a TSDB. Teams still use it as one when the access pattern is OLAP-shaped. Pretending it is Prometheus is how you lose alerting. Pretending Prometheus is ClickHouse is how you lose user_id.

Build the mental picture¶

Need PromQL + Alertmanager + Grafana scrape?
    → Prometheus, scale-out with VictoriaMetrics / Mimir

Need SQL, devices, joins, moderate rate?
    → Timescale (Postgres you already run)

Need IoT push, flexible tags, you accept the license?
    → Influx — or skip to CH/Timescale

Need high-card columns, heavy GROUP BY, batched ingest?
    → ClickHouse (or Pinot if user-facing QPS)

If the sentence contains label and user_id, you already left the Prom family. If it contains scrape and page me, you already left ClickHouse as the primary.

Internals by system¶

Prometheus¶

Write path: pull. Prometheus scrapes /metrics. Short local retention (often 15 d). WAL + 2 h blocks + compaction.

Read path: PromQL range vectors, not SQL GROUP BY. Inverted index on labels. Memory ~ active series.

Strength: Kubernetes, exporters, Alertmanager, Grafana, a dialect SREs can write at 03:00.

Weakness: cardinality; long-term storage is someone else (remote_write); not a device join engine; not 300k IoT push/s without a gateway story.

rate(http_requests_total{service="checkout", status=~"5.."}[5m])
histogram_quantile(0.95, sum by (le) (rate(http_request_duration_seconds_bucket{service="checkout"}[5m])))

Do not: user_id labels; 10 M device_id series on one Prom; 13-month local disk as the plan.

VictoriaMetrics¶

Prometheus-compatible (PromQL / MetricsQL, remote_write, exporters). Better compression, cheaper high-churn, cluster mode.

Use: the same access pattern as Prometheus when Prom’s single-node series/disk ceiling is the problem.

Still: labels are identity. user_id remains wrong. MetricsQL is not SQL joins to billing.

TimescaleDB¶

PostgreSQL + hypertables (chunk by time, optionally space by device_id) + compression + continuous aggregates + retention jobs.

CREATE TABLE readings (
    ts timestamptz NOT NULL,
    device_id text NOT NULL,
    sensor text NOT NULL,
    value double precision NOT NULL
);
SELECT create_hypertable('readings', 'ts',
    chunk_time_interval => INTERVAL '1 day');
SELECT add_dimension('readings', 'device_id', number_partitions => 16);

ALTER TABLE readings SET (
    timescaledb.compress,
    timescaledb.compress_segmentby = 'device_id, sensor',
    timescaledb.compress_orderby = 'ts DESC'
);

Strength: SQL, JOINs to customers, BI tools, transactions for small metadata, time_bucket_gapfill.

Weakness: it is still Postgres. 3×10⁵ inserts/s needs batching, fewer indexes, compression, and honest hardware. Vacuum/chunk world. Not PromQL. Not 200 ms at 8k QPS user-facing.

Access pattern: IoT / app metrics where the team is SQL-native and rates are “serious but not ClickHouse-or-die.”

InfluxDB¶

Push, tag/field model, good at irregular IoT. InfluxQL / Flux. Cardinality on tags is the same class of footgun as Prom labels (user_id as tag).

License: v3 BSL / product split — treat as a vendor decision, not a default open-source TSDB. For new greenfield, many teams pick Timescale or ClickHouse instead of betting the license.

Access pattern: IoT if already in-stack and tags are bounded. Not the default in this academy.

ClickHouse as a TSDB¶

MergeTree, ORDER BY (device_id, sensor, ts), batched inserts, MVs for downsampling, TTL, SQL including quantile, argMax, JOINs to small dims.

CREATE TABLE readings
(
    ts        DateTime64(3),
    device_id String,
    sensor    LowCardinality(String),
    value     Float64
)
ENGINE = MergeTree
PARTITION BY toYYYYMMDD(ts)
ORDER BY (device_id, sensor, ts)
TTL ts + INTERVAL 7 DAY;

SELECT device_id, argMax(value, ts)
FROM readings
WHERE sensor = 'temperature'
GROUP BY device_id;

Strength: high-cardinality columns, scan speed, rollups, one engine for events and measurements if you are disciplined.

Weakness: no PromQL/Alertmanager native; small inserts die (too many parts); you build recording/alerting; not scrape; FINAL habits from ReplacingMergeTree.

Access pattern: IoT + observability events; product analytics; anything that would have been “Prom + user_id.”

Pinot if the same events are user-facing high QPS (Pinot).

Compare by access pattern¶

Deeper OLAP vs TS: TSDB vs OLAP.

Put it to work¶

Pattern 1 — services:

app /metrics → Prometheus (2–4 weeks)
            → remote_write VictoriaMetrics (1 year, downsampled)
Alertmanager → PagerDuty
Grafana → PromQL

Keep label set reviewed. Recording rules for expensive dashboards.

Pattern 2 — IoT SQL:

device → Kafka → batch 10k → Timescale hypertable
                           → or ClickHouse Kafka Engine / MV
cagg / MV 1m, 1h
TTL raw 7d
Grafana SQL or a thin API

Join:

SELECT c.name, avg(r.value)
FROM readings r
JOIN devices d ON d.id = r.device_id
JOIN customers c ON c.id = d.customer_id
WHERE r.ts > now() - interval '1 day'
  AND r.sensor = 'temperature'
GROUP BY c.name;

Timescale: natural. ClickHouse: JOIN the dim table (ReplacingMergeTree + argMax) or denormalize customer_id onto the reading at ingest (usually better).

Pattern 3 — events with user_id:

app → Kafka → ClickHouse ORDER BY (customer_id, ts)
           → optional Pinot for the in-app tiles
Prometheus stays on service SLOs only

Do not remote_write these events into Prom.

Where teams get caught¶

How it fails¶

Debugging pointers: Prom head_series; Timescale EXPLAIN chunk exclusion; CH system.parts + EXPLAIN indexes = 1 + query_log.read_bytes; VM vm_rows_inserted.

How to investigate¶

Ask four questions of any slow tile:

1. How many series / groups? (count of identities in the range.)
2. Which clock? event vs scrape (time semantics).
3. Which layer? raw vs 1m vs 1h (downsampling).
4. Which engine is actually serving? Grafana datasource mixups (Prom vs SQL) are frequent.

ClickHouse:

SELECT read_rows, formatReadableSize(read_bytes), query
FROM system.query_log
WHERE type = 'QueryFinish' AND query LIKE '%readings%'
ORDER BY event_time DESC LIMIT 10;

Prometheus: query log + count({__name__="http_requests_total"}).

Timescale: EXPLAIN (ANALYZE) should show chunks excluded. If not, the time predicate is wrapped in a function.

Scale 10× / 100× / 1000׶

At 1000×, Iceberg is the lake; TSDBs/CH are hot tiers. Trino for humans on cold data.

Trade-offs¶

Alternatives¶

Apply¶

Write the access pattern in one sentence. If it has scrape and page, start Prom/VM. If it has device_id and SQL, start Timescale or CH (CH if you already need it for events or the rate is ugly). If it has user_id and dashboards, CH/Pinot, not labels.

Then implement downsampling before the disk is full. Cardinality review is a merge blocker for Prom PRs.

Related: IoT, observability, ClickHouse.

Check your understanding¶

Company today: Prometheus (15 d) for k8s; Postgres 2 TB of IoT rows (readings with indexes on (device_id, ts)), inserts 8k/s and climbing to 80k/s; product wants per-user API latency in Grafana using the existing Prom.

Pick a 12-month architecture for (a) paging, (b) IoT, © per-user latency. Name what you stop doing.