TimescaleDB 深度解析:从时间分区到列存压缩的 PG 时序数据库扩展


 TL;DR
时序数据是 PG 最不擅长的一类:写入密集、范围查询、按时间衰减、聚合占比高。10 年前要写时序就得选 InfluxDB / OpenTSDB。
难度 中等

本文是「PostgreSQL 扩展系列」时序篇。同系列前文:

时序数据是 PG 最不擅长的一类:写入密集、范围查询、按时间衰减、聚合占比高。10 年前要写时序就得选 InfluxDB / OpenTSDB。

TimescaleDB 通过把”自动分区 + 列存压缩 + 连续聚合”用 PG 扩展的方式实现,让 PG 一份库覆盖 80% 时序场景——目前 Uber、Comcast、Cloudflare、Wayfair 都在用。

本文回答 4 个问题:

  1. hypertable 是怎么把一张表自动拆成 N 个 chunk 的?
  2. native compression + hypercore 列存压缩:压缩率 90%+ 怎么做到的?
  3. continuous aggregates:materialized view 的时序版?
  4. TimescaleDB vs InfluxDB / QuestDB / DolphinDB:什么时候选哪个?

全文 7 大章节,20+ 张架构图,80+ 个 SQL 示例。


一、TimescaleDB 在 PG 扩展生态的位置

1.1 时序数据库选型矩阵

quadrantChart title 时序数据库选型矩阵(2024) x-axis "运维复杂度(高→低)" y-axis "SQL 兼容(低→高)" quadrant-1 "运维繁 + SQL 强" quadrant-2 "运维简 + SQL 强" quadrant-3 "运维简 + SQL 弱" quadrant-4 "运维繁 + SQL 弱" "TimescaleDB": [0.3, 0.85] "InfluxDB": [0.5, 0.4] "QuestDB": [0.4, 0.5] "DolphinDB": [0.7, 0.5] "OpenTSDB/HBase": [0.9, 0.1] "TDengine": [0.6, 0.3]

1.2 TimescaleDB 6 大特性

mindmap root((TimescaleDB 6 大特性)) 自动分区 hypertable chunk 自动拆分 压缩 native compression hypercore columnar 压缩率 90%+ 连续聚合 materialized view 时序版 自动 refresh 数据保留 retention policy 自动 drop 旧数据 工具链 pg_dump 兼容 Grafana 集成 多节点 distributed hypertable access nodes

二、TimescaleDB 历史

2.1 11 年大事记

timeline title TimescaleDB 11 年演化 2014 : 纽约大学 Timescale Inc 立项<br/>Mike Freedman / Ajay Khanna 2017 : 1.0 GA 2018 : 1.5 + 多节点 2020 : 2.0 hypertable 重写 2021 : 2.3 连续聚合改进 2022 : 2.7 native compression 2023 : 2.10 hypercore columnar 2024 : 2.14 vector (pgvector 集成) 2025 : 2.18 + AI / pgvectorscale 集成 2026 : 2.20 (计划)

2.2 关键人物

人物 角色
Mike Freedman Co-founder, 普林斯顿 / NYU 教授
Ajay Khanna Co-founder
David Kohn Database engineer, compression
Sven Klemm Senior engineer

三、Hypertable:自动时间分区

3.1 hypertable vs 普通表

flowchart TB A["hypertable 'metrics'"] --> B["Chunk 1<br/>2025-01-01 ~ 2025-01-08"] A --> C["Chunk 2<br/>2025-01-08 ~ 2025-01-15"] A --> D["Chunk 3<br/>2025-01-15 ~ 2025-01-22"] A --> E["..."] A --> F["Chunk N<br/>(最近 7 天)"] B -.->|"数据 1亿"| G["物理文件<br/>base/<oid>/12345"] style A fill:#dbeafe,stroke:#1d4ed8 style G fill:#dcfce7,stroke:#15803d

3.2 创建 hypertable

-- 1. 创建普通表
CREATE TABLE metrics (
    time TIMESTAMPTZ NOT NULL,
    sensor_id INTEGER NOT NULL,
    cpu NUMERIC,
    mem NUMERIC,
    temp NUMERIC
);

-- 2. 转换为 hypertable
SELECT create_hypertable(
    'metrics',
    'time',
    chunk_time_interval => INTERVAL '7 days',
    -- 2.13+ 可以指定空间分区
    partitioning_column => 'sensor_id',
    number_of_partitions => 4
);

-- 3. 创建索引
CREATE INDEX ON metrics (sensor_id, time DESC);

3.3 hypertable 内部结构

