Q60 — How does a time-series database differ from a relational one — and why?

What is time-series data?

A time-series is a sequence of (timestamp, value) pairs representing measurements of something over time. CPU usage sampled every 10 seconds. Stock prices every millisecond. HTTP request counts every minute. Temperature sensor readings every second. IoT device heartbeats. Application metrics from Prometheus.

What makes this data distinct from typical relational data:

Every row has a timestamp as a primary dimension. Queries are almost always time-bounded: "give me CPU usage for host-42 between 2 PM and 3 PM." The timestamp isn't just metadata — it's the primary axis of every meaningful query.

Data arrives in time order and is almost never updated. Once you record "CPU was 82% at 14:32:07", that measurement is immutable. You don't update past data. You only ever append new measurements. Old data might be deleted (after retention period), but it's never modified.

Write volume is enormous and sustained. An infrastructure monitoring system for 10,000 servers, each reporting 50 metrics every 10 seconds, generates 50,000 data points per second — 4.3 billion per day. A relational database handling this volume with a standard B-tree index structure would be overwhelmed.

Reads are time-range aggregations. You rarely query a single data point ("what was CPU at exactly 14:32:07?"). You almost always query a range with aggregation: "average CPU per 5-minute bucket over the last 24 hours." Relational databases can do this, but they're not optimized for it at extreme write rates and deep historical range scans.

Why a relational database struggles with time-series data

B-tree indexes are wrong for this access pattern. A B-tree index on a timestamp column enables time-range lookups efficiently — but the problem isn't lookups, it's the write overhead. A B-tree is a balanced tree that requires rebalancing on every insert. At 50,000 inserts per second, this creates enormous write amplification. New data always goes at the "end" of time (future timestamps are always larger), so the B-tree is constantly growing and rebalancing at the leading edge.

Row-oriented storage is inefficient for column aggregations. Relational databases (PostgreSQL, MySQL) store each row together on disk. A query asking for "average CPU usage over the last hour" needs only the cpu_value column across thousands of rows — but must read the entire row (host_id, timestamp, cpu_value, memory_value, disk_value) for each, wasting I/O on columns it doesn't need.

Cardinality explosion. Time-series data has tags — labels that identify what's being measured (host="web-42", region="us-east-1", environment="prod"). A query like "average CPU by region" requires filtering by tag and grouping. With millions of unique tag combinations (high cardinality), standard relational indexes become massive and slow.

Retention management is complex. Time-series data typically has a defined retention policy: keep raw data for 7 days, then downsampled (hourly averages) for 30 days, then daily averages for 1 year. In a relational database, implementing rolling deletion at billions of rows per day is expensive (DELETE WHERE timestamp < X takes full table scans). Time-series databases handle this natively.

How does a time-series database solve these problems?

Columnar storage: instead of storing all fields of a row together, store all values of the same field together. All CPU measurements for host-42 are stored contiguously on disk. A query for "average CPU for host-42 over the last hour" reads a continuous block of floats — maximally cache-efficient, minimally I/O-intensive.

Chunk/segment-based storage: data is organized into time-bounded chunks (e.g., 8-hour chunks in TimescaleDB, or blocks in InfluxDB). Each chunk covers a specific time range. When a time-range query arrives, the database identifies which chunks overlap the query range and ignores all others. Chunks outside the retention period are dropped as a unit — no row-level DELETE scans.

Write-optimized append paths: since time-series data always arrives in roughly chronological order and is never updated, the database can use an append-only write path (similar to LSM trees — Q12). New data goes into an in-memory buffer, periodically flushed to disk as immutable segment files. No B-tree rebalancing. Sequential disk writes are fast.

Compression: time-series values are highly compressible. CPU values are floats between 0 and 100 that change slowly over time. Delta encoding (store the difference from the previous value rather than the absolute value) reduces 8-byte floats to 1-2 bytes in many cases. Gorilla compression (developed at Facebook, used in Prometheus and InfluxDB) uses XOR delta on float bits for extraordinary compression ratios. A week of raw metrics data might compress 10-20x.

