Time-Series Data

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

-- Naive approach: store metrics in a relational table
CREATE TABLE metrics (
    id BIGSERIAL PRIMARY KEY,
    timestamp TIMESTAMPTZ NOT NULL,
    metric_name VARCHAR(255) NOT NULL,
    host VARCHAR(255) NOT NULL,
    datacenter VARCHAR(64),
    value DOUBLE PRECISION NOT NULL
);
 
-- Index for time-range queries
CREATE INDEX idx_metrics_timestamp ON metrics(timestamp);
 
-- Index for filtering by host
CREATE INDEX idx_metrics_host_time ON metrics(host, timestamp);
 
-- Compound index for common query patterns
CREATE INDEX idx_metrics_name_host_time 
    ON metrics(metric_name, host, timestamp);

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31

┌─────────────────────────────────────────────────────────────────────┐
│                        TIME-SERIES DATA MODEL                        │
└─────────────────────────────────────────────────────────────────────┘
 
MEASUREMENT (or METRIC):
┌─────────────────────────────────────────────────────────────────────┐
│  measurement: "cpu_usage"                                           │
│  ┌──────────────────────────────────────────────────────────────┐   │
│  │  TAGS (Indexed, Low Cardinality Preferred):                  │   │
│  │    host = "server-01"                                         │   │
│  │    region = "us-east-1"                                       │   │
│  │    env = "production"                                         │   │
│  └──────────────────────────────────────────────────────────────┘   │
│  ┌──────────────────────────────────────────────────────────────┐   │
│  │  FIELDS (Not Indexed, Multiple Values):                      │   │
│  │    user = 45.2                                                │   │
│  │    system = 22.1                                              │   │
│  │    idle = 32.7                                                │   │
│  └──────────────────────────────────────────────────────────────┘   │
│  ┌──────────────────────────────────────────────────────────────┐   │
│  │  TIMESTAMP:                                                   │   │
│  │    time = 2024-01-15T14:30:00.000000000Z                     │   │
│  └──────────────────────────────────────────────────────────────┘   │
└─────────────────────────────────────────────────────────────────────┘
 
SERIES = Measurement + Unique Tag Set
  Series 1: cpu_usage{host="server-01",region="us-east-1"} 
  Series 2: cpu_usage{host="server-02",region="us-east-1"}
  Series 3: cpu_usage{host="server-01",region="eu-west-1"}
 
Each series is stored and queried independently, enabling parallel processing.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30

TIME-SERIES DATABASE STORAGE LAYOUT
====================================
 
Data organized by time first, then by series:
 
Day 1 Partition (2024-01-15)
├── Series Index (inverted index for tags)
│   ├── host=server-01 → [series_1, series_4, series_7]
│   ├── host=server-02 → [series_2, series_5, series_8]
│   └── region=us-east → [series_1, series_2, series_3]
│
├── Series 1: cpu_usage{host=server-01,region=us-east}
│   ├── Timestamps: [t1, t2, t3, ...] (delta-encoded)
│   ├── Field 'user': [45.2, 46.1, 44.8, ...] (XOR compressed)
│   └── Field 'system': [22.1, 23.0, 21.9, ...] (XOR compressed)
│
├── Series 2: cpu_usage{host=server-02,region=us-east}
│   ├── Timestamps: [t1, t2, t3, ...]
│   ├── Field 'user': [38.1, 39.0, 37.5, ...]
│   └── Field 'system': [18.4, 19.1, 18.0, ...]
│
└── Metadata Block
    ├── Min/Max timestamps
    ├── Series count
    └── Compression statistics
 
Day 2 Partition (2024-01-16)
└── ... (same structure)
 
Retention: Simply delete entire day partitions older than threshold

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51

-- PATTERN 1: Time-Range Selection with Aggregation
-- "What was the average CPU usage per host in the last hour?"
SELECT 
    host,
    time_bucket('5 minutes', time) AS bucket,
    AVG(cpu_usage) AS avg_cpu
FROM metrics
WHERE time > NOW() - INTERVAL '1 hour'
GROUP BY host, bucket
ORDER BY bucket DESC;
 
-- PATTERN 2: Downsampling
-- "Give me hourly averages instead of raw second-level data"
SELECT 
    time_bucket('1 hour', time) AS hour,
    AVG(value) AS avg_val,
    MAX(value) AS max_val,
    PERCENTILE_CONT(0.95) WITHIN GROUP (ORDER BY value) AS p95
FROM measurements
WHERE time > NOW() - INTERVAL '7 days'
GROUP BY hour
ORDER BY hour;
 
-- PATTERN 3: Rate Calculation
-- "What's the request rate per second over time?"
SELECT 
    time_bucket('1 minute', time) AS minute,
    (MAX(counter_value) - MIN(counter_value)) / 60.0 AS requests_per_sec
FROM http_request_total
WHERE time > NOW() - INTERVAL '30 minutes'
GROUP BY minute;
 
-- PATTERN 4: Top-N Analysis
-- "Which hosts had the highest error rates?"
SELECT 
    host,
    SUM(errors) / SUM(requests) AS error_rate
FROM web_metrics
WHERE time > NOW() - INTERVAL '24 hours'
GROUP BY host
ORDER BY error_rate DESC
LIMIT 10;
 
-- PATTERN 5: Gap Filling
-- "Show me values for every minute, filling gaps with interpolation"
SELECT 
    time_bucket_gapfill('1 minute', time) AS minute,
    locf(AVG(temperature)) AS temperature  -- Last Observation Carried Forward
FROM sensor_data
WHERE time BETWEEN '2024-01-15' AND '2024-01-16'
GROUP BY minute;