✍️ 필사 모드: 시계열 데이터베이스 완전 가이드 2025: TimescaleDB, InfluxDB, Prometheus, ClickHouse 비교

2025-04-15 10:00:00, sensor_1, temperature, 23.5
2025-04-15 10:00:01, sensor_1, temperature, 23.6
2025-04-15 10:00:02, sensor_1, temperature, 23.5
...

CREATE TABLE metrics (
  time TIMESTAMP,
  device_id INT,
  metric_name VARCHAR(50),
  value FLOAT
);

CREATE INDEX idx_time ON metrics(time);

CREATE EXTENSION timescaledb;

CREATE TABLE metrics (
  time TIMESTAMPTZ NOT NULL,
  device_id INT,
  temperature DOUBLE PRECISION
);

-- 하이퍼테이블로 변환
SELECT create_hypertable('metrics', 'time', chunk_time_interval => INTERVAL '1 day');

from(bucket: "metrics")
  |> range(start: -1h)
  |> filter(fn: (r) => r._measurement == "cpu")
  |> aggregateWindow(every: 1m, fn: mean)

scrape_configs:
  - job_name: 'app'
    static_configs:
      - targets: ['localhost:8080']

# 지난 5분 평균 응답 시간
rate(http_request_duration_seconds_sum[5m]) / rate(http_request_duration_seconds_count[5m])

# CPU 사용률
100 - (avg by (instance) (rate(node_cpu_seconds_total{mode="idle"}[5m])) * 100)

CREATE TABLE metrics (
  time DateTime,
  device_id UInt32,
  temperature Float32
) ENGINE = MergeTree()
ORDER BY (device_id, time);

http_requests_total{method="GET", path="/api/users", status="200"}
http_requests_total{method="POST", path="/api/users", status="200"}
http_requests_total{method="GET", path="/api/users", status="500"}
...

http_requests_total{
  method="GET",         # 5개
  path="/api/users",    # 100개
  status="200",         # 10개
  user_id="12345",      # 1000000개 ← 위험!
}

path="/api/users/12345" → path="/api/users/{id}"

사용자별 → 사용자 그룹별로 집계

사용자 ID는 레이블 X, 이벤트 데이터로

# Prometheus에서 가장 큰 메트릭
topk(10, count by (__name__)({__name__=~".+"}))

T1=1000, T2=1010, T3=1020, T4=1030
↓
1000, +10, +0, +0  (10은 한 번만, 나머지는 0)

1000개 메트릭 × 초당 1개 × 1년 = 31,536,000,000 포인트

압축 전: 16 bytes × 31B = 504 GB
Gorilla: 1.37 bytes × 31B = 43 GB

-- 매 1시간마다 평균/최대/최소 미리 계산
CREATE MATERIALIZED VIEW metrics_hourly
WITH (timescaledb.continuous) AS
SELECT
  time_bucket('1 hour', time) AS hour,
  device_id,
  AVG(temperature) AS avg_temp,
  MAX(temperature) AS max_temp,
  MIN(temperature) AS min_temp,
  COUNT(*) AS sample_count
FROM metrics
GROUP BY hour, device_id;

-- 자동 갱신 정책
SELECT add_continuous_aggregate_policy('metrics_hourly',
  start_offset => INTERVAL '3 hours',
  end_offset => INTERVAL '1 hour',
  schedule_interval => INTERVAL '1 hour');

-- 30일 이상 raw 데이터 자동 삭제
SELECT add_retention_policy('metrics', INTERVAL '30 days');

-- 1년 이상 hourly 데이터 자동 삭제
SELECT add_retention_policy('metrics_hourly', INTERVAL '1 year');

# 인스턴스의 현재 CPU 사용률
node_cpu_seconds_total

# 특정 레이블 필터
node_cpu_seconds_total{instance="server-1", mode="user"}

# 여러 레이블
node_cpu_seconds_total{instance=~"server-.*", mode!="idle"}

# 지난 5분간의 데이터
node_cpu_seconds_total[5m]

# 지난 5분 평균 변화율
rate(http_requests_total[5m])

# 지난 5분 최대값 - 최소값
max_over_time(temperature[5m]) - min_over_time(temperature[5m])

# 백분위수
histogram_quantile(0.99, rate(http_request_duration_seconds_bucket[5m]))

# 인스턴스별 합산
sum by (instance) (rate(http_requests_total[5m]))

# 상위 10개
topk(10, rate(http_requests_total[5m]))

# 평균
avg by (job) (cpu_usage_percent)

