Time-Series Databases

The Workload Signature

 time-series data has a UNIQUE fingerprint:

 - APPEND-ONLY:     new data arrives; old data never updates
 - TIME-ORDERED:    queries are almost always time-ranged
 - RECENT-BIASED:   last hour/day queried 1000x more than last year
 - HIGH CARDINALITY: millions of series (host×metric×tag combos)
 - EXPIRY IS NORMAL: 90-day retention is a FEATURE, not data loss

 metrics, sensor/IoT streams, financial ticks, event logs,
 user analytics — all share this fingerprint.

Why General Databases Drown

 B-tree relational at 1M rows/min:

 - random-ish index inserts (timestamp + entity id) → page churn
 - indexes grow unboundedly (no expiry concept!)
 - "avg cpu by host over 30d" = aggregate billions of rows
   with OLTP-oriented machinery

 specialized stores exploit the append-only shape instead:

 LSM-style writes:   pure appends, no in-place updates ✓
 columnar layout:    per-column compression (delta-of-delta
                     timestamps compress to ~2 bytes/point!) ✓
 time-partitioned:   drop old partitions = instant expiry ✓

Core Capabilities

Capability	What it means
Downsampling	Raw per-second → rollups per-minute/hour automatically
Retention policies	Automatic age-based deletion by tier
Continuous aggregates	Materialized rollups maintained on ingest
Range-first queries	Time-window scans are THE optimized path

 downsampling math that makes it all work:
   raw 10s points for 1 year  = 3.15M points/series
   keep raw 7 days, then 1min for 90d, then 1h forever:
   ≈ 60k+4.3k+8.7k points ≈ 73k  (~97% reduction)
 dashboards query rollups; forensics still finds recent raw.

The Landscape

 purpose-built: InfluxDB, Prometheus(+TSDB), QuestDB, TimescaleDB*
 cloud-native:  AWS Timestream, Google Bigtable-TS patterns
 *Timescale = Postgres extension: full SQL + TS optimizations —
  the "keep your relational skills" path
 
 Prometheus deserves special mention as the MONITORING standard:
 pull-model scraping, local TSDB, PromQL — its own ecosystem.

Cardinality: The Silent Killer

 series count = product of label values:

 metric: http_requests_total
 labels: service(50) × route(200) × status(5) × region(6) × pod(500)
         = 150 MILLION series from ONE metric

 high cardinality → memory explosion, slow queries, OOM crashes.

 discipline:
 ✗ never label with unbounded values (user_id, request_id!)
 ✓ bound every label's value set deliberately
 ✓ move high-cardinality needs to trace/log systems instead

When TSDB Fits

 ✓ infrastructure/app metrics (the canonical case)
 ✓ IoT/sensor ingestion at scale
 ✓ financial market data
 ✓ user-facing analytics dashboards

 skip when:
 ✗ low-volume logs → regular DB fine
 ✗ data needs UPDATES → not actually time-series
 ✗ complex relational joins needed → warehouse territory

Interview Framing

“Store server metrics for 10k hosts” scored shape: recognize the workload signature out loud, quote cardinality arithmetic, design the downsample/retention tiers with numbers, pick engine with reasoning (Prometheus for monitoring; Timescale if SQL needed). The cardinality-explosion warning is the differentiator — candidates who’ve been paged for it never forget to mention it.