database

PostgreSQL 时序数据工作负载

面向时序场景的 PostgreSQL 实践指南。涵盖时序数据的写入模式与时间索引选型、BRIN 索引原理与适用边界、声明式分区（RANGE 按时间）的建表与管理、连续聚合与降采样、压缩与保留策略，以及 TimescaleDB 超表与原生分区的取舍。

Leeting Yan 8 分钟阅读 3652 字

时序数据（metrics、日志、传感器采样、交易流水）的共同特征是：只追加、按时间查询、数据量随时间无限增长、旧数据价值递减。这类负载对通用 B-tree 索引并不友好——写入热点集中、索引膨胀快、删除旧数据代价高。PostgreSQL 提供了三层原生武器来应对：BRIN 索引降低索引体积、声明式分区把大表切成可管理的时间片段、以及 pg_partman 之类的自动化工具；如果需求更进一步，TimescaleDB 扩展把超表、连续聚合与压缩封装成开箱即用的能力。

核心认知：时序表的性能瓶颈通常不在查询，而在写入与清理。分区解决删除，BRIN 解决索引膨胀，两者配合才是完整的时序方案。

一、时序数据的建模与索引

1.1 典型表结构

CREATE TABLE metrics (
    device_id   int         NOT NULL,
    ts          timestamptz NOT NULL,
    metric      text        NOT NULL,
    value       double precision NOT NULL,
    tags        jsonb       DEFAULT '{}'::jsonb
);

这类表的特征决定了索引策略：

特征影响应对
写入按时间递增B-tree 索引右侧热点BRIN 或按时间分区
查询总是带时间范围时间列选择性高时间列优先入索引
数据量线性增长索引体积膨胀分区 + 定期归档
旧数据按时间删除DELETE 产生大量死元组DROP PARTITION 秒删

1.2 复合索引的列顺序

时序查询几乎总是 WHERE device_id = ? AND ts BETWEEN ? AND ?，因此索引列顺序应为 (device_id, ts)——等值列在前，范围列在后：

CREATE INDEX idx_metrics_device_ts ON metrics (device_id, ts DESC);

反过来 (ts, device_id) 只在「查全局时间窗口内所有设备」时才有用。选择哪一个取决于查询模式，不要盲目建两个。

1.3 只追加表的 fillfactor

时序表几乎不更新，可以把 fillfactor 设高（如 100），减少页分裂与空间浪费：

CREATE TABLE metrics (
    device_id int NOT NULL,
    ts        timestamptz NOT NULL,
    value     double precision NOT NULL
) WITH (fillfactor = 100);

二、BRIN 索引原理与实践

2.1 BRIN 是什么

BRIN（Block Range INdex）不存储每一行的值，而是把表按物理顺序切成若干块范围，每个范围只记录该范围内某列的最小值和最大值。它的体积只有 B-tree 的千分之一量级，代价是查询时必须扫描范围内所有块。

B-tree:  每行一个索引条目 → 索引大小 ~ 表的 20%~50%
BRIN:    每 N 个块一条摘要   → 索引大小 ~ 表的 0.1%

2.2 为什么 BRIN 适合时序表

BRIN 有效的前提是物理顺序与列值相关。时序表天然满足：后写入的行时间戳更大，物理上按时间聚集。因此查询 WHERE ts BETWEEN '2026-09-01' AND '2026-09-02' 时，BRIN 能快速排除掉时间范围之外的所有块。

CREATE INDEX idx_metrics_ts_brin ON metrics USING brin (ts)
    WITH (pages_per_range = 128);

-- 观察索引大小差异
SELECT indexrelname, pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_user_indexes
WHERE relname = 'metrics';

2.3 pages_per_range 的取舍

pages_per_range 决定每个摘要覆盖多少个 8KB 页（默认 128）。它是一对矛盾：

pages_per_range索引大小扫描精度适用
32较大高查询范围很窄
128（默认）中中通用
512很小低查询范围宽、索引体积敏感
-- 重建 BRIN 索引并指定 pages_per_range
REINDEX INDEX idx_metrics_ts_brin;

2.4 BRIN 的失效场景

-- 反例：数据乱序写入（如按 device_id 回填历史数据）
-- 物理顺序与 ts 无关 → BRIN 摘要区间重叠严重 → 退化为全表扫描

判断 BRIN 是否有效，可以对比 EXPLAIN (ANALYZE, BUFFERS) 中 Rows Removed by Index Recheck 的比例。如果这个数字接近总行数，说明摘要没有起到过滤作用。

