如何使用TimescaleDB高效获取分组版本化时序数据的最新版本
2026-9-28
问题背景
我们正在迁移至TimescaleDB，已完成多个超4亿行、存储版本化时序预测数据的大表迁移。
其中dt_start_utc字段存储预测的实际日期，version_utc字段存储预测的发布日期（值越新代表越接近实际预测日期）。
表结构
sandbox_cord=# \d+ fc_power_raw_import_normalized Table "public.fc_power_raw_import_normalized" Column | Type | Collation | Nullable | Default | Storage | Stats target | Description ----------------------+-----------------------------+-----------+----------+---------+---------+--------------+------------- dt_start_utc | timestamp without time zone | | not null | | plain | | fc_id | integer | | not null | | plain | | fc_kwh | integer | | | | plain | | fc_power_supplier_id | integer | | not null | | plain | | fc_power_type_id | integer | | not null | | plain | | version_utc | timestamp without time zone | | not null | | plain | | Indexes: "fc_power_raw_import_normalized2_pkey" PRIMARY KEY, btree (dt_start_utc, fc_id, fc_power_supplier_id, fc_power_type_id, version_utc) "fc_power_raw_import_normalize2_dt_start_utc_fc_id_fc_power_s_id" btree (dt_start_utc DESC, fc_id, fc_power_supplier_id, fc_power_type_id, version_utc DESC) "fc_power_raw_import_normalize2_dt_start_utc_fc_power_supplie_id" btree (dt_start_utc DESC, fc_power_supplier_id, fc_power_type_id) "fc_power_raw_import_normalized2_dt_start_utc_idx" btree (dt_start_utc DESC) "fc_power_raw_import_normalized2_version_utc_idx" btree (version_utc DESC) Triggers: ts_insert_blocker BEFORE INSERT ON fc_power_raw_import_normalized2 FOR EACH ROW EXECUTE FUNCTION _timescaledb_internal.insert_blocker() Child tables: _timescaledb_internal._hyper_3_2334_chunk, _timescaledb_internal._hyper_3_2335_chunk, _timescaledb_internal._hyper_3_2336_chunk, _timescaledb_internal._hyper_3_2337_chunk, _timescaledb_internal._hyper_3_2338_chunk, _timescaledb_internal._hyper_3_2339_chunk Access method: heap ...
数据示例
sandbox_cord=# SELECT * FROM fc_power_raw_import_normalized ORDER BY fc_id ASC LIMIT 25; dt_start_utc | fc_id | fc_kwh | fc_power_supplier_id | fc_power_type_id | version_utc ---------------------+-------+--------+----------------------+------------------+--------------------- 2020-08-27 00:00:00 | 9 | 167 | 5 | 1 | 2020-08-23 00:27:03 2020-08-27 00:00:00 | 9 | 150 | 5 | 1 | 2020-08-23 01:12:37 2020-08-27 00:00:00 | 9 | 132 | 5 | 1 | 2020-08-23 07:11:42 2020-08-27 00:00:00 | 9 | 144 | 5 | 1 | 2020-08-23 13:12:11 2020-08-27 00:00:00 | 9 | 161 | 5 | 1 | 2020-08-23 19:13:05 2020-08-27 00:00:00 | 9 | 166 | 5 | 1 | 2020-08-24 01:11:53 ...
当前查询实现
子查询匹配方案
SELECT * FROM fc_power_raw_import_normalized WHERE (dt_start_utc, fc_id, fc_power_supplier_id, fc_power_type_id, version_utc) IN ( SELECT dt_start_utc, fc_id, fc_power_supplier_id, fc_power_type_id, MAX(version_utc) version_utc FROM fc_power_raw_import_normalized WHERE dt_start_utc > now() - INTERVAL '2 weeks' GROUP BY dt_start_utc, fc_id, fc_power_supplier_id, fc_power_type_id ) AND dt_start_utc > now() - INTERVAL '2 weeks' ORDER by fc_id, dt_start_utc, version_utc;
TimescaleDB last() 函数方案
SELECT dt_start_utc, fc_id, fc_power_supplier_id, fc_power_type_id, last(fc_kwh, version_utc) AS fc_kwh_last FROM fc_power_raw_import_normalized WHERE dt_start_utc > now () - INTERVAL '2 weeks' GROUP BY dt_start_utc, fc_id, fc_power_supplier_id, fc_power_type_id ORDER BY dt_start_utc ASC, fc_id ASC;
查询返回示例
dt_start_utc | fc_id | fc_kwh | fc_power_supplier_id | fc_power_type_id | version_utc ---------------------+-------+--------+----------------------+------------------+--------------------- 2021-10-12 16:45:00 | 19 | 99 | 4 | 1 | 2021-10-12 13:13:50 2021-10-12 16:45:00 | 19 | 99 | 4 | 2 | 2021-10-12 13:14:47 2021-10-12 17:00:00 | 19 | 100 | 4 | 1 | 2021-10-12 13:13:50 2021-10-12 17:00:00 | 19 | 100 | 4 | 2 | 2021-10-12 13:14:47 2021-10-12 17:15:00 | 19 | 103 | 4 | 1 | 2021-10-12 13:13:50 2021-10-12 17:15:00 | 19 | 103 | 4 | 2 | 2021-10-12 13:14:47 2021-10-12 17:30:00 | 19 | 105 | 4 | 1 | 2021-10-12 13:13:50 2021-10-12 17:30:00 | 19 | 105 | 4 | 2 | 2021-10-12 13:14:47 2021-10-12 17:45:00 | 19 | 108 | 4 | 1 | 2021-10-12 13:13:50 2021-10-12 17:45:00 | 19 | 108 | 4 | 2 | 2021-10-12 13:14:47 2021-10-12 18:00:00 | 19 | 108 | 4 | 1 | 2021-10-12 13:13:50 2021-10-12 18:00:00 | 19 | 108 | 4 | 2 | 2021-10-12 13:14:47 2021-10-12 18:15:00 | 19 | 105 | 4 | 1 | 2021-10-12 13:13:50 2021-10-12 18:15:00 | 19 | 105 | 4 | 2 | 2021-10-12 13:14:47 2021-10-12 18:30:00 | 19 | 82 | 4 | 1 | 2021-10-12 18:28:47 2021-10-12 18:30:00 | 19 | 82 | 4 | 2 | 2021-10-12 18:29:59 2021-10-12 18:45:00 | 19 | 82 | 4 | 1 | 2021-10-12 18:28:47 2021-10-12 18:45:00 | 19 | 82 | 4 | 2 | 2021-10-12 18:29:59 2021-10-12 19:00:00 | 19 | 81 | 4 | 1 | 2021-10-12 18:28:47 ...
性能问题
当前查询性能很差，查询3个月数据耗时约77秒，查询时间跨度更长时耗时会更高。
已尝试过使用INNER JOIN、窗口函数改写查询，也参考相关文章添加了多个不同索引，但均未带来明显性能提升。另外还测试了continuous aggregates功能，但该功能要求定义time_bucket，不适用于当前场景，调整chunk大小也未带来明显性能提升。
目前基于该表结构和查询需求，TimescaleDB的性能和已优化的传统关系型数据库MariaDB相比没有明显优势，未达到专用时序数据库的预期性能表现。
优化建议
- 调整复合索引顺序实现索引覆盖：现有索引均把
dt_start_utc放在首位，仅适合纯按时间维度过滤的查询，不符合当前先按业务维度分组的场景。可新建复合索引(fc_id, fc_power_supplier_id, fc_power_type_id, dt_start_utc DESC, version_utc DESC) INCLUDE (fc_kwh)，索引顺序匹配查询的分组、排序逻辑，同时包含查询需要的所有字段，完全避免回表扫描。 - 用PostgreSQL原生
DISTINCT ON改写查询：DISTINCT ON是PG针对「取分组第一条记录」场景做了专属优化的语法，性能远高于通用聚合、子查询匹配和TimescaleDB的last()函数，配合上面调整后的索引，查询不需要额外排序、聚合操作，直接走索引扫描即可拿到结果，改写后SQL如下：
SELECT DISTINCT ON (fc_id, fc_power_supplier_id, fc_power_type_id, dt_start_utc) dt_start_utc, fc_id, fc_power_supplier_id, fc_power_type_id, fc_kwh, version_utc FROM fc_power_raw_import_normalized WHERE dt_start_utc > now() - INTERVAL '2 weeks' ORDER BY fc_id, fc_power_supplier_id, fc_power_type_id, dt_start_utc, version_utc DESC;
- 添加跳变索引加速最值计算：针对
version_utc字段创建TimescaleDB专属的跳变索引，该索引专门为时序场景下的最值查询、范围查询做了优化，可进一步降低取最新版本的计算开销。 - 配置冷热数据分层：如果超过一定时间的历史数据不需要高频查询最新版本，可配置TimescaleDB的数据分层策略，把冷数据迁移到低成本存储层，热数据保留在高速存储介质中，进一步提升高频查询的响应速度。
- 裁剪不必要的查询范围：如果业务允许，尽量将查询时间范围限制在合理区间，避免一次性扫描过多chunk。
内容的提问来源于stack exchange，提问作者Cord Kaldemeyer
相关产品推荐
相关产品推荐
