时序数据库设计：高效存储与查询时间序列数据

时序数据的独特挑战

时序数据（Time-Series Data）是以时间戳为主要维度、按时间顺序产生的数据，广泛存在于IoT传感器、应用性能监控（APM）、金融行情、用户行为日志等场景。

与普通关系型数据相比，时序数据具有以下独特特性：

• 写入密集：数据持续高频写入，可能每秒产生数十万条记录
• 单调递增：时间戳总是向前，几乎不存在历史数据的更新
• 查询以时间范围为中心：绝大多数查询都包含时间范围过滤
• 数据老化：旧数据价值递减，通常需要降采样保留或直接删除
• 压缩效果好：相邻时间点的数据变化幅度通常较小，压缩比高

关系型数据库存储时序数据的局限

-- 朴素的时序表设计（关系型数据库）
CREATE TABLE sensor_readings (
  id          BIGSERIAL PRIMARY KEY,
  sensor_id   UUID NOT NULL,
  temperature DECIMAL(5,2),
  humidity    DECIMAL(5,2),
  pressure    DECIMAL(7,2),
  recorded_at TIMESTAMP NOT NULL
);

CREATE INDEX idx_sensor_readings_time ON sensor_readings (recorded_at DESC);
CREATE INDEX idx_sensor_readings_sensor_time ON sensor_readings (sensor_id, recorded_at DESC);

-- 问题1：单表数据量增长无上限，查询随数据量线性变慢
-- 问题2：UPDATE/DELETE历史数据代价高（但时序场景几乎不需要）
-- 问题3：标准B-Tree索引对时序查询的效率不如专用结构
-- 问题4：缺乏内置的降采样和数据生命周期管理

PostgreSQL + TimescaleDB：最佳平衡点

对于希望保留SQL生态系统、同时获得时序优化能力的团队，TimescaleDB是PostgreSQL的开源扩展：

-- 安装TimescaleDB扩展
CREATE EXTENSION IF NOT EXISTS timescaledb;

-- 创建超表（Hypertable）：自动按时间分区
CREATE TABLE sensor_readings (
  sensor_id   UUID NOT NULL,
  temperature DECIMAL(5,2),
  humidity    DECIMAL(5,2),
  pressure    DECIMAL(7,2),
  recorded_at TIMESTAMPTZ NOT NULL
);

-- 转换为超表，按1小时自动分区（chunk）
SELECT create_hypertable(
  'sensor_readings',
  'recorded_at',
  chunk_time_interval => INTERVAL '1 hour'
);

-- 创建复合索引（超表会自动为时间列建立高效索引）
CREATE INDEX idx_sensor_readings_sensor
ON sensor_readings (sensor_id, recorded_at DESC);

-- 数据压缩配置（相比原始数据可节省90%+存储空间）
ALTER TABLE sensor_readings SET (
  timescaledb.compress,
  timescaledb.compress_segmentby = 'sensor_id'
);

-- 自动压缩7天前的数据
SELECT add_compression_policy('sensor_readings', INTERVAL '7 days');

时序数据的聚合查询

-- TimescaleDB特有：time_bucket函数（比date_trunc更强大）
-- 查询每个传感器过去24小时的每小时平均温度
SELECT
  sensor_id,
  time_bucket('1 hour', recorded_at) AS bucket,
  AVG(temperature) AS avg_temp,
  MAX(temperature) AS max_temp,
  MIN(temperature) AS min_temp,
  COUNT(*) AS reading_count
FROM sensor_readings
WHERE recorded_at >= NOW() - INTERVAL '24 hours'
GROUP BY sensor_id, bucket
ORDER BY sensor_id, bucket;

-- 连续聚合（物化视图，自动增量更新）
CREATE MATERIALIZED VIEW sensor_hourly
WITH (timescaledb.continuous) AS
SELECT
  sensor_id,
  time_bucket('1 hour', recorded_at) AS bucket,
  AVG(temperature) AS avg_temp,
  AVG(humidity) AS avg_humidity,
  COUNT(*) AS sample_count
FROM sensor_readings
GROUP BY sensor_id, bucket;

-- 设置自动刷新策略
SELECT add_continuous_aggregate_policy(
  'sensor_hourly',
  start_offset => INTERVAL '3 hours',
  end_offset   => INTERVAL '1 hour',
  schedule_interval => INTERVAL '1 hour'
);

数据生命周期管理

-- 数据分层保留策略
-- 原始数据保留7天
SELECT add_retention_policy('sensor_readings', INTERVAL '7 days');

-- 小时级聚合保留30天
SELECT add_retention_policy('sensor_hourly', INTERVAL '30 days');

-- 日级聚合（永久保留）
CREATE MATERIALIZED VIEW sensor_daily
WITH (timescaledb.continuous) AS
SELECT
  sensor_id,
  time_bucket('1 day', recorded_at) AS bucket,
  AVG(temperature) AS avg_temp,
  AVG(humidity)    AS avg_humidity,
  MAX(temperature) AS max_temp,
  MIN(temperature) AS min_temp
FROM sensor_readings
GROUP BY sensor_id, bucket;

-- 降采样迁移：将原始数据降采样后归档
INSERT INTO sensor_daily_archive
SELECT
  sensor_id,
  date_trunc('day', recorded_at) AS day,
  AVG(temperature), AVG(humidity),
  MAX(temperature), MIN(temperature)
FROM sensor_readings
WHERE recorded_at < NOW() - INTERVAL '30 days'
GROUP BY sensor_id, day
ON CONFLICT (sensor_id, day) DO NOTHING;

专用时序数据库的选择

当数据规模超出PostgreSQL（即使有TimescaleDB）的处理能力时，可以考虑专用时序数据库：

选择标准：数据规模 < 10TB + 需要JOIN关系型数据 → TimescaleDB；数据规模 > 10TB或写入超过100万/秒 → 专用时序数据库。

使用Erdmates设计时序数据架构

在设计IoT平台或监控系统时，Erdmates帮助您可视化传感器设备表、读数表、聚合表和设备分组表之间的关系。清晰的ERD使团队在架构评审时能够快速理解数据流向——从原始采集到多级聚合的完整链路。即使引入了TimescaleDB超表，实体间的关系设计逻辑与普通关系型数据库完全一致，ERD同样适用。

时序数据库设计的核心原则是：写入优先于读取（不可变追加），时间是第一公民，数据生命周期需要提前规划。在项目初期就考虑好数据保留策略和降采样方案，比事后数据治理要容易得多。