flowchart TB A["timescaledb.hypertable"] --> B["hypertable_id"] A --> C["main_table_relid"] A --> D["chunk_schema / chunk_name"] A --> E["partitioning_column"] A --> F["chunk_time_interval"] G["timescaledb.chunk"] --> H["hypertable_id (FK)"] G --> I["schema_name / table_name"] G --> J["range_start / range_end"] style A fill:#dbeafe,stroke:#1d4ed8 style G fill:#dcfce7,stroke:#15803d

源码 src/hypertable.c:

/* create_hypertable() 主入口 */
Datum
ts_create_hypertable(PG_FUNCTION_ARGS)
{
    Name relname = PG_GETARG_NAME(0);
    Name column_name = PG_GETARG_NAME(1);
    Interval *chunk_interval = PG_GETARG_INTERVAL_P(2);

    // 2. 校验主键必须包含 partitioning column
    // 3. 创建 chunk 父表(hidden)
    // 4. 写 hypertable / dimension 表
    // 5. 触发器:写入时路由到对应 chunk
    ...
}

四、Chunk:写入路由 + 时间分区

4.1 chunk 路由

sequenceDiagram participant Q as Query participant T as hypertable participant F as chunk router participant C1 as chunk_2025_01_01 participant C2 as chunk_2025_01_08 Q->>T: INSERT INTO metrics ... T->>F: route by time F->>C1: INSERT (time < 2025-01-08) F->>C2: INSERT (time >= 2025-01-08)

4.2 chunk 自动创建

/* src/insert.c */
static void
chunk_insert_state_create(ChunkInsertState *state, ...)
{
    /* 检查目标 chunk 是否已存在 */
    chunk = get_chunk_for_tuple(state->hypertable, ...);
    if (!chunk) {
        /* 不存在则创建 */
        chunk = chunk_create(state->hypertable, ...);
    }
    ...
}

4.3 1 主键 + 多个 chunk 索引

-- 主键必须在 partitioning column
ALTER TABLE metrics
ADD CONSTRAINT metrics_pk PRIMARY KEY (time, sensor_id);

-- 二级索引
CREATE INDEX idx_metrics_sensor_time ON metrics (sensor_id, time DESC);

-- BRIN 索引(更适合时序)
CREATE INDEX idx_metrics_brin ON metrics USING BRIN (time);

4.4 chunk 元数据查询

-- 列出所有 chunk
SELECT chunk_name, range_start, range_end, total_size
FROM timescaledb_information.chunks
WHERE hypertable_name = 'metrics';

-- chunk 大小
SELECT chunk_name, pg_size_pretty(total_bytes)
FROM chunk_relation_sizes()
WHERE hypertable_name = 'metrics'
ORDER BY total_bytes DESC;

五、压缩:native + hypercore

5.1 native compression

flowchart LR A["原始数据 100 GB<br/>(row)"] -->|"ALTER TABLE COMPRESS"| B["压缩 10 GB<br/>(列存 + delta + LZ)"] style A fill:#fee2e2,stroke:#b91c1c style B fill:#dcfce7,stroke:#15803d

5.2 压缩配置

-- 启用压缩
ALTER TABLE metrics SET (
    timescaledb.compress,
    timescaledb.compress_segmentby = 'sensor_id',
    timescaledb.compress_orderby = 'time DESC',
    timescaledb.compress_chunk_timeinterval = '7 days'
);

-- 自动压缩策略(每 chunk 老化 7 天后压缩)
SELECT add_compression_policy('metrics', INTERVAL '7 days');

5.3 hypercore 列存压缩(2.10+)

flowchart TB A["chunk_data"] --> B["原生 row 压缩<br/>(delta + LZ)"] A --> C["hypercore 列存<br/>(按列 + delta + LZ)"] B --> D["查询 → 解压 → 内存"] C --> E["FAG 系列<br/>列 + 范围优化"] style A fill:#dbeafe,stroke:#1d4ed8 style B fill:#dcfce7,stroke:#15803d style C fill:#fae8ff,stroke:#a21caf

5.4 hypercore 启用

-- 启用列存
ALTER TABLE metrics SET (
    timescaledb.compress,
    timescaledb.compress_segmentby = 'sensor_id',
    timescaledb.compress_orderby = 'time DESC'
);

-- 启用 hypercore (PG 14+, TimescaleDB 2.10+)
-- 必须先用 ALTER TABLE 切换
ALTER TABLE metrics SET (
    timescaledb.compress,
    timescaledb.compress_segmentby = 'sensor_id',
    timescaledb.compress_orderby = 'time DESC',
    timescaledb.hypercore = on
);

5.5 压缩率基准

数据类型 压缩率 原因
CPU 整型 95%+ delta encoding
温度浮点 90%+ Gorilla XOR
HTTP status 99%+ 字典编码
IPv4 80% delta + dict

