Time-Series vs OLAP: When to Pick a TSDB Over ClickHouse / Druid / Pinot

time series database is the pick-one architectural decision that decides whether your metrics, telemetry, and event streams live in a purpose-built engine that treats time as a first-class citizen — or in a general-purpose OLAP engine that treats time as just another column — and it is the single storage-layer call senior data engineers get wrong most often because "ClickHouse can do everything" is not always the same as "ClickHouse is the right answer here." Every operational metric your infrastructure emits — a per-container CPU sample, a per-user click event, a per-vehicle GPS ping, a per-sensor temperature reading — arrives as an append-only ordered stream with high cardinality on the identity axis and low cardinality on the metric-name axis, and the engineering trade-off does not live in "should we store this in a database" — everything is stored somewhere — but in which category of database you pick and what it costs when the workload shape shifts underneath you.

This guide is the senior-DE walkthrough you wished existed the first time an interviewer asked "walk me through when you'd pick TimescaleDB over ClickHouse for a metrics workload," or "your dashboard needs sub-second queries at 1000 concurrent users — is that a TSDB or an OLAP job?", or "what does a time-series database actually give you that a columnar OLAP engine doesn't?" It walks through the four axes interviewers actually probe (workload shape, cardinality, latency, retention), the TSDB primitives that make time queries idiomatic (time_bucket, SAMPLE BY, GROUP BY time(), LAST/FIRST/DELTA/RATE, gap-fill, downsampling), the OLAP superpowers TSDBs cannot match (arbitrary dimensional slice-and-dice, big joins, dashboard concurrency, approximate functions), and the hybrid patterns (Prometheus + Druid, TimescaleDB ingest + ClickHouse analytics) that let one architecture serve both metrics and multi-dimensional analytics. Each section pairs a teaching block with a Solution-Tail interview answer — code, a step-by-step trace, an output table, then a concept-by-concept breakdown of why it works.

When you want hands-on reps immediately after reading, drill the SQL practice library →, rehearse aggregation muscle memory on the aggregation practice library →, and sharpen your system-design instinct with the design practice library →.

---

On this page

• Why the TSDB vs OLAP category matters
• Workload characteristics driving the choice
• TSDB strengths — time_bucket, hypertables, downsampling
• OLAP engine strengths — dimensional slice-and-dice + concurrency
• Decision matrix, hybrid patterns, interview signals
• Cheat sheet — TSDB vs OLAP recipes
• Frequently asked questions
• Practice on PipeCode

---

1. Why the TSDB vs OLAP category matters

Two categories, two wildly different cost curves — the choice binds you for years

The one-sentence invariant: choosing between a purpose-built time series database (TimescaleDB, InfluxDB, QuestDB, Prometheus) and a general-purpose OLAP engine (ClickHouse, Druid, Pinot, StarRocks) is a category decision that trades native time-window primitives, high-cardinality write throughput, automatic downsampling, and cheap retention against arbitrary dimensional slice-and-dice, big-table joins, hundreds-of-concurrent-users dashboard latency, and BI-tool integration — and each side of the trade-off is invisible until the workload shape shifts underneath you three years in. The category you pick in month one becomes the category you fight to migrate away from in year three because every downstream dashboard, alerting rule, retention policy, and application query hard-codes assumptions about the storage layer's cost curves.

The four axes interviewers actually probe.

• Workload shape. Time-series workloads are append-only, high-cardinality-on-identity (per-device, per-container, per-user), and read as time-window aggregations (last 24h avg CPU per host). Analytical workloads are mixed-write, arbitrary-cardinality-on-dimensions (product × region × channel), and read as multi-dim GROUP BYs (revenue by product by region by day). Interviewers open here because it separates people who understand the shape from people who've only seen one workload.
• Cardinality. TSDBs are built for unbounded identity cardinality — a Kubernetes cluster with 10K pods creates 10K distinct time-series streams per metric, and this is normal. OLAP engines choke at that cardinality unless the identity is materialized as a dimension. Getting cardinality wrong on either side kills query performance in different ways.
• Latency. TSDB queries on time-windowed data are sub-second by design (time is the partition key). OLAP queries on arbitrary GROUP BY combinations are seconds unless you pre-aggregate. Dashboard-serving latency at 100+ concurrent users is where OLAP engines with materialized views (Druid, Pinot) beat everything else.
• Retention. TSDBs ship first-class retention policies (drop chunks older than 30 days) and automatic downsampling (1-second raw → 1-minute rolled → 1-hour archived). OLAP engines require you to build these pipelines yourself — TTL clauses on ClickHouse MergeTree get you halfway; the downsampling job is manual. Retention economics dominate storage cost at multi-TB scale.

The 2026 reality — the boundary blurs but the category still matters.

• TSDBs are moving toward SQL and OLAP-like queries. TimescaleDB is Postgres, so window functions and joins work. QuestDB has native SQL with SAMPLE BY. InfluxDB 3.0 uses Apache DataFusion (Arrow-native columnar). These changes narrow the "TSDBs can't do analytics" gap, but the time-partition and downsampling story remains the differentiator.
• OLAP engines are adding time-series primitives. ClickHouse ships toStartOfMinute, runningDifference, groupArrayInsertAt for time gap-fill. Druid has native TIME_FLOOR and rollup at ingest. Pinot has explicit time-column support. These narrow the "OLAP engines don't understand time" gap.
• The category still binds you. Even with boundary blur, TSDBs remain 10-100x cheaper for pure metrics workloads (compression + retention automation) and OLAP engines remain 10-100x faster for dashboard-scale multi-dim aggregations (pre-aggregated indexes + MPP). Picking the wrong category costs a rewrite, not a query tune.
• The hybrid architecture is common. Prometheus for hot metrics + Druid for analytical dashboards; TimescaleDB for high-throughput ingest + ClickHouse for interactive analytics. Two engines, one pipeline. The senior architect knows both categories because the answer at scale is usually "both."

What interviewers listen for.

• Do you name both categories (TSDB and OLAP) as separate answer buckets? — senior signal.
• Do you name cardinality as the first axis, not latency? — senior signal (weaker candidates go straight to speed).
• Do you push back on "just use ClickHouse" with the cardinality-vs-slice trade-off? — senior signal.
• Do you name downsampling and retention automation as TSDB superpowers, not just "time-based partitioning"? — required answer.
• Do you describe OLAP concurrency (100–1000 users) as the workload no TSDB serves well? — required answer.

Worked example — the two-category workload comparison table

Detailed explanation. The single most useful artifact for a TSDB-vs-OLAP interview is a memorised 4×4 comparison table. Every senior time-vs-analytical discussion converges on this table within the first ten minutes; having it in your head is what separates a fluent answer from a stumbling one. Walk through building the table for two hypothetical workloads: a fleet-telemetry pipeline and a product-analytics dashboard suite.

• Workload A — fleet telemetry. 50K vehicles emitting GPS + engine metrics every 5 seconds. Reads: "last 24h vehicle path", "fleet-wide fuel efficiency by hour". Retention: 30 days raw + 2 years downsampled to 5-minute buckets.
• Workload B — product-analytics dashboard. 5M daily events with 30 dimensions (product, plan, region, channel, device, campaign, ...). Reads: "revenue by plan × region × channel last 90 days", 500 concurrent BI users.
• Target latency. Workload A: seconds tolerated. Workload B: sub-second at p95.
• Cardinality. Workload A: 50K vehicle_ids × 8 metrics = 400K series. Workload B: 30 dimensions with 10-1000 values each.

Question. Build the two-workload comparison and pick the category (TSDB or OLAP) each workload should live in.

Input.

Axis	Fleet telemetry (A)	Product analytics (B)
Write shape	append-only 10K rows/sec	mixed, ~60 events/sec
Read shape	time-range per vehicle + fleet rollups	multi-dim GROUP BY across 30 dims
Cardinality	50K vehicles × 8 metrics	30 dims × 10–1000 values
Latency SLA	seconds OK	sub-second p95
Retention	30d raw + 2y downsampled	90d full fidelity
Concurrent users	~5 ops engineers	500 BI users
Category	TSDB	OLAP

Code.

-- Workload A — TimescaleDB hypertable for fleet telemetry
CREATE TABLE fleet_telemetry (
    ts          TIMESTAMPTZ NOT NULL,
    vehicle_id  BIGINT      NOT NULL,
    metric      TEXT        NOT NULL,   -- 'gps_lat', 'gps_lon', 'fuel', 'rpm', ...
    value       DOUBLE PRECISION NOT NULL
);
SELECT create_hypertable('fleet_telemetry', 'ts', chunk_time_interval => INTERVAL '1 day');

-- Time-partition + compression + retention — one config each
SELECT add_compression_policy('fleet_telemetry', INTERVAL '7 days');
SELECT add_retention_policy('fleet_telemetry', INTERVAL '30 days');

-- Continuous aggregate for 5-minute rollups (auto-refreshed)
CREATE MATERIALIZED VIEW fleet_5m
WITH (timescaledb.continuous) AS
SELECT time_bucket('5 minutes', ts) AS bucket,
       vehicle_id,
       metric,
       avg(value)  AS avg_v,
       max(value)  AS max_v,
       min(value)  AS min_v
FROM   fleet_telemetry
GROUP  BY bucket, vehicle_id, metric;

SELECT add_continuous_aggregate_policy('fleet_5m',
    start_offset => INTERVAL '2 days',
    end_offset   => INTERVAL '5 minutes',
    schedule_interval => INTERVAL '5 minutes');

-- Workload B — ClickHouse MergeTree for product analytics
CREATE TABLE product_events (
    ts         DateTime,
    event_id   UInt64,
    user_id    UInt64,
    product_id LowCardinality(String),
    plan       LowCardinality(String),
    region     LowCardinality(String),
    channel    LowCardinality(String),
    device     LowCardinality(String),
    campaign   LowCardinality(String),
    revenue    Decimal(18, 4)
    -- + 22 more dimension columns
) ENGINE = MergeTree
ORDER BY (toDate(ts), product_id, plan, region)
TTL ts + INTERVAL 90 DAY;

-- Materialized view for the hot dashboard slice
CREATE MATERIALIZED VIEW product_daily
ENGINE = SummingMergeTree
ORDER BY (day, product_id, plan, region, channel)
AS SELECT
    toDate(ts)     AS day,
    product_id, plan, region, channel,
    sum(revenue)   AS revenue,
    count()        AS events
FROM   product_events
GROUP  BY day, product_id, plan, region, channel;

Step-by-step explanation.

1. Workload A is a classic TSDB workload — append-only writes, time-window reads, per-entity (vehicle) high cardinality on the identity axis, and a compression + retention story that a TSDB automates in one line each. TimescaleDB's create_hypertable + add_compression_policy + add_retention_policy + continuous aggregate are the four one-liners that would take 200 lines of custom ETL on a general-purpose OLAP engine.
2. Workload B is a classic OLAP workload — multi-dimensional GROUP BYs across 30 dimensions, sub-second latency at 500 concurrent users, and a fixed 90-day retention with no downsampling. ClickHouse's MergeTree with ORDER BY (day, product_id, ...) gives you sorted-storage co-location for the hot slice, and a SummingMergeTree materialized view pre-aggregates the "revenue by day × product × region × channel" query into a table small enough to serve sub-second.
3. The cardinality shape flips between the two workloads. In A, 50K vehicles × 8 metrics = 400K time-series identity strings — normal for a TSDB, catastrophic if you tried to encode each as a MergeTree partition. In B, the highest-cardinality dimension (user_id) is not used for slicing — it's a join key when needed, and the dashboard GROUP BYs go across low-cardinality columns (plan, region), which OLAP engines are optimised for.
4. Retention economics dominate. A TSDB compresses time-partitioned chunks 10-30x automatically after add_compression_policy. ClickHouse MergeTree with TTL drops old data but does not downsample automatically — if you want 5-minute rollups older than 30 days, you're building that pipeline yourself. On a fleet-telemetry workload, this is the cost delta that justifies the category choice.
5. Concurrency shape flips too. Workload A has ~5 ops engineers running queries. Workload B has 500 BI users hitting the dashboard concurrently. Serving 500 concurrent queries at sub-second p95 requires pre-aggregated materialized views + MPP horizontal scale — the OLAP superpower. A TSDB will work, but at 10x the CPU cost.

Output.

Workload	Category	Engine (2026 default)	Why
Fleet telemetry	TSDB	TimescaleDB	native time-partition + compression + downsampling
Product analytics dashboard	OLAP	ClickHouse	multi-dim GROUP BY + materialized views + concurrency
IoT sensor stream	TSDB	InfluxDB or QuestDB	fastest raw ingest for numeric time-series
Real-time user-facing analytics	OLAP	Pinot or Druid	pre-aggregated indexes + sub-second at 1000 users

Rule of thumb. Never pick between TSDB and OLAP based on "which one is trendy" or "which one my team knows." Pick it based on (workload shape × cardinality × latency × retention) — the four axes. Draw the two-workload comparison on a whiteboard; the category falls out of the constraints.

Worked example — what interviewers actually probe on TSDB vs OLAP

Detailed explanation. The senior data-engineering TSDB-vs-OLAP interview has a predictable structure: the interviewer opens with an ambiguous question ("we have a metrics pipeline — where would you store it?"), then progressively narrows to test whether you understand the four axes. The candidates who name both categories in sentence one score highest; the candidates who name a specific engine before understanding the workload score lowest. Walk through the interview grading rubric.

• Ambiguous opener. "Where would you store this metrics stream?" — invites you to name TSDB vs OLAP as the first fork.
• Follow-up 1. "What's the cardinality?" — probes cardinality axis.
• Follow-up 2. "How long do we keep the data?" — probes retention axis.
• Follow-up 3. "How many concurrent dashboard users?" — probes concurrency axis.
• Follow-up 4. "What does the query look like?" — probes read-shape axis (time-window vs multi-dim GROUP BY).

Question. Draft a 5-minute senior TSDB-vs-OLAP answer that covers all four axes without waiting to be asked.

Input.

Interview signal	Weak answer	Senior answer
Category named	"we'd use ClickHouse"	"TSDB if the reads are time-window aggregations; OLAP if they're multi-dim GROUP BYs"
Cardinality	"we'd shard"	"50K vehicles = 400K series — TSDB territory; 30 low-cardinality dims = OLAP territory"
Latency	"should be fast"	"seconds OK for ops = TSDB; sub-second at 500 users = OLAP with pre-aggregates"
Retention	"keep everything"	"TSDB automates 30d raw + 2y downsampled; OLAP needs a manual pipeline"
Read shape	"SELECT"	"time_bucket / SAMPLE BY = TSDB; GROUP BY across 10+ dims = OLAP"

Code.

Senior TSDB-vs-OLAP answer template (5 minutes)
===============================================

Minute 1 — name both categories, then pick
  "I'd split the answer into two: purpose-built time series databases —
   TimescaleDB, InfluxDB, QuestDB, Prometheus — and general-purpose OLAP
   engines — ClickHouse, Druid, Pinot, StarRocks. The workload picks the
   category. This looks like a time-series-heavy metrics stream, so I'd
   start with TimescaleDB."

Minute 2 — cardinality
  "The cardinality shape drives the choice. TSDBs are built for
   unbounded identity cardinality — 50K containers each emitting 20
   metrics is normal for InfluxDB, catastrophic for Druid if you tried
   to encode container_id as a dimension. OLAP engines want low-
   cardinality dimensions with high-cardinality metrics inside them —
   the opposite shape."

Minute 3 — latency + concurrency
  "TSDB queries on time-windowed data are sub-second by design because
   time is the partition key. OLAP wins at dashboard serving where you
   have 100-1000 concurrent users hitting the same materialised view.
   For 5 ops engineers looking at 'last hour CPU per host,' TSDB. For
   500 analysts running arbitrary dimensional cubes, OLAP."

Minute 4 — retention + downsampling
  "TSDBs ship retention and downsampling as one-liners — 'drop chunks
   older than 30 days,' 'materialize into 5-minute buckets on refresh.'
   OLAP engines have TTL clauses but not automatic downsampling — you
   build that pipeline yourself. For any workload that spans years of
   data with mixed hot + cold access, TSDB retention economics win."

Minute 5 — hybrid architectures
  "For orgs that need both metrics + dimensional analytics, the answer
   is often a hybrid: Prometheus scrapes hot metrics + Druid serves
   dimensional dashboards; or TimescaleDB ingests + ClickHouse serves
   interactive analytics. Two engines, one pipeline. Senior architects
   know both categories because at scale the answer is usually 'both.'"

Step-by-step explanation.

1. Minute 1 is the crucial framing. Naming both categories immediately — "TSDB or OLAP" — signals you're a decision-maker, not a tool-picker. Weak candidates dive into vendors ("we'd use ClickHouse because…") before naming the category. The category is the load-bearing choice; the vendor is a detail.
2. Minute 2 addresses cardinality before the interviewer asks. This preempts the common trap where you commit to an OLAP engine, then discover the per-container cardinality is 50K and query latency melts. Naming the cardinality axis up front is senior signal.
3. Minute 3 handles latency + concurrency together because they're linked. Sub-second latency at 5 concurrent users is trivial anywhere. Sub-second latency at 500 concurrent users requires either OLAP pre-aggregation (materialized views + MPP) or a caching layer on top of a TSDB. Say the number; the interviewer is listening for it.
4. Minute 4 is the retention argument. "Retention automation" and "downsampling automation" are the TSDB features that surprise engineers who've only used general-purpose OLAP. One line to compress; one line to drop; one line to downsample. Say this unprompted.
5. Minute 5 covers the hybrid pattern. The senior answer for a big org is rarely "one engine" — it's "two engines wired together." Naming a specific hybrid (Prometheus + Druid; TimescaleDB + ClickHouse) shows you've built one. Weak candidates commit to a single engine and dig themselves into a hole when the workload shape changes.

Output.

Grading criterion	Weak score	Senior score
Names both categories in minute 1	rare	mandatory
Names cardinality axis	rare	required
Names concurrency numbers	rare	senior signal
Names retention + downsampling	occasional	required
Names a hybrid architecture	rare	senior signal

Rule of thumb. The senior TSDB-vs-OLAP answer is a 5-minute monologue that covers workload shape, cardinality, latency, retention, and hybrid patterns without waiting for the follow-ups. Rehearse it once; deploy it every time.

Worked example — the "pick the category" decision tree

Detailed explanation. Given a new workload, the senior architect runs a 5-question decision tree in their head. Codifying the tree makes the interview answer reproducible: any interviewer can hand you a scenario and you can walk it out loud. Walk through the tree with three canonical scenarios: Kubernetes metrics scraping, a real-time gaming leaderboard, and a marketing-attribution warehouse.

