TimescaleDB 时序扩展使用说明

概述

TimescaleDB 是 PostgreSQL 数据库的一个开源时序扩展插件，解决海量时序数据查询性能下降和存储膨胀的问题。适用于物联网传感器数据采集与存储、无人机及车辆等移动对象轨迹数据管理、设备监控日志与性能指标采集，以及任何带有时间戳且持续写入的大规模时序数据场景。

TimescaleDB 以 PostgreSQL 扩展的形式存在，安装后即可使 PostgreSQL 具备高效存储和查询时序数据的能力。

为什么要使用 TimescaleDB ？

以 1000 台无人机场景为例，每台无人机每秒上报一次数据，包含时间戳、位置、电量、速度等字段：

每台每秒上报 1 条记录
1000 台 × 60 秒 × 60 分钟 × 24 小时 = 8640 万条/天
运行一个月约 26 亿条
每条约 200 字节，一年约 6.3 TB

使用普通数据库表存储上述数据后，会遇到以下问题：

查询性能下降：在亿级数据量的普通表上执行时间范围查询，即使 time 字段建立了索引，B-Tree 索引深度增加，需要多次磁盘 I/O 才能定位目标行，查询耗时从秒级增长至分钟级甚至超时。
存储持续膨胀：冷热数据混存于同一表中，历史数据占用与热数据相同的存储结构（索引、元数据等），导致备份时间增长、磁盘成本增加、VACUUM 和 ANALYZE 等维护操作耗时上升。

核心功能

TimescaleDB 的核心机制是超表（Hypertable）。用户创建普通表后，通过调用 create_hypertable() 函数将其转换为超表，底层自动按时间维度将数据切分为多个分区块（chunk）。查询时仅扫描相关时间分区（分区裁剪），老数据可自动压缩以节省存储空间。

超表（Hypertable）

超表是 TimescaleDB 的核心功能。将普通表转换为超表后，数据按时间自动分块存储，用户无需手动管理分区。

-- 创建表
CREATE TABLE sensor_data (
    time TIMESTAMPTZ NOT NULL,
    sensor_id INTEGER,
    temperature DOUBLE PRECISION,
    humidity DOUBLE PRECISION
);

-- 转换为超表，按天自动分区
SELECT create_hypertable('sensor_data', 'time',
                         chunk_time_interval => INTERVAL '1 day');

时序专用函数

TimescaleDB 提供了一系列时序专用函数：

time_bucket(interval, time)：按时间窗口聚合数据。

SELECT time_bucket('1 hour', time) AS bucket,
       sensor_id,
       AVG(temperature) AS avg_temp
FROM sensor_data
GROUP BY bucket, sensor_id;

last(value, time)：获取时间序列中最后一个值。

SELECT sensor_id, last(temperature, time) AS latest_temp
FROM sensor_data
GROUP BY sensor_id;

first(value, time)：获取时间序列中第一个值。

数据压缩

开启压缩后，历史数据体积可缩小至原来的约十分之一。

-- 对超过 7 天的数据启用压缩
ALTER TABLE sensor_data SET (
    timescaledb.compress,
    timescaledb.compress_segmentby = 'sensor_id'
);
SELECT add_compression_policy('sensor_data', INTERVAL '7 days');

数据保留策略（Retention Policy）

自动删除过期数据，控制存储规模。

-- 自动删除 90 天前的数据
SELECT add_retention_policy('sensor_data', INTERVAL '90 days');

连续聚合（Continuous Aggregate）

预先计算并缓存聚合结果，查询时直接读取，避免每次扫描原始数据。

CREATE MATERIALIZED VIEW sensor_hourly
WITH (timescaledb.continuous) AS
SELECT
    time_bucket('1 hour', time) AS bucket,
    sensor_id,
    AVG(temperature) AS avg_temp,
    MAX(temperature) AS max_temp,
    MIN(temperature) AS min_temp
FROM sensor_data
GROUP BY bucket, sensor_id;

-- 设置自动刷新策略
SELECT add_continuous_aggregate_policy('sensor_hourly',
    start_offset => NULL,
    end_offset => INTERVAL '1 hour',
    schedule_interval => INTERVAL '1 hour');

GaussDB 中实现类似效果

GaussDB 不支持 TimescaleDB 扩展，可通过原生分区表实现类似的时序数据管理效果。

创建分区表

使用 PARTITION BY RANGE 按时间维度对表进行分区，并配合 INTERVAL 实现自动创建新分区：

-- 创建分区表，按天自动分区
CREATE TABLE sensor_data (
    time TIMESTAMPTZ NOT NULL,
    sensor_id INTEGER,
    temperature DOUBLE PRECISION,
    humidity DOUBLE PRECISION
)
PARTITION BY RANGE (time) INTERVAL ('1 day') (
    PARTITION p_init VALUES LESS THAN ('2026-01-01')
);

创建表时定义一个初始分区，后续插入超出初始分区范围的数据时，数据库会自动按 INTERVAL 定义的间隔创建新分区。

创建索引

分区表需创建本地分区索引（LOCAL），以提升查询性能：

-- 创建本地分区索引
CREATE INDEX idx_time ON sensor_data (time DESC) LOCAL;
CREATE INDEX idx_sensor ON sensor_data (sensor_id) LOCAL;

查询

分区表查询与普通表查询语法一致，数据库会根据查询条件自动裁剪无关分区：

-- 查询最近 7 天数据，自动只扫描相关分区
SELECT * FROM sensor_data
WHERE time > NOW() - INTERVAL '7 days';

主要差异

TimescaleDB 的 create_hypertable() 自动管理分区和索引，使用更为简便；GaussDB 需手动定义初始分区边界。
GaussDB 缺少 TimescaleDB 的时序专用函数（time_bucket、last 等），聚合查询需使用 PostgreSQL 原生函数替代。
查询时的分区裁剪效果两者均可实现。

选型建议

不推荐使用场景

数据量较小（千万行以下）且查询简单时，使用 PostgreSQL 普通表即可，此时 TimescaleDB 的简单聚合查询性能可能不如普通表。若写入性能是首要考量，建议优先使用普通表，因为超表批量插入数据比普通表慢。如确需使用超表，可在数据插入完成后再创建额外索引，并减少插入过程中 ANALYZE 的执行频率，以缩小性能差距。