Time-Series Databases

What it is

Time-series databases (TSDBs) are storage systems optimized for data where each record is associated with a timestamp and the primary query patterns involve time-range scans, aggregations over time windows, and high-throughput sequential writes. Unlike general-purpose databases that store arbitrary records, TSDBs are designed around the assumption that data arrives in roughly chronological order, queries almost always filter by time range, and old data can be compressed or downsampled without loss of meaningful signal.

Major time-series databases by category: InfluxDB (purpose-built TSDB, InfluxQL + Flux query languages, line protocol ingestion); TimescaleDB (PostgreSQL extension — hypertables, continuous aggregates, native SQL); Prometheus (pull-based metrics collection for monitoring, PromQL, short retention, federation); VictoriaMetrics (Prometheus-compatible, high compression, long-term storage); Amazon Timestream (serverless managed TSDB); OpenTSDB (HBase-backed, for Hadoop ecosystems); QuestDB (high-performance columnar TSDB with SQL).

Why it exists

General-purpose relational databases degrade badly on time-series workloads. A table receiving 1 million inserts per second from IoT sensors creates severe write amplification on B-tree indexes. Queries like "average CPU usage per 5-minute bucket for the last 24 hours across 10,000 hosts" require scanning billions of rows with GROUP BY on timestamp ranges — a pattern that relational databases handle poorly without specialized partitioning and columnar storage. Time-series databases solve this by: (1) organizing data in time-ordered chunks/shards that can be compressed and evicted independently, (2) maintaining time-indexed column stores that enable vectorized time-range scans, (3) providing native time-bucket aggregation functions, (4) automating data lifecycle (retention policies, downsampling older data).

When to use

• Infrastructure and application monitoring: CPU, memory, request rate, error rate, latency percentiles — all time-series data generated continuously by every service.
• IoT sensor data: temperature, pressure, vibration, power consumption from thousands or millions of devices writing data every second.
• Financial market data: stock prices, order book updates, trade executions — high-frequency tick data that must be stored and queried by time range.
• User behavior analytics: page views, click streams, funnel events where time-windowed aggregations (sessions per hour, conversion rate per day) are the primary query pattern.
• When data retention policies and automatic downsampling of old data are important — storing raw sensor data at 1-second resolution forever is impractical; TSDBs automate rollup to 1-minute, then 1-hour resolution as data ages.

When not to use

• Don't use a TSDB for general application data — user accounts, orders, and relationships are not time-series data and the TSDB data model is a poor fit.
• Don't use Prometheus for long-term storage — Prometheus has a local retention window (typically 15 days) and is designed for real-time monitoring, not historical analysis; use VictoriaMetrics, Thanos, or Cortex for long-term storage of Prometheus metrics.
• Don't store high-cardinality labels in Prometheus — each unique combination of label values creates a new time series; 10 million unique user IDs as a label creates 10 million series, exhausting memory and causing OOM crashes.
• Don't use a TSDB if your time-series queries need to JOIN with non-time-series data from a relational database — the data models don't compose; consider TimescaleDB (PostgreSQL-based) which allows standard SQL JOINs across hypertables and regular tables.

Typical architecture

  InfluxDB data model:
  Measurement: cpu_usage
  Tags (indexed):  host=web-01, region=us-east, env=prod
  Fields (values): usage_percent=72.4, load_avg=2.1
  Timestamp:       2024-01-15T10:00:00Z

  Series = measurement + unique tag set
  High cardinality problem: 1M unique user_ids as tags = 1M series

  TimescaleDB hypertable (PostgreSQL):
  CREATE TABLE metrics (
    time        TIMESTAMPTZ NOT NULL,
    host        TEXT,
    cpu_percent DOUBLE PRECISION
  );
  SELECT create_hypertable('metrics', 'time', chunk_time_interval => INTERVAL '1 day');
  -- Data auto-partitioned into daily chunks
  -- Old chunks compressed: chunk_compression SEGMENTBY host ORDERBY time DESC

  Continuous aggregate (TimescaleDB):
  CREATE MATERIALIZED VIEW cpu_5min
  WITH (timescaledb.continuous) AS
    SELECT time_bucket('5 minutes', time) AS bucket,
           host, AVG(cpu_percent) AS avg_cpu
    FROM metrics GROUP BY 1, 2;

  Retention policy (InfluxDB):
  raw data:       30 days  (1-second resolution)
  1-min rollup:   1 year   (continuous query downsamples)
  1-hour rollup:  forever  (trend data)
          

