Understanding Time‑Series Databases: InfluxDB, TimescaleDB, and Prometheus Explained

Time‑series databases are specialized for storing and querying data ordered by timestamps, making them ideal for monitoring, IoT, financial market data, and similar scenarios.

1. Overview of Time‑Series Databases

1.1 What is time‑series data

Time‑series data consists of data points arranged chronologically, for example:

时序数据示例
┌─────────────────────────────────────────────────────────────┐
│                     时间戳   温度   湿度   设备ID               │
│ 2024-01-01 10:00   25.5   60   sensor-001                │
│ 2024-01-01 10:01   25.6   61   sensor-001                │
│ 2024-01-01 10:02   25.4   60   sensor-001                │
│ 2024-01-01 10:00   22.0   55   sensor-002                │
│ 2024-01-01 10:01   22.1   56   sensor-002                │
└─────────────────────────────────────────────────────────────┘

1.2 Characteristics

High write volume : continuous high‑speed ingestion.

Cold data : older records are rarely queried.

Aggregation needs : frequent statistical aggregation.

TTL : data has a defined lifetime.

1.3 Typical Scenarios

Monitoring metrics – servers, applications.

IoT – sensor streams.

Financial market data – stock prices.

Log analysis – application logs.

Performance tracing – user behavior.

Energy management – electricity/water meters.

2. InfluxDB

2.1 Data Model

InfluxDB 数据模型
┌─────────────────────────────────────────────────────────────┐
│ Database                                                    │
│ └─► Measurement (similar to a table)                        │
│      ├─► Tag Set (indexed strings) – e.g., device_id, …    │
│      └─► Field Set (values) – e.g., temperature, …        │
│ Point = Timestamp + Tags + Fields                           │
│ Series = Measurement + Tag combination                     │
└─────────────────────────────────────────────────────────────┘

2.2 Line Protocol

measurement,tag1=value1,tag2=value2 field1=value1,field2=value2 timestamp

# Example
temperature,device_id=sensor001,location=beijing value=25.5 1704067200000000000
humidity,device_id=sensor001,location=beijing value=60 1704067200000000000

2.3 Operations (Python client)

from influxdb import InfluxDBClient

client = InfluxDBClient(host='localhost', port=8086, database='iot')

# Write data
json_body = [
    {
        "measurement": "temperature",
        "tags": {"device_id": "sensor001", "location": "beijing"},
        "time": "2024-01-01T10:00:00Z",
        "fields": {"value": 25.5}
    }
]
client.write_points(json_body)

# Query
result = client.query('''
    SELECT mean(value)
    FROM temperature
    WHERE time > now() - 24h
    GROUP BY location
''')

# Continuous Query
client.query('''
    CREATE CONTINUOUS QUERY cq_1h ON iot
    BEGIN
        SELECT mean(value) AS mean_value
        INTO temperature_1h
        FROM temperature
        GROUP BY time(1h), location
    END
''')

# Retention Policy
client.query('''
    CREATE RETENTION POLICY "1h" ON iot
    DURATION 7d
    REPLICATION 1
    DEFAULT
''')

2.4 Cluster Edition (Enterprise)

# InfluxDB Enterprise configuration
meta:
  host: node1:8091
  meta-autovacuum: true

data:
  max-shard-group-duration: 1h
  max-series-per-database: 1000000
  max-values-per-tag: 100000

3. TimescaleDB

3.1 Architecture

TimescaleDB Hypertable
┌─────────────────────────────────────────────────────────────┐
│ PostgreSQL ordinary table                                 │
│   ┌─────────────────────────────────────────────────┐   │
│   │               Hypertable (auto‑partitioned)      │   │
│   │   ┌─────────┐ ┌─────────┐ ┌─────────┐            │   │
│   │   │ Chunk 1 │ │ Chunk 2 │ │ Chunk 3 │ …          │   │
│   │   │ (1 day) │ │ (1 day) │ │ (1 day) │            │   │
│   │   └─────────┘ └─────────┘ └─────────┘            │   │
│   └─────────────────────────────────────────────────┘   │
│ Each Chunk is an independent PostgreSQL table            │
└─────────────────────────────────────────────────────────────┘

