TimescaleDB 时序扩展

只要你的业务里出现「按时间不断追加、几乎不改、但要频繁按时间窗口聚合」的数据，就应该考虑 TimescaleDB：设备心跳、传感器读数、应用指标、订单流水、行情快照、用户行为埋点，都是典型时序（time-series）负载。

PostgreSQL 原生也能存这些数据，但有两道坎。第一是分区维护：单表上亿行后你必须手工建分区表、写定时任务滚动建新分区、定期删旧分区，漏一次就写入报错。第二是降采样查询：WHERE ts >= now() - interval '7 days' GROUP BY date_trunc('hour', ts) 在明细表上会扫掉大量行，实时看板上线后就把库拖垮。

TimescaleDB 的定位就是「把这两件事自动化，并且仍然是一个 PostgreSQL 扩展」：它把普通表自动转成按时间切分（可选再按维度哈希切分）的 hypertable，把降采样结果物化成 continuous aggregate，把冷数据压缩成列存，把过期数据自动删除。所有这一切都用标准 SQL 操作，应用层代码几乎不用改。

本篇按「建表 → 分区 → 写入 → 查询 → 汇总 → 压缩 → 清理」的运维主线展开。

前置知识

本篇假定你已读过 PostGIS 地理扩展，了解扩展的安装与 schema 归属；也假定你熟悉 CREATE EXTENSION 扩展管理里讲的 shared_preload_libraries 机制 —— TimescaleDB 必须写进 shared_preload_libraries 才能工作。

基础概念

时序数据负载的特征

理解「为什么需要专门的时序方案」，先看时序数据的四个特征：

写入量大且持续：每秒数千到数百万个点，且不停止。
只追加、极少更新：一条数据落库后基本不变，晚到的修正补写很少。
查询有强时间局部性：绝大多数查询只关心最近几小时/几天。
按时间窗口聚合：均值、最大值、P95、计数，通常按固定粒度分组。

这四条决定了：按时间物理切分 + 自动滚动 + 预聚合是天然的优化方向。

hypertable（超表）

hypertable 是 TimescaleDB 的核心概念。它是一个逻辑表，表面上看和普通表一模一样，但你 SELECT / INSERT 时，TimescaleDB 会在背后路由到多个物理分片。

sql

-- 普通表 -> hypertable 只需一行
SELECT create_hypertable('metrics', by_range('ts'));

关键性质：

仍然是一张表。主键、外键、触发器、视图、pg_dump、ORM 都照常工作。
应用无感。你的 SQL 不需要知道分片存在。
分区自动管理。新时间段的 chunk 在写入时自动创建，不需要定时任务建分区。

chunk（分片）

chunk 是 hypertable 背后的物理子表。每个 chunk 覆盖一个时间区间（默认 7 天，由 chunk_time_interval 决定），chunk 本身是标准的分区，名字形如 _timescaledb_internal._hyper_1_1_chunk。

分区带来两个直接收益：

B-tree 索引只覆盖单个 chunk，索引树高度大幅降低，写入与查询都更快。
删除旧数据变成 DROP TABLE 级别的操作（丢弃整块），而不是 DELETE 加 VACUUM。这是时序数据库最关键的运维优势。

chunk 的粒度是个权衡：太小 → chunk 数量爆炸、规划开销大；太大 → 索引变大、删除粒度粗、压缩收益低。经验值是让最新的 chunk 能放进内存（shared_buffers 的一个合理份额）。

dimension（维度）

hypertable 至少有一个时间维度，可以有零个或多个空间维度（按某个列的值哈希分区）：

sql

-- 时间维度 + 空间维度（按 device_id 哈希成 4 份）
SELECT create_hypertable(
    'metrics',
    by_range('ts', INTERVAL '1 day'),
    by_hash('device_id', 4)
);

时间维度的切分（by_range）让「按时间范围扫描」只碰少数 chunk；空间维度的切分（by_hash）让「按设备并行写入/查询」可以分散到不同 chunk，降低单 chunk 的锁争用。

三个自动策略

TimescaleDB 把时序运维里最烦的三件事做成了「策略」（policy），由后台作业自动执行：

策略	函数	作用
压缩（Compression）	`add_compression_policy()`	把超过指定年龄的 chunk 转成列存压缩
保留（Retention）	`add_retention_policy()`	把超过指定年龄的 chunk 整块删除
连续聚合刷新	`add_continuous_aggregate_policy()`	定时刷新物化视图的新数据区间