• Q1. Are the primary reads time-window aggregations (last N hours / by minute)? → yes = go to Q2; no = OLAP.
• Q2. Is the identity cardinality unbounded (per-container, per-user, per-device)? → yes = TSDB; no = go to Q3.
• Q3. Do the reads span many dimensions (10+ GROUP BY columns)? → yes = OLAP; no = go to Q4.
• Q4. Are there big joins across fact + dimension tables? → yes = OLAP; no = go to Q5.
• Q5. Is the dashboard serving 100+ concurrent users? → yes = OLAP with pre-aggregates; no = TSDB is fine.

Question. Walk the decision tree for the three scenarios and record the category each ends up with.

Input.

Scenario	Q1 (time-window?)	Q2 (unbounded cardinality?)	Q3 (multi-dim?)	Q5 (100+ users?)
K8s metrics scraping	yes	yes	—	—
Real-time gaming leaderboard	mixed	yes (per-player)	limited	yes
Marketing-attribution warehouse	no	no	yes	yes

Code.

# Decision-tree helper (illustrative)
def pick_category(time_window_reads: bool,
                   unbounded_cardinality: bool,
                   multi_dim_reads: bool,
                   big_joins: bool,
                   concurrent_users: int) -> str:
    """Return the recommended category for a new workload."""
    if not time_window_reads:
        return "OLAP"
    if unbounded_cardinality:
        return "TSDB"
    if multi_dim_reads:
        return "OLAP"
    if big_joins:
        return "OLAP"
    if concurrent_users >= 100:
        return "OLAP with pre-aggregates"
    return "TSDB"


# Walk the three scenarios
print(pick_category(True,  True,  False, False, 10))
# → 'TSDB'  (K8s metrics scraping)

print(pick_category(True,  True,  False, False, 500))
# → 'TSDB'  (gaming leaderboard — but pre-aggregate the leaderboard view)

print(pick_category(False, False, True,  True,  500))
# → 'OLAP'  (marketing-attribution warehouse)

Step-by-step explanation.

1. Scenario 1 — Kubernetes metrics scraping. Prometheus (or Mimir/Cortex/Thanos backends) is the archetypal TSDB workload: time-window reads (last 5 minutes p95 latency), unbounded pod cardinality (pods come and go), few concurrent users (SRE team). Q1=yes, Q2=yes → TSDB. This is not a close call.
2. Scenario 2 — real-time gaming leaderboard. Per-player time-series (score over time), yes; but the dominant read is "top 100 players in the last hour" — a windowed GROUP BY with sub-second serving at potentially 100K concurrent users. The senior answer is TSDB for ingest + a pre-aggregated leaderboard view (Redis sorted set or a materialized view in the TSDB) for reads. Category is TSDB with a caching front.
3. Scenario 3 — marketing-attribution warehouse. Reads are 90-day windows GROUP BY (channel × campaign × geo × device × plan), 500 concurrent BI users. Q1=no (not time-window; day-of-week / hour-of-day are dimensions, not filters), Q3=yes, Q5=yes → OLAP. ClickHouse or Druid.
4. The parallel Q3-Q4-Q5 branch (multi-dim reads + joins + concurrency) is the OLAP fork. Any one of those three tipping to yes pushes the category to OLAP. The decision tree short-circuits aggressively toward OLAP because OLAP engines can serve time-window queries adequately (via materialized views), but TSDBs cannot serve arbitrary dimensional cubes efficiently.
5. If Q1 is genuinely ambiguous (both time-window and multi-dim), the answer is a hybrid — TSDB for the time-series identity dimension + OLAP for the analytical rollups. Refuse to pick a single category when the workload has two distinct read patterns.

Output.

Scenario	Primary category	Add-on
K8s metrics scraping	TSDB (Prometheus)	Mimir or Thanos for long-term storage
Real-time gaming leaderboard	TSDB + cache	Redis sorted set for the leaderboard read
Marketing-attribution warehouse	OLAP (ClickHouse)	scheduled ETL from raw event lake
IoT sensor telemetry + BI	Hybrid	InfluxDB for ingest, ClickHouse for analytics

Rule of thumb. The five-question decision tree is a whiteboard-friendly answer. Practice walking it end-to-end so an interviewer can hand you any scenario and get a category name in under 60 seconds. Refuse to pick a category before hearing the answer to Q1 and Q2 — they are load-bearing.

Senior interview question on TSDB vs OLAP category selection

A senior interviewer often opens with: "You inherit a metrics pipeline that scrapes 200K time-series from a Kubernetes cluster every 15 seconds and writes them to Postgres. Query latency is bad (5+ seconds for a 1-hour rollup dashboard) and Postgres disk fills up every three weeks. Walk me through the category migration you'd propose, the engine you'd pick, and the retention story."

Solution Using a TSDB migration to TimescaleDB with hypertables, compression, retention, and continuous aggregates

-- Step 1 — install TimescaleDB extension on the Postgres primary
CREATE EXTENSION IF NOT EXISTS timescaledb;

-- Step 2 — recreate the metrics table as a hypertable partitioned by ts
CREATE TABLE metrics (
    ts             TIMESTAMPTZ      NOT NULL,
    metric_name    TEXT             NOT NULL,
    labels         JSONB            NOT NULL,   -- {pod, namespace, container, ...}
    value          DOUBLE PRECISION NOT NULL
);

SELECT create_hypertable('metrics', 'ts',
    chunk_time_interval => INTERVAL '6 hours');

CREATE INDEX ix_metrics_labels ON metrics USING GIN (labels);
CREATE INDEX ix_metrics_name_ts ON metrics (metric_name, ts DESC);

-- Step 3 — compression policy on chunks older than 1 day (5-20x compression)
ALTER TABLE metrics SET (
    timescaledb.compress,
    timescaledb.compress_segmentby = 'metric_name',
    timescaledb.compress_orderby   = 'ts DESC'
);
SELECT add_compression_policy('metrics', INTERVAL '1 day');

-- Step 4 — retention policy: drop chunks older than 30 days
SELECT add_retention_policy('metrics', INTERVAL '30 days');

-- Step 5 — continuous aggregate for 1-minute rollups (serves the dashboard)
CREATE MATERIALIZED VIEW metrics_1m
WITH (timescaledb.continuous) AS
SELECT time_bucket('1 minute', ts)          AS bucket,
       metric_name,
       labels->>'pod'                       AS pod,
       labels->>'namespace'                 AS namespace,
       avg(value)                           AS avg_v,
       max(value)                           AS max_v,
       percentile_cont(0.95) WITHIN GROUP (ORDER BY value) AS p95_v
FROM   metrics
GROUP  BY bucket, metric_name, pod, namespace;

SELECT add_continuous_aggregate_policy('metrics_1m',
    start_offset      => INTERVAL '2 hours',
    end_offset        => INTERVAL '1 minute',
    schedule_interval => INTERVAL '1 minute');

-- Step 6 — the dashboard query now hits the continuous aggregate
SELECT bucket, pod, avg_v, p95_v
FROM   metrics_1m
WHERE  metric_name = 'container_cpu_usage_seconds_total'
  AND  namespace   = 'production'
  AND  bucket      > now() - INTERVAL '1 hour'
ORDER  BY bucket, pod;

Step-by-step trace.

Step	Before (raw Postgres)	After (TimescaleDB)
Storage engine	one big heap table	time-partitioned hypertable (6h chunks)
Compression	none (raw heap)	5-20x on chunks > 1 day old
Retention	manual DELETE WHERE ts < ...	automatic policy; drops chunks in O(1)
Dashboard latency	~5 s per query	~40 ms from continuous aggregate
Disk footprint	grows unbounded	flat at ~30 days × compressed size
Ingest throughput	~10K rows/s	100K+ rows/s (per hypertable)
Query planner	full-table scan	chunk-exclusion; only relevant chunks scanned

After the migration, the dashboard query on metric_name = 'container_cpu_usage_seconds_total' for production namespace over last 1 hour reads from metrics_1m (a continuous aggregate) and returns in ~40 ms instead of the ~5 seconds it took on the raw heap table. The disk footprint stops growing at ~30 days of compressed data; the retention policy silently drops chunks older than that; the compression policy silently compresses chunks older than 1 day.

Output:

Metric	Before	After
Dashboard p95 latency	~5 s	~40 ms
Ingest throughput	~10K rows/s	~100K rows/s
Disk footprint (steady state)	growing unbounded	~200 GB compressed
Retention mechanism	manual DELETE	policy: INTERVAL '30 days'
Downsampling mechanism	none (dashboard scans raw)	continuous aggregate (1-min buckets)

Why this works — concept by concept:

• Hypertable + time-partitioned chunks — the hypertable is a virtual view over many time-partitioned child tables (chunks). Every query with a WHERE ts BETWEEN ... clause skips chunks outside the range (chunk exclusion). This is O(chunks touched), not O(rows in table) — the same win a partitioned OLAP engine gives you, but automatic and time-shaped.
• Compression policy + segmentby — compress_segmentby = 'metric_name' groups the compressed rows by metric name, so a query on a single metric decompresses only the relevant column group. Compression ratios of 5-20x are typical for numeric time-series. This is TSDB territory; general Postgres has no automatic compression.
• Retention policy — add_retention_policy('metrics', INTERVAL '30 days') is one line. Under the hood, TimescaleDB drops entire chunks (fast, O(1) per chunk) rather than running DELETE WHERE ts < ... (slow, O(rows)). Retention automation is one of the two features that separates TSDBs from general OLAP.
• Continuous aggregate — a materialized view that incrementally refreshes as new data arrives. The dashboard query reads from the aggregate (small, indexed by bucket), not the raw table. Refresh happens every 1 minute for the trailing 2 hours; older data is already materialized. This is downsampling as a first-class citizen.
• Cost — hypertables, compression, retention, and continuous aggregates are all one-line ops. Compared to rolling your own on raw Postgres (custom partitioning, custom compression, custom TTL job, custom materialized-view refresh trigger), this is 10-100x less code and 10-100x less operational risk. The eliminated cost is the DBA time spent maintaining the custom pipeline. Net O(chunks touched) per query versus O(rows in table) before.

SQL
Topic — sql
SQL time-series and windowed-aggregation problems

Practice →

Aggregation Topic — aggregation Aggregation problems on time-bucketed rollups

Practice →

---

2. Workload characteristics driving the choice

Cardinality shape, read shape, and retention shape — the three sub-axes that pick the category before you ever name an engine

The mental model in one line: workload characteristics (write shape, read shape, cardinality shape, latency SLA, retention span, concurrency count) are what actually pick TSDB vs OLAP — not the vendor pitch deck — and every senior architect draws these six axes on the whiteboard before naming a single engine because the category falls out of the workload shape, not the other way around. Every senior DE has been in a room where an engineer named "ClickHouse" before hearing what the workload was; every senior DE has watched that decision cost six months of rewrites.

The six workload axes for TSDB vs OLAP.

• Write shape. TSDBs assume append-only, time-ordered, high-throughput writes (100K–1M rows/sec per node is normal for InfluxDB or QuestDB). OLAP engines assume mixed writes — appends, occasional updates, bulk loads — at a lower steady-state throughput (10K–100K rows/sec per node for ClickHouse without careful tuning). If your write shape is "10 CSV files a day," write throughput is not the axis; category is decided elsewhere. If your write shape is "1 million rows a second from Kafka," TSDBs are the natural home.
• Read shape. TSDBs excel when reads are time-window aggregations (SELECT time_bucket('1m', ts), avg(v) FROM t WHERE ts > now() - '1h' GROUP BY 1). OLAP engines excel when reads are multi-dim GROUP BYs across arbitrary dimensions (SELECT product, region, channel, sum(revenue) FROM t GROUP BY 1,2,3). The dominant read query — not the average or the edge cases — picks the category.
• Cardinality shape. TSDBs handle unbounded identity cardinality (per-container, per-user, per-device) natively because identity is a label on each time-series, not a dimension that participates in indexing. OLAP engines handle unbounded value cardinality (large fact tables) natively because MergeTree-style sorted storage scales linearly with rows. Confusing the two — trying to slice a Druid dashboard by container_id at 50K unique values — is the number-one cause of "our OLAP engine is slow" incidents.
• Latency SLA. Sub-second at low concurrency is trivial for both categories. Sub-second at 100+ concurrent users is OLAP-with-pre-aggregates territory (Druid, Pinot). Seconds tolerated with automatic downsampling is TSDB territory. Interviewers ask for the number because it's the single most differentiating axis.
• Retention span. Days-to-months with automatic downsampling to older granularities is TSDB territory (Timescale continuous aggregates + retention policies; InfluxDB retention policies + downsampling tasks). Months-to-years at full fidelity is OLAP territory (ClickHouse MergeTree + TTL). Multi-year with mixed hot + cold access is hybrid territory (TSDB for recent, OLAP or object storage for archive).
• Concurrency count. Fewer than 10 concurrent users (ops engineers, SREs) — either category works. 50-500 concurrent users (analysts, product managers) — OLAP with materialized views. 500-5000+ concurrent users (user-facing analytics dashboards embedded in a SaaS product) — Pinot or Druid, purpose-built for the concurrency shape.

The cardinality trap — the failure mode senior engineers pre-empt.

• The trap. An engineer picks ClickHouse for a metrics workload, defines container_id as a MergeTree ORDER BY column, and cardinality reaches 50K unique containers with hourly turnover (300K total across a week). MergeTree part merging slows to a crawl; queries touching multiple containers scan too many parts; the "obvious" solution (add more nodes) doesn't help because the problem is data layout, not compute.
• The fix. Either (a) migrate the identity dimension out of the ORDER BY and into a JSONB / Map column read only when specific containers are queried — accepting slower "single-container" queries in exchange for keeping cluster-wide queries fast — or (b) migrate to a TSDB where per-identity cardinality is a native concept (TimescaleDB with compress_segmentby = container_id, InfluxDB series-key-based storage).
• The lesson. Cardinality shape is the first axis to check when evaluating an OLAP engine for a time-series-flavoured workload. If the identity cardinality is unbounded, the OLAP engine will fight you for years.

The concurrency trap — the other failure mode.

• The trap. A team picks TimescaleDB (or any TSDB) for a user-facing analytics feature that will eventually serve 500 concurrent dashboard users. Each query is fine on its own (sub-second on the continuous aggregate). Under load, the Postgres connection pool saturates, query planner overhead compounds, and p99 latency blows out.
• The fix. Serve the user-facing dashboard from a purpose-built low-latency-at-concurrency engine — Pinot or Druid — and use the TSDB only for the ingest + storage layer. The pattern is "TSDB for the storage; OLAP for the serving." Two engines, one pipeline.
• The lesson. Concurrency is the second axis that pushes a workload out of pure-TSDB territory even when the write and read shapes are TSDB-native.

Common interview probes on workload characteristics.

• "What's the cardinality?" — required to answer before naming an engine.
• "What does the query look like?" — required to answer before naming an engine.
• "How many concurrent users?" — required to answer before recommending Pinot/Druid vs TimescaleDB.
• "How far back does the data go, and at what granularity?" — required for the downsampling / retention story.
• "What's the write throughput?" — usually a proxy for "can Postgres handle this?" (answer: not at 100K rows/sec without help).

Worked example — the six-axis workload audit for a fleet-telemetry pipeline

Detailed explanation. Given a fleet-telemetry pipeline (50K vehicles, GPS + engine metrics every 5 seconds, 30-day hot + 2-year archive, 5 ops engineers looking at dashboards), walk through the six-axis audit. Land on TimescaleDB as the answer, not because "TimescaleDB is trendy" but because every axis pushes toward TSDB.

• Write shape. 50K vehicles × 8 metrics × (1 sample / 5s) = 80K rows/sec average, ~500K rows/sec peak during rush hour.
• Read shape. "Last 24h path for vehicle X" (per-vehicle time-window); "fleet-wide fuel efficiency by hour" (rollup with GROUP BY hour).
• Cardinality. 50K vehicles × 8 metrics = 400K distinct time-series.
• Latency SLA. Seconds OK; dashboards refresh every 15s.
• Retention. 30 days raw + 2 years downsampled to 5-minute buckets.
• Concurrency. 5 ops engineers.

Question. Walk each of the six axes for the fleet-telemetry pipeline and record which category each axis pushes toward.

Input.

Axis	Value	Pushes toward
Write shape	80K–500K rows/sec, append-only	TSDB
Read shape	time-window per-vehicle + hourly rollup	TSDB
Cardinality	400K distinct series	TSDB
Latency SLA	seconds OK	either
Retention	30d raw + 2y downsampled	TSDB (native downsampling)
Concurrency	5 users	either

Code.

# Six-axis audit helper — returns per-axis category and an aggregate recommendation
def audit_workload(write_rows_per_sec: int,
                    read_shape: str,   # 'time_window' | 'multi_dim' | 'mixed'
                    identity_cardinality: int,
                    latency_p95_ms: int,
                    retention_days: int,
                    downsampling_needed: bool,
                    concurrent_users: int) -> dict:
    votes = {"TSDB": 0, "OLAP": 0}

    if write_rows_per_sec >= 50_000:      votes["TSDB"] += 1
    if read_shape == "time_window":       votes["TSDB"] += 1
    elif read_shape == "multi_dim":       votes["OLAP"] += 1
    if identity_cardinality >= 10_000:    votes["TSDB"] += 1
    if latency_p95_ms <= 500 and concurrent_users >= 100:
                                          votes["OLAP"] += 1
    if downsampling_needed:               votes["TSDB"] += 1
    if concurrent_users >= 500:           votes["OLAP"] += 1

    return {"votes": votes,
             "recommendation": max(votes, key=votes.get)}


# Fleet-telemetry audit
audit_workload(write_rows_per_sec=500_000,
                read_shape="time_window",
                identity_cardinality=400_000,
                latency_p95_ms=2000,
                retention_days=30 + 730,
                downsampling_needed=True,
                concurrent_users=5)
# → {'votes': {'TSDB': 4, 'OLAP': 0}, 'recommendation': 'TSDB'}

Step-by-step explanation.

1. The write-shape axis alone (80K–500K rows/sec append-only) pushes toward TSDB — the highest-throughput OLAP engines (ClickHouse, StarRocks) can hit these rates, but only with careful batching, and the operational overhead is greater than a TSDB shipping this out of the box.
2. The read-shape axis (time-window per-vehicle + hourly rollup) is the archetypal TSDB read pattern. Every read has a WHERE ts BETWEEN ... AND ... clause; every aggregation is time-bucketed. TSDBs' chunk-exclusion + continuous aggregates map exactly onto this shape.
3. The cardinality axis (400K distinct series) is where OLAP engines struggle unless the identity is decomposed into low-cardinality sub-dimensions (vehicle_id → fleet × truck-model × truck-number, for example). TSDBs handle 400K series as a straightforward series-key concept; no decomposition needed.
4. The retention axis (30 days raw + 2 years downsampled) is a TSDB-native pattern. TimescaleDB's continuous aggregate + retention policy combination is the one-liner; the OLAP equivalent is a manual downsampling ETL job scheduled on the side.
5. The concurrency axis (5 users) is neutral — either category serves 5 concurrent users trivially. If concurrency were 500, this axis alone would flip the recommendation to Pinot / Druid regardless of the other five axes.