2.5 BRIN 与 B-tree 混用

实务中常见组合：时间列用 BRIN，设备/标签列用 B-tree。PostgreSQL 会通过位图 AND 合并两个索引的结果：

CREATE INDEX idx_metrics_device ON metrics (device_id);      -- B-tree
CREATE INDEX idx_metrics_ts_brin ON metrics USING brin (ts); -- BRIN

-- 计划中可能出现 BitmapAnd，合并两个索引
EXPLAIN (ANALYZE, BUFFERS)
SELECT avg(value) FROM metrics
WHERE device_id = 42 AND ts >= now() - interval '1 hour';

三、声明式分区

3.1 按时间 RANGE 分区

PostgreSQL 10 引入声明式分区，10~13 需要逐个建子分区，14 之后可以 CREATE TABLE ... PARTITION OF 批量挂载。

CREATE TABLE metrics (
    device_id int NOT NULL,
    ts        timestamptz NOT NULL,
    value     double precision NOT NULL
) PARTITION BY RANGE (ts);

-- 按月建分区
CREATE TABLE metrics_2026_09 PARTITION OF metrics
    FOR VALUES FROM ('2026-09-01') TO ('2026-10-01');

CREATE TABLE metrics_2026_10 PARTITION OF metrics
    FOR VALUES FROM ('2026-10-01') TO ('2026-11-01');

-- 兜底默认分区，防止写入报错
CREATE TABLE metrics_default PARTITION OF metrics DEFAULT;

3.2 分区裁剪

查询带上分区键时，优化器只扫描命中的分区：

EXPLAIN (ANALYZE)
SELECT count(*) FROM metrics
WHERE ts >= '2026-10-01' AND ts < '2026-10-02';

计划中只会出现 metrics_2026_10，metrics_2026_09 被裁剪掉。注意分区裁剪发生在计划期或执行期，如果条件里对 ts 用了函数（如 date_trunc('day', ts)），裁剪可能失效。

-- 反例：函数包裹分区键，裁剪失效
SELECT count(*) FROM metrics WHERE date_trunc('day', ts) = '2026-10-01';

-- 正例：写成范围条件，裁剪生效
SELECT count(*) FROM metrics
WHERE ts >= '2026-10-01' AND ts < '2026-10-02';

3.3 分区上的索引

在父表上建的索引会自动传播到所有子分区（CREATE INDEX ON metrics(...) 会级联）。分区表的主键必须包含分区键：

-- 合法：分区键 ts 是主键的一部分
ALTER TABLE metrics ADD PRIMARY KEY (device_id, ts);

3.4 秒级删除旧数据

分区最大的价值在于删除：

-- 传统 DELETE：产生大量死元组，需要 VACUUM 回收
DELETE FROM metrics WHERE ts < now() - interval '90 days';   -- 慢、膨胀

-- 分区 DROP：瞬间完成，不产生死元组
DROP TABLE metrics_2026_06;                                   -- 毫秒级

3.5 自动化分区管理

手动建分区容易漏，生产环境用 pg_partman 自动滚动：

CREATE EXTENSION pg_partman;

SELECT partman.create_parent(
    p_parent_table => 'public.metrics',
    p_control      => 'ts',
    p_type         => 'range',
    p_interval     => '1 month',
    p_premake      => 3              -- 预建未来 3 个分区
);

-- 把 pg_partman 维护任务加入 cron 或 pg_cron
SELECT cron.schedule('partman-maintenance', '0 * * * *',
                     $$SELECT partman.run_maintenance()$$);

四、聚合、降采样与保留策略

4.1 连续聚合的朴素实现

原始数据保留 7 天，长期趋势用小时级汇总表：

CREATE TABLE metrics_hourly (
    device_id int NOT NULL,
    bucket    timestamptz NOT NULL,
    metric    text NOT NULL,
    avg_value double precision,
    max_value double precision,
    samples   int,
    PRIMARY KEY (device_id, bucket, metric)
);

INSERT INTO metrics_hourly (device_id, bucket, metric, avg_value, max_value, samples)
SELECT device_id,
       date_trunc('hour', ts) AS bucket,
       metric,
       avg(value),
       max(value),
       count(*)
FROM metrics
WHERE ts >= now() - interval '1 hour'
  AND ts <  now()
GROUP BY device_id, bucket, metric
ON CONFLICT (device_id, bucket, metric) DO UPDATE
SET avg_value = EXCLUDED.avg_value,
    max_value = EXCLUDED.max_value,
    samples   = EXCLUDED.samples;

