时序数据设计

监控指标、传感器采样、交易流水、访问日志——时序数据（time-series data）有一个共同的形状：按时间不断追加、几乎不更新、按时间范围查询、老数据逐渐失去查询价值。这四个特征决定了它的存储与查询策略和普通业务表完全不同：普通表优化「随机读写」，时序表优化「顺序写 + 时间范围扫 + 老数据廉价淘汰」。

PostgreSQL 原生就有应对这套形状的完整工具箱：PARTITION BY RANGE 时间分区让老数据可以整分区丢弃；BRIN 索引用几十 KB 的代价索引几个 TB 的按时间物理有序的表；物化视图承担降采样聚合；COPY 批量写入榨干 I/O；pg_partman 把分区滚动运维自动化；需要更专业的能力（自动压缩、连续聚合）时还有 TimescaleDB 扩展。

本页把这些工具组装成一套完整的时序数据设计方法：从表结构与分区策略，到索引选择、降采样、TTL 淘汰与写入优化。

前置知识

建议先阅读：树形结构（递归 CTE） 与 表分区概述。

基础概念

概念	英文 / 记法	说明
时序数据	time-series data	按时间顺序产生的、以时间戳为主键成分的数据
度量	metric / measurement	一类被采集的量（CPU 使用率、气温、订单流水）
标签	tags / labels	区分数据来源的维度（主机名、设备 ID、租户）
时间分区	range partitioning	按时间范围切分的分区表，时序数据的标配
BRIN	block range index	记录块范围 min/max 的轻量索引，适合物理有序列
降采样	downsampling	把细粒度数据聚合成粗粒度（每分钟 → 每小时）
连续聚合	continuous aggregate	持续维护的预聚合结果（TimescaleDB 概念）
TTL	time to live	数据保留期，过期自动淘汰
区间分桶	time bucket	date_trunc / time_bucket 的时间分桶聚合
批量写入	bulk load	用 COPY 或多值 INSERT 聚攒写入

时序数据的四个决定性特征

1. 追加为主。新数据不断到达，老数据几乎不改。UPDATE/DELETE 极少，这让分区、轻量索引都成为可能。
2. 时间范围查询为主。「最近 1 小时的 CPU」「昨天的订单」——几乎所有查询都带时间范围条件，时间列的物理有序性是最有价值的资产。
3. 老数据价值衰减。秒级数据查询价值几周内衰减殆尽，但合规或回溯分析仍可能需要粗粒度历史。TTL 与降采样就是为此服务。
4. 写入吞吐高。成百上千采集源、秒级采样，单表每天轻松几千万行。单表无结构地扛会先撞上索引维护成本，再撞上 VACUUM。

这四个特征共同指向一个设计公式：范围分区（时间） + BRIN（时间） + 降采样（聚合） + 分区 DROP（TTL）。

基础表结构

CREATE TABLE metrics (
    time       timestamptz   NOT NULL,
    device_id  bigint        NOT NULL,
    metric     text          NOT NULL,      -- 'cpu_usage', 'mem_usage', ...
    value      double precision NOT NULL,
    attrs      jsonb
) PARTITION BY RANGE (time);

后续所有机制都挂在这张分区主表上。

语法与用法

创建时间分区

-- 按月分区（也可按天/周，见「分区粒度选择」）
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');

-- 时间分区上的主键必须包含分区键
ALTER TABLE metrics ADD PRIMARY KEY (time, device_id, metric);

查询永远打在主表上，优化器按时间条件做分区裁剪（partition pruning）：

EXPLAIN SELECT avg(value)
FROM metrics
WHERE time >= '2026-10-01' AND time < '2026-10-02'
  AND device_id = 42 AND metric = 'cpu_usage';

Aggregate (cost=8.51..8.52 rows=1 width=8)
  ->  Index Scan using metrics_2026_10_pkey on metrics_2026_10
       Index Cond: ((time >= '2026-10-01 00:00:00+00') AND (time < '2026-10-02 00:00:00+00')
                    AND (device_id = 42) AND (metric = 'cpu_usage'::text))

