Time-Series Database Design — InfluxDB, TimescaleDB & Prometheus Guide

In this tutorial, you'll learn about Time. We cover key concepts, practical examples, and best practices.

A time-series database (TSDB) is optimized for storing and querying timestamped data points — metrics, sensor readings, and events — using specialized storage engines, downsampling, and retention policies that differ fundamentally from traditional row-oriented databases.

Why Time-Series Database Design Matters

Monitoring a modern infrastructure generates millions of data points per second. Each server emits CPU, memory, disk, and network metrics every 10-60 seconds. A Kubernetes cluster adds container-level metrics. Traditional databases can't handle this volume efficiently — a single metric stored naively would consume 40GB+ per year. Prometheus at SoundCloud ingests 2 billion time series. TimescaleDB powers IoT platforms tracking millions of devices. Doda Browser's performance monitoring pipeline streams page-load metrics to an internal TimescaleDB instance, enabling real-time dashboards for the engineering team.

Plain-Language Explanation

Imagine tracking the temperature in your house every minute for a year. That's 525,600 data points. A regular database stores each as a separate row: timestamp, temperature, room name. Querying "average temperature per day" means scanning all 525K rows. A TSDB automatically organizes data by time, compresses consecutive similar values, and pre-computes aggregates. The query that took 30 seconds now takes 30 milliseconds. And it stores the data in 80% less disk space.

graph TD
    subgraph "Ingestion"
        APP[Applications] --> WRITER[Ingestion Layer]
        WRITER --> SHARD[Time-Based Shards
hourly / daily]
    end
    subgraph "Storage"
        SHARD --> TS1[Shard: 2026-06-24T00]
        SHARD --> TS2[Shard: 2026-06-24T01]
        SHARD --> TSN[Shard: ...]
        TS1 --> CHUNK[Compressed Chunks
column-oriented]
    end
    subgraph "Query"
        QRY[Query Engine] --> SHARD
        QRY --> DSP[Downsampling Layer]
        DSP --> RET[Retention Policies]
        DSP --> CST[Continuous Queries]
    end
    style SHARD fill:#3498db,color:#fff
    style TS1 fill:#27ae60,color:#fff
    style QRY fill:#e67e22,color:#fff

Schema Design for TimescaleDB

TimescaleDB extends PostgreSQL with hypertables:

CREATE TABLE cpu_metrics (
    time TIMESTAMPTZ NOT NULL,
    host_id VARCHAR(64) NOT NULL,
    region VARCHAR(32),
    cpu_user DOUBLE PRECISION,
    cpu_system DOUBLE PRECISION,
    cpu_idle DOUBLE PRECISION,
    memory_used BIGINT,
    memory_total BIGINT
);

SELECT create_hypertable('cpu_metrics', 'time', chunk_time_interval => INTERVAL '1 day');

CREATE INDEX idx_cpu_host_time ON cpu_metrics(host_id, time DESC);

ALTER TABLE cpu_metrics SET (
    timescaledb.compress,
    timescaledb.compress_segmentby = 'host_id',
    timescaledb.compress_orderby = 'time DESC'
);

SELECT add_compression_policy('cpu_metrics', INTERVAL '7 days');
SELECT add_retention_policy('cpu_metrics', INTERVAL '90 days');

Prometheus Storage Engine

Prometheus uses a custom TSDB with 1-hour blocks:

global:
  scrape_interval: 15s
  evaluation_interval: 15s

storage:
  tsdb:
    path: /data/prometheus
    retention:
      time: 15d
      size: 50GB
    wal-compression: true

scrape_configs:
  - job_name: 'node'
    static_configs:
      - targets: ['localhost:9100']
    metrics_path: /metrics
    scrape_interval: 30s

PromQL Query Examples

# Average CPU usage per instance over 5 minutes
avg by(instance) (rate(node_cpu_seconds_total{mode="user"}[5m]))

# 99th percentile of request latency
histogram_quantile(0.99, rate(http_request_duration_seconds_bucket[5m]))

# Predict disk fullness in 24 hours
predict_linear(node_filesystem_free_bytes[1h], 86400)

InfluxDB Line Protocol

InfluxDB uses a text-based line protocol for writes:

cpu_usage,host=web-01,region=us-east cpu_user=45.2,cpu_system=12.1 1719244800000000000
cpu_usage,host=web-02,region=us-east cpu_user=38.7,cpu_system=9.8 1719244800000000000
memory_used,host=web-01,region=us-east bytes=4294967296 1719244800000000000

