基于Thingsboard CE和PostgreSQL的传感数据定期删除聚合方案咨询

场景背景 #

我们基于ThingsBoard CE和PostgreSQL开发传感器监测类应用，业务要求每秒采集一次传感数据实现实时监测，但1秒粒度的原始数据存储空间占用极高，且仅需为实时监测服务无需长期留存：查询上周及更早的历史数据时不需要1秒间隔的精度，保留每5分钟的平均值即可满足使用需求。

分层存储需求 #

需要实现按周期降精度的存储规则：

• 当日数据保留1秒粒度的原始全量数据，支撑实时查询需求
• 当月及更早的数据保留5分钟粒度的聚合平均值，支撑历史趋势查询需求
• 执行逻辑要求：删除过期原始数据的同时，对被删除数据按指定时间间隔计算平均值，插入聚合数据表留存

方案选型与落地 #

PostgreSQL无原生支持的周期性数据聚合+过期删除能力，最初计划采用操作系统层cron任务定期执行聚合与删除操作，最终选用更轻量化的数据库层方案：使用PostgreSQL的定时任务调度插件pg_cron实现需求，无需依赖外部服务，配置简单，运行效果完全符合预期。

基础配置示例 #

可直接在数据库中配置定时任务，以下为每日凌晨2点聚合前一天原始数据的任务参考：

-- 创建每日数据聚合任务
SELECT cron.schedule(
  'daily-sensor-data-agg',
  '0 2 * * *', -- 每日凌晨2点执行
  $$
  -- 聚合前一天的原始数据为5分钟粒度
  INSERT INTO sensor_data_agg (ts, device_id, avg_value)
  SELECT date_trunc('5 minutes', ts) as agg_ts, device_id, avg(value)
  FROM sensor_raw
  WHERE ts < CURRENT_DATE
  GROUP BY agg_ts, device_id;
  -- 删除已完成聚合的过期原始数据
  DELETE FROM sensor_raw WHERE ts < CURRENT_DATE;
  $$
);

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