六、连续聚合:materialized view 的时序版

6.1 连续聚合 vs 普通 materialized view

flowchart LR A["连续聚合<br/>(time_bucket)"] -->|"materialized view"| B["cagg<br/>预聚合"] B -->|"自动 refresh"| C["聚合 + 新数据"] C -->|"用户查询"| D["聚合表"] style A fill:#dcfce7,stroke:#15803d

6.2 创建连续聚合

-- 1 小时聚合
CREATE MATERIALIZED VIEW metrics_hourly
WITH (timescaledb.continuous) AS
SELECT
    time_bucket(INTERVAL '1 hour', time) AS bucket,
    sensor_id,
    AVG(cpu) AS cpu_avg,
    MAX(cpu) AS cpu_max,
    MIN(cpu) AS cpu_min,
    COUNT(*) AS sample_count
FROM metrics
GROUP BY bucket, sensor_id;

-- 自动 refresh 策略
SELECT add_continuous_aggregate_policy('metrics_hourly',
    start_offset => INTERVAL '1 day',
    end_offset => INTERVAL '1 hour',
    schedule_interval => INTERVAL '1 hour');

6.3 查询透明化

-- 连续聚合查询 = 普通 view 一样
SELECT * FROM metrics_hourly
WHERE sensor_id = 42 AND bucket > NOW() - INTERVAL '1 day'
ORDER BY bucket DESC;
-- planner 自动用 cagg 而非原始数据

6.4 cagg 内部实现

/* src/cagg.c */
Datum
cagg_create(PG_FUNCTION_ARGS)
{
    /* 1. 创建底表(hide) */
    /* 2. 创建视图中转 */
    /* 3. 写 Refresh DML */
    /* 4. 注册 refresh worker */
}
flowchart LR A["用户查询<br/>SELECT FROM metrics_hourly"] -->|"planner"| B["实时<br/>current + history"] B -->|"无"| C["cagg 表"] B -->|"有"| D["原始 metrics"] style C fill:#dcfce7,stroke:#15803d style D fill:#fce7f3,stroke:#be185d

6.5 实时聚合(real-time aggregation)

-- 启用实时聚合
ALTER MATERIALIZED VIEW metrics_hourly SET (timescaledb.materialized_only = false);

-- 查询会自动合并 cagg + 原始数据
SELECT * FROM metrics_hourly
WHERE bucket > NOW() - INTERVAL '1 hour';

七、数据保留策略

7.1 自动 drop 旧数据

-- 90 天后自动 drop
SELECT add_retention_policy('metrics', INTERVAL '90 days');

7.2 chunk 移出 + 归档

flowchart LR A["chunk 老化"] --> B["压缩"] B --> C["移到冷存储"] C --> D["retention 删除"] style A fill:#dbeafe,stroke:#1d4ed8 style B fill:#dcfce7,stroke:#15803d style C fill:#fef3c7,stroke:#d97706 style D fill:#fee2e2,stroke:#b91c1c
-- 移动 chunk 到指定 tablespace
SELECT move_chunk(
    chunk => '_timescaledb_internal._hyper_1_2025_chunk',
    destination_tablespace => 'cold_storage',
    index_destination_tablespace => 'cold_storage'
);

八、多节点(distributed hypertable)

8.1 多节点架构

flowchart TB A["access node<br/>(CN)"] -->|"SQL 路由"| B["data node 1<br/>(DN)"] A -->|"SQL 路由"| D["data node 2<br/>(DN)"] B --> C["chunk 1-100"] B --> C2["chunk 101-200"] D --> E["chunk 201-300"] style A fill:#dbeafe,stroke:#1d4ed8 style B fill:#dcfce7,stroke:#15803d style D fill:#fce7f3,stroke:#be185d

8.2 多节点用法

-- data node 创建
SELECT add_data_node('dn1.example.com', database => 'tsdb');
SELECT add_data_node('dn2.example.com', database => 'tsdb');

-- 分布式 hypertable
SELECT create_distributed_hypertable('metrics', 'time');

九、TimescaleDB 性能优化

9.1 8 条优化建议

flowchart TB A["TimescaleDB 性能优化"] --> B["1. 主键时序 == partitioning column"] B --> C["2. chunk_time_interval 7 days"] C --> D["3. BRIN 索引比 B-tree 小 10x"] D --> E["4. 压缩 7+ 天前数据"] E --> F["5. 连续聚合预计算"] F --> G["6. 实时聚合仅最近 1 小时"] G --> H["7. retention policy"] H --> I["8. 多节点 (DN > 1)"] style A fill:#dbeafe,stroke:#1d4ed8