这三者组合起来，就是一条完整的「热数据留明细、温数据压缩、冷数据滚动删除、聚合视图持续更新」的数据生命周期流水线。

连续聚合（Continuous Aggregate）

continuous aggregate 是 TimescaleDB 对「物化视图 + 增量刷新」的实现。你声明一个基于 hypertable 的聚合查询，TimescaleDB 会：

把它物化成一张（隐藏的）hypertable。
提供一个可刷新的增量窗口机制，只重算新数据所在的时间段。
查询时自动改写：如果你的查询能匹配上聚合粒度，规划器会直接读物化结果。

sql

CREATE MATERIALIZED VIEW metrics_hourly
WITH (timescaledb.continuous) AS
SELECT time_bucket('1 hour', ts) AS bucket,
       device_id,
       avg(value)  AS avg_v,
       max(value)  AS max_v,
       count(*)    AS n
FROM metrics
GROUP BY bucket, device_id;

time_bucket() 就是 TimescaleDB 提供的「按任意宽度分桶」函数，是连续聚合的基础。

超表与分区表的关系

需要澄清一点：hypertable 底层用的就是 PostgreSQL 的原生分区机制。你可以把它理解成「一个自动维护分区、自动路由、自动压缩的增强版分区表」。因此：

它受 PostgreSQL 分区的一切限制（分区键必须包含在唯一索引里等）。
它能享受 PostgreSQL 18 的分区剪枝、并行扫描等优化。
它比手写分区表多了「自动创建/删除 chunk、跨 chunk 聚合、压缩」这些能力。

与「原生分区表 + 定时任务」的对比

维度	手写分区表	hypertable
建分区	定时任务 `CREATE TABLE ... PARTITION OF`	写入时自动创建 chunk
删旧数据	`DROP TABLE` + 维护任务	`add_retention_policy()` 自动
压缩	自己做，复杂	`add_compression_policy()` 自动列存
降采样	自己写物化视图 + 刷新	continuous aggregate 自动增量
时间分桶	`date_trunc` 只能固定粒度	`time_bucket` 任意宽度
查询改写	无	命中聚合视图时自动改写
生态	纯 SQL	额外函数与视图（`timescaledb_information.*`）

语法与用法

安装与预加载

TimescaleDB 依赖预加载库，必须三步走：

bash

# 1. 修改 postgresql.conf
shared_preload_libraries = 'timescaledb'

bash

# 2. 重启 PostgreSQL（不是 reload）
pg_ctl restart -D /var/lib/postgresql/18/data

sql

-- 3. 每个数据库里创建扩展
CREATE EXTENSION timescaledb;

-- 验证
SELECT extversion FROM pg_extension WHERE extname = 'timescaledb';

结果示意（text）：

text

 extversion
------------
 2.18.0

注意

TimescaleDB 版本与 PostgreSQL 大版本有明确支持矩阵，且新版（2.18+）引入了 Hypercore 等新概念。具体版本兼容性与函数行为以项目文档与所用版本的官方文档为准。

创建 hypertable

sql

CREATE TABLE metrics (
    ts         timestamptz        NOT NULL,
    device_id  bigint             NOT NULL,
    value      double precision   NOT NULL
);

-- 基础用法：仅时间维度，chunk 默认 7 天
SELECT create_hypertable('metrics', by_range('ts'));

-- 指定 chunk 粒度（更细，适合高频写入）
SELECT create_hypertable('metrics', by_range('ts', INTERVAL '1 day'));

已有数据的表也能转换：

sql

-- 已有数据时迁移（会锁表，且会移动数据）
SELECT create_hypertable('metrics', by_range('ts'), migrate_data => true);

带空间维度：

sql

SELECT create_hypertable(
    'metrics',
    by_range('ts', INTERVAL '1 day'),
    by_hash('device_id', 4)
);

查看 hypertable 与 chunk

sql

-- 列出所有 hypertable
SELECT hypertable_name, num_dimensions, num_chunks
FROM timescaledb_information.hypertables;

-- 查看 chunk 明细
SELECT chunk_name, range_start, range_end, is_compressed
FROM timescaledb_information.chunks
WHERE hypertable_name = 'metrics'
ORDER BY range_start DESC
LIMIT 10;