只扫了 10 月分区——这就是分区的直接收益：查询与维护都只接触相关的分区。分区原理与更多分区策略见表分区概述。

BRIN 索引

时序表的数据按时间物理有序（追加写入），BRIN 索引是为这种分布量身定做的：

-- 每个块范围（默认 128 页）记录 time 的 min/max，几十 KB 索引覆盖 TB 级表
CREATE INDEX metrics_time_brin ON metrics USING BRIN (time)
    WITH (pages_per_range = 128);

对一张 500GB 的按时间追加表，B-tree 索引可能是几十 GB，而 BRIN 只有几 MB。代价是 BRIN 是粗过滤：它把「块范围的时间上下界与查询条件有交集」的块交给执行器，命中块内仍要逐行过滤。适用判断：数据按索引列物理聚集（时序表的时间列天然满足）+ 表大到 B-tree 不划算。BRIN 的机制与参数细节见 BRIN 索引。

降采样与物化视图

把细粒度原始数据聚合成粗粒度汇总，查询直接打汇总表：

-- 原始表上建物化视图：按小时、按设备聚合
CREATE MATERIALIZED VIEW metrics_hourly AS
SELECT
    date_trunc('hour', time)          AS bucket,
    device_id,
    metric,
    count(*)                          AS samples,
    avg(value)::numeric(20,6)         AS avg_value,
    min(value)                        AS min_value,
    max(value)                        AS max_value
FROM metrics
GROUP BY 1, 2, 3
WITH NO DATA;

-- 首次填充与定期刷新
REFRESH MATERIALIZED VIEW metrics_hourly;

-- 查询走物化视图（小时及以上粒度的图表全部命中这里）
SELECT bucket, avg_value
FROM metrics_hourly
WHERE device_id = 42 AND metric = 'cpu_usage'
  AND bucket >= '2026-10-01' AND bucket < '2026-10-08'
ORDER BY bucket;

物化视图的刷新是全量重建（持有读锁期间数据不可见更新），数据量大时要用 REFRESH MATERIALIZED VIEW CONCURRENTLY（要求视图带唯一索引）或改用「增量聚合到普通表」的自建方案。原生物化视图的能力边界决定了为什么大规模时序场景需要 TimescaleDB 的连续聚合。

分区 DROP 实现 TTL

老数据的淘汰不是 DELETE（产生海量死元组与 WAL），而是整分区移除：

-- 移除 2026 年 3 月的分区（O(1) 元数据操作，瞬间完成）
DROP TABLE metrics_2026_03;

-- 或先卸载到归档（detach 保留数据文件，之后可归档或再 DROP）
ALTER TABLE metrics DETACH PARTITION metrics_2026_03;

配合降采样形成完整的保留策略：原始数据保留 90 天（分区 DROP），小时级汇总保留 2 年，天级汇总永久保留。TTL 滚动创建新分区与删除过期分区用 pg_partman 自动化。

详细说明

分区粒度选择：天、周还是月

分区粒度是时序设计的第一决策：

粒度	单分区规模（示例：2000 万行/天）	分区总数（保留 1 年）	适用
按天	2000 万	365	日增千万级以上
按周	1.4 亿	52	日增百万级
按月	6 亿	12	日增十万级

判断标准有三条：

1. 单分区大小。期望单分区在 10GB ~ 100GB 量级（VACUUM、备份、迁移单分区都可管理）。
2. 分区总数。PostgreSQL 能处理成千上万个分区，但数百到千级是执行计划与元数据操作的舒适区；保留期 ÷ 粒度 = 分区总数，粒度过细会让分区数爆炸。
3. TTL 步长。DROP 的最小单位是一个分区，保留期 30 天配按月分区意味着「要么 0 天要么 30 天」的跳变；按天分区可以精确按天淘汰。