from influxdb_client import InfluxDBClient

client = InfluxDBClient(url="http://localhost:8086", token="my-token", org="my-org")
write_api = client.write_api()

data = {
    "measurement": "cpu_usage",
    "tags": {"host": "web-01", "region": "us-east"},
    "fields": {"cpu_user": 45.2, "cpu_system": 12.1},
    "time": "2026-06-24T00:00:00Z",
}
write_api.write(bucket="metrics", record=data)

query = 'from(bucket:"metrics") |> range(start: -1h) |> filter(fn: (r) => r._measurement == "cpu_usage")'
tables = client.query_api().query(query)
for table in tables:
    for record in table.records:
        print(f"{record['_field']}: {record['_value']} at {record['_time']}")

Downsampling and Continuous Queries

import psycopg2

conn = psycopg2.connect("dbname=tsdb")
cur = conn.cursor()

cur.execute("""
    SELECT time_bucket('5 minutes', time) AS bucket,
           host_id,
           AVG(cpu_user) AS avg_cpu_user,
           MAX(cpu_user) AS max_cpu_user,
           AVG(memory_used) AS avg_memory
    FROM cpu_metrics
    WHERE time > NOW() - INTERVAL '24 hours'
    GROUP BY bucket, host_id
    ORDER BY bucket DESC
    LIMIT 100
""")

for row in cur.fetchall():
    print(f"{row[0]}: host={row[1]}, avg_cpu={row[2]:.1f}, max_cpu={row[3]:.1f}")

Common Mistakes

1. No downsampling: Storing raw 1-second metrics forever. Costs grow linearly with time. Downsample after 7 days to 1-minute, after 30 days to 1-hour aggregates.

2. Too many tags/cardinality: InfluxDB and Prometheus struggle with high cardinality. Each unique tag combination creates a new time series. Keep tag cardinality under 100,000.

3. Ignoring timezone handling: Store all timestamps in UTC. Convert to local time in the application layer. Mixing timezones breaks range queries.

4. No retention policy: Data accumulates until disk fills. Set retention policies from day one.

5. Poor chunk interval: TimescaleDB's default 7-day chunks may be too large for high-ingestion workloads. Tune chunk_time_interval to keep chunks under 10GB.

Practice Questions

1. What makes a TSDB different from a relational database? Column-oriented storage for efficient range scans, automatic time-based partitioning, compression of consecutive values, and built-in downsampling and retention policies.

2. What is cardinality in a TSDB? The number of unique time series, defined by the combination of all tag values. High cardinality (millions of unique series) degrades write and query performance.

3. How does downsampling work? Pre-computing aggregates (avg, max, min, sum) over time windows and storing them separately. Raw data is removed after retention expires; downsampled data lives longer.

4. Why use column-oriented storage for time-series? Time-series queries typically access a few columns over a time range. Columnar storage reads only the needed columns from disk, reducing I/O by 10-100x.

5. Prometheus vs TimescaleDB: when to use which? Prometheus is best for metrics monitoring with built-in alerting. TimescaleDB is better for mixed workloads (metrics + business data) and complex SQL queries.

Mini Project

Build a simple TSDB in Python:

import time, struct, os

class SimpleTSDB:
    def __init__(self, path: str):
        self.path = path
        os.makedirs(path, exist_ok=True)

    def write(self, metric: str, value: float, ts: float = None):
        ts = ts or time.time()
        filepath = f"{self.path}/{metric}.tsdb"
        with open(filepath, "ab") as f:
            f.write(struct.pack("!dd", ts, value))

    def query(self, metric: str, start: float, end: float) -> list:
        filepath = f"{self.path}/{metric}.tsdb"
        if not os.path.exists(filepath):
            return []
        results = []
        with open(filepath, "rb") as f:
            while chunk := f.read(16):
                ts, val = struct.unpack("!dd", chunk)
                if start <= ts <= end:
                    results.append((ts, val))
        return results

tsdb = SimpleTSDB("/tmp/tsdb_demo")
for i in range(10):
    tsdb.write("temperature", 20.0 + (i * 0.5), time.time() + i)

results = tsdb.query("temperature", time.time(), time.time() + 20)
print(f"Queried {len(results)} points")
for ts, val in results[:3]:
    print(f"  t={ts}: {val:.1f}")

Cross-References

• Blob Store Design
• Key-Value Store Design
• Distributed Queue
• Caching
• Observability