结果示意（text）：

text

    chunk_name     |      range_start       |       range_end        | is_compressed
-------------------+------------------------+------------------------+---------------
 _hyper_1_12_chunk | 2026-09-22 00:00:00+08 | 2026-09-29 00:00:00+08 | f
 _hyper_1_11_chunk | 2026-09-15 00:00:00+08 | 2026-09-22 00:00:00+08 | t

time_bucket：任意宽度的分桶

这是 TimescaleDB 最常用的函数，date_trunc 只能按固定单位（hour/day/month），而 time_bucket 支持任意宽度：

sql

SELECT time_bucket('5 minutes', ts) AS bucket,
       device_id,
       avg(value) AS avg_v,
       max(value) AS max_v,
       count(*)   AS n
FROM metrics
WHERE device_id = 42
  AND ts >= now() - interval '6 hours'
GROUP BY bucket, device_id
ORDER BY bucket;

带时区（让桶按本地时间对齐）：

sql

-- 按北京时间分「天」
SELECT time_bucket('1 day', ts, 'Asia/Shanghai') AS bucket, count(*)
FROM metrics
GROUP BY bucket
ORDER BY bucket DESC
LIMIT 7;

带偏移（让桶从某个偏移点开始，如「8 点到 8 点」算一天）：

sql

-- offset 是保留字，必须加双引号
SELECT time_bucket('1 day', ts, 'Asia/Shanghai', "offset" => INTERVAL '8 hours') AS bucket,
       count(*)
FROM metrics
GROUP BY bucket
ORDER BY bucket DESC;

连续聚合的创建与刷新

sql

CREATE MATERIALIZED VIEW metrics_hourly
WITH (timescaledb.continuous) AS
SELECT time_bucket('1 hour', ts)  AS bucket,
       device_id,
       avg(value)                 AS avg_v,
       max(value)                 AS max_v,
       min(value)                 AS min_v,
       count(*)                   AS n
FROM metrics
GROUP BY bucket, device_id
WITH NO DATA;   -- 先不物化，稍后手动刷新/加策略

手动刷新指定时间窗口（必需参数：视图名、窗口起点、窗口终点）：

sql

CALL refresh_continuous_aggregate('metrics_hourly', '2026-09-01', '2026-10-01');

自动刷新策略：

sql

SELECT add_continuous_aggregate_policy('metrics_hourly',
    start_offset      => INTERVAL '3 days',
    end_offset        => INTERVAL '1 hour',
    schedule_interval => INTERVAL '30 minutes');

含义：每 30 分钟跑一次，刷新「3 天前」到「1 小时前」之间的区间（end_offset 留出 1 小时避免刷新还没写完的最新数据）。

压缩

sql

-- 开启压缩并为该表指定分段、排序键
ALTER TABLE metrics SET (
    timescaledb.compress,
    timescaledb.compress_segmentby = 'device_id',
    timescaledb.compress_orderby   = 'ts DESC'
);

-- 自动压缩策略：压缩 7 天前的 chunk
SELECT add_compression_policy('metrics', compress_after => INTERVAL '7 days');

手动压缩/解压指定 chunk：

sql

SELECT compress_chunk(c.chunk_name)
FROM timescaledb_information.chunks c
WHERE c.hypertable_name = 'metrics'
  AND c.range_end < now() - interval '7 days'
  AND NOT c.is_compressed;

-- 需要回填历史数据时解压
SELECT decompress_chunk('_timescaledb_internal._hyper_1_11_chunk');

保留策略

sql

-- 超过 6 个月的 chunk 整块删除
SELECT add_retention_policy('metrics', drop_after => INTERVAL '6 months');

-- 查看策略
SELECT job_id, application_name, schedule_interval
FROM timescaledb_information.jobs
WHERE application_name LIKE '%Retention%';

查看与调整 chunk 粒度

sql

-- 查看当前 chunk 时间间隔
SELECT hypertable_name, dimension_number, column_name, time_interval
FROM timescaledb_information.dimensions
WHERE hypertable_name = 'metrics';

-- 修改（只影响之后新建的 chunk）
SELECT set_chunk_time_interval('metrics', INTERVAL '1 day');

详细说明

create_hypertable 的参数语义

create_hypertable() 的签名（核心参数）为：

text