Native downsampling and continuous aggregates: TimescaleDB (built on Postgres) allows you to define a continuous aggregate: "precompute the hourly average CPU per host, refreshed every 15 minutes." Long-range historical queries hit the precomputed aggregate instead of scanning raw data. InfluxDB has similar "tasks" for downsampling. This keeps historical query latency fast even as raw data is retained at full resolution for short periods.

The tag cardinality problem

Every time-series has identifying tags: host, region, env, service, endpoint. Tags let you filter and group time-series: "average latency for endpoint=/api/orders in region=us-east-1."

The number of unique tag combinations — cardinality — is a critical scaling problem. 10,000 hosts × 50 metrics = 500,000 unique time-series. That's manageable. But 10,000 hosts × 50 metrics × 1,000 unique user_ids (if you tag metrics per user) = 500 million unique time-series. InfluxDB and Prometheus struggle significantly at high cardinality — their in-memory inverted index for tag lookups grows to consume gigabytes of RAM.

High-cardinality tags (user_id, request_id, session_id) should not be time-series tags — they belong in logs or traces (Q54). Time-series tags should be low-cardinality dimensions: host, region, service, environment, status_code (a handful of values). This is a fundamental design constraint of time-series databases.

When to use which system

Use a time-series database (Prometheus, InfluxDB, TimescaleDB, ClickHouse) when:

Data is append-only measurements with timestamps as the primary dimension
Write volume is very high (thousands to millions of data points per second)
Queries are time-range aggregations (avg, max, percentile over a time window)
Retention policies with downsampling are needed
The primary use case is metrics, monitoring, IoT sensor data, financial tick data

Use a relational database when:

Data has complex relationships requiring joins
Reads are point lookups or complex multi-dimensional queries
Data is updated and transactional consistency is needed
Time is just one attribute, not the primary organizing dimension

Use ClickHouse or a columnar analytical store when:

Time-series data needs joins with other data (users, products)
Complex analytical queries beyond simple aggregations
Event tracking (each event has many attributes, not just a value and timestamp)

The mental model

code

Time-series data characteristics:
  (timestamp, metric_name, tags, value) — append-only, never updated
  Time = primary query dimension
  Write volume: enormous and sustained (10k metrics/sec is common)
  Queries: time-range aggregations (avg/max/p99 over a time window)

Why relational databases struggle:
  B-tree writes: rebalance at leading edge → write amplification at high ingest rate
  Row storage: query for one column → read all columns → wasted I/O
  Retention: DELETE WHERE timestamp < N → full scans at scale
  High cardinality tags → massive indexes → RAM exhaustion

How TSDBs solve each:
  Columnar storage → read only the column you need → fast aggregations
  Chunk-based storage → query skips irrelevant time chunks → fast range scans
                        retention = drop whole chunks → no DELETE scans
  Append-only write path (LSM-like) → no rebalancing → fast high-volume ingest
  Compression (delta + XOR) → 10-20x smaller → more data fits in cache
  Continuous aggregates → precomputed hourly/daily aggregations → fast historical queries

Cardinality constraint:
  Tags must be low-cardinality (host, region, service — N values)
  NOT high-cardinality (user_id, request_id — M unique values per series)
  High-cardinality → index explosion → OOM → use logs/traces instead

Systems:
  Prometheus: pull-based metrics, short retention, PromQL, ecosystem standard
  InfluxDB: push-based, longer retention, Flux query language
  TimescaleDB: Postgres extension — SQL + time-series optimizations
  ClickHouse: columnar OLAP, great for event analytics + time-series with joins

A time-series database is not "a database for data with timestamps" — it's a database whose entire storage model is built around the assumption that data arrives in time order, is never updated, is queried by time ranges, and must be compressed and downsampled efficiently. Relational databases can store time-series data; they just weren't designed for this access pattern and suffer proportionally at the write volumes and query patterns time-series workloads demand.