4.2 降采样查询的代价

-- 从原始表聚合一天的曲线：扫描 86400 × 设备数 行
SELECT date_trunc('minute', ts) AS m, avg(value)
FROM metrics
WHERE device_id = 42 AND ts >= '2026-09-30' AND ts < '2026-10-01'
GROUP BY m ORDER BY m;

-- 从小时表聚合：扫描 24 行
SELECT bucket, avg_value FROM metrics_hourly
WHERE device_id = 42 AND bucket >= '2026-09-30' AND bucket < '2026-10-01'
ORDER BY bucket;

4.3 保留策略与归档

-- 短期：分区直接 DROP
DROP TABLE metrics_2026_06;

-- 长期：先归档到冷存储再删除
COPY (SELECT * FROM metrics WHERE ts < '2026-07-01')
TO '/archive/metrics_2026_06.csv' WITH (FORMAT csv, HEADER true);

4.4 autovacuum 在时序表上的调整

时序表写入密集，autovacuum 需要更激进：

ALTER TABLE metrics SET (
    autovacuum_vacuum_scale_factor  = 0.02,
    autovacuum_analyze_scale_factor = 0.01,
    autovacuum_vacuum_cost_delay    = 2
);

如果采用分区，则应对每个子分区单独设置，或通过父表的 ALTER TABLE ... SET 让新分区继承参数。

五、TimescaleDB 超表

5.1 超表是什么

TimescaleDB 把分区管理、连续聚合、压缩、保留策略封装成扩展。核心概念是超表（hypertable）——逻辑上是一张表，物理上按时间和空间自动分区：

CREATE EXTENSION IF NOT EXISTS timescaledb;

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

SELECT create_hypertable('metrics', 'ts', chunk_time_interval => interval '1 day');

5.2 连续聚合

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

-- 自动刷新策略
SELECT add_continuous_aggregate_policy('metrics_hourly',
    start_offset => interval '3 hours',
    end_offset   => interval '1 hour',
    schedule_interval => interval '30 minutes');

5.3 原生压缩

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

SELECT add_compression_policy('metrics', interval '7 days');

-- 压缩比通常可达 10x~20x
SELECT pg_size_pretty(before_compression_total_bytes) AS before,
       pg_size_pretty(after_compression_total_bytes)  AS after
FROM hypertable_compression_stats('metrics');

5.4 原生分区 vs TimescaleDB 的取舍

维度原生声明式分区TimescaleDB
依赖内置需扩展
分区粒度手动/DBA 脚本自动（时间 + 空间）
连续聚合手写 + 调度内置策略
压缩无（需列存扩展）内置列式压缩
保留策略手写 DROPadd_retention_policy
托管兼容全平台部分托管需企业版
学习成本低中

如果只是「按时间分区 + 定期删除」，原生分区足够；如果需要连续聚合、压缩、自动保留，TimescaleDB 的封装能省掉大量自研代码。

六、性能验证与踩坑

6.1 写入吞吐测试

-- 批量写入，避免逐行 INSERT
INSERT INTO metrics (ts, device_id, value)
SELECT now() - (g || ' seconds')::interval, (g % 100), random() * 100
FROM generate_series(1, 100000) g;

单行 INSERT：~5000 行/秒
COPY 批量：  ~200000 行/秒

6.2 常见踩坑

-- 坑 1：分区表查询漏写分区键 → 全分区扫描
SELECT * FROM metrics WHERE device_id = 42;   -- 扫描所有分区

-- 坑 2：BRIN 建在乱序写入的列上 → 形同虚设
-- 坑 3：时间列用 timestamptz 但比较时用了本地时间字符串 → 时区偏移
-- 坑 4：分区过多（如按分钟分区）→ 计划期开销爆炸，建议单表分区数 < 1000

6.3 监控

-- 分区数量与大小
SELECT child.relname AS partition,
       pg_size_pretty(pg_relation_size(child.oid)) AS size
FROM pg_inherits
JOIN pg_class parent ON pg_inherits.inhparent = parent.oid
JOIN pg_class child  ON pg_inherits.inhrelid  = child.oid
WHERE parent.relname = 'metrics'
ORDER BY child.relname;

-- 索引使用率
SELECT indexrelname, idx_scan, pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_user_indexes
WHERE relname LIKE 'metrics%'
ORDER BY idx_scan;

常见问题（FAQ）

时序表的索引选型 B-tree 还是 BRIN

