云数据库 PostgreSQL timescaledb

这里说明 timescaledb 插件的简介及使用方法。

一、概述

timescaledb 是时序数据库插件，将普通表扩展为按时间自动分片的超表，支持高效写入和时序聚合查询，适用于监控、物联网、金融行情等时序场景。

二、支持版本

注意：说明：

注意：你可在控制台实例详情页查看当前实例的内核版本，或执行 SHOW tencentdb_version; 查询。

三、插件简介

timescaledb 在 PostgreSQL 的基础上提供时序数据管理能力：通过超表（hypertable）将数据按时间自动切分为多个分片（chunk），配合时间分桶函数（time_bucket）进行时序聚合，并提供首尾值、直方图等时序聚合函数，提升时序数据的写入和查询性能。

五、加载插件

timescaledb 是需要预加载的插件，使用前请先在控制台参数设置的 shared_preload_libraries 参数中勾选 timescaledb，保存后重启实例。参数设置方法可参考 设置实例参数。

六、创建扩展

连接到需要使用的数据库，执行以下语句创建扩展：

postgres=> CREATE EXTENSION timescaledb;CREATE EXTENSION

七、创建超表

先创建普通表，再使用 create_hypertable 将其转换为超表，第二个参数为时间列：

postgres=> CREATE TABLE metrics ( time timestamptz NOT NULL, device_id int, temperature float);CREATE TABLEpostgres=> SELECT create_hypertable('metrics', 'time'); create_hypertable———————- (1,public,metrics,t)(1 row)

返回结果中的 t 表示超表创建成功。

八、写入时序数据

向超表写入数据的方式与普通表一致：

postgres=> INSERT INTO metricsSELECT now() – interval '1 hour' * i, i % 3, 20 + (i % 10)FROM generate_series(0, 500) AS i;INSERT 0 501

十、按时间分桶

使用 time_bucket 函数按时间分桶进行聚合。以下示例按天统计温度的平均值：

postgres=> SELECT time_bucket('1 day', time) AS day, count(*) AS cnt, round(avg(temperature)::numeric, 2) AS avg_tempFROM metrics GROUP BY day ORDER BY day LIMIT 7; day | cnt | avg_temp————————+—–+———- 2026-08-13 08:00:00+08 | 12 | 24.50 2026-08-14 08:00:00+08 | 24 | 24.83 2026-08-15 08:00:00+08 | 24 | 24.17 2026-08-16 08:00:00+08 | 24 | 24.75 2026-08-17 08:00:00+08 | 24 | 24.50 2026-08-18 08:00:00+08 | 24 | 24.25 2026-08-19 08:00:00+08 | 24 | 24.83(7 rows)

注意：说明：

注意：time_bucket 默认按 UTC 时区分桶，在中国时区（UTC+8）下，按天分桶的边界为每天08:00。如需按本地时区聚合，请先设置会话时区，例如 SET timezone = 'Asia/Shanghai';。

十一、首尾值

使用 first 和 last 函数获取分组内按时间排序的首尾值：

postgres=> SELECT first(temperature, time) AS first_temp, last(temperature, time) AS last_tempFROM metrics WHERE device_id = 0; first_temp | last_temp————+———– 28 | 20(1 row)

注意：说明：

注意：first(temperature, time) 返回该组内时间最早的温度值，last(temperature, time) 返回时间最晚的值。

十二、直方图

使用 histogram 函数按区间统计数值分布：

postgres=> SELECT histogram(temperature, 0, 30, 5) FROM metrics; histogram——————— {0,0,0,0,201,300,0}(1 row)

注意：说明：

注意：histogram(value, min, max, nbuckets) 将 [min, max] 等分为 nbuckets 个桶，并返回长度为 nbuckets+2 的数组：第1个元素为小于 min 的计数（下溢），最后1个元素为大于 max 的计数（上溢），中间 nbuckets 个为各桶计数。上述结果中501条数据分别落在两个温度区间，其中201条落在18至24度区间、300条落在24至30度区间。

十四、查看分片

使用 show_chunks 函数查看超表的分片：

