using-timeseries-databases

时序数据库

为带时间戳的数据（指标、物联网传感器、金融行情、日志）实现高效存储和查询。

数据库选择

根据主要用例进行选择：

TimescaleDB - PostgreSQL 扩展

• 适用场景：已使用 PostgreSQL，需要 SQL + JOIN，以及混合工作负载
• 查询：标准 SQL
• 扩展能力：每秒 100K-1M 次插入

InfluxDB - 专用时序数据库

• 适用场景：DevOps 指标、Prometheus 集成、Telegraf 生态
• 查询：InfluxQL 或 Flux
• 扩展能力：每秒 500K-1M 个数据点

ClickHouse - 列式分析数据库

• 适用场景：需要最快的聚合、分析仪表盘、日志分析
• 查询：SQL
• 扩展能力：每秒 1M-10M 次插入，查询每秒 100M-1B 行

QuestDB - 高吞吐量物联网数据库

• 适用场景：需要最高写入性能、金融行情数据
• 查询：SQL + Line Protocol
• 扩展能力：每秒 4M+ 次插入

核心模式

1. 超表（TimescaleDB）

基于时间的自动分区：

CREATE TABLE sensor_data (
  time        TIMESTAMPTZ NOT NULL,
  sensor_id   INTEGER NOT NULL,
  temperature DOUBLE PRECISION,
  humidity    DOUBLE PRECISION
);

SELECT create_hypertable('sensor_data', 'time');

优点：

• 高效地过期数据（删除旧数据块）
• 并行执行查询
• 压缩较旧的数据块（节省 10-20 倍空间）

2. 连续聚合

预计算汇总数据，加速仪表盘查询：

-- TimescaleDB: hourly rollup
CREATE MATERIALIZED VIEW sensor_data_hourly
WITH (timescaledb.continuous) AS
SELECT time_bucket('1 hour', time) AS hour,
       sensor_id,
       AVG(temperature) AS avg_temp,
       MAX(temperature) AS max_temp,
       MIN(temperature) AS min_temp
FROM sensor_data
GROUP BY hour, sensor_id;

-- Auto-refresh policy
SELECT add_continuous_aggregate_policy('sensor_data_hourly',
  start_offset => INTERVAL '3 hours',
  end_offset => INTERVAL '1 hour',
  schedule_interval => INTERVAL '1 hour');

查询策略：

• 短时间范围（最近一小时）：原始数据
• 中等时间范围（最近一天）：1 分钟汇总
• 长时间范围（最近一个月）：1 小时汇总
• 超长时间范围（最近一年）：每日汇总

3. 保留策略

自动过期数据：

-- TimescaleDB: delete data older than 90 days
SELECT add_retention_policy('sensor_data', INTERVAL '90 days');

常见模式：

• 原始数据：7-90 天
• 每小时汇总：1-2 年
• 每日汇总：永久保留

4. 可视化降采样

使用 LTTB（最大三角形三桶）算法减少图表中的数据点。

问题：浏览器无法流畅地渲染 1M 个数据点 解决方案：降采样至 500-1000 个数据点，同时保留视觉保真度

-- TimescaleDB toolkit LTTB
SELECT time, value
FROM lttb(
  'SELECT time, temperature FROM sensor_data WHERE sensor_id = 1',
  1000  -- target number of points
);

阈值：

• 少于 1,000 个数据点：不降采样
• 1,000-10,000 个数据点：使用 LTTB 降至 1,000 个数据点
• 10,000+ 个数据点：使用 LTTB 降至 500 个数据点，或使用预聚合数据

仪表盘集成

时序数据库是实时仪表盘的主要数据源。

各组件的查询模式：

组件	查询模式	示例
KPI 卡片	最新值	SELECT temperature FROM sensors ORDER BY time DESC LIMIT 1
趋势图	按时间分桶求平均值	SELECT time_bucket('5m', time), AVG(cpu) GROUP BY 1
热力图	多指标时间窗口	SELECT hour, AVG(cpu), AVG(memory) GROUP BY hour
告警	阈值检查	SELECT COUNT(*) WHERE cpu > 80 AND time > NOW() - '5m'

数据流：

1. 摄取指标（Prometheus、MQTT、应用事件）
2. 将数据与连续聚合一起存储在时序数据库中
3. 应用保留策略（原始数据：30 天，汇总数据：1 年）
4. 查询层将数据降采样至最佳数据点数（LTTB）
5. 前端使用 Recharts/visx 渲染

自动刷新间隔：

• 严重告警：1-5 秒（WebSocket）
• 运维仪表盘：10-30 秒（轮询）
• 分析仪表盘：1-5 分钟（缓存）
• 历史报告：仅按需刷新

数据库专属详情

实现指南请参阅：

• references/timescaledb.md - 设置、调优、压缩
• references/influxdb.md - InfluxQL/Flux、保留策略
• references/clickhouse.md - MergeTree 引擎、集群
• references/questdb.md - Line Protocol、SIMD 优化

降采样实现请参阅：

• references/downsampling-strategies.md - LTTB 算法、聚合方法

示例：

• examples/metrics-dashboard-backend/ - TimescaleDB + FastAPI
• examples/iot-data-pipeline/ - 用于物联网的 InfluxDB + Go

脚本：

• scripts/setup_hypertable.py - 创建 TimescaleDB 超表
• scripts/generate_retention_policy.py - 生成保留策略

性能优化

写入优化

批量插入：

数据库	批次大小	预期吞吐量
TimescaleDB	1,000-10,000	100K-1M 行/秒
InfluxDB	5,000+	500K-1M 点/秒
ClickHouse	10,000-100,000	1M-10M 行/秒
QuestDB	10,000+	4M+ 行/秒

查询优化

规则 1：始终先按时间筛选（已建立索引）

-- BAD: Full table scan
SELECT * FROM metrics WHERE metric_name = 'cpu';

-- GOOD: Time index used
SELECT * FROM metrics
WHERE time > NOW() - INTERVAL '1 hour'
  AND metric_name = 'cpu';

规则 2：使用连续聚合处理仪表板查询

-- BAD: Aggregate 1B rows every dashboard load
SELECT time_bucket('1 hour', time), AVG(cpu)
FROM metrics
WHERE time > NOW() - INTERVAL '30 days'
GROUP BY 1;

-- GOOD: Query pre-computed rollup
SELECT hour, avg_cpu
FROM metrics_hourly
WHERE hour > NOW() - INTERVAL '30 days';

规则 3：为可视化进行降采样

// Request optimal point count
const points = Math.min(1000, chartWidth);
const query = `/api/metrics?start=${start}&end=${end}&points=${points}`;

使用场景

DevOps 监控 → InfluxDB 或 TimescaleDB

• Prometheus 指标、应用程序跟踪、基础设施

IoT 传感器数据 → QuestDB 或 TimescaleDB

• 数百万台设备、高写入吞吐量

金融逐笔数据 → QuestDB 或 ClickHouse

• 亚毫秒级查询、OHLC 聚合

用户分析 → ClickHouse

• 事件跟踪、日活跃用户、漏斗分析

实时仪表板 → 任意 TSDB + 连续聚合

• 预计算汇总、WebSocket 流式传输、LTTB 降采样