Output.

Axis	Fleet vote
Write shape	TSDB
Read shape	TSDB
Cardinality	TSDB
Latency SLA	neutral
Retention + downsampling	TSDB
Concurrency	neutral
Aggregate	TSDB — TimescaleDB or InfluxDB

Rule of thumb. For any new workload, walk the six axes out loud before naming an engine. Any axis that votes strongly for one category is enough to short-circuit the decision if no other axis votes strongly for the opposite category. When axes conflict, you have a hybrid architecture on your hands — not a single-engine decision.

Worked example — the six-axis audit for a product-analytics dashboard

Detailed explanation. The counter-example: a product-analytics dashboard suite (5M daily events with 30 dimensions, 500 BI users, 90 days full fidelity, sub-second at p95). Walk the same six axes and land on ClickHouse (or Druid / Pinot for the highest-concurrency embedded scenarios).

• Write shape. 5M events / day = ~60 events/sec average; batch-loaded from a Kafka → Snowflake pipeline. Not a write-throughput bottleneck.
• Read shape. "Revenue by plan × region × channel × day, last 90 days" — multi-dim GROUP BY across 4–10 dimensions per query.
• Cardinality. 30 dimensions with 10–1000 values each; user_id (~1M unique) present but never used for slicing.
• Latency SLA. Sub-second p95 for 500 concurrent BI users.
• Retention. 90 days full fidelity; no downsampling requirement.
• Concurrency. 500 BI users.

Question. Walk each axis for the product-analytics dashboard and record which category each axis pushes toward.

Input.

Axis	Value	Pushes toward
Write shape	60 events/sec, batch-loaded	either
Read shape	multi-dim GROUP BY across 4–10 dims	OLAP
Cardinality	30 low-cardinality dims	OLAP
Latency SLA	sub-second p95	OLAP with pre-aggregates
Retention	90d full fidelity	either
Concurrency	500 BI users	OLAP

Code.

audit_workload(write_rows_per_sec=60,
                read_shape="multi_dim",
                identity_cardinality=1_000,   # low-cardinality slicing dims
                latency_p95_ms=500,
                retention_days=90,
                downsampling_needed=False,
                concurrent_users=500)
# → {'votes': {'TSDB': 0, 'OLAP': 4}, 'recommendation': 'OLAP'}

Step-by-step explanation.

1. The write-shape axis is neutral — 60 events/sec is trivial for any engine, batch-loaded overnight from a warehouse. This axis contributes nothing to the decision; ignore it.
2. The read-shape axis is where the OLAP argument dominates. Multi-dim GROUP BY across plan × region × channel × day is exactly what MergeTree-style engines are built to serve. A TSDB could answer this query, but it would scan more data because time is the primary partition key, not the dimensions being sliced.
3. The cardinality axis pushes toward OLAP because the slicing dimensions are low-cardinality (plan × region × channel = maybe 200 total combinations); the high-cardinality dimension (user_id) is not used for slicing, only for joins or filters. This is the OLAP-native cardinality shape.
4. The latency + concurrency combination (sub-second at 500 users) is where OLAP with pre-aggregates dominates. A ClickHouse SummingMergeTree materialized view pre-aggregates the dashboard slice; the query hits the materialized view; the p95 latency is 40ms even at 500 concurrent users.
5. The retention axis is neutral. 90 days is short enough that neither category has an economic advantage; full fidelity means no downsampling gain.

Output.

Axis	Product-analytics vote
Write shape	neutral
Read shape	OLAP
Cardinality	OLAP
Latency SLA + concurrency	OLAP
Retention	neutral
Aggregate	OLAP — ClickHouse (or Druid for embedded)

Rule of thumb. The six-axis audit gives the same answer regardless of who runs it, as long as the workload characteristics are honestly captured. When the answer surprises the team ("we thought this was a TSDB job but the audit says OLAP"), the audit is doing its job. Trust the axes.

Worked example — the mixed workload that forces a hybrid decision

Detailed explanation. A common awkward-scenario in senior interviews: the workload has some TSDB characteristics and some OLAP characteristics. IoT sensor telemetry (TSDB write shape) that must also serve a BI-tool analytics dashboard (OLAP read shape) with 200 concurrent users. Walk through the audit, notice the split, and propose a hybrid architecture.

• Write shape. 20K sensors × 1 sample/sec = 20K rows/sec — TSDB territory.
• Read shape. Two dominant queries: (a) "last 24h sensor trace for sensor X" (time-window; TSDB), (b) "energy consumption by plant × line × shift × hour, last 30 days" (multi-dim GROUP BY; OLAP).
• Cardinality. 20K sensor_ids (TSDB territory) + 5 low-cardinality dims for the BI dashboard (OLAP territory).
• Latency + concurrency. 200 BI users hitting the dashboard — sub-second required.
• Retention. 90 days raw + 5 years downsampled — mixed.

Question. Recognize the workload split, propose a hybrid architecture, and specify the wiring.

Input.

Axis	Value	Category
Ingest	20K rows/sec sensor stream	TSDB
Time-window reads	trace queries	TSDB
Multi-dim reads	BI dashboard	OLAP
Concurrency	200 BI users	OLAP
Retention	90d raw + 5y downsampled	TSDB (downsampling)

Code.

# Hybrid architecture — TimescaleDB for ingest + storage, ClickHouse for BI serving

ingest_layer:
  engine: TimescaleDB
  role: durable append-only ingest, time-window queries, downsampling
  writes: 20K rows/sec direct from Kafka via kafka-connect-timescaledb
  reads: sensor-trace UI (5 ops engineers)
  retention: 90 days raw + continuous aggregate to 5-min buckets
  compression: chunks > 3 days, 10-20x compressed

downsampling_pipeline:
  source: TimescaleDB continuous aggregate (1-min buckets)
  sink: ClickHouse SummingMergeTree
  schedule: every 5 minutes via Airflow
  transform: sensor_id → (plant, line, shift) join with dim_sensor table

serving_layer:
  engine: ClickHouse
  role: multi-dim BI dashboard for 200 concurrent users
  data_source: downsampled 1-min buckets from TimescaleDB, joined with dims
  materialized_views:
    - name: sensor_hourly_by_plant_line_shift
      engine: SummingMergeTree
      order_by: [day, plant, line, shift]
  ttl: 5 years

Step-by-step explanation.

1. The workload split is unambiguous once you draw the audit: writes and single-sensor reads are TSDB-native, but the BI dashboard queries are OLAP-native and the concurrency is over the TSDB threshold. Trying to serve both from one engine leaves half the workload badly served.
2. The hybrid architecture wires TimescaleDB as the ingest and storage layer — receives the 20K rows/sec Kafka stream, stores 90 days of raw data, downsamples to 1-min buckets via continuous aggregate, and serves the single-sensor trace UI.
3. A scheduled downsampling pipeline (Airflow → SQL) pulls the 1-min-bucket continuous aggregate from TimescaleDB, joins it with dim_sensor to add plant / line / shift dimensions, and inserts into a ClickHouse SummingMergeTree table. The ClickHouse copy has richer dimensions than the TimescaleDB copy because it serves a different query pattern.
4. ClickHouse's SummingMergeTree materialized view pre-aggregates the "hourly by plant × line × shift" query. The 200 BI users hit this materialized view; p95 latency is under 200ms; concurrency is not a concern because the pre-aggregated table is small.
5. Retention is split: TimescaleDB drops raw data after 90 days (native retention policy); ClickHouse keeps the 1-min-bucket data with 5-year TTL. This is the "hot at high fidelity, warm/cold at low fidelity" pattern that mixes TSDB and OLAP strengths cleanly.

Output.

Layer	Engine	Data granularity	Query pattern	Users
Ingest	TimescaleDB	raw (1-sec samples)	Kafka append-only	Kafka connector
Trace UI	TimescaleDB	raw + continuous aggregate	single-sensor time-window	5 ops
Dashboard	ClickHouse	1-min buckets, dimensions joined	multi-dim GROUP BY	200 BI
Archive	ClickHouse	1-min buckets, 5-year TTL	historical trends	5 analysts

Rule of thumb. When the six-axis audit gives a split answer (some axes → TSDB, others → OLAP), the workload is a hybrid workload and the architecture should reflect that. Two engines, one pipeline, wired via a scheduled ETL that transforms the TSDB shape into the OLAP shape. Don't try to force one category to serve both; you'll be rewriting in year two.

Senior interview question on workload-characteristics-driven category selection

A senior interviewer might ask: "You are given a new observability workload — 100K servers, each emitting 40 metrics per second, dashboards for 200 SREs looking at time-window queries, alerting rules that scan the last 5 minutes at high frequency, retention 45 days hot + 3 years archived. Walk me through the six-axis audit, name the category, name the engine, and specify the retention + downsampling story."

Solution Using a purpose-built TSDB (VictoriaMetrics / TimescaleDB) with tiered retention + downsampling

# 1. Six-axis audit — recorded on the whiteboard first
write_shape:
  value: 100K servers x 40 metrics/sec = 4M samples/sec
  votes: TSDB (write throughput is TSDB territory)
read_shape:
  value: time-window aggregations + alerting rules
  votes: TSDB (time is the partition key; PromQL-shaped queries)
cardinality:
  value: 100K server-ids x 40 metric-names x label combinations ~ 5-10M series
  votes: TSDB (unbounded identity cardinality is TSDB-native)