Pros and cons

Implementation notes

In InfluxDB, use the line protocol for bulk ingestion — it's significantly more efficient than JSON over HTTP. Line protocol format: measurement[,tag_key=tag_value]... field_key=field_value[,field_key2=field_value2]... [unix_nanosecond_timestamp]. Example: cpu_usage,host=web-01,env=prod usage_percent=72.4,load_avg=2.1 1705312800000000000. Batch writes (1000+ points per HTTP request) and use of the /api/v2/write endpoint significantly increase throughput compared to single-point writes. Keep tag cardinality below 100,000 unique combinations per measurement to avoid index memory exhaustion.

For TimescaleDB, enable chunk compression for chunks older than your query hot window: ALTER TABLE metrics SET (timescaledb.compress, timescaledb.compress_segmentby = 'host') and SELECT add_compression_policy('metrics', INTERVAL '7 days'). Compression typically achieves 90–95% space reduction for time-series numeric data. Continuous aggregates handle the downsampling lifecycle: define a 1-minute aggregate from the raw data and a 1-hour aggregate from the 1-minute aggregate; set refresh policies to keep each materialized view up to date automatically.

Common failure modes

• Prometheus cardinality explosion: A developer adds a user_id label to a business metric; with 1 million users, Prometheus creates 1 million time series; memory usage spikes from 4GB to 40GB; Prometheus crashes with OOM.
• InfluxDB series cardinality: Similar to Prometheus — using high-cardinality values (request IDs, session IDs) as tags creates unbounded series cardinality; the inverted index for tags exhausts memory.
• Prometheus long-term storage gap: The team uses Prometheus for 2 years with 15-day retention; they realize they need 1-year trend analysis; the data doesn't exist; no long-term storage solution (Thanos/Cortex/VictoriaMetrics) was configured.
• Late data write failure: An IoT gateway buffers data during a network outage and retries; the 6-hour-old data is outside InfluxDB's default write consistency window; the data is rejected silently or causes shard write failures.
• Missing continuous aggregate refresh: A continuous aggregate refresh policy is not set; the materialized view becomes hours or days stale; dashboards show outdated data; the issue is only noticed when someone manually inspects refresh lag.

Decision checklist

• Is the primary workload time-stamped sequential writes with time-range aggregation queries? If yes, a TSDB is appropriate.
• What is the expected write throughput and cardinality? Ensure the chosen TSDB can handle the series cardinality without memory issues.
• Is long-term storage needed beyond Prometheus's local retention window? Configure Thanos, VictoriaMetrics, or Cortex for remote write/read.
• Are retention policies and downsampling schedules defined for each data stream to control storage growth?
• Are all labels/tags in Prometheus kept to bounded cardinality (no per-request, per-user, or per-session labels)?
• If using TimescaleDB, are chunk compression and continuous aggregate refresh policies configured and monitored?

Example use cases

• Infrastructure monitoring stack: Prometheus scrapes Kubernetes node and pod metrics every 15 seconds. Grafana dashboards query Prometheus for real-time metrics. VictoriaMetrics receives remote writes from Prometheus and stores 12 months of downsampled history for trend analysis and capacity planning.
• Industrial IoT platform: 50,000 sensors send temperature and vibration readings every second (50M data points/sec total). InfluxDB ingests via line protocol, retains 30 days of raw data, downsample to 1-minute averages retained for 1 year, and 1-hour averages retained permanently. Alerts trigger when temperature exceeds thresholds.
• Application performance monitoring: TimescaleDB stores request latency, error counts, and throughput for 200 microservices, partitioned by service and time. Continuous aggregates provide pre-computed p50/p95/p99 latency per 5-minute window. SQL JOINs with the service registry table enable context-enriched queries impossible in InfluxDB.

Related patterns

• Polyglot Persistence — Time-series databases are typically one component in a polyglot persistence architecture.
• Graph Databases — Another specialized database type for relationship-heavy data, contrasting with time-series' sequential access pattern.
• Observability & Operations — Time-series databases are the storage layer for metrics in the observability stack.

Further reading

• InfluxDB Schema Design Best Practices
• TimescaleDB Compression Documentation
• Prometheus Metric and Label Naming Best Practices