create_hypertable(
    hypertable              REGCLASS,
    dimension               DIMENSION_INFO,      -- by_range(...) / by_hash(...)
    [ , create_default_indexes BOOL DEFAULT TRUE ]
    [ , if_not_exists          BOOL DEFAULT FALSE ]
    [ , migrate_data           BOOL DEFAULT FALSE ]
) → TABLE(hypertable_id INT, schema_name TEXT, table_name TEXT, created BOOL)

几个要点：

by_range(column, interval)：时间维度。interval 省略时用默认值（7 天）。
by_hash(column, partitions)：空间维度，按值的哈希分成 N 份。
create_default_indexes 默认为 TRUE，会自动在时间列上建索引；如果你的表本来就有合适的主键，可以设为 FALSE 避免冗余索引。
migrate_data => true 只用于转换已有数据的普通表；新表不需要。

另一个容易踩的点：唯一索引必须包含分区键。因为分区表在 PostgreSQL 里无法跨分区保证唯一性：

sql

-- 错误！唯一约束不含时间列
CREATE UNIQUE INDEX ON metrics (device_id, value);
-- ERROR:  cannot create a unique index without the column "ts"

-- 正确：把时间列包含进来
CREATE UNIQUE INDEX ON metrics (device_id, ts);

time_bucket 的完整语义

签名：

text

time_bucket(bucket_width INTERVAL, ts TIMESTAMP/TIMESTAMPTZ
            [, timezone TEXT ]
            [, origin TIMESTAMP/TIMESTAMPTZ ]
            [, "offset" INTERVAL ]) → TIMESTAMP/TIMESTAMPTZ

关键规则：

桶默认对齐 UTC+0 的午夜。time_bucket('1 hour', ts) 的桶是 00:00、01:00…（UTC）。
timezone 参数改变对齐基准。传 'Asia/Shanghai' 后，「1 day」的桶按北京时间午夜对齐。
origin 指定对齐原点。默认原点在无月/年间隔时是 2000-01-03，含月/年/世纪间隔时是 2000-01-01。
月间隔不能与其他组件混用。INTERVAL '1 month 1 day' 是非法的，因为它无法被分解为固定宽度。
"offset" 是保留字，作为命名参数时必须写成 "offset" => ...。

sql

-- 对齐到整 5 分钟
SELECT time_bucket('5 minutes', ts) FROM metrics LIMIT 3;

-- 非法：月与日混用
SELECT time_bucket(INTERVAL '1 month 1 day', ts) FROM metrics;
-- ERROR:  ... not supported

连续聚合的参数与限制

创建语法：

text

CREATE MATERIALIZED VIEW <view_name> [ ( <column_list> ) ]
    WITH ( timescaledb.continuous [, <option> = <value> ] [, ...] )
    AS <select_query>
    [ WITH [ NO ] DATA ]

timescaledb.continuous 是必需选项；常用可选项：

选项	默认	说明
`chunk_interval`	hypertable 的 10 倍	聚合视图自身的 chunk 宽度
`create_group_indexes`	`TRUE`	是否在分组列上自动建索引
`finalized`	`TRUE`（2.7+）	是否允许对已封闭窗口做增量更新
`materialized_only`	`TRUE`	查询是否只读物化数据（`FALSE` 时实时补算最新数据）

默认是 WITH DATA（立即物化）。大数据量时建议先 WITH NO DATA 再手动刷新，避免 CREATE 卡住。

连续聚合的限制（都很重要）：

SELECT 里必须包含 time_bucket。
不能对 continuous aggregate 做 INSERT/UPDATE/DELETE（只读）。
不能对 hypertable 的直接列建连续聚合，必须先做聚合。
不能有窗口函数、ORDER BY、LIMIT、DISTINCT 等。
不能直接对 hypertable 做 JOIN 后再聚合（需先落到普通表或视图）。

刷新策略的两个偏移量

add_continuous_aggregate_policy() 的三个参数是理解增量刷新的关键：

sql

SELECT add_continuous_aggregate_policy('metrics_hourly',
    start_offset      => INTERVAL '3 days',   -- 从「3 天前」开始重算
    end_offset        => INTERVAL '1 hour',   -- 只重算到「1 小时前」
    schedule_interval => INTERVAL '30 minutes' -- 每 30 分钟执行一次
);

