# 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 的核心功能。将普通表转换为超表后，数据按时间自动分块存储，用户无需手动管理分区。

```sql
-- 创建表
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)**：按时间窗口聚合数据。

  ```sql
  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)**：获取时间序列中最后一个值。

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

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

### 数据压缩

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

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

### 数据保留策略（Retention Policy）

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

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

### 连续聚合（Continuous Aggregate）

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

```sql
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`` 实现自动创建新分区：

```sql
-- 创建分区表，按天自动分区
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``），以提升查询性能：

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

### 查询

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

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

### 主要差异

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

## 选型建议

### 推荐使用场景

- 数据量大（亿级以上），且查询以时间范围过滤为主
- 需要压缩历史数据以节省存储空间
- 需要自动清理过期数据
- 复杂聚合查询频繁，如多表 JOIN + 时间窗口聚合
- 时间 + 空间组合查询

### 不推荐使用场景

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