3.2 Usage

-- Create hypertable
SELECT create_hypertable('sensor_data', 'time', chunk_time_interval => INTERVAL '1 day');

-- Insert data
INSERT INTO sensor_data (time, device_id, temperature, humidity)
VALUES
    ('2024-01-01 10:00:00', 'sensor001', 25.5, 60),
    ('2024-01-01 10:01:00', 'sensor001', 25.6, 61);

-- Time‑bucket aggregation (automatic parallelism)
SELECT time_bucket('1 hour', time) AS hour,
       device_id,
       AVG(temperature) AS avg_temp,
       MAX(temperature) AS max_temp
FROM sensor_data
WHERE time > NOW() - INTERVAL '24 hours'
GROUP BY hour, device_id
ORDER BY hour DESC;

-- Real‑time query
SELECT * FROM sensor_data
WHERE time > NOW() - INTERVAL '1 hour'
ORDER BY time DESC;

-- Compression policy
ALTER TABLE sensor_data SET (timescaledb.compress, timescaledb.compress_segmentby = 'device_id');
SELECT add_compression_policy('sensor_data', INTERVAL '7 days');

3.3 Features

Automatic partitioning : time‑based chunks are created automatically.

Continuous aggregation : real‑time materialized views.

Compression : old data is compressed, saving up to 10× space.

Data retention : automatic deletion of expired rows.

Preferred domains : query patterns are optimized for time‑series workloads.

4. Prometheus

4.1 Data Model

Prometheus 数据模型
┌─────────────────────────────────────────────────────────────┐
│ metric_name{label1=value1,label2=value2} value timestamp │
│ Example: go_goroutines{job="prometheus",instance="localhost:9090"} 123 1627847265 │
└─────────────────────────────────────────────────────────────┘

4.2 Metric Types

Counter : only increments (e.g., request count, error count).

Gauge : can increase or decrease (e.g., CPU usage, temperature).

Histogram : bucketed distribution (e.g., request latency).

Summary : quantiles (e.g., P50/P90/P99 response times).

4.3 Configuration (prometheus.yml)

global:
  scrape_interval: 15s
  evaluation_interval: 15s

scrape_configs:
  - job_name: 'prometheus'
    static_configs:
      - targets: ['localhost:9090']

  - job_name: 'node'
    static_configs:
      - targets: ['node-exporter:9100']

  - job_name: 'myapp'
    kubernetes_sd_configs:
      - role: pod
    relabel_configs:
      - source_labels: [__meta_kubernetes_pod_annotation_prometheus_io_scrape]
        action: keep
        regex: true

4.4 Queries

# All metrics
go_goroutines

# Filter by label
go_goroutines{job="prometheus"}

# Instant vector (latest value)
go_goroutines

# Range vector (over time)
rate(http_requests_total[5m])

# Aggregation
sum(rate(http_requests_total[5m])) by (job)

# Quantile from histogram
histogram_quantile(0.99, rate(http_request_duration_seconds_bucket[5m]))

4.5 Python Integration

from prometheus_client import Counter, Gauge, Histogram, start_http_server

# Define metrics
REQUEST_COUNT = Counter('http_requests_total', 'Total HTTP requests', ['method', 'endpoint'])
CPU_USAGE = Gauge('cpu_usage_percent', 'CPU usage percentage')
REQUEST_LATENCY = Histogram('http_request_duration_seconds', 'HTTP request latency')

# Use metrics
REQUEST_COUNT.labels(method='GET', endpoint='/api/users').inc()
CPU_USAGE.set(45.6)

# Start metric server
start_http_server(8000)