end_offset：留出安全边界。最新写入的数据可能还在内存/未提交，立刻聚合会得到「不完整」的桶，之后还要再算一次。
start_offset：决定每次重算的历史跨度。设太大 → 每次刷新都算很多历史数据，浪费 CPU；设太小 → 迟到数据（如设备离线后补传）无法被纳入。
如果你的场景有长时间延迟的数据（如 IoT 设备断网后补传 2 天的数据），把 start_offset 放大到 3 天或更多。

压缩的分段列与排序键

压缩配置两个关键参数：

sql

ALTER TABLE metrics SET (
    timescaledb.compress,
    timescaledb.compress_segmentby = 'device_id',
    timescaledb.compress_orderby   = 'ts DESC'
);

compress_segmentby：按这些列分组压缩（每组的各列值作为独立「段」的原信息）。默认无。
compress_orderby：段内按这些列排序后压缩，能大幅提升压缩率与范围扫描效率。默认按时间列降序。
timescaledb.compress：必需项，不写则不启用压缩。

选列的原则：把常出现在 WHERE 里的等值过滤列放进 segmentby，因为按该列过滤可以直接跳过段；把需要范围扫描的列（通常是时间）放进 orderby。

text

segmentby = device_id  →  WHERE device_id = 42 能跳段（快）
orderby   = ts DESC    →  WHERE ts > x 在段内有序（快）

一个常见误区是「压缩后写入会变慢」，其实压缩只作用于已封闭的历史 chunk，新写入的 chunk 不压缩，因此热路径不受影响。

保留策略与「删除为何这么快」

sql

SELECT add_retention_policy('metrics', drop_after => INTERVAL '6 months');

这条策略背后的机制是：整块 DROP chunk，而不是 DELETE 行。

对比一下：

方式	代价
`DELETE FROM metrics WHERE ts < x`	逐行标记删除 → 大量 WAL → 需要 `VACUUM` → 索引膨胀
丢弃整个 chunk	`DROP TABLE` 级别，近乎瞬间，无 VACUUM 需求

这也解释了为什么 chunk 粒度不能太小：如果 chunk 是 1 小时，删「6 个月前」会涉及 4300 多张表；如果 chunk 是 1 天，就只有 180 张。

drop_after 必须是 INTERVAL 类型，而不是时间戳；语义是「距离现在多久之前的 chunk」，而不是「某个绝对时间点之前」。

压缩与保留策略的执行顺序

两条策略配合时要注意先后：先压缩、后删除（压缩的对象是历史数据，删除的也是历史数据）。如果你的压缩阈值是 7 天、保留阈值是 90 天，那么 7~90 天的数据会被压缩保存，90 天以上的直接删除。

若两者阈值设反了（压缩阈值 > 保留阈值），会出现「还没来得及压缩就被删了」的空转。

常用元数据视图

TimescaleDB 把内部状态集中在 timescaledb_information schema 下：

sql

-- 1. hypertable 列表
SELECT hypertable_name, num_chunks, compression_enabled
FROM timescaledb_information.hypertables;

-- 2. chunk 明细
SELECT * FROM timescaledb_information.chunks LIMIT 5;

-- 3. 维度定义
SELECT * FROM timescaledb_information.dimensions;

-- 4. 后台作业（策略）
SELECT job_id, application_name, schedule_interval, last_run_status
FROM timescaledb_information.jobs
ORDER BY job_id;

-- 5. 连续聚合定义
SELECT view_name, materialized_only, materialization_hypertable_name
FROM timescaledb_information.continuous_aggregates;

-- 6. 压缩统计
SELECT * FROM timescaledb_information.compression_settings;
SELECT * FROM hypertable_compression_stats('metrics');

hypertable_compression_stats() 会给出压缩前后的字节数，是评估压缩收益最直接的依据：

结果示意（text）：

text

 hypertable_name | total_chunks | number_compressed_chunks | before_compression_total_bytes | after_compression_total_bytes
-----------------+--------------+--------------------------+--------------------------------+-------------------------------
 metrics         |           52 |                       45 |                 42949672960   |                     3865470566

（示意数据：压缩比约 11:1。）

与 PostgreSQL 原生功能的边界

TimescaleDB 不是「另一个数据库」，而是 PostgreSQL 的增强层。因此要清楚它的边界：

它给你什么