# 5분간 CPU 사용률 80% 초과
avg(cpu_usage_percent) > 80

# 1분간 5xx 비율 1% 초과
sum(rate(http_requests_total{status=~"5.."}[1m])) 
  / sum(rate(http_requests_total[1m])) > 0.01

[Apps] → /metrics endpoint
            ↓ (scrape)
       [Prometheus]
            ↓ (long-term)
        [Thanos/Cortex]
            ↓ (query)
         [Grafana]
            ↓
        [User/Alert]

const express = require('express')
const promClient = require('prom-client')

const register = new promClient.Registry()
promClient.collectDefaultMetrics({ register })

const httpRequestDuration = new promClient.Histogram({
  name: 'http_request_duration_seconds',
  help: 'Duration of HTTP requests in seconds',
  labelNames: ['method', 'route', 'status'],
  buckets: [0.005, 0.01, 0.025, 0.05, 0.1, 0.25, 0.5, 1, 2.5, 5, 10]
})
register.registerMetric(httpRequestDuration)

const app = express()
app.use((req, res, next) => {
  const end = httpRequestDuration.startTimer()
  res.on('finish', () => {
    end({ method: req.method, route: req.route?.path || 'unknown', status: res.statusCode })
  })
  next()
})

app.get('/metrics', async (req, res) => {
  res.set('Content-Type', register.contentType)
  res.end(await register.metrics())
})

app.listen(8080)

global:
  scrape_interval: 15s

scrape_configs:
  - job_name: 'my-app'
    static_configs:
      - targets: ['app:8080']
    
  - job_name: 'kubernetes-pods'
    kubernetes_sd_configs:
      - role: pod
    relabel_configs:
      - source_labels: [__meta_kubernetes_pod_annotation_prometheus_io_scrape]
        action: keep
        regex: true

# Rate
sum(rate(http_requests_total[5m])) by (route)

# Errors
sum(rate(http_requests_total{status=~"5.."}[5m])) by (route)

# Duration (p99)
histogram_quantile(0.99,
  sum(rate(http_request_duration_seconds_bucket[5m])) by (le, route)
)

groups:
  - name: app
    rules:
      - alert: HighErrorRate
        expr: sum(rate(http_requests_total{status=~"5.."}[5m])) / sum(rate(http_requests_total[5m])) > 0.01
        for: 5m
        annotations:
          summary: "High error rate on {{ $labels.instance }}"

1,000,000 디바이스 × 1 point/s × 86,400 s/day = 86.4B/day
86.4B/day × 365 days = 31.5 trillion points/year

압축 (1.37 bytes): 43 TB

[100만 디바이스]
      ↓ MQTT
[Mosquitto / EMQX]
      ↓
[Telegraf / Vector]
      ↓
[TimescaleDB Cluster] or [VictoriaMetrics Cluster]
      ↓
[Grafana]

CREATE TABLE sensors (
  time TIMESTAMPTZ NOT NULL,
  device_id BIGINT,
  metric_type SMALLINT,  -- enum (1=temp, 2=humidity, ...)
  value DOUBLE PRECISION
);

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

-- 인덱스
CREATE INDEX ON sensors(device_id, time DESC);

-- 압축
ALTER TABLE sensors SET (
  timescaledb.compress,
  timescaledb.compress_segmentby = 'device_id'
);

SELECT add_compression_policy('sensors', INTERVAL '7 days');

CREATE TABLE trades (
  symbol String,
  trade_time DateTime64(6, 'UTC'),
  price Float64,
  volume UInt32,
  side Enum8('buy' = 1, 'sell' = 2)
) ENGINE = MergeTree()
PARTITION BY toYYYYMM(trade_time)
ORDER BY (symbol, trade_time);

-- 1분봉 집계
SELECT
  symbol,
  toStartOfMinute(trade_time) AS minute,
  argMin(price, trade_time) AS open,
  max(price) AS high,
  min(price) AS low,
  argMax(price, trade_time) AS close,
  sum(volume) AS volume
FROM trades
WHERE symbol = 'AAPL' AND trade_time >= now() - INTERVAL 1 DAY
GROUP BY symbol, minute
ORDER BY minute;

PostgreSQL을 이미 사용 중인가?
├─ YES → TimescaleDB (시작점)
└─ NO
   ├─ 모니터링/메트릭 전용?
   │   ├─ 작은 규모 → Prometheus
   │   └─ 큰 규모 → VictoriaMetrics
   ├─ IoT/일반?
   │   └─ InfluxDB v3 또는 TimescaleDB
   └─ 분석 + 시계열?
       └─ ClickHouse