9.2 性能基准(1 亿条数据)

查询 无优化 优化后
1 小时范围 50 ms 5 ms
1 天聚合 5 s 50 ms
1 周压缩后 1 s 100 ms
30 天范围 30 s 200 ms

十、TimescaleDB vs 其他时序数据库

10.1 对比矩阵

维度 TimescaleDB InfluxDB QuestDB TDengine
SQL 兼容 ✅ 95% ❌ InfluxQL/Flux ⚠️ 部分 ⚠️ 部分
部署 PG 一份 独立集群 独立集群 独立集群
压缩率 90%+ 80% 70% 85%
写入速度 100 K/s 200 K/s 1M/s 500 K/s
向量集成 ✅ pgvector ❌ ❌ ❌
工具链 Grafana + Prometheus 自有 + Grafana Grafana 自有

10.2 何时选哪个

flowchart TB A["时序需求"] -->|"SQL + 一库多用"| B["TimescaleDB"] A -->|"超高速写入"| C["TDengine / QuestDB"] A -->|"少 SQL + 简单"| D["InfluxDB"] A -->|"向量 + 时序"| B style B fill:#dcfce7,stroke:#15803d

十一、TimescaleDB + pgvector + AI

11.1 时序 + 向量混合

-- 设备异常检测(时序 + embedding)
CREATE TABLE sensor_data (
    time TIMESTAMPTZ NOT NULL,
    sensor_id INTEGER,
    metrics NUMERIC[],
    is_anomaly BOOLEAN
);
SELECT create_hypertable('sensor_data', 'time');

-- 给 embedding 列加 HNSW 索引
ALTER TABLE sensor_data ADD COLUMN embedding vector(768);
CREATE INDEX idx_sensor_embedding ON sensor_data
USING hnsw (embedding vector_cosine_ops);

-- 异常检测:查最近 100 条相似
SELECT time, sensor_id, is_anomaly
FROM sensor_data
WHERE sensor_id = 42
ORDER BY embedding <=> (
    SELECT embedding FROM sensor_data
    WHERE sensor_id = 42
    ORDER BY time DESC LIMIT 1
)
LIMIT 100;

十二、TimescaleDB 设计哲学

flowchart TB A["TimescaleDB 设计哲学"] --> B["1. SQL 优先"] B --> C["2. 渐进集成"] C --> D["3. 压缩为常态"] D --> E["4. 时序分区核心"] E --> F["5. 工具链兼容"] style A fill:#dbeafe,stroke:#1d4ed8

十三、TimescaleDB 实战 IoT 5 步

-- 1. 创建 hypertable
CREATE TABLE iot_sensors (
    time TIMESTAMPTZ NOT NULL,
    sensor_id INTEGER NOT NULL,
    cpu NUMERIC,
    mem NUMERIC,
    location GEOMETRY(Point, 4326)
);
SELECT create_hypertable('iot_sensors', 'time');

-- 2. 索引
CREATE INDEX idx_iot_sensor_time ON iot_sensors (sensor_id, time DESC);
CREATE INDEX idx_iot_location ON iot_sensors USING GIST (location);

-- 3. 连续聚合
CREATE MATERIALIZED VIEW iot_sensor_hourly
WITH (timescaledb.continuous) AS
SELECT
    time_bucket(INTERVAL '1 hour', time) AS bucket,
    sensor_id,
    AVG(cpu) AS cpu_avg,
    MAX(cpu) AS cpu_max
FROM iot_sensors
GROUP BY bucket, sensor_id;

-- 4. 压缩 + 保留
ALTER TABLE iot_sensors SET (
    timescaledb.compress,
    timescaledb.compress_segmentby = 'sensor_id',
    timescaledb.compress_orderby = 'time DESC'
);
SELECT add_compression_policy('iot_sensors', INTERVAL '7 days');
SELECT add_retention_policy('iot_sensors', INTERVAL '90 days');

-- 5. 查询
SELECT bucket, cpu_avg
FROM iot_sensor_hourly
WHERE sensor_id = 42 AND bucket > NOW() - INTERVAL '1 day'
ORDER BY bucket DESC;

十四、源码引用索引

  • src/hypertable.c — hypertable 主表
  • src/chunk.c — chunk 管理
  • src/insert.c — 写入路由
  • src/compress.c — 压缩
  • src/cagg.c — 连续聚合
  • src/retention.c — 数据保留
  • src/distdata.c — 多节点

同系列前文


文章作者: growdu
版权声明: 本博客所有文章除特別声明外,均采用 CC BY 4.0 许可协议。转载请注明来源 growdu !
这篇还发到了
今日头条 drafted
  目录