经验公式：分区数 = 保留期 / 粒度，控制在数百以内；单分区控制在百 GB 以内。两者冲突时优先保分区数合理，用「热数据按天、冷数据按月」的分级分区缓解。

pg_partman：分区滚动自动化

手工建分区总有忘记的一天（写入落不进任何分区会直接报错），pg_partman 扩展负责分区的自动预创建与过期清理：

CREATE EXTENSION IF NOT EXISTS pg_partman;

-- 配置：按天分区 metrics 表，预创建 4 个，保留 90 个
SELECT partman.create_parent(
    p_parent_table => 'public.metrics',
    p_control      => 'time',
    p_interval     => '1 day',
    p_premake      => 4
);

-- 过期分区改为 detach（而不是直接 drop，便于归档）
UPDATE partman.part_config
SET retention = '90 days',
    retention_keep_table = true      -- detach 但不删表
WHERE parent_table = 'public.metrics';

-- 常配合 pg_cron 定时运行维护任务
SELECT partman.run_maintenance('public.metrics');

pg_partman 的职责清单：按当前时间滚动创建未来分区（p_premake）、按保留期 detach/drop 过期分区、可插拔的模板表管理（索引、约束随模板自动复制）。它不解决分区内的任何问题（索引、聚合），只解决「分区生命周期」这一件事。

BRIN 的适用边界

BRIN 快的前提是物理聚集。时序表按时间追加，时间列天然聚集，BRIN 是绝配；但如果同一张表还有「设备 ID」这类穿插写入的列（多设备并发写同一分区），device_id 的 BRIN 选择率会明显变差。实践准则：

1. 时间列 BRIN 几乎无脑可用（追加型时序表）。
2. 查询要的是「某设备某段时间」时，（device_id, time) 的 B-tree 复合索引更合适——按设备定位后走索引范围扫。B-tree 该上还是上，BRIN 的角色是「替代那个因表太大而建不起的巨型时间 B-tree」。
3. 乱序写入会破坏 BRIN 有效性。批量导入历史数据时按时间排序后再 COPY（COPY (SELECT ... ORDER BY time) TO ...），或导完后 ANALYZE 并评估 BRIN 选择率。
4. BRIN 的 pages_per_range 越小过滤越准、索引越大；128（默认）是合理起点，极端场景按数据分布调整。

date_trunc 与 time_bucket：区间聚合

-- date_trunc：原生函数，固定自然边界（小时/天/周）
SELECT date_trunc('hour', time) AS bucket, avg(value)
FROM metrics
WHERE time >= now() - interval '1 day'
GROUP BY 1 ORDER BY 1;

-- 任意间隔分桶：date_bin（PG14+）
SELECT date_bin(interval '5 minutes', time, timestamp '2026-01-01') AS bucket,
       max(value)
FROM metrics
WHERE time >= now() - interval '3 hours'
GROUP BY 1 ORDER BY 1;

-- time_bucket：TimescaleDB 提供，支持月/周等自然边界 + 时区
-- SELECT time_bucket('1 hour', time) FROM metrics;

date_bin 是原生的任意间隔分桶（对齐到指定 origin），date_trunc 只能取自然单位。两者都注意时区：date_trunc('day', time) 按 UTC 切天，中国业务要 date_trunc('day', time AT TIME ZONE 'Asia/Shanghai') 再转回——跨时区错天是时序报表的经典 bug。

写入优化：批量 COPY

时序写入的黄金法则：把 N 条单行 INSERT 变成 1 条批量操作。

-- 反例：每条一个事务、一行一个 INSERT
INSERT INTO metrics VALUES ('2026-10-07T10:00:00Z', 42, 'cpu_usage', 0.31);

-- 正例 1：多值 INSERT
INSERT INTO metrics (time, device_id, metric, value) VALUES
    ('2026-10-07T10:00:00Z', 42, 'cpu_usage', 0.31),
    ('2026-10-07T10:00:01Z', 42, 'cpu_usage', 0.33),
    ('2026-10-07T10:00:02Z', 42, 'cpu_usage', 0.29);

