1. 项目背景与需求分析在物联网(IoT)平台ThingsBoard的实际应用中电能数据的统计分析一直是个棘手问题。传统方案通常只计算总量或平均值但电力行业特有的尖峰平谷分时计价机制要求我们对数据进行更精细的处理。每个客户可能有不同的时段划分标准比如A客户定义早8点到晚10点为峰时段而B客户可能将下午2点到6点设为尖峰时段。这种业务特性带来了两个技术挑战时段定义存储在客户属性中是动态可变的JSON结构设备注册初期可能还未分配客户归属无法获取时段规则最初我们尝试用数据库触发器实时计算但发现当面对百万级设备的海量遥测数据时触发器频繁解析JSON规则导致数据库性能急剧下降。实测显示高峰期CPU利用率可达90%以上严重影响了系统整体响应速度。2. 技术方案选型2.1 触发器方案的局限性触发器(Trigger)看似是实时计算的理想选择但在我们的场景中存在致命缺陷性能瓶颈每条数据插入都会触发规则解析和计算数据完整性风险设备注册初期没有客户归属时无法计算维护困难业务规则变更需要重建触发器通过pgBadger分析慢查询日志发现单个触发器的执行时间可能长达200ms这在批量插入场景下完全不可接受。2.2 定时批处理方案的优势改用PostgreSQL的定时任务(pgAgent)方案后计算集中化每天凌晨统一处理避开业务高峰资源可控可以限制任务执行时的CPU和内存占用容错性强单日数据问题不影响整体系统我们设计了两个聚合函数put_ts_kv_daily_a通用统计总量/平均/最大/最小put_ts_kv_daily_b专用于电能的分时段统计3. 数据库结构优化3.1 表结构变更为支持分时段统计我们对ts_kv_daily表进行了扩展ALTER TABLE ts_kv_daily ADD COLUMN sum_value_a DOUBLE PRECISION DEFAULT 0; ALTER TABLE ts_kv_daily ADD COLUMN sum_value_b DOUBLE PRECISION DEFAULT 0; ALTER TABLE ts_kv_daily ADD COLUMN sum_value_c DOUBLE PRECISION DEFAULT 0; ALTER TABLE ts_kv_daily ADD COLUMN sum_value_d DOUBLE PRECISION DEFAULT 0;这四个字段分别对应sum_value_a尖峰时段总和sum_value_b高峰时段总和sum_value_c平段总和sum_value_d谷段总和3.2 JSON规则解析函数创建专用函数解析客户配置的时段规则CREATE OR REPLACE FUNCTION public.parse_electric_quantity_rule( rule_json jsonb ) RETURNS TABLE( month integer, time_type text, start_time time, end_time time ) LANGUAGE sql IMMUTABLE AS $$ -- 实现JSON解析逻辑 $$;4. 核心聚合函数实现4.1 函数架构设计put_ts_kv_daily_b函数的主要处理流程计算当日时间范围转为毫秒时间戳联查设备-客户-时段规则关系按时段分类聚合数据执行UPSERT操作更新日统计表4.2 关键SQL片段解析WITH customer_parsed_rules AS ( SELECT ak.entity_id as customer_id, per.month, per.time_type, per.start_time, per.end_time FROM attribute_kv ak INNER JOIN key_dictionary kd ON kd.key_id ak.attribute_key JOIN public.parse_electric_quantity_rule(ak.json_v::jsonb) per ON TRUE WHERE kd.key 尖峰平谷 ) INSERT INTO ts_kv_daily (...) SELECT a.entity_id, p_key, v_daily_ts, SUM(...), COUNT(*), AVG(...), MIN(...), MAX(...), SUM(CASE WHEN cpr.time_type times_a THEN ... END), -- 尖峰 SUM(CASE WHEN cpr.time_type times_b THEN ... END), -- 高峰 SUM(CASE WHEN cpr.time_type times_c THEN ... END), -- 平段 SUM(CASE WHEN cpr.time_type times_d THEN ... END) -- 谷段 FROM ts_kv a /* 关联逻辑 */ ON CONFLICT (entity_id, key, ts) DO UPDATE SET ...;5. 定时任务配置5.1 pgAgent任务设置通过pgAdmin创建定时任务每天凌晨2点执行先处理常规指标功率因素等再处理电能分时段统计-- 凌晨2:05执行 SELECT put_ts_kv_daily_a(CURRENT_DATE - 1, 96); -- 功率因素 SELECT put_ts_kv_daily_b(CURRENT_DATE - 1, 98); -- 有功电量5.2 监控与日志添加任务执行日志检查-- 检查任务执行状态 SELECT * FROM pgagent.pga_joblog WHERE jobjclid (SELECT jobid FROM pgagent.pga_job WHERE jobname daily_stats); -- 检查错误信息 SELECT * FROM pgagent.pga_jobsteplog WHERE jsljclid (SELECT jobid FROM pgagent.pga_job WHERE jobname daily_stats) AND jslstatus f;6. 性能优化技巧6.1 索引优化建议必须确保以下索引存在-- 原始数据表索引 CREATE INDEX IF NOT EXISTS idx_ts_kv_entity_key_ts ON ts_kv(entity_id, key, ts); -- 日统计表索引 CREATE INDEX IF NOT EXISTS idx_ts_kv_daily_entity_key_ts ON ts_kv_daily(entity_id, key, ts);6.2 内存参数调整在postgresql.conf中增加工作内存work_mem 16MB # 原值通常为4MB maintenance_work_mem 256MB # 原值通常为64MB6.3 并行查询配置对于大型客户启用并行查询SET max_parallel_workers_per_gather 4;7. 常见问题排查7.1 数据缺失问题现象某设备的分时段统计数据为空 排查步骤检查设备是否已分配客户SELECT customer_id FROM device WHERE id 设备ID;检查客户是否配置时段规则SELECT * FROM attribute_kv WHERE entity_id 客户ID AND attribute_key ( SELECT key_id FROM key_dictionary WHERE key 尖峰平谷 );7.2 性能下降问题现象聚合任务执行时间变长 优化方案按客户分批处理-- 先获取客户列表 WITH customers AS ( SELECT DISTINCT customer_id FROM device WHERE id IN ( SELECT entity_id FROM ts_kv WHERE key 98 AND ts BETWEEN ... AND ... ) ) -- 然后逐个客户处理 SELECT put_ts_kv_daily_b_for_customer( CURRENT_DATE - 1, 98, customer_id ) FROM customers;8. 实际应用效果实施该优化后系统性能指标显著提升数据库CPU峰值负载从90%降至35%每日统计任务平均耗时从47分钟缩短到8分钟报表查询响应时间从秒级降到毫秒级某电力公司客户反馈分时段统计准确率从原来的78%提升到99.9%极大提升了他们的电费核算效率。