latency_sla:
  value: sub-second at 200 concurrent SREs
  votes: TSDB with careful indexing (SREs' queries are windowed, not multi-dim)
retention_span:
  value: 45d hot + 3y archive with downsampling
  votes: TSDB (native downsampling + tiered retention)
concurrency_count:
  value: 200 SREs
  votes: neutral (200 is inside TSDB range for windowed queries)

recommendation: TSDB — specifically VictoriaMetrics or TimescaleDB for
                the write throughput; Prometheus is undersized at 4M samples/sec

# 2. VictoriaMetrics cluster config for the ingest + storage layer
vmstorage:
  nodes: 6
  retentionPeriod: 45d          # hot tier
  storageDataPath: /data/vm

vminsert:
  nodes: 3
  addr: :8480

vmselect:
  nodes: 3
  addr: :8481
  cacheDataPath: /cache/vm

vmagent:
  # deployed alongside each server; scrapes local exporters and remote-writes
  # to vminsert with 20-second scrape interval
  scrape_configs:
    - job_name: node
      scrape_interval: 20s
  remote_write:
    - url: http://vminsert:8480/insert/0/prometheus/

-- 3. Downsampling to a cold tier (ClickHouse or long-term VictoriaMetrics)
-- Use vmalert recording rules to pre-aggregate to 5-minute buckets

-- vmalert config (YAML)
-- groups:
--   - name: downsampling
--     interval: 5m
--     rules:
--       - record: node:cpu:avg5m
--         expr:   avg_over_time(node_cpu_seconds_total[5m])
--       - record: node:memory:avg5m
--         expr:   avg_over_time(node_memory_MemAvailable_bytes[5m])
--       - record: node:disk_io:sum5m
--         expr:   sum_over_time(node_disk_io_time_seconds_total[5m])

-- Cold tier: separate VictoriaMetrics cluster with 3-year retention
-- receives the downsampled recording rules via remote_write

# 4. Alerting query — hits the hot tier (raw 20s samples), sub-second
groups:
  - name: node_alerts
    rules:
      - alert: HighCPU
        expr: avg_over_time(node_cpu_seconds_total[5m]) > 0.90
        for:  10m
        labels:
          severity: page

      - alert: LowMemory
        expr: node_memory_MemAvailable_bytes / node_memory_MemTotal_bytes < 0.05
        for:  5m
        labels:
          severity: page

Step-by-step trace.

Concern	Answer	Reasoning
Category	TSDB	six-axis audit — write throughput + cardinality + downsampling all TSDB
Engine	VictoriaMetrics	4M samples/sec ingest; better than Prometheus at scale
Hot tier	45 days raw at 20s	fits on 6-node vmstorage cluster with ~500 GB/node
Cold tier	3 years at 5-min buckets	separate VM cluster; ~10x smaller than raw
Downsampling	vmalert recording rules	pre-aggregate at 5-min buckets; remote_write to cold
Alerting	vmalert on hot tier	sub-second query on 20s-resolution data
Dashboards	Grafana → vmselect	query splits between hot + cold based on time range

After deployment, the ingest layer sustains 4M samples/sec across the vmstorage cluster; hot-tier queries (last 24h) return in ~100 ms; alerting evaluates every 20 seconds without saturating vmselect; downsampled data flows continuously to the cold tier; historical dashboards (>45 days) transparently query the cold tier via the vmselect federation. The 200 SREs see one Grafana pane; the two-tier storage is invisible.

Output:

Metric	Value
Ingest throughput	4M samples/sec sustained
Hot-tier storage footprint	~3 TB total (6 nodes × 500 GB)
Cold-tier storage footprint	~2 TB (5-min buckets, 3 years)
Query p95 latency (hot tier)	~100 ms for last-24h windowed
Query p95 latency (cold tier)	~500 ms for historical trends
Alerting frequency	every 20 s (matches scrape interval)
Downsampling ratio	15× (5-min buckets vs 20-sec raw)

Why this works — concept by concept:

• VictoriaMetrics as the ingest engine — VictoriaMetrics is a Prometheus-compatible TSDB optimized for the 1M+ samples/sec ingest that vanilla Prometheus struggles with. It uses a mergeset-style storage engine (like ClickHouse's MergeTree, but time-series-shaped) that compresses time-series data 5-10× on disk while accepting append-only writes at line rate.
• Tiered retention hot + cold — the hot tier keeps 45 days at raw resolution (20-second samples) for high-fidelity debugging; the cold tier keeps 3 years at 5-minute resolution for trend analysis. This is the classic TSDB downsampling pattern — impossible to do declaratively in a general OLAP engine without a custom scheduled ETL.
• vmalert recording rules — recording rules pre-aggregate expensive queries into cheaper time-series that are stored back in the TSDB. avg_over_time(node_cpu_seconds_total[5m]) becomes node:cpu:avg5m, a 5-minute-resolution series that the cold tier stores efficiently. This is downsampling as a first-class citizen in the query language.
• Query federation across hot + cold — vmselect can query both clusters and merge results transparently. A dashboard asking for "last 90 days CPU" gets the last 45 days from the hot tier at raw resolution and the earlier 45 days from the cold tier at 5-minute resolution, all in one query.
• Cost — six-node hot cluster + three-node cold cluster + vmagent scrape sidecars per server. Compared to trying to serve this workload from a general OLAP engine, the ingest throughput alone would require 5-10x the hardware, and the downsampling story would be a bespoke ETL. Net O(scrapes per second × compression) storage cost per host, versus O(raw samples per query) scan cost on OLAP. The elimination is Prometheus's single-node throughput ceiling; the trade is operational familiarity for VictoriaMetrics's Prometheus-compatible surface.

SQL
Topic — sql
SQL time-window and workload-audit problems

Practice →

Design Topic — design Design problems on observability + metrics pipelines

Practice →

---

3. TSDB strengths — time_bucket, hypertables, downsampling

time_bucket / SAMPLE BY / GROUP BY time() and their friends — the primitives OLAP engines cannot match without custom SQL gymnastics

The mental model in one line: TSDBs earn their category by shipping native time primitives — time_bucket (TimescaleDB), SAMPLE BY (QuestDB), GROUP BY time() (InfluxDB Flux/InfluxQL), rate/delta/gap-fill/LOCF interpolation, and continuous aggregates that refresh incrementally — that turn time-window aggregation queries from 20-line SQL gymnastics into 3-line one-liners, and by shipping time-partitioned storage with per-chunk compression and one-line retention policies that turn what would be a custom ETL pipeline in an OLAP engine into declarative config. Every senior DE who has used both categories will tell you that the TSDB primitives are the reason time-series queries feel natural in a TSDB and awkward everywhere else.

The five TSDB superpowers.

• Native time-bucketing SQL. time_bucket('5 minutes', ts) (TimescaleDB) / SAMPLE BY 5m (QuestDB) / |> aggregateWindow(every: 5m, fn: mean) (InfluxDB Flux) all produce the same effect: bucket rows by fixed time intervals and aggregate within each bucket. In ANSI SQL this is date_trunc('minute', ts) + INTERVAL '5 minute' * (extract(minute from ts)::int / 5) — technically correct, painful to type. The TSDB primitive is not just syntactic sugar; the query planner uses it to route to the right chunk, apply chunk exclusion, and hit continuous aggregates when available.
• Time-domain aggregate functions. LAST(value, ts) returns the most recent value; FIRST(value, ts) returns the earliest; DELTA(value) returns the difference between consecutive samples; RATE(counter) computes per-second rate from a monotonically-increasing counter; gap_fill and LOCF (last observation carried forward) interpolate missing samples so the returned series is contiguous. OLAP engines can compute these with window functions, but the TSDB versions know about time gaps and per-series ordering natively.
• Time-partitioned chunks + compression. TimescaleDB's hypertable is a view over many time-partitioned child tables (chunks). Each chunk covers a contiguous time interval (default 1 week, configurable); older chunks can be compressed 5-30x via add_compression_policy. Query planner uses chunk exclusion to skip chunks outside the query's time range. This is transparent to the query — you write SELECT ... FROM hypertable WHERE ts > now() - '1h' and TimescaleDB routes to the one relevant chunk.
• Continuous aggregates. A continuous aggregate is a materialized view that TimescaleDB refreshes incrementally — only the buckets whose source rows changed since the last refresh are recomputed. SELECT time_bucket('1 hour', ts), avg(value) FROM raw GROUP BY 1 becomes a materialized view refreshed every 5 minutes; the dashboard reads from the aggregate; the raw table only sees writes. Continuous aggregates are TSDB territory; OLAP engines have materialized views but they don't refresh incrementally by time-window.
• Retention + downsampling policies. add_retention_policy('metrics', INTERVAL '30 days') is one line and drops entire chunks older than 30 days. add_continuous_aggregate_policy(...) refreshes rollups on a schedule. Combining the two gives you "keep 30 days raw + 2 years downsampled to 1-hour buckets" in five lines of config. In an OLAP engine, this is a custom Airflow DAG.

The write-throughput edge.

• QuestDB. ILP (InfluxDB Line Protocol) ingest at ~4M rows/sec per node in benchmarks; SQL query engine on top.
• InfluxDB. TSM (time-structured merge tree) storage; ~1M writes/sec per node; Flux and InfluxQL query languages.
• TimescaleDB. Postgres extension; ~500K-1M rows/sec per node with careful batching; full SQL surface.
• VictoriaMetrics. Prometheus-compatible; ~2-5M samples/sec per node; PromQL query language.
• Prometheus. ~500K-1M samples/sec per node on modern hardware; horizontally scaled via Mimir / Thanos / Cortex.

The compression edge — why TSDBs are 10x cheaper on disk.

• Delta-of-delta encoding. Consecutive timestamps differ by nearly-constant amounts (scrape intervals). Delta-of-delta reduces them to a few bits per timestamp.
• XOR / Gorilla compression on float values. Consecutive float values in a time-series often differ only in the low bits. Facebook's Gorilla algorithm exploits this.
• Dictionary encoding on labels. container_id, namespace, pod values repeat billions of times across a time-series; dictionary encoding compresses them to a few bytes per row.
• Combined effect. TimescaleDB and VictoriaMetrics regularly achieve 10-30x compression on real metrics workloads. ClickHouse achieves 3-10x on the same data because its compression is column-generic, not time-series-specific.

Common interview probes on TSDB strengths.

• "How does a TSDB bucket by time?" — time_bucket / SAMPLE BY / GROUP BY time().
• "What is a continuous aggregate?" — incrementally-refreshed materialized view over time-bucketed aggregates.
• "How does a TSDB compress data?" — time-partitioned chunks + delta-of-delta + XOR / Gorilla float compression.
• "What's a hypertable?" — TimescaleDB's abstraction; virtual table backed by many time-partitioned child tables (chunks).
• "How does retention work?" — one-line policy that drops entire chunks (O(1)) rather than DELETE-based row deletion (O(rows)).

Worked example — TimescaleDB continuous aggregate for CPU rollup

Detailed explanation. The canonical TSDB pattern: raw 1-second CPU samples land in a hypertable at 100K rows/sec; a continuous aggregate materializes 1-minute average and p95; the dashboard reads from the aggregate. Walk through the entire pipeline.

• Raw table. cpu_usage(ts, host, container, cpu_pct) as a hypertable with 6-hour chunks.
• Aggregate. 1-minute buckets with avg(cpu_pct) and percentile_cont(0.95).
• Policy. Refresh every minute for the trailing 2 hours.
• Dashboard query. Reads from the aggregate; sub-second even at 30 days.

Question. Write the hypertable DDL, the continuous aggregate, the refresh policy, and the dashboard query.

Input.

Object	Purpose
cpu_usage	raw 1-second samples hypertable
cpu_usage_1m	1-minute continuous aggregate
refresh policy	incremental refresh every 1 minute
dashboard query	reads from cpu_usage_1m

Code.

-- 1. Raw hypertable — chunks of 6 hours
CREATE TABLE cpu_usage (
    ts        TIMESTAMPTZ NOT NULL,
    host      TEXT        NOT NULL,
    container TEXT        NOT NULL,
    cpu_pct   DOUBLE PRECISION NOT NULL
);

SELECT create_hypertable('cpu_usage', 'ts',
    chunk_time_interval => INTERVAL '6 hours');

CREATE INDEX ix_cpu_host_ts ON cpu_usage (host, ts DESC);

-- 2. Compression on chunks older than 1 day (10-20x on CPU metrics)
ALTER TABLE cpu_usage SET (
    timescaledb.compress,
    timescaledb.compress_segmentby = 'host, container',
    timescaledb.compress_orderby   = 'ts DESC'
);
SELECT add_compression_policy('cpu_usage', INTERVAL '1 day');

-- 3. Continuous aggregate — 1-minute buckets with avg + p95
CREATE MATERIALIZED VIEW cpu_usage_1m
WITH (timescaledb.continuous) AS
SELECT time_bucket('1 minute', ts) AS bucket,
       host,
       container,
       avg(cpu_pct)                                                     AS avg_cpu,
       percentile_cont(0.95) WITHIN GROUP (ORDER BY cpu_pct)            AS p95_cpu,
       max(cpu_pct)                                                     AS max_cpu
FROM   cpu_usage
GROUP  BY bucket, host, container;

-- 4. Refresh policy — every minute, materialize the trailing 2 hours
SELECT add_continuous_aggregate_policy('cpu_usage_1m',
    start_offset      => INTERVAL '2 hours',
    end_offset        => INTERVAL '1 minute',
    schedule_interval => INTERVAL '1 minute');

-- 5. Retention policy — drop raw chunks older than 30 days
SELECT add_retention_policy('cpu_usage', INTERVAL '30 days');

-- 6. Dashboard query — reads from the aggregate; sub-second even at 30 days
SELECT bucket, host, avg_cpu, p95_cpu
FROM   cpu_usage_1m
WHERE  host = 'prod-web-42'
  AND  bucket > now() - INTERVAL '24 hours'
ORDER  BY bucket;

Step-by-step explanation.

1. The raw cpu_usage hypertable is partitioned into 6-hour chunks. At 100K rows/sec that is ~2.2 billion rows per chunk — a substantial slab of data, but TimescaleDB handles it as long as the query planner can exclude chunks outside the query range. Chunk exclusion means a "last hour" query touches one chunk; a "last day" query touches 4 chunks; a "last 30 days" query touches 120 chunks (with parallel scan).
2. The compression policy converts each chunk to a columnar-compressed form after it becomes 1 day old. compress_segmentby = 'host, container' groups rows by host + container so decompression only reads relevant groups; compress_orderby = 'ts DESC' sorts within each group. Compression ratios of 10-20x are typical for CPU metrics.
3. The continuous aggregate cpu_usage_1m is a materialized view that TimescaleDB refreshes incrementally. Only the buckets whose source rows changed since the last refresh are recomputed; the older buckets are stored and reused. This is O(delta) refresh cost, not O(all data) — critical for the pipeline to keep up with 100K rows/sec ingest.
4. The refresh policy runs every 1 minute, materializing the trailing 2 hours. start_offset = '2 hours' and end_offset = '1 minute' mean the aggregate is always slightly stale (up to 1 minute) but the refresh window is bounded (2 hours), so a single refresh never has to compute more than 2 hours worth of buckets.
5. The dashboard query hits cpu_usage_1m directly. For "last 24h on prod-web-42" the aggregate returns 1440 rows (one per minute) in ~10 ms because the aggregate is small and indexed by (host, bucket). Without the aggregate, the same query would scan 8.6 million rows in the raw hypertable — under a second with proper indexing, but 100x more CPU per query.

Output.

Query	Rows scanned	Latency
Dashboard: last 24h avg + p95 per minute	1440 (from aggregate)	~10 ms
Same query on raw hypertable	8.64M rows (100K/sec × 86.4K sec)	~600 ms
Aggregate refresh (every 1m)	~6M raw rows → 60 buckets	~800 ms
Retention (drop chunks > 30d)	O(chunks_dropped) per day	milliseconds

Rule of thumb. For any TSDB workload that will be read by a dashboard, materialize the dashboard's aggregation as a continuous aggregate. Set the refresh window to match the acceptable staleness (1 minute is usual for ops dashboards); set the retention to match the compliance requirement (30 days is common for metrics). The three one-liners — create_hypertable, add_compression_policy, add_continuous_aggregate_policy — cover 80% of TSDB engineering.

Worked example — QuestDB SAMPLE BY for financial tick data

Detailed explanation. QuestDB is the fastest-writing TSDB in the SQL-native family, purpose-built for financial tick data and IoT streams. Its SAMPLE BY clause is the time-bucket primitive; combined with FILL(LINEAR) or FILL(PREV) it handles gap interpolation natively. Walk through a tick-data aggregation pipeline.

• Data. trades(ts, symbol, price, volume) streamed via InfluxDB Line Protocol at ~500K rows/sec.
• Query. OHLC (open, high, low, close) bars at 1-minute granularity for the last hour per symbol.
• Interpolation. Fill gaps with the previous value so charts look continuous.

Question. Write the QuestDB table DDL, the ILP ingest example, and the SAMPLE BY query for 1-minute OHLC bars.

Input.

Component	Value
Table	trades(ts, symbol, price, volume)
Ingest protocol	InfluxDB Line Protocol on port 9009
Sample interval	1 minute
Gap fill	FILL(PREV) for continuity
Symbols	~2000 tickers

Code.

-- 1. QuestDB table with time-based partitioning by day
CREATE TABLE trades (
    ts     TIMESTAMP,
    symbol SYMBOL,     -- SYMBOL type is QuestDB's dictionary-encoded string
    price  DOUBLE,
    volume LONG
) TIMESTAMP(ts) PARTITION BY DAY WAL;

# 2. ILP ingest — clients push newline-delimited samples to port 9009
trades,symbol=AAPL price=175.42,volume=100 1721984400000000000
trades,symbol=AAPL price=175.43,volume=200 1721984400500000000
trades,symbol=MSFT price=413.55,volume=50  1721984401000000000

-- 3. SAMPLE BY query — 1-minute OHLC bars for the last hour, per symbol
SELECT
    ts,
    symbol,
    first(price) AS open,
    max(price)   AS high,
    min(price)   AS low,
    last(price)  AS close,
    sum(volume)  AS volume
FROM   trades
WHERE  ts > dateadd('h', -1, now())
SAMPLE BY 1m
FILL(PREV);

-- 4. Query for a single symbol's OHLC over the last day
SELECT
    ts,
    first(price) AS open,
    max(price)   AS high,
    min(price)   AS low,
    last(price)  AS close,
    sum(volume)  AS volume
FROM   trades
WHERE  symbol = 'AAPL'
  AND  ts > dateadd('d', -1, now())
SAMPLE BY 5m
FILL(PREV);

Step-by-step explanation.

1. QuestDB's TIMESTAMP(ts) PARTITION BY DAY declares ts as the time column and partitions storage by day. Each day gets its own partition file; queries with WHERE ts > now() - '1d' scan only the last one or two partitions. The WAL suffix enables write-ahead-logging for out-of-order ingest (financial tick data is rarely perfectly ordered).
2. SYMBOL is QuestDB's dictionary-encoded string type. Because tick data has a small set of unique symbols (~2000) that repeat billions of times, dictionary encoding compresses each symbol to a small integer on disk. This is 100x-1000x smaller than storing them as VARCHAR.
3. Ingest via InfluxDB Line Protocol on TCP port 9009 is the fastest path — QuestDB benchmarks at ~4M rows/sec via ILP on modest hardware. The wire format is one line per sample: table,tag1=v1 field1=v1,field2=v2 nanosecond_timestamp. Order-of-magnitude faster than SQL INSERT.
4. SAMPLE BY 1m buckets rows into 1-minute intervals aligned on the minute boundary. FILL(PREV) fills empty buckets with the previous bucket's value — critical for financial charts, where "no trade for 3 seconds" should not create a hole. Other fill modes: LINEAR (interpolation), NULL (leave as null), <value> (constant fill).
5. first(price) and last(price) compute open and close from the ordered samples within each bucket. This is the OHLC primitive built into QuestDB — the equivalent in ANSI SQL requires window functions with FIRST_VALUE / LAST_VALUE and is much less pleasant to write.

Output.

ts	symbol	open	high	low	close	volume
2026-07-26 09:00:00	AAPL	175.42	175.55	175.30	175.48	120000
2026-07-26 09:01:00	AAPL	175.48	175.60	175.40	175.52	95000
2026-07-26 09:02:00	AAPL	175.52	175.62	175.48	175.58	110000
2026-07-26 09:03:00	AAPL	175.58	175.58	175.58	175.58	0 (FILL(PREV))

Rule of thumb. For financial tick data or any high-frequency numeric stream, SAMPLE BY N + FILL(mode) is the QuestDB idiom that turns hundreds of lines of ANSI SQL window-function gymnastics into a 6-line query. ILP ingest is 10-100x faster than SQL INSERT; use it for anything over 10K rows/sec.

Worked example — InfluxDB Flux downsampling task

Detailed explanation. InfluxDB 2.x uses the Flux language for both queries and scheduled tasks. A common TSDB pattern is a scheduled downsampling task that reads raw data from a "hot" bucket and writes 1-minute aggregates to a "warm" bucket with different retention. Walk through the Flux script.

• Hot bucket. metrics_raw with 7-day retention (raw 10-second samples).
• Warm bucket. metrics_1m with 90-day retention (1-minute aggregates).
• Task. Runs every 5 minutes; aggregates the trailing 10 minutes.

Question. Write the Flux downsampling task and the retention configuration.

Input.

Component	Value
Source bucket	metrics_raw (7d retention)
Sink bucket	metrics_1m (90d retention)
Aggregation	1-minute mean + p95
Task schedule	every 5m
Task window	trailing 10m

Code.

// InfluxDB Flux downsampling task
option task = {
    name: "downsample_metrics_1m",
    every: 5m,
    offset: 30s,
}

from(bucket: "metrics_raw")
    |> range(start: -10m)
    |> filter(fn: (r) => r._measurement == "cpu" or r._measurement == "memory")
    |> aggregateWindow(every: 1m, fn: mean, createEmpty: false)
    |> set(key: "_field", value: "mean_1m")
    |> to(bucket: "metrics_1m", org: "prod")

// Companion task — p95 aggregation
option task = {
    name: "downsample_metrics_p95_1m",
    every: 5m,
    offset: 45s,
}

from(bucket: "metrics_raw")
    |> range(start: -10m)
    |> filter(fn: (r) => r._measurement == "cpu")
    |> aggregateWindow(
        every: 1m,
        fn: (column, tables=<-) => tables |> quantile(q: 0.95, column: column),
        createEmpty: false)
    |> set(key: "_field", value: "p95_1m")
    |> to(bucket: "metrics_1m", org: "prod")

# Bucket retention configuration (via influx CLI)
buckets:
  - name: metrics_raw
    retention: 7d
    shard_group_duration: 1h    # 168 shards for 7d retention

  - name: metrics_1m
    retention: 90d
    shard_group_duration: 1d    # 90 shards for 90d retention

Step-by-step explanation.

1. The Flux option task block declares this script as a scheduled task named downsample_metrics_1m. every: 5m means InfluxDB runs it every 5 minutes; offset: 30s staggers execution to avoid contention with other tasks at the top-of-minute boundary.
2. from(bucket: "metrics_raw") |> range(start: -10m) reads the trailing 10 minutes from the hot bucket. The 10-minute window (vs the 5-minute schedule) is a deliberate overlap: if a task run fails, the next run re-processes the missed window, so re-runs are idempotent when combined with to() which upserts on (measurement, tag_set, ts).
3. aggregateWindow(every: 1m, fn: mean) buckets rows into 1-minute intervals and computes the mean within each bucket. createEmpty: false skips empty buckets (no output for time ranges with no source data) — different behaviour from FILL() which would emit interpolated values.
4. to(bucket: "metrics_1m", org: "prod") writes the aggregated rows to the warm bucket. Because the warm bucket has 90-day retention (vs 7 days for raw), the downsampled data outlives its source. This is the tiered-retention story: fine-grained recent + coarse-grained long-term.
5. The companion task computes p95 quantiles using a custom aggregation function. quantile(q: 0.95, column: column) is a Flux-native quantile that operates per-bucket. Splitting mean and p95 into two tasks (rather than one task with two aggregations) is a Flux idiom that keeps each task's compute cost bounded.

Output.

Bucket	Retention	Granularity	Storage estimate (per metric)
metrics_raw	7 days	10-second samples	~604K rows / week
metrics_1m	90 days	1-minute samples	~130K rows / 90 days
Compression ratio (raw → 1m)	—	6× (10s → 1m)	4.6× smaller in metrics_1m
Task frequency	every 5m	trailing 10m window	idempotent overlap

Rule of thumb. For any InfluxDB deployment that has to answer questions across multiple time horizons, use tiered buckets (raw + downsampled at 1-minute + downsampled at 1-hour) with different retentions. Flux tasks are the pipeline; the retention config is the enforcement. The Flux language is unusual enough that new engineers struggle with it — invest in a Flux linter and code review on the tasks.

Senior interview question on TSDB primitives

A senior interviewer might ask: "Design a metrics pipeline for a Kubernetes cluster with 5000 pods, each emitting 50 metrics every 15 seconds. Requirements: 30-day hot at raw resolution for debugging, 2-year archive at 5-minute resolution for capacity planning, sub-second alerting on the hot tier, and a Grafana dashboard for 50 SREs. Walk me through TSDB choice, hypertable / bucket configuration, continuous aggregate / downsampling, retention policies, and the alerting story."

Solution Using TimescaleDB hypertables + tiered continuous aggregates + retention policies + alerting on the raw table

-- 1. Hypertable — raw 15-second metric samples
CREATE TABLE metrics (
    ts             TIMESTAMPTZ NOT NULL,
    metric_name    TEXT NOT NULL,
    pod            TEXT NOT NULL,
    namespace      TEXT NOT NULL,
    node           TEXT NOT NULL,
    value          DOUBLE PRECISION NOT NULL
);

SELECT create_hypertable('metrics', 'ts',
    chunk_time_interval => INTERVAL '4 hours');

CREATE INDEX ix_metrics_lookup ON metrics (metric_name, namespace, ts DESC);
CREATE INDEX ix_metrics_pod    ON metrics (pod, ts DESC);

-- 2. Compression policy: compress chunks older than 12h (10-20x)
ALTER TABLE metrics SET (
    timescaledb.compress,
    timescaledb.compress_segmentby = 'metric_name, namespace',
    timescaledb.compress_orderby   = 'pod, ts DESC'
);
SELECT add_compression_policy('metrics', INTERVAL '12 hours');

-- 3. Retention: raw metrics dropped after 30 days
SELECT add_retention_policy('metrics', INTERVAL '30 days');

-- 4. Tier 1 continuous aggregate — 1-minute buckets, feeds the SRE dashboard
CREATE MATERIALIZED VIEW metrics_1m
WITH (timescaledb.continuous) AS
SELECT time_bucket('1 minute', ts) AS bucket,
       metric_name,
       namespace,
       pod,
       avg(value)   AS avg_v,
       max(value)   AS max_v,
       percentile_cont(0.95) WITHIN GROUP (ORDER BY value) AS p95_v
FROM   metrics
GROUP  BY bucket, metric_name, namespace, pod;

SELECT add_continuous_aggregate_policy('metrics_1m',
    start_offset      => INTERVAL '2 hours',
    end_offset        => INTERVAL '1 minute',
    schedule_interval => INTERVAL '1 minute');

SELECT add_retention_policy('metrics_1m', INTERVAL '90 days');

-- 5. Tier 2 continuous aggregate — 5-minute buckets, feeds the 2-year archive
CREATE MATERIALIZED VIEW metrics_5m
WITH (timescaledb.continuous) AS
SELECT time_bucket('5 minutes', bucket) AS bucket_5m,
       metric_name,
       namespace,
       avg(avg_v)  AS avg_v,
       max(max_v)  AS max_v,
       avg(p95_v)  AS p95_v_approx
FROM   metrics_1m
GROUP  BY bucket_5m, metric_name, namespace;

SELECT add_continuous_aggregate_policy('metrics_5m',
    start_offset      => INTERVAL '1 day',
    end_offset        => INTERVAL '5 minutes',
    schedule_interval => INTERVAL '5 minutes');

SELECT add_retention_policy('metrics_5m', INTERVAL '2 years');

-- 6. Alerting query — hits the raw table for freshness; sub-second on 4-hour chunk
SELECT pod,
       max(value) AS peak_cpu,
       avg(value) AS avg_cpu
FROM   metrics
WHERE  metric_name = 'container_cpu_usage_percent'
  AND  namespace   = 'production'
  AND  ts          > now() - INTERVAL '5 minutes'
GROUP  BY pod
HAVING max(value) > 0.90;

Step-by-step trace.

Layer	Config	Result
Raw hypertable	4-hour chunks, compressed after 12h	~2 TB raw → ~150 GB compressed for 30d
Tier 1 continuous aggregate	1-min buckets, refresh every 1m	~40 GB for 90 days; sub-second dashboard
Tier 2 continuous aggregate	5-min buckets from tier 1	~5 GB for 2 years; capacity-planning queries
Retention	tiered: 30d raw, 90d 1m, 2y 5m	automated; O(1) drop per chunk
Alerting	raw table WHERE ts > now() - 5m	hits latest chunk only; < 100 ms
SRE dashboard	reads from metrics_1m	1440 rows/day/pod; < 50 ms

After deployment, the ingest layer sustains 5000 pods × 50 metrics × 4 samples/min = ~17K rows/sec into the raw table. The compression policy converts chunks older than 12 hours to a columnar-compressed form; the retention policy drops chunks older than 30 days. The 1-minute continuous aggregate refreshes every minute for the trailing 2 hours; the 5-minute cascade aggregates on top for 2-year archival. SRE dashboards query metrics_1m in under 50 ms; capacity planning queries metrics_5m in under 200 ms; alerting queries hit the raw table's most recent chunk with sub-100ms latency.

Output:

Metric	Value
Raw ingest throughput	~17K rows/sec sustained
Raw hypertable footprint (30d compressed)	~150 GB
Tier 1 (1-min) footprint (90d)	~40 GB
Tier 2 (5-min) footprint (2y)	~5 GB
Dashboard p95 latency (last 24h)	~50 ms from metrics_1m
Alerting p95 latency (last 5 min)	~80 ms from raw
Capacity-plan p95 latency (last 1y)	~200 ms from metrics_5m

Why this works — concept by concept:

• Hypertable + chunk-exclusion — the hypertable partitions rows into 4-hour chunks; every query with a WHERE ts BETWEEN ... clause skips chunks outside the range. A "last 5 minutes" alerting query scans one chunk (~250 MB compressed); a "last 24 hours" dashboard query scans 6 chunks. This is O(chunks touched), not O(rows in table).
• compress_segmentby on labels — grouping compressed rows by metric_name, namespace means a query filtering on those labels decompresses only the relevant groups. On mixed-metric queries this is 10x-20x faster than compressing everything together.
• Cascading continuous aggregates — the 5-minute aggregate is built on top of the 1-minute aggregate, not on top of raw. This makes the tier-2 refresh cheap (aggregating 5 buckets per iteration, not 300 raw rows per bucket) and lets the tier-2 archive outlive the raw table by nearly 2 years.
• Tiered retention — 30 days raw + 90 days 1-min + 2 years 5-min. Each retention policy is one line; each drops entire chunks in O(1). Compared to a general OLAP TTL that only supports single-tier retention, this is the differentiator that lets the workload keep 2 years of data at 1% of the storage cost of keeping it all raw.
• Cost — the whole pipeline is 6 SQL statements plus 3 policy declarations. Compared to building the equivalent on ClickHouse (custom Airflow downsampling DAG + manual TTL management + no per-chunk compression tuning), this is ~10x less code and ~10x less operational risk. Net O(1) per policy invocation for retention, O(delta) for aggregate refresh. The eliminated cost is the custom ETL that a general OLAP engine would require; the trade is that TimescaleDB peaks at ~1M rows/sec per node (vs 10M+ for ClickHouse), so at extreme write scale the balance flips.

SQL
Topic — sql
SQL continuous-aggregate and rollup problems

Practice →

Aggregation Topic — aggregation Aggregation problems on tiered downsampling

Practice →

---

4. OLAP engine strengths — dimensional slice-and-dice + concurrency

Arbitrary GROUP BY across many dimensions, big joins, dashboard concurrency, and BI-tool integration — the workload TSDBs cannot serve well

The mental model in one line: OLAP engines (ClickHouse, Druid, Pinot, StarRocks, Trino, Redshift) earn their category by shipping dimensional-analytics primitives — MPP horizontal scale, materialized-view pre-aggregation, star-tree / bitmap / min-max indexes, HyperLogLog and T-Digest approximate aggregations, star-schema join optimization, and high-concurrency dashboard serving that TSDBs cannot match without either massive over-provisioning or a caching layer that reinvents everything the OLAP engine ships out of the box. Every senior DE has been asked to serve a 500-concurrent-user dashboard from a TSDB and learned the hard way why OLAP is a separate category.

The five OLAP superpowers.

• Arbitrary dimensional aggregation. A GROUP BY across 10 dimensions with 1000 combinations each is what MergeTree, Druid segments, and Pinot star-trees are designed to answer in sub-second. TSDBs can compute the same query but have to scan (partitions × dimensions) rather than sorted-storage-co-located rows. ClickHouse in particular has an ORDER BY primary-key that co-locates rows by leading dimensions; a query filtering + grouping on those dimensions reads only the relevant range.
• Star-schema joins at scale. OLAP engines optimize joins across a large fact table and multiple dimension tables — the classic star-schema pattern. ClickHouse's join_algorithm = 'grace_hash' handles fact-to-dim joins with billions of rows; Druid's join semantics are limited but sufficient for the "lookup dim" pattern; StarRocks has full MPP joins. TSDBs generally have poor join performance because the storage layout is optimized for time, not for join keys.
• High-concurrency dashboard serving. Druid and Pinot were purpose-built for user-facing analytics — 1000+ concurrent users querying pre-aggregated materialized views ("segments" in Druid, "segments + star-tree index" in Pinot). Each query is served from a small hot-cache slice; MPP scale-out handles the concurrency. ClickHouse serves 100+ concurrent users comfortably; TSDBs typically saturate connection pools well before that.
• Approximate functions. HyperLogLog (uniq() / HLLSKETCH / APPROX_COUNT_DISTINCT) for cardinality estimation; T-Digest for quantiles; Bloom filters for existence checks. These trade a tunable error rate for a massive latency win and memory savings. OLAP engines ship these as native functions; TSDBs generally don't.
• BI-tool integration. Superset, Metabase, Tableau, Looker, and Power BI all have first-class connectors for ClickHouse, Druid, Pinot, Snowflake, BigQuery, and Redshift. Grafana is the TSDB frontend; the BI tools are the OLAP frontend. If your workload has to plug into an existing BI stack, category is pre-decided.

The MergeTree family — ClickHouse's storage superpower.

• MergeTree. The base engine; sorted-storage by ORDER BY (col1, col2, ...); primary-key index at 8192-row granularity (index_granularity); columnar storage with per-column compression.
• ReplacingMergeTree. Deduplicates by ORDER BY key on background merges — useful for CDC-fed tables.
• SummingMergeTree. Sums numeric columns by ORDER BY key on merges — the OLAP equivalent of a pre-aggregated fact table.
• AggregatingMergeTree. Stores aggregate function state (avg-state, quantile-state) and combines them incrementally — the ClickHouse continuous-aggregate equivalent.
• CollapsingMergeTree. Handles paired positive/negative rows for the "cancel and re-issue" pattern — Trader-workflow-friendly.

Druid segments — the concurrency superpower.

• Segment. A time-chunked, columnar, immutable slab of data. Each segment covers a fixed time interval (usually 1 hour or 1 day) and is stored on historical nodes.
• Query broker. Routes queries to the relevant historical nodes based on time interval; parallelizes across segments; merges results.
• Rollup at ingest. Druid can pre-aggregate on ingest — for a metrics workload, "count events per hour per dimension" is computed once at ingest and stored as a smaller "rolled-up" segment.
• Historical vs middle-manager nodes. Historical nodes serve queries from immutable segments (fast, cacheable); middle-manager nodes handle real-time ingest and hand off to historicals after N minutes.

Pinot star-tree indexes — the user-facing analytics superpower.

• Star-tree. An index that pre-aggregates over all combinations of a subset of dimensions. A star-tree on (product, region, day) pre-computes the sum/avg/count for every product+region+day combination; a query filtering on any subset reads only the relevant pre-computed node.
• When it helps. Dashboards where the filter and GROUP BY columns are known in advance (embedded product analytics, user-facing metric drilldowns).
• Cost. Extra storage (2-5x the raw), longer ingest time, and star-tree schema must be defined up front. For workloads that fit the pattern, sub-100ms latency at 10K concurrent users.

Common interview probes on OLAP strengths.

• "What's a MergeTree ORDER BY key?" — the sorted-storage key that co-locates rows for range queries.
• "How does Druid handle sub-second dashboard queries at 1000 users?" — time-chunked segments + rollup at ingest + query broker parallelism.
• "What's a star-tree index?" — Pinot's pre-aggregated multi-dimensional index; sub-100ms latency at concurrency.
• "What's HyperLogLog?" — approximate cardinality estimator; O(1) memory per unique-count query.
• "How do OLAP engines handle joins?" — big-fact-to-small-dim broadcast; big-fact-to-big-fact hash / grace-hash / MPP shuffle joins.

Worked example — ClickHouse MergeTree for a product-analytics fact table

Detailed explanation. The canonical OLAP pattern: a fact table (product_events) with 30 dimensions and one measure (revenue); a SummingMergeTree materialized view pre-aggregates the hot dashboard slice; the dashboard query hits the materialized view in sub-second. Walk through the entire pipeline.

• Fact table. product_events(ts, user_id, product_id, plan, region, channel, device, campaign, revenue, ...).
• Materialized view. Pre-aggregates sum(revenue), count() by day × product × region × channel.
• Dashboard query. Reads from the materialized view; sub-second at 500 concurrent users.

Question. Write the MergeTree DDL, the materialized view, and the dashboard query.

Input.

Object	Purpose
product_events	raw fact table (ReplicatedMergeTree in production)
product_daily_agg	SummingMergeTree materialized view
product_daily_agg_mv	trigger view that maintains the aggregate on insert
dashboard query	reads from product_daily_agg

Code.

-- 1. Raw fact table — sorted by hot dashboard filter columns
CREATE TABLE product_events (
    ts         DateTime,
    event_id   UInt64,
    user_id    UInt64,
    product_id LowCardinality(String),
    plan       LowCardinality(String),
    region     LowCardinality(String),
    channel    LowCardinality(String),
    device     LowCardinality(String),
    campaign   LowCardinality(String),
    revenue    Decimal(18, 4),
    -- 22 more dimensions elided
    PROJECTION proj_by_channel (
        SELECT * ORDER BY (channel, toDate(ts))
    )
) ENGINE = MergeTree
PARTITION BY toYYYYMM(ts)
ORDER BY (toDate(ts), product_id, plan, region)
TTL ts + INTERVAL 90 DAY
SETTINGS index_granularity = 8192;

-- 2. Pre-aggregated materialized view — SummingMergeTree
CREATE TABLE product_daily_agg (
    day        Date,
    product_id LowCardinality(String),
    plan       LowCardinality(String),
    region     LowCardinality(String),
    channel    LowCardinality(String),
    revenue    Decimal(18, 4),
    events     UInt64
) ENGINE = SummingMergeTree
ORDER BY (day, product_id, plan, region, channel);

-- 3. Trigger view: incrementally maintains the aggregate as new events arrive
CREATE MATERIALIZED VIEW product_daily_agg_mv TO product_daily_agg AS
SELECT
    toDate(ts)   AS day,
    product_id, plan, region, channel,
    sum(revenue) AS revenue,
    count()      AS events
FROM   product_events
GROUP  BY day, product_id, plan, region, channel;

-- 4. Dashboard query — sub-second even at 500 concurrent users
SELECT
    day,
    plan,
    region,
    sum(revenue) AS revenue,
    sum(events)  AS events
FROM   product_daily_agg
WHERE  day >= today() - 90
  AND  channel = 'organic'
GROUP  BY day, plan, region
ORDER  BY day, plan, region;

Step-by-step explanation.

1. The raw product_events table uses MergeTree with ORDER BY (toDate(ts), product_id, plan, region). This means rows are physically sorted on disk by day → product → plan → region. Queries filtering on those columns read only the relevant sorted range, not the whole table. LowCardinality(String) dictionary-encodes the low-cardinality dimensions (10-1000 unique values each), shrinking storage 10x.
2. PARTITION BY toYYYYMM(ts) creates one part per month — coarser than TSDB chunks (which are typically hourly or daily). Partitioning at the month level keeps merge overhead low; ClickHouse's part-merging is O(parts merged) and monthly parts strike a good balance.
3. The SummingMergeTree materialized view pre-aggregates the "revenue and event count by day × product × plan × region × channel" query. SummingMergeTree sums numeric columns by the ORDER BY key on background merges — so the aggregate table stays small (one row per combination per day) even as raw events accumulate.
4. The MATERIALIZED VIEW ... TO product_daily_agg trigger view runs on every INSERT into product_events. It computes the per-batch aggregate and inserts into product_daily_agg; SummingMergeTree merges duplicate keys automatically. This is the ClickHouse pattern for incrementally-maintained pre-aggregations — no scheduled refresh, no window management, just insert-time processing.
5. The dashboard query hits product_daily_agg directly. For "last 90 days revenue by day × plan × region for the organic channel," the aggregate returns ~90 × 5 × 20 = 9000 rows in under 50 ms. At 500 concurrent users, ClickHouse's shared-cache and multi-threaded execution handle the concurrency comfortably.

Output.

Query	Rows scanned	Latency (p95)
Dashboard: last 90d revenue by day × plan × region (organic)	~9K from aggregate	~50 ms
Same query on raw fact table (90d × 500K events/day)	~45M rows	~1500 ms
Aggregate maintenance (per 10K-event INSERT batch)	~10K → ~200 aggregate rows	~50 ms overhead
Concurrency at 500 users	scales linearly on 8-core node	p95 stays flat

Rule of thumb. For any OLAP workload with a fixed set of dashboard queries, define a SummingMergeTree or AggregatingMergeTree materialized view that pre-aggregates the hot slice, and let the trigger view maintain it incrementally. The dashboard query reads from the aggregate table (small, fast); the raw table is retained for ad-hoc analysis. This is the ClickHouse equivalent of Druid's rollup-at-ingest.

Worked example — Apache Druid rollup segments for user-facing analytics

Detailed explanation. Druid's strength is user-facing analytics at extreme concurrency — 10K concurrent users hitting a dashboard is normal. The pattern is rollup-at-ingest: pre-aggregate the raw events into segments at ingest time, so the historical nodes only have to serve pre-aggregated data. Walk through a Druid ingestion spec and query.

• Ingest source. Kafka topic product_events.
• Rollup grain. 1-minute buckets, grouped by (product, region, channel).
• Retention. 90 days on historical nodes.
• Query. Aggregate rollup segments across time and dimensions.

Question. Write the Druid ingestion spec (Kafka-indexing service) and a native Druid query.

Input.

Component	Value
Kafka topic	product_events
Rollup grain	1 minute
Rollup dims	product, region, channel
Metrics	count, sum(revenue), thetaSketch(user_id)
Segment granularity	1 hour

Code.

// Druid Kafka indexing spec — rollup at ingest
{
  "type": "kafka",
  "spec": {
    "dataSchema": {
      "dataSource": "product_events_rolled",
      "timestampSpec": {"column": "ts", "format": "iso"},
      "dimensionsSpec": {
        "dimensions": [
          {"name": "product", "type": "string"},
          {"name": "region",  "type": "string"},
          {"name": "channel", "type": "string"}
        ]
      },
      "metricsSpec": [
        {"name": "count",         "type": "count"},
        {"name": "revenue_sum",   "type": "doubleSum",   "fieldName": "revenue"},
        {"name": "unique_users",  "type": "thetaSketch", "fieldName": "user_id"}
      ],
      "granularitySpec": {
        "type": "uniform",
        "segmentGranularity": "HOUR",
        "queryGranularity":   "MINUTE",
        "rollup": true
      }
    },
    "ioConfig": {
      "topic": "product_events",
      "consumerProperties": {"bootstrap.servers": "kafka:9092"},
      "taskCount": 4,
      "replicas": 2
    }
  }
}

// Native Druid query — "last 24 hours revenue + unique users by product + region"
{
  "queryType": "groupBy",
  "dataSource": "product_events_rolled",
  "intervals": ["2026-07-25T00:00Z/2026-07-26T00:00Z"],
  "granularity": {"type": "period", "period": "PT1H"},
  "dimensions": ["product", "region"],
  "aggregations": [
    {"type": "doubleSum",         "name": "revenue",       "fieldName": "revenue_sum"},
    {"type": "longSum",           "name": "event_count",   "fieldName": "count"},
    {"type": "thetaSketch",       "name": "user_sketch",   "fieldName": "unique_users"}
  ],
  "postAggregations": [
    {"type": "thetaSketchEstimate",
     "name": "unique_users_est",
     "field": {"type": "fieldAccess", "fieldName": "user_sketch"}}
  ]
}

Step-by-step explanation.

1. The Druid Kafka-indexing spec ingests from product_events topic. The granularitySpec.rollup: true + queryGranularity: MINUTE tells Druid to pre-aggregate incoming events into 1-minute buckets grouped by all dimensions. If 10,000 events land in the same minute for the same (product, region, channel) triple, they collapse into 1 row with count=10000 and revenue_sum=<sum>.
2. The metricsSpec defines the aggregations Druid maintains per rollup row. count counts input rows; doubleSum sums a numeric field; thetaSketch maintains a probabilistic sketch of unique user_ids that supports approximate distinct-count estimation with O(1) memory per rollup row. The Theta Sketch is a competitor to HyperLogLog with better set-operation semantics.
3. segmentGranularity: HOUR means Druid finalizes segments on hour boundaries. Each hour's data becomes one immutable, columnar-compressed segment on historical nodes. taskCount: 4 + replicas: 2 gives 8 concurrent Kafka reader tasks with fault tolerance.
4. The native groupBy query specifies the time interval, the grouping dimensions, and the aggregations. Druid's query broker routes the query to the historical nodes holding segments in the interval, parallelizes across segments, and merges results. postAggregations runs the thetaSketchEstimate function on the aggregated sketch to produce a scalar approximate unique count.
5. Because the segments are pre-aggregated, the query scans one row per (minute × product × region × channel) combination — a few million rows at most for 24 hours × 100 products × 20 regions × 10 channels. At sub-100ms per query, Druid can serve thousands of concurrent users on a modest cluster.

Output.

Aspect	Value
Segment granularity	1 hour
Query granularity (rollup)	1 minute
Ingest rate	~200K events/sec (Kafka lag < 1s)
Rollup ratio	~20x (10K events → 500 rolled rows per minute)
Historical segment size	~200 MB compressed per hour
Query p95 (last 24h groupBy)	~80 ms
Concurrent users supported	~10K on 6-node historical cluster

Rule of thumb. For any workload that will serve 1000+ concurrent dashboard users, use Druid with rollup-at-ingest. Define the rollup dimensions to match the dashboard's GROUP BY columns; use Theta Sketches or HyperLogLog for distinct counts. Never turn off rollup on a Druid ingest that has more than 10x compression potential.

Worked example — Apache Pinot star-tree index for embedded analytics

Detailed explanation. Pinot's star-tree index is the OLAP primitive that hits sub-100ms at 10K concurrent users on truly interactive dashboards — the kind you embed in a SaaS product where every logged-in user drilling into their own analytics is a separate query. Walk through the Pinot table config and the query.

• Data. page_views(ts, user_id, page, browser, country, device, campaign).
• Star-tree dims. (page, browser, country, device).
• Star-tree metric. count(*), sum(dwell_ms).
• Query. "Page views by country for a specific browser last 30 days."

Question. Write the Pinot table config with a star-tree index and a Pinot SQL query.

Input.

Component	Value
Table	page_views
Star-tree dims	page, browser, country, device
Star-tree metrics	count, sum(dwell_ms)
Query filter	browser + time range
Concurrency	10K concurrent embedded users

Code.

// Pinot table config — REALTIME with star-tree index
{
  "tableName": "page_views_REALTIME",
  "tableType": "REALTIME",
  "segmentsConfig": {
    "timeColumnName": "ts",
    "timeType":       "MILLISECONDS",
    "retentionTimeUnit": "DAYS",
    "retentionTimeValue": "90"
  },
  "tableIndexConfig": {
    "sortedColumn": ["ts"],
    "invertedIndexColumns": ["page", "browser", "country", "device"],
    "starTreeIndexConfigs": [
      {
        "dimensionsSplitOrder": ["browser", "country", "page", "device"],
        "functionColumnPairs": [
          "COUNT__*",
          "SUM__dwell_ms"
        ],
        "maxLeafRecords": 10000
      }
    ]
  },
  "ingestionConfig": {
    "streamIngestionConfig": {
      "streamConfigMaps": [{
        "streamType": "kafka",
        "stream.kafka.topic.name": "page_views",
        "stream.kafka.consumer.type": "lowlevel"
      }]
    }
  }
}

-- Pinot SQL query — page views by country for a specific browser
SELECT country,
       COUNT(*)      AS views,
       SUM(dwell_ms) AS dwell_total
FROM   page_views_REALTIME
WHERE  browser = 'safari'
  AND  ts >= 1721260800000     -- 30 days ago in ms
GROUP  BY country
ORDER  BY views DESC
LIMIT  20;

Step-by-step explanation.

1. The Pinot table config declares a REALTIME table with 90-day retention. sortedColumn: ["ts"] sorts rows within segments by timestamp, enabling fast time-range scans; invertedIndexColumns gives Pinot per-column inverted indexes for the dimensions frequently filtered on.
2. The star-tree index config is the key primitive. dimensionsSplitOrder: [browser, country, page, device] builds a tree that splits nodes by browser first, then country, then page, then device. functionColumnPairs: [COUNT__*, SUM__dwell_ms] defines the aggregations pre-computed at each tree node.
3. maxLeafRecords: 10000 bounds the size of each leaf node — a leaf with more than 10K matching raw rows triggers further splitting. This is the star-tree's efficiency knob: smaller leaves mean more storage but faster queries.
4. The query filters on browser = 'safari' and ts >= 30-days-ago, then groups by country. Because the star-tree splits on browser first, Pinot navigates directly to the safari sub-tree, then aggregates across country sub-nodes. The query touches ~200 pre-aggregated rows regardless of how many raw rows exist.
5. At 10K concurrent users each running a similar query with different filter values, the star-tree cache warmth stays high (all queries hit the same tree). Pinot's segment-per-hour + tiered storage means the tree indexes are compact enough to fit in memory across the historical nodes.

Output.

Aspect	Value
Star-tree dims	4 (browser × country × page × device)
Star-tree pre-aggregated rows	~50K per segment (vs 10M raw)
Query p95 (10K concurrent)	~50 ms
Storage overhead	~30% (star-tree + inverted indexes)
Ingest lag	< 5 seconds from Kafka

Rule of thumb. For embedded analytics workloads where users drill into their own data at high concurrency, Pinot with a star-tree index is the OLAP engine of choice. Define the star-tree dimensions to match the top 4-6 filter columns the dashboards will use; keep maxLeafRecords around 10K for a good latency-vs-storage tradeoff. Never expose a raw fact table directly to a 10K-user dashboard.

Senior interview question on OLAP dimensional analytics

A senior interviewer might ask: "Design an analytics platform for a SaaS product that serves 5000 concurrent tenants, each drilling into their own event data (500K events/day per tenant, 200 dimensions available for GROUP BY, sub-second p95 required). Walk me through OLAP engine choice, table layout, pre-aggregation strategy, and how you'd guarantee tenant isolation."

Solution Using Pinot with per-tenant partitioning + star-tree indexes on the top filter dims + tenant-scoped queries

// 1. Pinot table config — hybrid REALTIME + OFFLINE, tenant-partitioned
{
  "tableName": "tenant_events_REALTIME",
  "tableType": "REALTIME",
  "segmentsConfig": {
    "timeColumnName": "ts",
    "timeType": "MILLISECONDS",
    "retentionTimeUnit": "DAYS",
    "retentionTimeValue": "7",
    "segmentPushType": "APPEND",
    "replication": "2"
  },
  "tableIndexConfig": {
    "sortedColumn": ["ts"],
    "invertedIndexColumns": ["tenant_id", "event_type", "country", "platform", "campaign"],
    "starTreeIndexConfigs": [
      {
        "dimensionsSplitOrder": ["tenant_id", "event_type", "country", "platform"],
        "functionColumnPairs": ["COUNT__*", "SUM__revenue", "DISTINCTCOUNTHLL__user_id"],
        "maxLeafRecords": 10000
      }
    ],
    "bloomFilterColumns": ["tenant_id"]
  },
  "routing": {
    "segmentPrunerTypes": ["partition"],
    "instanceSelectorType": "replicaGroup"
  },
  "instanceAssignmentConfigMap": {
    "REALTIME": {
      "partitionSelector": "FD_AWARE_INSTANCE_PARTITION_SELECTOR",
      "replicaGroupPartitionConfig": {
        "replicaGroupBased": true,
        "numReplicaGroups": 2,
        "numInstancesPerReplicaGroup": 6
      }
    }
  }
}

// 2. OFFLINE table for data older than 7 days (moved from REALTIME by segment relocator)
{
  "tableName": "tenant_events_OFFLINE",
  "tableType": "OFFLINE",
  "segmentsConfig": {
    "timeColumnName": "ts",
    "retentionTimeUnit": "DAYS",
    "retentionTimeValue": "90",
    "segmentPushType": "APPEND",
    "replication": "1"
  },
  "tableIndexConfig": {
    "sortedColumn": ["ts"],
    "invertedIndexColumns": ["tenant_id", "event_type"],
    "starTreeIndexConfigs": [
      {
        "dimensionsSplitOrder": ["tenant_id", "event_type", "country"],
        "functionColumnPairs": ["COUNT__*", "SUM__revenue"],
        "maxLeafRecords": 10000
      }
    ]
  }
}

-- 3. Query template — always scoped to a single tenant_id
SELECT country,
       platform,
       COUNT(*)              AS events,
       SUM(revenue)          AS revenue,
       DISTINCTCOUNTHLL(user_id) AS unique_users
FROM   tenant_events
WHERE  tenant_id  = 42                                -- MANDATORY tenant filter
  AND  event_type = 'purchase'
  AND  ts        >= 1721260800000
GROUP  BY country, platform
ORDER  BY revenue DESC
LIMIT  50;

# 4. Broker-side query guard — reject any query missing tenant_id
brokerQueryGuardConfig:
  requiredFilterColumns:
    - tenant_id
  denyQuery:
    - no_tenant_filter

Step-by-step trace.

Concern	Answer	Reasoning
Engine	Pinot	star-tree + 10K concurrent proven at Uber, LinkedIn scale
Retention	tiered: 7d REALTIME + 90d OFFLINE	hot data in RAM; cold data on disk with lighter indexes
Tenant isolation	tenant_id as leading star-tree dim + broker guard	tree navigation collapses to per-tenant sub-tree
Star-tree	(tenant_id, event_type, country, platform)	matches dashboard filter+GROUP BY pattern
Concurrency	5000 tenants x ~1 QPS avg	historical replicas absorb read load
Approximate distinct	DISTINCTCOUNTHLL(user_id)	O(1) memory per rollup row

After deployment, each tenant's dashboard hits Pinot with a mandatory tenant_id = X filter; the star-tree navigates to the tenant's sub-tree in one hop; the query aggregates a few hundred pre-aggregated rows and returns in ~40 ms. The broker query guard rejects any query missing tenant_id, preventing cross-tenant data leaks. At 5000 tenants × 1 QPS = 5000 sustained QPS, the 12-instance historical cluster stays under 40% CPU.

Output:

Metric	Value
p95 query latency	~40 ms per tenant query
Sustained QPS	~5000 (12-instance cluster)
Star-tree pre-aggregation ratio	~50× (10M raw → 200K rolled)
Storage footprint	~2 TB REALTIME + 20 TB OFFLINE
Broker query rejection rate	~2% (queries missing tenant_id)
DISTINCTCOUNTHLL error rate	~2% (default HLL precision)

Why this works — concept by concept:

• Pinot star-tree with tenant_id as leading dim — the star-tree's dimension-split-order determines which filter combinations get the fastest navigation. Putting tenant_id first means every per-tenant query navigates directly to a compact sub-tree; the rest of the star-tree stays untouched. This is the trick that lets one Pinot cluster serve 5000 tenants without cross-tenant scan pollution.
• Hybrid REALTIME + OFFLINE tables — REALTIME table serves the last 7 days from Kafka + hot RAM; OFFLINE table serves the older 90 days from disk with lighter indexes. Pinot's segment relocator moves segments from REALTIME to OFFLINE automatically. This is the analytics-tier equivalent of TSDB tiered retention.
• Broker-side query guard — the requiredFilterColumns: [tenant_id] config rejects any query that doesn't filter on tenant_id. This is defense-in-depth against the "buggy dashboard runs a cross-tenant query" incident that would otherwise leak data or saturate the cluster.
• DISTINCTCOUNTHLL for user counts — HyperLogLog stores O(1) memory per rollup row (a few KB per sketch) and estimates unique-count with 2-3% error. Compared to exact COUNT(DISTINCT user_id) which would require touching every raw row, this is 100-1000x cheaper at negligible accuracy cost.
• Cost — 12-instance historical cluster + Kafka connector + Pinot broker layer + segment relocator. Compared to serving the same workload from ClickHouse (would need per-tenant materialized views or careful ORDER BY tuning) or from a TSDB (would saturate at ~100 concurrent, not 5000), Pinot is the purpose-built answer for user-facing analytics at extreme concurrency. Net O(1) per query on the star-tree; the trade is Pinot's operational complexity (Zookeeper, Controller, Broker, Historical roles).

Design
Topic — design
Design problems on OLAP dimensional-analytics systems

Practice →

Aggregation Topic — aggregation Aggregation problems on multi-dim GROUP BY

Practice →

---

5. Decision matrix, hybrid patterns, interview signals

The 5-question decision tree, three canonical hybrid architectures, and what interviewers actually listen for

The mental model in one line: the TSDB-vs-OLAP category call collapses to a 5-question decision tree that any senior architect can walk in under 60 seconds — time-based reads? unbounded cardinality? multi-dim slice? joins? high concurrency? — and when the answers split across the two categories, the answer is a hybrid architecture (Prometheus + Druid, TimescaleDB + ClickHouse, InfluxDB + Pinot) that wires each engine to the workload it serves best, because in 2026 the honest answer at scale is almost always "both". Every senior interview probes this decision tree; every real-world architecture uses at least one hybrid.

The 5-question decision tree — the whiteboard artifact every senior architect memorises.

• Q1 — Are the primary reads time-window aggregations? SELECT ... GROUP BY time_bucket(...) or WHERE ts BETWEEN ... AND ... with no other GROUP BY column. Yes → TSDB territory (go to Q2). No → OLAP territory (go to Q3).
• Q2 — Is the identity cardinality unbounded? Per-container, per-user, per-device, per-sensor identity that grows without limit. Yes → TSDB (decision made). No → go to Q3 to check the analytical axes.
• Q3 — Do reads span 10+ dimensions in GROUP BY? Multi-dim slice-and-dice with arbitrary column combinations. Yes → OLAP (decision made). No → go to Q4.
• Q4 — Are there big fact-to-dim joins? Star-schema joins with fact tables in the millions-to-billions and dim tables in the thousands-to-millions. Yes → OLAP (decision made). No → go to Q5.
• Q5 — Is the concurrency 100+ users on the same dashboard? Sub-second p95 at 100+ concurrent readers. Yes → OLAP with pre-aggregates (Druid / Pinot). No → TSDB is fine for the remaining workload.

The three canonical hybrid architectures.

• Prometheus + Druid. Prometheus (or VictoriaMetrics) scrapes and serves the hot metrics tier (last N days, high-resolution, ops-team dashboards); Druid serves the analytical dashboards (weekly / monthly rollups, PM-team dashboards, capacity planning). A recording-rule pipeline pushes rolled-up metrics from Prometheus to Druid every N minutes. Ops uses Grafana → Prometheus; analytics uses Superset → Druid. Two engines, one data pipeline.
• TimescaleDB + ClickHouse. TimescaleDB ingests raw high-throughput time-series (100K+ rows/sec), stores 30-90 days at raw resolution with compression, downsamples via continuous aggregates. A scheduled ETL joins the downsampled TimescaleDB data with dimension tables and inserts into ClickHouse for BI-tool serving (Superset / Metabase / Tableau). Time-series stays in TimescaleDB; the analytical view lives in ClickHouse.
• InfluxDB + Pinot. InfluxDB handles the highest-write-throughput ingest (IoT, financial ticks). A Flux task downsamples to 1-minute buckets and pushes to Kafka. A Pinot REALTIME table consumes the Kafka topic with rollup-at-ingest. User-facing dashboards (embedded in a SaaS product) query Pinot with per-tenant star-tree indexes at 10K concurrent users.

The six-engine pick-per-workload cheat sheet.

• TimescaleDB. SQL-native, Postgres-compatible, continuous aggregates. Pick when your team already knows Postgres and the workload is time-series-heavy but not extreme-throughput.
• InfluxDB. Metrics-DSL (Flux), TSM storage engine, downsampling tasks built in. Pick for pure metrics workloads that don't need SQL joins.
• QuestDB. Fastest ingest (~4M rows/sec ILP), SQL surface, SAMPLE BY and FILL(). Pick for financial ticks or high-frequency numeric streams.
• ClickHouse. MergeTree family, materialized views, MPP scale, mature ecosystem. Pick for batch analytics + interactive analytics + BI-tool serving.
• Apache Druid. Rollup-at-ingest, time-chunked segments, high concurrency. Pick for internal analytics dashboards at 100-1000 concurrent users where time is the dominant filter.
• Apache Pinot. Star-tree indexes, sub-100ms p95 at 10K concurrent users. Pick for user-facing embedded analytics where each user drills into their own data.

The migration-cost reality.

• TSDB → TSDB. ~1 sprint per aggregate table (Timescale ↔ Influx ↔ QuestDB). Query rewrites are the main cost; storage layouts are similar.
• OLAP → OLAP. ~2 sprints per fact table (ClickHouse ↔ Druid ↔ Pinot). Ingest pipeline changes are the main cost; query rewrites are moderate.
• TSDB → OLAP. ~1-2 quarters. Ingest pipeline, downsampling pipeline, retention story, dashboard rewrites all change. The category migration is genuinely expensive.
• OLAP → TSDB. Rarer but similar cost. Usually happens when a workload's cardinality shape shifts underneath the OLAP engine and the query latency degrades unrecoverably.

Common senior interview probes on the decision matrix.

• "Walk me through your decision tree for picking between TSDB and OLAP." — expect a 5-question tree in under 60 seconds.
• "When would you pick a hybrid architecture over one engine?" — expect a named pattern (Prometheus + Druid, etc.).
• "What's the migration cost to move from ClickHouse to Druid?" — expect an honest estimate in engineer-quarters.
• "You picked TimescaleDB — what would push you to switch?" — expect a named workload shift (cardinality explosion, concurrency growth).
• "Which engine would you not use for X?" — expect a confident anti-recommendation with reasoning.

Worked example — the 5-question decision tree on three canonical scenarios

Detailed explanation. Walk the 5-question decision tree on three realistic senior-interview scenarios: (a) a Kubernetes observability platform, (b) an ecommerce analytics warehouse, (c) an IoT SaaS with embedded dashboards. Show the walk each time.

• Scenario A — Kubernetes observability. 10K pods, 50 metrics each, 15-second scrape, 45-day retention, 100 SREs, no analytics dashboard needed.
• Scenario B — Ecommerce analytics warehouse. 500M events/day, 40 dimensions, 500 BI users on Superset, 90-day retention, mixed batch + interactive queries.
• Scenario C — SaaS embedded analytics. 5000 tenants × 500K events/day/tenant, dashboards embedded in the product, sub-second required at 10K concurrent users, 90-day retention.

Question. Walk the decision tree for each scenario and record the recommended category and engine.

Input.

Scenario	Q1 (time-window?)	Q2 (unbounded card?)	Q3 (multi-dim?)	Q4 (joins?)	Q5 (100+ users?)
A — K8s observability	yes	yes (10K pods)	—	—	100 users but time-window only
B — Ecommerce warehouse	mixed	no	yes (40 dims)	yes	yes (500 users)
C — SaaS embedded	mixed	no (per-tenant enum)	yes	limited	yes (10K users)

Code.

# Extended decision-tree helper
def decide(time_windowed_reads: bool,
            unbounded_cardinality: bool,
            multi_dim_group_by: bool,
            big_joins: bool,
            concurrency: int) -> tuple[str, str]:
    """Return (category, recommended_engine)."""
    if time_windowed_reads and unbounded_cardinality:
        return "TSDB", "TimescaleDB or VictoriaMetrics"
    if multi_dim_group_by or big_joins:
        if concurrency >= 5000:
            return "OLAP", "Pinot (extreme concurrency)"
        elif concurrency >= 100:
            return "OLAP", "ClickHouse or Druid"
        else:
            return "OLAP", "ClickHouse"
    if concurrency >= 100 and time_windowed_reads:
        return "TSDB + cache", "TimescaleDB + Redis"
    return "TSDB", "TimescaleDB"


# Walk each scenario
print(decide(True,  True,  False, False, 100))
# → ('TSDB', 'TimescaleDB or VictoriaMetrics')  — Scenario A

print(decide(False, False, True,  True,  500))
# → ('OLAP', 'ClickHouse or Druid')  — Scenario B

print(decide(True,  False, True,  False, 10000))
# → ('OLAP', 'Pinot (extreme concurrency)')  — Scenario C

Step-by-step explanation.

1. Scenario A (K8s observability) trips Q2 immediately — 10K pods × 50 metrics is unbounded cardinality on the pod identity axis. Q1 is also yes (time-window reads for SRE dashboards). TSDB wins in two questions; the specific engine (TimescaleDB vs VictoriaMetrics) comes down to team familiarity and write throughput. If write throughput exceeds ~500K samples/sec, VictoriaMetrics; otherwise TimescaleDB.
2. Scenario B (ecommerce warehouse) fails Q1 (mixed reads, not time-dominant) and Q2 (dimensions are enumerated, not unbounded), passes Q3 (40 dims across product × region × channel × device × plan × ...) and Q4 (joins from fact_events to dim_product, dim_region, etc.). OLAP wins; concurrency of 500 keeps it in ClickHouse or Druid territory, not requiring Pinot.
3. Scenario C (SaaS embedded) has mixed time-window plus multi-dim reads; the deciding factor is Q5 concurrency at 10K. Pinot's star-tree with tenant_id as leading dim is the purpose-built answer for this concurrency shape. Neither TSDB nor ClickHouse serves 10K concurrent embedded users at sub-100ms without extreme over-provisioning.
4. The pattern in all three: the first strongly-yes answer short-circuits the tree. Q2 short-circuits Scenario A; Q3+Q4 short-circuit Scenario B; Q5 short-circuits Scenario C (into a specific OLAP engine choice). Rarely does the tree need to walk all five questions.
5. The engine name that falls out of the tree is a recommendation, not a mandate. Team familiarity, existing infra, and licensing constraints all modify the final pick. But the category (TSDB vs OLAP) is settled by the tree; category-level rewrites are quarters of work, so the tree is worth the 60 seconds.

Output.

Scenario	Category	Recommended engine
K8s observability	TSDB	TimescaleDB or VictoriaMetrics
Ecommerce warehouse	OLAP	ClickHouse or Druid
SaaS embedded analytics	OLAP (concurrency)	Pinot with star-tree

Rule of thumb. The 5-question decision tree is the single most valuable interview artifact for this topic. Rehearse it out loud with three canonical scenarios; deploy it live in interviews. When the interviewer changes a scenario mid-answer ("what if concurrency were 10K?"), re-walk the tree; don't rebuild your answer from scratch.

Worked example — the Prometheus + Druid hybrid for observability + analytics

Detailed explanation. A canonical hybrid: Prometheus / VictoriaMetrics scrapes the operational metrics tier (hot, high-resolution, ops dashboards); Druid serves the analytical dashboards (rolled-up trends, capacity planning, cross-service comparisons). A recording-rule + remote-write pipeline pushes the downsampled data from the TSDB tier into a Kafka topic that Druid consumes. Walk through the wiring.

• Hot tier (Prometheus / VM). 15-second scrape, 15-day retention, PromQL alerting, Grafana dashboards for SRE.
• Analytical tier (Druid). 1-hour rollup, 2-year retention, Superset dashboards for eng leadership + capacity planners.
• Bridge. Prometheus recording rules aggregate to 5-min buckets; remote-write to Kafka; Druid ingestion reads Kafka.

Question. Design the wiring: Prometheus recording rules, Kafka bridge, Druid ingestion spec.

Input.

Component	Role
Prometheus	scrape + hot alerting + SRE dashboards
Recording rules	downsample to 5-min buckets
Kafka topic	metrics.rolled.5m
Druid	consume Kafka; serve analytical dashboards
Grafana	Prometheus datasource
Superset	Druid datasource

Code.

# 1. Prometheus recording rules — downsample to 5-min buckets
groups:
  - name: metrics_downsample
    interval: 5m
    rules:
      - record: service:request_rate_5m
        expr:   sum by (service, method, status) (
                  rate(http_requests_total[5m])
                )
      - record: service:request_latency_p95_5m
        expr:   histogram_quantile(0.95,
                  sum by (service, method, le) (
                    rate(http_request_duration_seconds_bucket[5m])
                  )
                )
      - record: service:cpu_avg_5m
        expr:   avg by (service, node) (
                  rate(container_cpu_usage_seconds_total[5m])
                )

# 2. Prometheus remote-write to a Kafka gateway
remote_write:
  - url: http://kafka-gateway:9091/api/v1/write
    queue_config:
      capacity: 100000
      max_shards: 50
      max_samples_per_send: 5000
    write_relabel_configs:
      - source_labels: [__name__]
        regex: 'service:.*'          # only downsampled recording-rule metrics
        action: keep

// 3. Druid Kafka ingestion spec — consume the rolled metrics
{
  "type": "kafka",
  "spec": {
    "dataSchema": {
      "dataSource": "service_metrics_5m",
      "timestampSpec": {"column": "ts", "format": "iso"},
      "dimensionsSpec": {
        "dimensions": [
          {"name": "service", "type": "string"},
          {"name": "method",  "type": "string"},
          {"name": "status",  "type": "string"},
          {"name": "node",    "type": "string"}
        ]
      },
      "metricsSpec": [
        {"name": "count",        "type": "count"},
        {"name": "request_rate", "type": "doubleSum", "fieldName": "value"},
        {"name": "cpu_avg",      "type": "doubleAvg", "fieldName": "cpu"},
        {"name": "latency_p95",  "type": "doubleMax", "fieldName": "p95"}
      ],
      "granularitySpec": {
        "segmentGranularity": "HOUR",
        "queryGranularity":   "FIVE_MINUTE",
        "rollup": true
      }
    },
    "ioConfig": {
      "topic": "metrics.rolled.5m",
      "consumerProperties": {"bootstrap.servers": "kafka:9092"}
    }
  }
}

Step-by-step explanation.

1. The Prometheus recording rules downsample the hot metrics to 5-minute buckets by service, method, status, and node. record: service:request_rate_5m is a new metric name that stores the pre-aggregated rate — subsequent queries on service:request_rate_5m are cheap because the aggregation is already done.
2. The remote-write config ships the pre-aggregated metrics to a Kafka gateway. The write_relabel_configs filter keeps only the service: prefixed recording rules (not the raw high-cardinality series), reducing the Kafka topic volume by 100-1000x. This is the "bridge" that connects the two tiers.
3. Druid's Kafka ingestion consumes the topic and applies its own rollup: rollup: true + queryGranularity: FIVE_MINUTE matches the recording-rule granularity, so incoming samples with matching dimensions collapse into single rows. The Druid segment granularity is 1 hour — each hour becomes one immutable segment on historical nodes.
4. Superset queries Druid for the analytical dashboards: "avg CPU by service over last 30 days," "p95 latency trend by service by week for the last quarter." These queries are impossible to serve efficiently from raw Prometheus data at 15-second resolution — that would scan gigabytes of samples per query.
5. Grafana continues to hit Prometheus for the operational dashboards: "last 6 hours request rate by pod." The two frontends see two datasources, but the underlying data is one pipeline. Each tier is scaled independently: Prometheus scales for ingest + hot query volume; Druid scales for analytical concurrency + long-term storage.

Output.

Layer	Engine	Granularity	Retention	Consumer
Hot tier	Prometheus / VM	15s samples	15 days	Grafana (SRE dashboards)
Bridge	recording rules + Kafka	5-min buckets	Kafka retention (7 days)	Druid indexer
Analytical tier	Druid	5-min rolled	2 years	Superset (analytical)
Alerting	vmalert / Prometheus	15s eval on hot	—	on-call notification

Rule of thumb. For any org that has both an SRE observability need (hot metrics, alerting) and an eng-leadership analytical need (long-term trends, capacity planning), the Prometheus + Druid hybrid is the pattern that serves both without compromise. The bridge is recording rules + remote-write; the trick is the aggressive write_relabel_configs filter that keeps only pre-aggregated metrics from crossing into the analytical tier.

Worked example — senior interview signals on category + hybrid design

Detailed explanation. The senior data-engineering TSDB-vs-OLAP interview grades you on five signals: category naming, cardinality awareness, concurrency awareness, hybrid instinct, and migration honesty. Walk through the grading rubric and the model senior answers.

• Signal 1 — Category naming. Names both TSDB and OLAP as separate answer buckets before naming any specific engine.
• Signal 2 — Cardinality awareness. Asks about cardinality shape before recommending an engine.
• Signal 3 — Concurrency awareness. Asks about concurrent-user count before recommending a serving tier.
• Signal 4 — Hybrid instinct. Proposes a hybrid architecture when workload axes split.
• Signal 5 — Migration honesty. Estimates category-migration cost in engineer-quarters, not "a sprint or two."

Question. Draft a senior CDC-style monologue for the "we have a metrics workload; where do we put it?" opener that hits all five signals.

Input.

Signal	Weak answer	Senior answer
Category naming	"we'd use ClickHouse"	"TSDB or OLAP — depends on the workload shape"
Cardinality	"we'd shard"	"what's the identity cardinality — 10K containers is TSDB territory"
Concurrency	"should be fast"	"how many concurrent dashboard users — 500 pushes to Druid/Pinot"
Hybrid	"one engine is simpler"	"at this scale it's almost always TSDB + OLAP hybrid"
Migration	"we could switch later"	"category migration is 1-2 quarters; pick right the first time"

Code.

Senior TSDB-vs-OLAP monologue (5 minutes)
=========================================

Opener (30 seconds)
  "Two categories on the table — TSDBs like TimescaleDB, InfluxDB,
   QuestDB, VictoriaMetrics, and OLAP engines like ClickHouse, Druid,
   Pinot, StarRocks. The workload picks the category. Let me ask three
   questions before recommending one:
     1. What's the identity cardinality?
     2. Do the reads GROUP BY on 10+ dimensions?
     3. How many concurrent dashboard users?"

If TSDB territory (60 seconds)
  "Unbounded identity cardinality + time-window reads = TSDB. My default
   would be TimescaleDB for SQL-native teams, VictoriaMetrics for
   Prometheus-compatible operations at >500K samples/sec. Continuous
   aggregates + retention policies get you 30-day hot + multi-year
   downsampled in five lines of config. Compression is 10-30x on
   numeric metrics."

If OLAP territory (60 seconds)
  "Multi-dim GROUP BY or big joins = OLAP. My default would be ClickHouse
   for batch + interactive analytics, Druid for internal dashboards at
   100-1000 concurrent users, Pinot for user-facing embedded analytics
   at 10K concurrent. Star-schema joins + materialized-view pre-aggregates
   are the OLAP superpower."

If hybrid (90 seconds)
  "When the workload has both — high-throughput time-series ingest AND
   dashboard concurrency — the answer is a hybrid: TimescaleDB or InfluxDB
   for ingest + downsampling, ClickHouse or Pinot for serving. Two
   engines, one pipeline, wired via a scheduled ETL or Kafka bridge.
   Every serious observability + analytics stack in 2026 is a hybrid."

Migration caveat (60 seconds)
  "Category-level migration (TSDB to OLAP or vice versa) is 1-2 quarters
   of engineering, not a sprint. Ingest pipelines change, downsampling
   changes, retention changes, dashboards rewrite. Vendor-within-category
   migrations (Timescale to Influx or ClickHouse to Druid) are cheaper
   but still 2-4 sprints per fact table. Pick the category right the
   first time."

Step-by-step explanation.

1. The opener names both categories in the first sentence and refuses to commit to an engine before answering three axis questions. This immediately establishes category-level thinking; weak candidates skip straight to a vendor name and never recover.
2. The TSDB branch names the two dominant engines (TimescaleDB for SQL-native, VictoriaMetrics for Prometheus-compatible) and their differentiating features (continuous aggregates, retention policies, compression). Naming why you'd pick each variant is what separates senior signal from vendor namedropping.
3. The OLAP branch cleanly splits the three OLAP engines by concurrency tier: ClickHouse for batch + interactive, Druid for 100-1000 users, Pinot for 10K+ embedded users. This is the mental model interviewers listen for — each engine has a "when I'd reach for it" niche.
4. The hybrid branch is where senior candidates shine. Naming that "every serious observability + analytics stack in 2026 is a hybrid" acknowledges the reality; naming the specific bridge pattern (scheduled ETL, Kafka connector) shows you've built one.
5. The migration caveat is the "senior honesty" signal. Estimating category-migration cost in quarters (not sprints) shows you've done it; naming the specific things that change (ingest, downsampling, retention, dashboards) shows you've felt the pain. Interviewers listen for this because it separates the "read about it" candidates from the "built it" candidates.

Output.

Signal	Delivered?	Interviewer note
Category naming	✓	strong senior signal
Cardinality question	✓	required for any engine recommendation
Concurrency question	✓	separates OLAP engines
Hybrid proposal	✓	the "senior architect" signal
Migration honesty	✓	separates "read about it" from "built it"

Rule of thumb. The 5-minute monologue is your rehearsed answer to the ambiguous opener. Rehearse it out loud until it flows in four minutes with a minute for follow-ups. The interviewer's follow-ups will drill into whichever signal you delivered least confidently — reinforce all five in the base monologue.

Senior interview question on end-to-end category + hybrid design

A senior interviewer might close with: "You are designing a data platform from scratch for a mid-stage SaaS: (a) product-usage analytics for 1000 tenants with dashboards embedded in the product, (b) operational observability for the SRE team covering 500 nodes and 5000 containers, (c) business analytics for the exec team on customer cohorts + revenue. Walk me through the category call for each workload, the engine picks, the hybrid architecture that wires them together, and the migration story if any of the categories turned out wrong."

Solution Using a three-engine hybrid — Pinot for embedded, VictoriaMetrics for observability, ClickHouse for business analytics

# 1. Three workloads → three category calls → three engines
workloads:
  embedded_product_analytics:
    tenants: 1000
    events_per_day: 500K per tenant   # 500M total
    dashboards: embedded in product
    concurrency: 10K concurrent users
    category: OLAP (concurrency + multi-dim)
    engine: Apache Pinot
    reason: star-tree index + tenant_id as leading dim; 10K concurrent

  sre_observability:
    nodes: 500
    containers: 5000
    metrics_per_container: 50
    scrape_interval: 15s
    concurrency: 20 SREs
    category: TSDB (cardinality + time-window reads)
    engine: VictoriaMetrics
    reason: 5K containers x 50 metrics = 250K series; unbounded cardinality; PromQL

  business_analytics:
    events_per_day: 500M (same as embedded, joined with CRM dims)
    dashboards: Superset for exec team
    concurrency: 30 exec + PM
    category: OLAP (multi-dim + joins)
    engine: ClickHouse
    reason: fact-to-dim joins + materialized views + Superset connector

# 2. Data flow — Kafka is the shared backbone
kafka_topics:
  product_events_raw:
    producers: [product app services]
    consumers: [pinot_ingester, clickhouse_kafka_engine]

  vm_metrics_5m_downsampled:
    producer: victoria_metrics_recording_rules
    consumers: [clickhouse_kafka_engine]     # for exec-level infra-cost dashboards

pinot_ingester:
  table: tenant_events_REALTIME
  rollup_dims: [tenant_id, event_type, country, platform]
  rollup_metrics: [count, sum(revenue), DISTINCTCOUNTHLL(user_id)]

clickhouse_ingester:
  engine: Kafka + MaterializedView
  sink: analytics.fact_events (MergeTree)
  materialized_views:
    - name: revenue_daily_by_tenant_channel_region
      engine: SummingMergeTree
      order_by: [day, tenant_id, channel, region]

# 3. Migration paths if a category call turns out wrong
migration_paths:
  embedded_analytics_wrong:
    from: Pinot
    to:   ClickHouse (if concurrency drops below 500)
    cost: ~2 quarters (ingest rewrites, dashboard rewrites, star-tree → materialized view)

  observability_wrong:
    from: VictoriaMetrics
    to:   TimescaleDB (if SQL joins needed for cost-attribution)
    cost: ~1 quarter (PromQL → SQL rewrites, retention policy re-config)

  business_analytics_wrong:
    from: ClickHouse
    to:   Snowflake or BigQuery (if managed-service pressure wins)
    cost: ~1 quarter (SQL dialect + Kafka connector rewrites)

Step-by-step trace.

Workload	Category	Engine	Concurrency SLA	Retention
Embedded product analytics	OLAP	Pinot	10K users, p95 < 100ms	7d REALTIME + 90d OFFLINE
SRE observability	TSDB	VictoriaMetrics	20 SREs, p95 < 200ms	45d hot + 2y downsampled
Business analytics	OLAP	ClickHouse	30 execs, p95 < 500ms	5y with monthly partitions
Bridge	Kafka	3 topics + relabel filters	—	7d Kafka retention
Migration cost per category	—	—	—	~1-2 quarters each

After deployment, three engines serve three workload families with the same Kafka backbone. Pinot's per-tenant star-tree handles 10K concurrent embedded users at sub-100ms; VictoriaMetrics scrapes 5K containers × 50 metrics = 250K series at 15-second resolution and serves PromQL alerts + Grafana SRE dashboards; ClickHouse ingests product events + CRM dim tables and serves Superset for the exec team's revenue + cohort analysis. Cross-cutting queries (e.g. "infra cost per tenant") join the ClickHouse fact table with the VictoriaMetrics-derived infra cost table using a scheduled Kafka bridge.

Output:

Layer	Engine	Users	Latency SLA
Embedded	Pinot	10K concurrent	p95 < 100 ms
SRE	VictoriaMetrics	20 SREs	p95 < 200 ms
Business	ClickHouse	30 execs	p95 < 500 ms
Bridge	Kafka + connectors	infra pipeline	~1-min data lag
Storage total	~50 TB active	across three tiers	—

Why this works — concept by concept:

• Three-engine, one-backbone architecture — Kafka is the shared source-of-truth event stream; each engine consumes what it needs with its own rollup strategy. This avoids the "one engine to serve all workloads" fallacy that costs teams two years of query-tuning pain. Two engines is a hybrid; three engines is a mature analytics stack.
• Pinot for extreme concurrency — the star-tree with tenant_id as leading dim collapses each per-tenant query to a compact sub-tree traversal. This is the primitive that lets 10K concurrent users hit sub-100ms without the cluster melting.
• VictoriaMetrics for observability throughput — VM's mergeset storage + Prometheus-compatible surface lets the SRE team keep their PromQL muscle memory while getting 5-10x the throughput of vanilla Prometheus. Recording rules downsample to 5-minute buckets for the 2-year archive; the bridge pushes those into ClickHouse for cross-cutting exec queries.
• ClickHouse for business analytics — MergeTree + SummingMergeTree materialized views serve the exec + PM team's revenue + cohort dashboards through Superset's native connector. Concurrency of 30 is well within ClickHouse's comfortable range; retention of 5 years is one TTL clause.
• Cost — three engines to operate (Pinot, VM, ClickHouse) + Kafka + three separate ingest pipelines + three separate dashboarding surfaces (embedded, Grafana, Superset). Compared to trying to serve all three workloads from one engine (would require ~10x the hardware and 3x the query-tuning engineer time), this is the mature architecture that pays for its complexity within the first six months. Net O(1) per query per engine on the pre-aggregated views; the operational trade is that on-call must know three engines' failure modes.

Design
Topic — design
Design problems on hybrid TSDB + OLAP architectures

Practice →

SQL
Topic — sql
SQL practice on cross-engine analytical queries

Practice →

---

Cheat sheet — TSDB vs OLAP recipes

• Category picker in one line. TSDB (TimescaleDB / InfluxDB / QuestDB / VictoriaMetrics / Prometheus) when reads are time-window aggregations and identity cardinality is unbounded; OLAP (ClickHouse / Druid / Pinot / StarRocks) when reads are multi-dim GROUP BYs or concurrency exceeds 100 users; hybrid (TSDB + OLAP with a Kafka or scheduled-ETL bridge) whenever the six-axis workload audit splits votes across categories. Draw the six-axis table on a whiteboard first; the category falls out of the constraints without argument.
• Time-bucket SQL template — TimescaleDB. SELECT time_bucket('1 minute', ts) AS bucket, dim, avg(value) AS avg_v, percentile_cont(0.95) WITHIN GROUP (ORDER BY value) AS p95_v FROM metrics WHERE ts > now() - INTERVAL '1 hour' GROUP BY bucket, dim ORDER BY bucket; — the canonical TSDB query shape. Pair with create_hypertable('metrics', 'ts', chunk_time_interval => INTERVAL '6 hours') and add_compression_policy('metrics', INTERVAL '1 day') for the storage side; pair with CREATE MATERIALIZED VIEW ... WITH (timescaledb.continuous) to serve dashboards from a pre-aggregated view instead of the raw table.
• Time-bucket SQL template — QuestDB. SELECT ts, symbol, first(price) AS open, max(price) AS high, min(price) AS low, last(price) AS close, sum(volume) AS volume FROM trades WHERE ts > dateadd('h', -1, now()) SAMPLE BY 1m FILL(PREV); — OHLC pattern for financial ticks. TIMESTAMP(ts) PARTITION BY DAY WAL + SYMBOL type + ILP ingest on port 9009 gives you ~4M rows/sec on modest hardware. FILL(PREV) for chart continuity; FILL(LINEAR) for interpolation; FILL(NULL) to expose gaps.
• Time-bucket SQL template — InfluxDB Flux. from(bucket: "metrics_raw") |> range(start: -10m) |> filter(fn: (r) => r._measurement == "cpu") |> aggregateWindow(every: 1m, fn: mean, createEmpty: false) |> to(bucket: "metrics_1m") — canonical downsampling task. option task = {name: "downsample_1m", every: 5m, offset: 30s} header makes it a scheduled task. Overlap the window (range(start: -10m) for a 5m schedule) so re-runs are idempotent.
• Continuous aggregate template — TimescaleDB. CREATE MATERIALIZED VIEW cpu_1m WITH (timescaledb.continuous) AS SELECT time_bucket('1 minute', ts), host, avg(value) FROM cpu_usage GROUP BY 1, 2; SELECT add_continuous_aggregate_policy('cpu_1m', start_offset => INTERVAL '2 hours', end_offset => INTERVAL '1 minute', schedule_interval => INTERVAL '1 minute'); — the pattern that turns a 5-second dashboard query into a 40-ms one. The start_offset bounds the refresh work; the end_offset keeps the aggregate fresh; the schedule_interval controls the refresh cadence.
• Retention policy template — TSDB. TimescaleDB: SELECT add_retention_policy('metrics', INTERVAL '30 days'); drops entire chunks older than 30 days in O(1). InfluxDB: bucket config retention: 90d + shard_group_duration: 1d. VictoriaMetrics: retentionPeriod: 45d on vmstorage. All three are one-line ops; the OLAP equivalent (a TTL clause) is similar but requires scheduled TTL merges rather than chunk drops.
• MergeTree template — ClickHouse. CREATE TABLE events (ts DateTime, dim1 LowCardinality(String), dim2 LowCardinality(String), value Decimal(18, 4)) ENGINE = MergeTree PARTITION BY toYYYYMM(ts) ORDER BY (toDate(ts), dim1, dim2) TTL ts + INTERVAL 90 DAY SETTINGS index_granularity = 8192; — the canonical OLAP fact-table shape. Pair with a SummingMergeTree materialized view for pre-aggregation: CREATE MATERIALIZED VIEW ... TO agg_table AS SELECT toDate(ts), dim1, dim2, sum(value), count() FROM events GROUP BY ...; — the trigger view maintains the aggregate on every INSERT.
• Druid rollup-at-ingest template. granularitySpec: {segmentGranularity: HOUR, queryGranularity: MINUTE, rollup: true} + metricsSpec with count, doubleSum, thetaSketch — Druid pre-aggregates on ingest, so 10K raw events collapsing into 100 rolled rows per minute is the norm. thetaSketch for approximate distinct counts, doubleSum for numeric sums, hyperUnique for unique-count on IDs. Choose rollup dimensions to match the dashboards' GROUP BY columns.
• Pinot star-tree template. starTreeIndexConfigs: [{dimensionsSplitOrder: [tenant_id, event_type, country, platform], functionColumnPairs: [COUNT__*, SUM__revenue, DISTINCTCOUNTHLL__user_id], maxLeafRecords: 10000}] — the primitive that hits sub-100ms at 10K concurrent users. Put the highest-selectivity dimension first (tenant_id for multi-tenant SaaS); the rest in decreasing selectivity order. maxLeafRecords: 10000 is the tuning knob for latency vs storage.
• Hybrid Prometheus + Druid recipe. Recording rules downsample hot metrics to 5-min buckets; remote_write with write_relabel_configs: [{source_labels: [__name__], regex: 'service:.*', action: keep}] filters to keep only pre-aggregated series; Kafka gateway bridges to Druid Kafka ingestion with queryGranularity: FIVE_MINUTE and matching metricsSpec. Grafana + Prometheus for SRE; Superset + Druid for analytics; one data pipeline serving both.
• Cardinality budget cheat. For TSDBs: TimescaleDB tolerates up to ~10M active series per node (index-scaled); InfluxDB fights back around 1-5M series (in-memory index); VictoriaMetrics scales to 10M+ per node (mergeset). For OLAP: ClickHouse LowCardinality(String) handles up to ~50K unique values well; beyond that consider a separate dim table. Druid rollup tolerates up to ~1M rolled dim combinations per segment; Pinot star-tree scales linearly with dim1 × dim2 × ... × dimN in the split order, so keep tree depth under 6 dims for practical latency.
• Concurrency budget cheat. TimescaleDB: 5-50 concurrent users comfortably; beyond that saturate connection pool. InfluxDB: 10-100 concurrent Flux queries; beyond that hits task-scheduler limits. VictoriaMetrics: 100+ PromQL queries (vmselect scales horizontally). ClickHouse: 100-500 concurrent users comfortably per shard; MPP scale-out helps. Druid: 1000-5000 concurrent users (query broker + historicals scale independently). Pinot: 10K+ concurrent users (purpose-built for user-facing analytics). Match concurrency to engine tier; don't force a TSDB to serve 500 concurrent BI users.
• Compression ratio cheat. TimescaleDB compressed chunks: 10-30x on numeric metrics, 5-15x on mixed metrics + labels. InfluxDB TSM: 10-20x on numeric time-series. VictoriaMetrics mergeset: 15-30x on Prometheus-shaped metrics (delta-of-delta + Gorilla). ClickHouse LZ4: 3-10x on generic columnar data; ZSTD: 5-15x. Druid segments: 5-15x compressed columnar. TSDBs win on numeric time-series because of Gorilla-family float compression; OLAP wins on mixed-column data because of generic columnar codecs.
• Migration cost cheat. TSDB↔TSDB (Timescale ↔ Influx ↔ QuestDB): ~1-2 sprints per aggregate table, mostly query rewrites. OLAP↔OLAP (ClickHouse ↔ Druid ↔ Pinot): ~2-4 sprints per fact table, mostly ingest pipeline changes. TSDB↔OLAP category migration: ~1-2 quarters minimum, sometimes 2-3 quarters when dashboards + retention + downsampling all rewrite. The rule: pick the category right the first time; engine-within-category migrations are cheap; category migrations are quarterly projects.
• When to reach for each engine. TimescaleDB: your team already knows Postgres + workload is time-series-heavy but not extreme-throughput. InfluxDB: pure metrics workload without SQL joins; team OK with Flux/InfluxQL. QuestDB: financial ticks or high-frequency numeric streams needing the fastest possible ingest. VictoriaMetrics: Prometheus-compatible operational metrics at >500K samples/sec. ClickHouse: batch + interactive analytics + BI tools at 100-500 concurrent. Druid: internal analytical dashboards at 100-5000 concurrent. Pinot: user-facing embedded analytics at 5000-10000+ concurrent. StarRocks: MPP OLAP with strong JOIN performance and Iceberg support.

Frequently asked questions

What is a time series database in one sentence?

A time series database is a database engine purpose-built for the workload where data arrives as an append-only ordered stream of samples tagged with a timestamp, an identity (host, container, sensor, user), and one or more numeric values — and where queries are almost always time-window aggregations (avg over last 5 minutes, p95 over last hour, count per day for the last 30 days) rather than arbitrary multi-dimensional GROUP BYs. The category includes TimescaleDB (Postgres extension with hypertables), InfluxDB (TSM storage + Flux/InfluxQL), QuestDB (SQL + SAMPLE BY + fastest ingest via ILP), VictoriaMetrics (Prometheus-compatible mergeset), and Prometheus itself (scrape-based + PromQL). What separates TSDBs from general-purpose databases is that time is the partition key, not just a column: storage is chunked by time, compression is optimised for consecutive-timestamp deltas and consecutive-float XOR (Gorilla-family), retention is a one-line declarative policy that drops entire chunks in O(1), and downsampling is a native primitive (continuous aggregates, recording rules, Flux tasks) rather than a custom ETL pipeline. Every senior data-engineering interviewer probes this category because the storage-layer choice binds every downstream dashboard and alerting rule for years.

TSDB vs OLAP — when do I pick each?

Default to a time series database when the primary reads are time-window aggregations and the identity cardinality is unbounded (per-container, per-user, per-device metrics that grow without limit). Default to an OLAP engine when the primary reads span 10+ dimensions in GROUP BY or concurrency exceeds 100 dashboard users. The 5-question decision tree resolves 90% of scenarios in under 60 seconds: (1) time-window reads? (2) unbounded cardinality? → TSDB; (3) multi-dim GROUP BY? (4) big joins? (5) 100+ concurrent users? → OLAP. When the answers split across the two categories, the answer is a hybrid architecture — Prometheus + Druid for observability + analytics, TimescaleDB + ClickHouse for ingest + BI, InfluxDB + Pinot for ingest + embedded — with a Kafka or scheduled-ETL bridge between the two tiers. The category call binds you for years because category migrations (TSDB → OLAP or vice versa) cost 1-2 engineer-quarters; engine-within-category migrations are cheaper (2-4 sprints per fact table). Pick the category right the first time; don't reach for "the popular engine" before walking the tree.

Is ClickHouse a time series database?

ClickHouse is a general-purpose columnar OLAP engine that has some time-series features but is not a purpose-built time series database in the way TimescaleDB, InfluxDB, QuestDB, VictoriaMetrics, or Prometheus are. ClickHouse's MergeTree engine supports time-based partitioning (PARTITION BY toYYYYMM(ts)), sorted-storage co-location on time-leading ORDER BY keys, TTL clauses for retention, time-related functions (toStartOfMinute, runningDifference, groupArrayInsertAt for gap-fill), and it will happily handle a time-series workload with good performance — many teams use it exactly that way. What it doesn't have out of the box: automatic downsampling with incremental refresh (you build a materialized view manually and re-aggregate; Timescale continuous aggregates refresh only the changed buckets), Gorilla-family float compression (ClickHouse uses generic LZ4/ZSTD, so its compression ratio on numeric time-series is 3-10x vs 10-30x for TSDBs), and native time-series primitives like LAST/FIRST/DELTA/RATE/gap_fill/LOCF interpolation. For pure metrics workloads at extreme write throughput, TSDBs remain 3-10x cheaper on storage and 10-100x easier to operate. For mixed workloads that need multi-dim slice-and-dice on top of time-series data, ClickHouse is the natural single-engine answer.

Should I use InfluxDB or TimescaleDB?

Pick TimescaleDB when your team already knows Postgres, when your workload benefits from full SQL (window functions, joins across dimension tables, FOR UPDATE locks), when you want continuous aggregates as first-class materialized views, and when compression + tiered retention + policy-based automation matter more than raw ingest throughput. Timescale peaks at ~500K-1M rows/sec per node with careful batching and offers a full ANSI-SQL surface plus hypertables + chunks + continuous aggregates + retention policies as extensions on top of standard Postgres. Pick InfluxDB when your workload is pure metrics (no dimension-table joins needed), when your team is comfortable with Flux or InfluxQL as query languages, when your data model fits the tag/field/measurement shape natively, and when you want built-in downsampling tasks and retention policies without extending an existing database. InfluxDB peaks at ~1M writes/sec per node with the TSM engine; the Flux language is powerful but has a learning curve; the ecosystem includes Telegraf for ingest, Chronograf for dashboards, and Kapacitor for alerting. Neither engine handles multi-dim slice-and-dice at OLAP scale; both handle time-window reads at TSDB scale. The choice comes down to language preference (SQL vs Flux) and ecosystem lock-in appetite (Postgres extension vs standalone TSDB stack). For most greenfield teams in 2026, TimescaleDB wins on SQL familiarity and the Postgres ecosystem; for teams already on the Telegraf → InfluxDB → Chronograf stack, InfluxDB wins on continuity.

What is Apache Druid and when is it better than ClickHouse?

Apache Druid is a purpose-built OLAP engine for time-partitioned analytical workloads with high concurrency — designed for internal dashboards where 100-5000 concurrent users query rolled-up time-series data across arbitrary dimensions. Druid's superpower is rollup at ingest: as data arrives from Kafka or batch files, Druid pre-aggregates it into segments grouped by (time_bucket × dimension_combination), so historical nodes only serve pre-aggregated data. Each segment is a time-chunked, columnar, immutable slab on disk; the query broker parallelizes across segments and merges results. Compared to ClickHouse: Druid wins on concurrency (its architecture separates ingest, coordination, brokering, and historical serving; each tier scales independently to 1000+ concurrent users) and on operational simplicity for the specific rollup-at-ingest pattern (no manual materialized view management). ClickHouse wins on flexibility (arbitrary SQL, no rollup requirement, better joins, larger ecosystem) and on raw batch throughput (10M+ rows/sec sustained inserts). Pick Druid when the workload is dashboard-serving at 100-5000 concurrent users with time-heavy queries and known rollup dimensions. Pick ClickHouse when the workload is mixed batch + interactive analytics with arbitrary ad-hoc queries and moderate concurrency (< 500 users). At extreme concurrency (5000-10000+ users), reach for Apache Pinot instead — its star-tree index outperforms both for the pre-aggregated user-facing analytics pattern.

Can I use PostgreSQL as a time series database?

Yes, and it's a common answer for small-to-medium workloads — but not out-of-the-box vanilla Postgres. Vanilla Postgres on a time-series workload runs into three failure modes at scale: (1) B-tree indexes on (ts) bloat as chunks accumulate, slowing queries; (2) DELETE WHERE ts < ... for retention is O(rows), not O(chunks), and creates massive vacuum load; (3) there's no automatic compression, so storage grows linearly with ingest. The fix is to install the TimescaleDB extension — which is fully Postgres-compatible (same SQL, same drivers, same tooling) but adds hypertables (time-partitioned chunks with per-chunk indexes and compression), continuous aggregates (incrementally-refreshed materialized views), retention policies (one-line automation that drops entire chunks in O(1)), and compression policies (Gorilla-family float encoding + delta-of-delta timestamp encoding + columnar layout for chunks older than N days). With TimescaleDB, Postgres becomes a legitimate first-tier TSDB handling 500K-1M rows/sec per node and 10-30x compression on numeric metrics. Without TimescaleDB, vanilla Postgres works up to a few million rows total but breaks down at production time-series scale. If you can't install an extension (e.g. some managed tiers), consider a purpose-built TSDB (InfluxDB, QuestDB, VictoriaMetrics) rather than fighting vanilla Postgres for a workload it wasn't designed to serve.

Practice on PipeCode

• Drill the SQL practice library → for the time-window, time_bucket, SAMPLE BY, and continuous-aggregate query patterns senior interviewers love.
• Rehearse on the aggregation practice library → for the multi-dim GROUP BY, rollup, and OLAP-style dashboard aggregations that separate TSDB from OLAP thinking.
• Sharpen the system-design axis with the design practice library → for the TSDB-vs-OLAP category decisions, hybrid-architecture proposals, and Prometheus + Druid / TimescaleDB + ClickHouse pipeline sketches interviewers probe.
• Stack the prerequisites against PipeCode's broader 450+ data-engineering catalogue to anchor the 5-question decision tree, the six-axis workload audit, and the three canonical hybrid architectures against real graded inputs.

Lock in time series database muscle memory

Docs explain what a time series database is. PipeCode drills explain the decision — when TimescaleDB's continuous aggregates outrun a raw ClickHouse table, when Druid's rollup-at-ingest wins at 1000 concurrent users, when Pinot's star-tree is the only engine that survives 10K embedded dashboards, when the honest answer is a hybrid architecture wired through Kafka. Pipecode.ai is Leetcode for Data Engineering — pattern-first practice tuned for the production trade-offs senior data engineers actually face.

Practice SQL problems →
Practice design problems →