-- 正例 2：COPY（速度最快）
COPY metrics (time, device_id, metric, value) FROM stdin WITH (FORMAT csv);
2026-10-07T10:00:00Z,42,cpu_usage,0.31
2026-10-07T10:00:01Z,42,cpu_usage,0.33
2026-10-07T10:00:02Z,42,cpu_usage,0.29
\.

COPY 的吞吐可达单行 INSERT 的数十倍：省掉每行的解析开销、以块为单位写 WAL、批量维护索引。PHP 侧用 pg_copy_from 或驱动的 COPY API：

<?php
// 把积攒在内存的 N 条采样一次性 COPY 进去
$rows = [];
foreach ($samples as $s) {
    $rows[] = implode(',', [$s['time'], $s['device_id'], $s['metric'], $s['value']]);
}
pg_copy_from($conn, 'metrics (time, device_id, metric, value)', $rows);

配套策略：应用侧微批（每 1000 条或每 500ms 一次），摊平网络往返；空闲时段的回填（backfill）按时间排序后 COPY，保护 BRIN 有效性。COPY 的更多姿势（含 PG17 的容错加载）见下一阶段 PG17 批量加载一页。

索引策略：少而精

时序表索引遵循「查得到就够」：

-- 必备：主键（含分区键）已覆盖 (time, device_id, metric)
-- 常用：按设备+时间倒序取最新值（每设备最新状态面板）
CREATE INDEX metrics_device_time_idx ON metrics (device_id, time DESC);

-- 常用：BRIN 兜底大范围时间扫
CREATE INDEX metrics_time_brin ON metrics USING BRIN (time);

-- 谨慎：attrs jsonb 的 GIN 索引——确认真的要按 attrs 过滤再建，GIN 写放大不小

两个反模式：

1. 按 value 建索引。数值列几乎不作为定位条件（「value > 0.9」的查询走时间范围 + 过滤更便宜），索引纯付写放大。
2. 每张汇总表重建全套索引。汇总表行数骤减，主键往往就够。

TimescaleDB：何时引入

TimescaleDB 在原生能力之上补了三块拼图：

1. 自动分区管理。create_hypertable('metrics', 'time', chunk_time_interval => interval '1 day') 之后，chunk 自动滚动，不需要 pg_partman + cron 的组合。
2. 原生压缩。列式压缩（delta-delta 编码时间、Gorilla 编码浮点）对时序数据通常有 90%+ 的压缩比，让「保留 90 天原始数据」的存储成本降到可接受。
3. 连续聚合。CREATE MATERIALIZED VIEW ... WITH (timescaledb.continuous) 按增量自动维护聚合，刷新不重算历史，还支持层级聚合（小时 → 天 → 月）。

引入门槛是运维与许可成本：额外的扩展安装与升级、社区版/企业版的功能边界（压缩在社区版、部分高级特性在企业版）。判断顺序：先用原生方案（分区 + BRIN + 物化视图）跑起来，规模和自动化诉求到位后（通常 TB 级 + 多源聚合需求）再评估 TimescaleDB。扩展的安装与核心用法见 TimescaleDB 扩展。

回填与乱序数据

历史数据导入、传感器离线补传都会造成乱序写入：

1. 分区归属没问题——RANGE 分区按值路由，乱序只是落到对应旧分区，前提是旧分区还存在（TTL 已删的要先重建分区或拒绝回填）。
2. BRIN 有效性受损——补传的行物理上落在最新块，旧块范围的 min/max 不再覆盖它们。回填量大时：先 COPY 到临时表、按时间排序、再 INSERT SELECT（或直接对目标分区重建 BRIN）。
3. 物化视图/汇总表要重刷受影响的时间段——物化视图全量重建天然覆盖；自建增量聚合要有「重刷窗口」的调度设计。
4. 回填永远用 COPY + 关闭触发器/索引的策略路径，完成后重建索引再 ANALYZE。