如果数据按时间顺序写入、查询按时间范围过滤，BRIN 的性价比远高于 B-tree。但若查询经常按 device_id 等值定位，则应为该列建 B-tree，时间列用 BRIN，让优化器做位图合并。

分区表能否跨分区建唯一索引

可以，但唯一索引必须包含分区键。因为唯一性只能在单个分区内保证，跨分区的全局唯一需要额外的协调表或应用层保证。

分区键选 timestamp 还是 timestamptz

强烈建议 timestamptz。timestamp 不带时区，跨时区或夏令时会出错；timestamptz 内部统一按 UTC 存储，比较与分区边界都更可靠。

何时该上 TimescaleDB

当你发现自己要写调度脚本维护分区、手写降采样表并保证幂等、再想办法压缩冷数据时，这三件事 TimescaleDB 都有现成方案。反之，如果只是分区加删除，原生足够且没有扩展依赖。

BRIN 索引是否需要定期重建

不需要像 B-tree 那样频繁维护。但如果表经历了大量乱序写入或 UPDATE 导致物理顺序漂移，可以 REINDEX 重建 BRIN 摘要，成本远低于重建 B-tree。

相关阅读

延伸阅读

完整示例（一键复制）

-- ========== 1. 建时序表（原生分区） ==========
CREATE TABLE metrics (
    device_id int NOT NULL,
    ts        timestamptz NOT NULL,
    metric    text NOT NULL,
    value     double precision NOT NULL
) PARTITION BY RANGE (ts);

CREATE TABLE metrics_2026_09 PARTITION OF metrics
    FOR VALUES FROM ('2026-09-01') TO ('2026-10-01');
CREATE TABLE metrics_2026_10 PARTITION OF metrics
    FOR VALUES FROM ('2026-10-01') TO ('2026-11-01');
CREATE TABLE metrics_default PARTITION OF metrics DEFAULT;

-- 复合索引：等值列在前，范围列在后
CREATE INDEX idx_metrics_device_ts ON metrics (device_id, ts DESC);
-- BRIN：时间列低开销索引
CREATE INDEX idx_metrics_ts_brin ON metrics USING brin (ts)
    WITH (pages_per_range = 128);

-- ========== 2. 批量写入 ==========
INSERT INTO metrics (ts, device_id, metric, value)
SELECT now() - (g || ' seconds')::interval, (g % 100), 'cpu', random() * 100
FROM generate_series(1, 100000) g;

-- ========== 3. 分区裁剪验证 ==========
EXPLAIN (ANALYZE)
SELECT count(*) FROM metrics
WHERE ts >= '2026-10-01' AND ts < '2026-10-02';

-- ========== 4. 小时级降采样 ==========
CREATE TABLE metrics_hourly (
    device_id int NOT NULL,
    bucket    timestamptz NOT NULL,
    metric    text NOT NULL,
    avg_value double precision,
    max_value double precision,
    samples   int,
    PRIMARY KEY (device_id, bucket, metric)
);

INSERT INTO metrics_hourly (device_id, bucket, metric, avg_value, max_value, samples)
SELECT device_id, date_trunc('hour', ts), metric,
       avg(value), max(value), count(*)
FROM metrics
WHERE ts >= now() - interval '1 hour' AND ts < now()
GROUP BY device_id, date_trunc('hour', ts), metric
ON CONFLICT (device_id, bucket, metric) DO UPDATE
SET avg_value = EXCLUDED.avg_value,
    max_value = EXCLUDED.max_value,
    samples   = EXCLUDED.samples;

-- ========== 5. 秒级删除旧分区 ==========
DROP TABLE IF EXISTS metrics_2026_06;

-- ========== 6. 维护参数 ==========
ALTER TABLE metrics SET (
    autovacuum_vacuum_scale_factor  = 0.02,
    autovacuum_analyze_scale_factor = 0.01
);

-- ========== 7. 分区与索引监控 ==========
SELECT child.relname AS partition,
       pg_size_pretty(pg_relation_size(child.oid)) AS size
FROM pg_inherits
JOIN pg_class parent ON pg_inherits.inhparent = parent.oid
JOIN pg_class child  ON pg_inherits.inhrelid  = child.oid
WHERE parent.relname = 'metrics'
ORDER BY child.relname;

继续阅读

探索更多技术文章

浏览归档，发现更多关于系统设计、工具链和工程实践的内容。

全部文章 返回首页

「database」更多文章

  1. PostgreSQL 数据类型深入
  2. PostgreSQL 大版本升级
  3. PostgreSQL PostGIS 地理空间