含低频变化变量的时序数据高效存储与分析方案咨询

你现在按变化率拆表、仅存变化值的存储优化思路是对的，但没必要在应用层实现整套逻辑，依托TimescaleDB原生能力就能同时满足高存储压缩率、低查询复杂度的需求，不用再手动写复杂的Last-Observation-Carried-Forward(LOCF) 数据重构流程，具体可选落地路径如下：

方案1：宽表+TimescaleDB原生压缩（改造成本最低） #

• 建单张设备指标宽表，包含timestamp、device_id和全部50+指标字段，将表配置为TimescaleDB超表后开启列式压缩
• TimescaleDB的列式压缩会自动处理重复值：对变化极慢的枚举、布尔值，连续相同的数值段会被压缩为单个存储条目，压缩比可达10:1到100:1；对高频变化的连续数值，也会采用专用浮点压缩算法，最终存储体积和你现在手动拆表只存变化值的方案基本持平，部分场景下压缩率更高
• 查询阶段直接调用TimescaleDB原生的时间缺口填充、LOCF函数，几行SQL就能拿到任意时间点对齐的全量指标值，不需要再在pandas里做多表拼接、前向填充的操作，示例查询代码：

-- 查询指定设备、指定时间段内1秒粒度的全量对齐指标
SELECT
  time_bucket('1s', timestamp) AS ts,
  locf(last(Data_1, timestamp)) AS Data_1,
  locf(last(Data_2, timestamp)) AS Data_2,
  locf(last(Data_3, timestamp)) AS Data_3
  -- 其余指标按相同逻辑补充即可
FROM device_metrics
WHERE
  device_id = 'target_device_id'
  AND timestamp >= '2022-06-12 17:52:43'
  AND timestamp <= '2022-06-12 17:52:48'
GROUP BY ts
ORDER BY ts;

返回结果直接是对齐后的完整数据集，不需要额外做数据重构。

方案2：窄表写入+原生透视（灵活度最高） #

• 如果你想彻底保留「数值变化才写入」的逻辑，不需要建宽表，也不需要按变化率拆多张表，只需要建一张窄格式超表，固定字段为timestamp、device_id、metric_name、metric_value，哪个指标发生变化就写一条对应记录，数值不变就不写入，后续新增指标时完全不需要修改表结构
• 查询时调用TimescaleDB原生的透视（pivot）、缺口填充、LOCF能力，直接输出对齐后的宽表结果，不需要在应用层做额外处理。

超大数据量折中方案 #

如果单设备数据量级极大，可以简单把指标分成两类存储，进一步平衡性能和成本：

• 高频变化的连续数值存入主宽表，开启列式压缩
• 变化频率极低（小时级甚至天级才变化）的状态类指标单独存入一张小体积状态表
  两类表关联查询的成本极低，维护成本远低于你现在按变化率拆分多张表的模式。

你当前方案的核心问题是把数据库原生支持的压缩、填充、对齐逻辑全部放到应用层实现，不仅维护成本高，数据量上涨后pandas的内存计算很容易成为性能瓶颈，把这类计算下推到TimescaleDB层面就能解决绝大多数问题。

内容的提问来源于stack exchange，提问作者Thomas Marin