数据保留的多层设计

「保留多久」不是单一数字，而是一张分层表。设计时把数据的查询价值曲线和合规要求铺在一起：

层	粒度	介质	保留期（示例）	服务对象
原始数据	秒级	热分区表	90 天	明细钻取、实时告警
小时汇总	1 小时	物化视图/汇总表	2 年	日常看板、周报
天级汇总	1 天	汇总表	5 年/永久	趋势分析、容量规划
归档	原样	对象存储/冷库	按合规	审计回溯（低频）

落地的三个要点：

1. 先定 TTL 再定粒度。保留期与分区粒度共同决定分区总数（保留期 ÷ 粒度）。保留 90 天按天分区是 90 个分区，合理；保留 10 年按天分区是 3650 个，要改用「热数据按天、冷数据按月」的分级分区或把冷数据整段归档。
2. 降采样在归档之前完成。原始分区 DETACH 之后再想聚合就没有数据了，汇总链路（小时 → 天）必须先于 TTL 跑完。给降采样任务留出重跑窗口，例如 TTL 定在数据龄 90 天，降采样按周重刷近 30 天，任务晚跑也不丢数据。
3. 归档要「可重放」。DETACH 出来的分区或 COPY 导出的 CSV，打上「表名 + 分区时间范围 + schema 版本」标签存进对象存储；将来要查，重新导入即可。裸丢不如归档，归档不可读等于没归档——schema 变更后旧归档的导入路径要留测试。

写入风暴的削峰手段

采集峰值（整点上报、批量回推、告警风暴）是时序系统的常态，削峰工具箱按成本从低到高排列：

1. 应用侧微批：攒 1000~5000 行或 500ms 批量 COPY，把单行 INSERT 的网络与解析开销摊平（本页示例三的做法），是性价比最高的第一层；
2. 入队缓冲：高峰把写入先塞进任务队列（前一页的 jobs 表），消费端按数据库能承受的速率 COPY 落库——削峰填谷，代价是写入可见性延迟与一套队列运维；
3. 并行度控制：数据库侧能做的是限制写入连接数上限，防止风暴把 CPU 打满影响读路径；精细限流靠应用层令牌桶；
4. 结构让步：极端场景在采集端降精度（秒级样本桶内聚合后再写），把风暴挡在数据库之外。

红线只有一条：削峰不能改变数据的有序性与可追溯性——乱序重排交给「回填与乱序数据」一节的策略，缓冲队列要可观测（队列长度、消费延迟），「为了不丢点而引入的黑盒队列」是排障噩梦。

实战示例

示例一：完整时序管道搭建

-- 1. 主表（分区）
CREATE TABLE metrics (
    time      timestamptz NOT NULL,
    device_id bigint      NOT NULL,
    metric    text        NOT NULL,
    value     double precision NOT NULL
) PARTITION BY RANGE (time);

-- 2. 按天分区（配合 pg_partman 自动滚动）
SELECT partman.create_parent(
    p_parent_table => 'public.metrics',
    p_control      => 'time',
    p_interval     => '1 day',
    p_premake      => 4
);

-- 3. 索引
ALTER TABLE metrics ADD PRIMARY KEY (time, device_id, metric);
CREATE INDEX metrics_device_time_idx ON metrics (device_id, time DESC);
CREATE INDEX metrics_time_brin ON metrics USING BRIN (time);

-- 4. 小时级物化视图（降采样）
CREATE MATERIALIZED VIEW metrics_hourly AS
SELECT date_trunc('hour', time) AS bucket,
       device_id, metric,
       count(*) AS samples,
       avg(value) AS avg_value,
       max(value) AS max_value
FROM metrics
GROUP BY 1, 2, 3
WITH NO DATA;

CREATE UNIQUE INDEX ON metrics_hourly (bucket, device_id, metric);

-- 5. TTL：保留 90 天原始数据
UPDATE partman.part_config
SET retention = '90 days', retention_keep_table = false
WHERE parent_table = 'public.metrics';