create_hypertable() 自动分区与 chunk 生命周期管理。
time_bucket() 任意宽度时间分桶。
continuous aggregate 的增量刷新与查询改写。
chunk 级列存压缩。
策略化的保留与压缩后台作业。

它不改变什么

事务、MVCC、WAL、备份恢复机制完全一致。
EXPLAIN 仍是 PostgreSQL 的计划器输出（会看到 _hyper_*_chunk 的 append/merge）。
索引类型仍是 PostgreSQL 的那些（B-tree / GiST / GIN / BRIN）。
权限模型照旧（hypertable 是普通表，GRANT 正常生效）。

一条实用推论：很多 PostgreSQL 调优经验在 TimescaleDB 上依然适用（如 work_mem 对聚合的影响、EXPLAIN ANALYZE 读计划），只是多了一层 chunk 剪枝。

查询自动改写：何时读到聚合视图

当你对明细表写下一个恰好匹配聚合粒度的查询时，规划器会把数据源替换成物化视图：

sql

-- 原查询
SELECT time_bucket('1 hour', ts) AS bucket, avg(value)
FROM metrics
WHERE ts >= now() - interval '30 days'
GROUP BY bucket;

-- 若存在配套的连续聚合，规划器可能改写为读 metrics_hourly

改写条件包括：聚合函数一致、分桶宽度一致（或可整除）、时间范围在物化范围内。验证方式是 EXPLAIN：

sql

EXPLAIN (COSTS OFF)
SELECT time_bucket('1 hour', ts) AS bucket, avg(value)
FROM metrics
WHERE ts >= now() - interval '30 days'
GROUP BY bucket;

若计划里出现 metrics_hourly，说明改写成功。若不出现，通常是分桶宽度或聚合函数不匹配，可考虑显式查询视图。

这个「自动改写」很省事，但也带来一个坑：加了新聚合视图后，原有查询的性能曲线可能突然变化。上线后建议用 EXPLAIN 复核关键看板查询。

实战示例

场景一：设备指标入库

sql

-- 建表
CREATE TABLE device_metrics (
    ts        timestamptz      NOT NULL,
    device_id bigint           NOT NULL,
    cpu       double precision,
    mem       double precision,
    net_in    bigint
);

SELECT create_hypertable('device_metrics', by_range('ts', INTERVAL '1 day'),
                         by_hash('device_id', 4));

-- 批量写入（time 列自动路由到对应 chunk）
INSERT INTO device_metrics (ts, device_id, cpu, mem, net_in)
SELECT now() - (g || ' seconds')::interval,
       (random() * 99 + 1)::bigint,
       random() * 100,
       random() * 100,
       (random() * 1000000)::bigint
FROM generate_series(1, 10000) g;

场景二：最近一小时按 5 分钟看板

sql

SELECT time_bucket('5 minutes', ts) AS bucket,
       round(avg(cpu)::numeric, 1)  AS avg_cpu,
       round(max(cpu)::numeric, 1)  AS max_cpu,
       count(*)                     AS samples
FROM device_metrics
WHERE device_id = 42
  AND ts >= now() - interval '1 hour'
GROUP BY bucket
ORDER BY bucket;

这个查询只会扫描最近 1~2 个 chunk，是 hypertable「时间局部性」收益最直观的体现。

场景三：建连续聚合 + 自动刷新 + 保留策略

sql

-- 1. 小时级聚合
CREATE MATERIALIZED VIEW device_metrics_hourly
WITH (timescaledb.continuous) AS
SELECT time_bucket('1 hour', ts) AS bucket,
       device_id,
       avg(cpu)   AS avg_cpu,
       max(cpu)   AS max_cpu,
       avg(mem)   AS avg_mem,
       sum(net_in) AS total_net_in,
       count(*)   AS n
FROM device_metrics
GROUP BY bucket, device_id
WITH NO DATA;

-- 2. 回填历史
CALL refresh_continuous_aggregate('device_metrics_hourly',
     now() - interval '30 days', now());

-- 3. 自动刷新策略
SELECT add_continuous_aggregate_policy('device_metrics_hourly',
    start_offset      => INTERVAL '3 days',
    end_offset        => INTERVAL '1 hour',
    schedule_interval => INTERVAL '30 minutes');

-- 4. 明细表保留 90 天
SELECT add_retention_policy('device_metrics', drop_after => INTERVAL '90 days');