postgres=> SELECT show_chunks('metrics'); show_chunks—————————————- _timescaledb_internal._hyper_1_1_chunk _timescaledb_internal._hyper_1_2_chunk _timescaledb_internal._hyper_1_3_chunk _timescaledb_internal._hyper_1_4_chunk(4 rows)

注意：说明：

注意：数据按时间自动切分为4个 chunk。

十五、查看分片大小

使用 chunks_detailed_size 函数查看各分片占用的空间：

postgres=> SELECT chunk_name, total_bytes FROM chunks_detailed_size('metrics') ORDER BY chunk_name; chunk_name | total_bytes——————+————- _hyper_1_1_chunk | 24576 _hyper_1_2_chunk | 57344 _hyper_1_3_chunk | 57344 _hyper_1_4_chunk | 24576(4 rows)

十六、删除过期分片

使用 drop_chunks 函数删除指定时间之前的分片：

postgres=> SELECT drop_chunks('metrics', older_than => now() – interval '2 days'); drop_chunks—————————————- _timescaledb_internal._hyper_1_3_chunk _timescaledb_internal._hyper_1_4_chunk(2 rows)

函数返回被删除的分片列表。删除后再查看剩余分片：

postgres=> SELECT show_chunks('metrics'); show_chunks—————————————- _timescaledb_internal._hyper_1_1_chunk _timescaledb_internal._hyper_1_2_chunk(2 rows)

十七、设置分片时间间隔

使用 set_chunk_time_interval 函数设置超表的分片时间跨度：

postgres=> SELECT set_chunk_time_interval('metrics', interval '1 day'); set_chunk_time_interval————————-(1 row)

注意：说明：

注意：设置后，后续新写入的数据将按每天一个 chunk 的粒度切分；已存在的 chunk 保持原间隔不变。合理的分片间隔可兼顾写入性能与查询效率。

十八、多维度分区

对于既需要按时间、又需要按其他维度（如设备、地域）分区查询的场景，可使用 add_dimension 添加空间分区：

postgres=> CREATE TABLE tracking ( time timestamptz NOT NULL, location_id int, temp float);CREATE TABLEpostgres=> SELECT create_hypertable('tracking', 'time'); create_hypertable———————– (2,public,tracking,t)(1 row)postgres=> SELECT add_dimension('tracking', 'location_id', number_partitions => 4); add_dimension———————————– (3,public,tracking,location_id,t)(1 row)

查看维度信息：

postgres=> SELECT dimension_number, column_name, dimension_type, num_partitionsFROM timescaledb_information.dimensions WHERE hypertable_name = 'tracking' ORDER BY dimension_number; dimension_number | column_name | dimension_type | num_partitions——————+————-+—————-+—————- 1 | time | Time | 2 | location_id | Space | 4(2 rows)

注意：说明：

注意：超表现在按 time 时间维度和 location_id 空间维度进行分区，location_id 被划分为4个分区。

20、查看超表大小

使用 hypertable_size 函数查看超表占用的空间：

postgres=> SELECT pg_size_pretty(hypertable_size('metrics')) AS size; size——- 88 kB(1 row)

21、查看超表元数据

通过 timescaledb_information.hypertables 视图查看超表信息：

postgres=> SELECT hypertable_name, num_chunks, compression_enabledFROM timescaledb_information.hypertables WHERE hypertable_name = 'metrics'; hypertable_name | num_chunks | compression_enabled—————–+————+——————— metrics | 2 | f(1 row)

注意：说明：

注意：删除过期分片后，num_chunks 为2，与 show_chunks 的结果一致。

23、Q：创建扩展时报 must be preloaded？

A：timescaledb 需要预加载。请在控制台参数设置的 shared_preload_libraries 参数中勾选 timescaledb，保存并重启实例后再创建扩展。

24、Q：连续聚合（continuous aggregate）、压缩（compression）、保留策略（retention policy）不可用？

A：腾讯云提供的 timescaledb 为 Apache 2.0版本，连续聚合、压缩、数据保留策略等 Timescale License 功能不在支持范围内，使用时会提示 not supported under the current "apache" license。

25、相关参考

timescaledb 产品文档