示例二：EXPLAIN 验证分区裁剪与 BRIN

EXPLAIN ANALYZE
SELECT device_id, avg(value)
FROM metrics
WHERE time >= '2026-10-06' AND time < '2026-10-07'
  AND metric = 'cpu_usage'
GROUP BY device_id;

HashAggregate (actual time=48.231..48.414 rows=120 loops=1)
  ->  Append (actual time=0.041..31.876 rows=864000 loops=1)
        ->  Seq Scan on metrics_2026_10 m_2026_10
             Filter: ((time >= '2026-10-06 00:00:00+00') AND (time < '2026-10-07 00:00:00+00')
                      AND (metric = 'cpu_usage'::text))
             Rows Removed by Filter: 432000
             (actual time=0.040..25.117 rows=432000 loops=1)
        ->  Bitmap Heap Scan on metrics_2026_10 m_2026_10_1
             Recheck Cond: (time >= ... AND time < ...)
             Filter: (metric = 'cpu_usage'::text)
             Heap Blocks: lossy=128 ...
             ->  Bitmap Index Scan on metrics_2026_10_time_brin
                  Index Cond: (time >= ... AND time < ...)
Planning Time: 0.9 ms
Execution Time: 49.8 ms

注意 Heap Blocks: lossy=128：BRIN 以块范围为粒度过滤（lossy 意味着块内仍需逐行过滤），一天分区、百万行的范围聚合依然在 50ms 内完成——分区裁剪 + BRIN 的组合收益。

示例三：监控面板查询（最新值）

「每台设备的最新 CPU 使用率」是监控系统的经典查询，两种写法：

-- 写法一：DISTINCT ON（PostgreSQL 特色，简洁高效）
SELECT DISTINCT ON (device_id) device_id, time, value
FROM metrics
WHERE metric = 'cpu_usage'
ORDER BY device_id, time DESC;

-- 写法二：LATERAL 每设备取最新一条（设备数多时配合索引更稳）
SELECT d.device_id, latest.time, latest.value
FROM (SELECT DISTINCT device_id FROM metrics WHERE metric = 'cpu_usage') d
CROSS JOIN LATERAL (
    SELECT time, value FROM metrics
    WHERE device_id = d.device_id AND metric = 'cpu_usage'
    ORDER BY time DESC LIMIT 1
) latest;

metrics_device_time_idx (device_id, time DESC) 让两种写法都是每设备一次索引顶端探测，万级设备也在毫秒级返回。

示例四：PHP 批量写入采集数据

<?php
// Metrics/Ingest.php —— 应用侧微批 + COPY 写入
class Ingest
{
    private array $buffer = [];
    private int $bufferMax;

    public function __construct(
        private \PgSql\Connection $conn,
        int $bufferMax = 1000,
    ) {
        $this->bufferMax = $bufferMax;
    }

    public function add(string $time, int $deviceId, string $metric, float $value): void
    {
        $this->buffer[] = sprintf(
            "%s,%d,%s,%F\n",
            $time, $deviceId, $metric, $value
        );

        if (count($this->buffer) >= $this->bufferMax) {
            $this->flush();
        }
    }

    public function flush(): void
    {
        if (!$this->buffer) {
            return;
        }

        // pg_copy_from：一次 COPY 提交整个缓冲
        pg_copy_from(
            $this->conn,
            'metrics (time, device_id, metric, value)',
            $this->buffer
        );

        $this->buffer = [];
    }

    public function __destruct()
    {
        $this->flush();
    }
}

// 定时器/协程里周期性 flush，避免缓冲长时间滞留内存
$ingest = new Ingest($conn, bufferMax: 2000);
$ingest->add('2026-10-07 10:00:00+00', 42, 'cpu_usage', 0.31);
$ingest->add('2026-10-07 10:00:01+00', 42, 'cpu_usage', 0.33);