-- 5. 聚合视图保留 2 年
SELECT add_retention_policy('device_metrics_hourly', drop_after => INTERVAL '2 years');

注意第 5 步：聚合视图也是 hypertable，也可以加保留策略。这是「明细短期、聚合长期」这个常见数据保留策略的关键。

场景四：压缩历史数据

sql

ALTER TABLE device_metrics SET (
    timescaledb.compress,
    timescaledb.compress_segmentby = 'device_id',
    timescaledb.compress_orderby   = 'ts DESC'
);

SELECT add_compression_policy('device_metrics', compress_after => INTERVAL '7 days');

-- 手动压缩已有的历史 chunk（策略要等调度，先手动跑一遍）
SELECT compress_chunk(c.chunk_name)
FROM timescaledb_information.chunks c
WHERE c.hypertable_name = 'device_metrics'
  AND c.range_end < now() - interval '7 days'
  AND NOT c.is_compressed;

-- 评估压缩收益
SELECT * FROM hypertable_compression_stats('device_metrics');

场景五：迟到数据的处理

IoT 设备经常离线后重连，补传几小时甚至几天的数据。这会影响：

保留策略：如果 drop_after 太短，补传的数据可能落进已删除的区间 → 写入失败。
连续聚合：start_offset 必须覆盖最大延迟，否则补传数据不会被重新聚合。

sql

-- 假设设备最坏离线 3 天
SELECT add_continuous_aggregate_policy('device_metrics_hourly',
    start_offset      => INTERVAL '7 days',   -- 放大到 7 天，覆盖补传
    end_offset        => INTERVAL '1 hour',
    schedule_interval => INTERVAL '15 minutes');

-- 补传数据的插入照常走 INSERT，会自动路由到正确 chunk
INSERT INTO device_metrics (ts, device_id, cpu)
VALUES (now() - interval '2 days', 42, 88.5);

场景六：跨设备对比与百分位

sql

-- 每小时各设备的 P95（TimescaleDB 支持 PostgreSQL 的 ordered-set 聚合）
SELECT time_bucket('1 hour', ts) AS bucket,
       device_id,
       percentile_cont(0.95) WITHIN GROUP (ORDER BY cpu) AS p95_cpu
FROM device_metrics
WHERE ts >= now() - interval '24 hours'
GROUP BY bucket, device_id
ORDER BY bucket DESC, p95_cpu DESC;

注意：percentile_cont 这类有序集聚合不能直接用在连续聚合里（continuous aggregate 对聚合函数有限制），需要退化成 avg/max，或者用 approx_percentile（如版本支持，以文档为准）。

注意事项

注意一：忘记 shared_preload_libraries 会直接报错。

sql

CREATE EXTENSION timescaledb;
-- ERROR:  extension "timescaledb" must be preloaded
-- HINT:  Please preload the timescaledb library via shared_preload_libraries.

注意二：唯一索引必须包含分区键。

sql

-- 错误！
CREATE UNIQUE INDEX ON metrics (device_id);
-- ERROR:  cannot create a unique index without the column "ts"

注意三：time_bucket 不能混用月与日。

sql

-- 错误！
SELECT time_bucket(INTERVAL '1 month 1 day', ts) FROM metrics;

注意四：offset 是保留字，必须加引号。

sql

-- 错误！语法解析失败
SELECT time_bucket('1 day', ts, 'Asia/Shanghai', offset => INTERVAL '8 hours');

-- 正确
SELECT time_bucket('1 day', ts, 'Asia/Shanghai', "offset" => INTERVAL '8 hours');

注意五：连续聚合不能写 ORDER BY / LIMIT / 窗口函数。

sql

-- 错误！
CREATE MATERIALIZED VIEW bad WITH (timescaledb.continuous) AS
SELECT time_bucket('1 hour', ts), value FROM metrics ORDER BY ts;

注意六：连续聚合默认 materialized_only = TRUE，实时数据不包含在内。

sql

-- 不推荐：期望看到「刚刚」的数据，却因为没刷新而缺失
SELECT * FROM metrics_hourly ORDER BY bucket DESC LIMIT 1;

-- 需要实时性就显式关闭
ALTER MATERIALIZED VIEW metrics_hourly SET (timescaledb.materialized_only = false);

注意七：compress_after / drop_after 用 INTERVAL，不是时间戳。