微批的大小是「网络往返成本 × 缓冲延迟」的折中：1000~5000 行或 500ms 触发一次是常见区间，采集端崩溃时最多丢一个缓冲——对可容忍丢点的监控数据通常可接受，对计费流水要用事务 + 确认机制。

示例五：看板查询集（Grafana 直连友好）

监控看板直连 PostgreSQL 时，四类最高频的查询模式与优化写法：

-- 1. 单设备单指标最近 6 小时曲线（走 (device_id, time DESC) B-tree）
SELECT time, value
FROM metrics
WHERE device_id = 42 AND metric = 'cpu_usage'
  AND time >= now() - interval '6 hours'
ORDER BY time;

-- 2. 全设备最近 1 小时的指标均值（走小时级物化视图，不打原始表）
SELECT device_id, avg_value, max_value
FROM metrics_hourly
WHERE bucket >= date_trunc('hour', now()) - interval '1 hour'
  AND metric = 'cpu_usage';

-- 3. Top N：过去 5 分钟 CPU 均值最高的一批设备
SELECT device_id, avg(value) AS avg_5m
FROM metrics
WHERE metric = 'cpu_usage' AND time >= now() - interval '5 minutes'
GROUP BY device_id
ORDER BY avg_5m DESC
LIMIT 10;

-- 4. 数据新鲜度自检（面板顶部的心跳卡）
SELECT max(time) AS last_sample, now() - max(time) AS lag
FROM metrics
WHERE metric = 'cpu_usage';

看板查询的三条纪律：时间窗口显式下推——每个面板都带时间条件，让分区裁剪与 BRIN 生效；粗粒度面板打汇总表——小时级以上曲线全走 metrics_hourly，原始表只服务分钟级明细；高频面板固化为视图——把口径写成 CREATE VIEW dashboard_cpu_top10，面板只查视图，改口径不用挨个改面板。

示例六：分区维护的自动化脚本

把「建未来分区 + 摘过期分区」固化成日跑脚本（pg_partman 的最小等价实现，便于看清机制）：

-- 建未来 7 天分区（幂等：IF NOT EXISTS）
DO $$
DECLARE d date;
BEGIN
    FOR i IN 1..7 LOOP
        d := current_date + i;
        EXECUTE format(
            'CREATE TABLE IF NOT EXISTS metrics_%s PARTITION OF metrics FOR VALUES FROM (%L) TO (%L)',
            to_char(d, 'YYYYMMDD'), d, d + 1);
    END LOOP;
END $$;

-- 摘 91 天前的过期分区（连续尝试 7 天，不存在则跳过）
DO $$
DECLARE d date;
BEGIN
    FOR i IN 0..6 LOOP
        d := current_date - 91 - i;
        BEGIN
            EXECUTE format('ALTER TABLE metrics DETACH PARTITION metrics_%s',
                           to_char(d, 'YYYYMMDD'));
        EXCEPTION WHEN undefined_table THEN NULL;
        END;
    END LOOP;
END $$;

生产上更推荐 pg_partman——它处理默认分区兜底、日历边界、与调度器配合等细节。手写版的价值是让你明白托管工具在替你做三件事：按日历建分区、按 TTL 摘分区、维护默认分区。摘下的分区先 DETACH 再归档/删除，给「后悔」留一步。

注意事项

没有分区键的条件无法裁剪，跨全表的时序查询要设防。 「全表最近 30 天某标签的统计」如果设备维度没进分区键或索引，就是全分区扫。报表类查询走汇总表，别打原始表。

主键必须包含分区键。 PRIMARY KEY (time, device_id, metric) 是硬约束——想在分区内保证全局唯一 id，把分区键纳入主键或用唯一索引组合重新设计。

分区 DROP 才是时序 TTL 的正确姿势。 DELETE FROM metrics WHERE time < ... 会产生与数据量等量的死元组与 WAL，拖垮 VACUUM；整分区 DROP/DETACH 是元数据操作，瞬间完成。

BRIN 只对物理有序的列有效。 时间列天然满足；设备 ID、标签列慎用 BRIN，需要精确定位就建 B-tree。

物化视图 REFRESH 是全量重建。 大视图的 REFRESH 会重算所有历史并短暂持有旧版本，生产用 CONCURRENTLY（需唯一索引）或改为增量聚合的普通表方案。

乱序回填要按时间排序。 回填保护 BRIN 有效性与分区命中，顺序：临时表 → ORDER BY time → COPY/INSERT SELECT → 重建/分析。

时区是报表 bug 的第一来源。 date_trunc 按 UTC 切分，中国业务的时间桶要显式带 AT TIME ZONE 'Asia/Shanghai'；存储一律 timestamptz。

单分区过大说明粒度选错了。 百 GB 以上的分区让 VACUUM、备份、迁移全部变慢；数据已经落进去的只能等待 TTL 换代，新分区按更细粒度重建。

COPY 大批量导入会放大 WAL 与复制延迟。 TB 级回填要限流（分批 + 间隔），观察物理复制的 replay 延迟，必要时调大 max_wal_size 并接受短窗口的延迟抬升。

最佳实践

1. 时序表一律 RANGE 分区，粒度让单分区落在 10~100GB。 分区是时序设计的第一决策，其余一切（BRIN、TTL、维护窗口）都建立在它之上。
2. 分区生命周期交给 pg_partman + pg_cron。 预创建防写入报错，保留期自动清理防表膨胀；手工维护分区是事故温床。
3. 时间列 BRIN 索引无脑加上。 几 MB 的代价换大范围时间扫的快速裁剪；同时保留 (device_id, time DESC) B-tree 服务「每设备最新值」类查询。
4. 原始数据降采样：小时/天两级物化汇总。 图表与报表打汇总表，原始数据只服务明细钻取；汇总表的保留期远长于原始数据。
5. TTL 用分区 DROP/DETACH 实现，永远不用 DELETE。 DETACH 优先于 DROP，给归档留一道门；归档介质（对象存储、冷库）由外部任务承接。
6. 写入用应用侧微批 + COPY。 单行 INSERT 是时序写入的头号性能杀手；缓冲 1000 行或 500ms 一次，配合失败重试与背压。
7. 回填与批量导入按时间排序。 保护 BRIN 有效性、减少分区跳转；导完 ANALYZE，必要时重建 BRIN。
8. 时区显式化。 存储 timestamptz、聚合显式 AT TIME ZONE、报表口径写进文档——三条同时做到，跨时区错位才能根治。
9. 规模到 TB 级再评估 TimescaleDB。 原生方案覆盖 80% 场景；需要自动压缩、连续聚合、海量 chunk 管理时，扩展的收益才压过运维成本。
10. 为时序表单列监控：写入速率、分区数、单分区大小、物化视图延迟。 写入速率异常（采集断流/风暴）、分区数失控、REFRESH 滞后是最常见的三类故障。
11. 原始数据的保留期与合规要求对齐。 监控数据 90 天、计费流水数年——TTL 策略写进文档并纳入审计，避免「顺手删了要审计的数据」。
12. 多租户时序数据叠加租户维度设计。 tenant_id 进主键与索引、按租户的配额与限流在采集端执行，防止单租户的采样风暴拖垮共享管道。

下一节

继续学习：PG16：SQL/JSON 构造与检查

参考链接

• PostgreSQL 官方文档：Table Partitioning
• PostgreSQL 官方文档：BRIN Indexes
• PostgreSQL 官方文档：CREATE MATERIALIZED VIEW / REFRESH
• PostgreSQL 官方文档：COPY
• PostgreSQL 官方文档：date_bin（时间分桶）
• pg_partman GitHub（分区管理扩展）
• TimescaleDB 官方文档
• PostgreSQL 官方文档：DISTINCT ON
• PostgreSQL 官方文档：Routine Vacuuming（膨胀控制）
• PostgreSQL 官方文档：WAL Configuration（批量导入限流）