sql

-- 错误！类型不符
SELECT add_retention_policy('metrics', drop_after => '2026-01-01');

注意八：加载顺序问题 —— timescaledb 必须排在 shared_preload_libraries 的第一位。

text

# 错误！放在后面可能与其他预加载库冲突
shared_preload_libraries = 'pg_stat_statements,timescaledb'

# 推荐
shared_preload_libraries = 'timescaledb,pg_stat_statements'

具体顺序要求以所用版本的官方文档为准。

注意九：create_hypertable(..., migrate_data => true) 会锁表并搬运数据。

sql

-- 大表上不要在生产高峰执行
SELECT create_hypertable('big_table', by_range('ts'), migrate_data => true);

注意十：改 chunk 粒度只影响未来，历史 chunk 不会重分。

sql

SELECT set_chunk_time_interval('metrics', INTERVAL '1 day');
-- 已有 chunk 保持原粒度，新旧混用会让维护逻辑变复杂

最佳实践

按查询模式定 chunk 粒度：目标是「一次查询只碰 1~3 个 chunk」，且最新 chunk 能放进内存。日级数据常用 1 day，秒级高频数据常用 1 hour。
高并发写入时加空间维度：用 by_hash(device_id, N) 把写入分散到多个 chunk，降低单 chunk 的锁与索引争用。
time_bucket 一律显式带时区参数，尤其在按「天/月」分组时，避免默认 UTC 对齐造成的「跨日错位」。
明细与聚合分层保留：明细保留几天到几周，聚合视图保留几个月到几年，兼顾成本与可回溯性。
连续聚合先 WITH NO DATA 建，再手动回填，最后加策略，避免 CREATE 时长时间锁表。
end_offset 务必留出足够安全边界（至少一个写入批次的时间跨度 + 时钟偏差容忍），否则会反复重算最新窗口。
有迟到数据就必须放大 start_offset，让它覆盖最坏情况的数据延迟。
压缩的 segmentby 选高频等值过滤列，orderby 选时间列，这是压缩收益与查询性能兼顾的通用配方。
压缩阈值和保留阈值拉开距离（如压缩 7 天、保留 90 天），让数据在「被压缩保存」和「被删除」之间有足够长的低成本留存期。
上线后定期巡检 timescaledb_information.jobs，确认后台策略的 last_run_status 正常 —— 策略静默失败是时序库最常见的隐性故障。
用 hypertable_compression_stats() 量化压缩收益，并把它纳入容量规划；同时监控 chunk 数量，避免因粒度过细导致 pg_class 膨胀。
升级 TimescaleDB 前先在副本演练，重点关注版本兼容矩阵、新引入的 API 变化（如旧压缩 API 被新 API 取代）以及连续聚合的重建需求。

下一篇 Citus 分布式扩展会把视角从「单机内的时序优化」提升到「多机水平扩展」：coordinator 与 worker 如何分工、分片表与引用表如何选择、citus_rebalance_start() 如何做无停机再平衡。

TimescaleDB 时序扩展 ​

基础概念 ​

时序数据负载的特征 ​

hypertable（超表） ​

chunk（分片） ​

dimension（维度） ​

三个自动策略 ​

连续聚合（Continuous Aggregate） ​

超表与分区表的关系 ​

与「原生分区表 + 定时任务」的对比 ​

语法与用法 ​

安装与预加载 ​

创建 hypertable ​

查看 hypertable 与 chunk ​

time_bucket：任意宽度的分桶 ​

连续聚合的创建与刷新 ​

压缩 ​

保留策略 ​

查看与调整 chunk 粒度 ​

详细说明 ​

create_hypertable 的参数语义 ​

time_bucket 的完整语义 ​

连续聚合的参数与限制 ​

TimescaleDB 时序扩展

基础概念

时序数据负载的特征

hypertable（超表）

chunk（分片）

dimension（维度）

三个自动策略

连续聚合（Continuous Aggregate）

超表与分区表的关系

与「原生分区表 + 定时任务」的对比

语法与用法

安装与预加载

创建 hypertable

查看 hypertable 与 chunk

time_bucket：任意宽度的分桶

连续聚合的创建与刷新

压缩

保留策略

查看与调整 chunk 粒度

详细说明

create_hypertable 的参数语义

time_bucket 的完整语义

连续聚合的参数与限制