BRIN 索引与大数据量时序数据

时序数据（time-series data）——监控指标、访问日志、事件流水、IoT 上报——有一个共同特征：只追加、不修改、按时间范围查。这类表行数增长极快，但查询几乎总是「某段时间」。给时间列建 B-Tree，索引会随行数线性膨胀，几十 GB 的索引既占空间又拖慢写入，而查询根本用不到这种精度。

BRIN 恰好命中这个场景：它按物理块范围存 min/max 摘要，索引可以小几个数量级。本节把 BRIN 在时序场景的落地讲完——pages_per_range 怎么调、多 TB 表怎么实测体积、与分区表如何组合、批量导入后怎么汇总、乱序写入后如何重建，以及三者的选型决策树。

前置知识

阅读本节前，建议先了解：BRIN 索引原理

基础概念

时序数据为什么天生适合 BRIN

BRIN 成立的唯一前提是列值与行的物理位置有天然相关性（natural correlation）。时序数据天然满足：

时序表的写入模式：

t1  INSERT (ts=10:00:00)  ──→  追加到表尾
t2  INSERT (ts=10:00:01)  ──→  追加到表尾
t3  INSERT (ts=10:00:02)  ──→  追加到表尾
...
tn  INSERT (ts=12:00:00)  ──→  追加到表尾

物理文件布局（页 0 → 页 N）：
  ┌─────────────┬─────────────┬─────────────┬─────────────┐
  │ 10:00 ~ 10:30│ 10:30 ~ 11:00│ 11:00 ~ 11:30│ 11:30 ~ 12:00│
  └─────────────┴─────────────┴─────────────┴─────────────┘
       时间递增 ────────────→ ────────────→ ────────────→

时间和物理位置同步递增，于是每个块范围的 (min, max) 只覆盖一小段时间，彼此几乎不重叠。查「11:10 ~ 11:20」时，BRIN 扫一遍摘要就能跳过绝大多数范围。

-- 时序表的列相关性长这样：时间列 ≈ 1，业务列 ≈ 0
SELECT attname, correlation, n_distinct
FROM pg_stats
WHERE tablename = 'metrics'
ORDER BY abs(correlation) DESC;

   attname   | correlation | n_distinct
-------------+-------------+------------
 created_at  |    0.999997 |   17328193
 id          |    0.999997 |         -1
 device_id   |    0.001204 |        512
 metric_name |   -0.000318 |         28
 value       |    0.002341 |      -0.87
(5 rows)

created_at 与 id 是两个天然的 BRIN 候选列，其余列只适合 B-Tree（或不建索引）。

追加写入：BRIN 的理想写入模式

BRIN 对写入的假设非常具体：新数据落在物理上更靠后的位置。时序表的写入几乎都是 INSERT 且时间戳单调递增，正好符合。

写入模式	与 BRIN 的关系
单调追加（INSERT 且时间递增）	理想，新范围摘要窄，剪枝有效
乱序追加（回填历史、多 Writer 时间跳动）	摘要区间被撑大，剪枝退化
就地更新（UPDATE 时间列）	行被搬到表尾，破坏顺序，correlation 下降
频繁 DELETE + 重插	产生空洞，摘要与现状失真

时序表最怕 UPDATE

时序数据的语义通常是「已记录即不可变」。如果有一列会被反复更新（例如 status 从 pending 变 done），即使更新的是别的列，PostgreSQL 也会重写整行并把新版本放到表尾——这会慢慢磨掉 correlation。真正的时序表应设计成 append-only；需要变更状态时，另建一张小的状态表按主键更新。

单调递增主键与 BRIN

时序表几乎都有 id bigserial 或 id bigint GENERATED ALWAYS AS IDENTITY，它同样单调递增，correlation 与时间列一样接近 1：

SELECT attname, correlation
FROM pg_stats
WHERE tablename = 'metrics' AND attname IN ('id', 'created_at');

   attname   | correlation
-------------+-------------
 created_at  |    0.999997
 id          |    0.999997
(1 row)

两条实践结论：id 上的 BRIN 可以当作「便宜的时间索引」的替身（分页、WHERE id > ? 能剪枝）；但不要指望 id 的 BRIN 做点查——WHERE id = 12345 是等值查询，BRIN 只能判断「可能在这个范围里」，最后往往要回表扫一整个范围（默认 128 页）才能确认一行。唯一的点查请交给主键的 B-Tree。

-- 时序表的典型索引组合
CREATE INDEX metrics_created_brin ON metrics USING brin (created_at);  -- 大范围时间查询
-- PRIMARY KEY (id) 自动带来的 B-Tree 负责点查

pages_per_range 的取值直觉

pages_per_range 决定「一个摘要覆盖多少页」，它是精度与体积的旋钮：

pages_per_range 小（如 32）        pages_per_range 大（如 1024）
┌────┬────┬────┬────┐            ┌──────────────┬──────────────┐
│摘要│摘要│摘要│摘要│            │    摘要      │    摘要      │
└────┴────┴────┴────┘            └──────────────┴──────────────┘
摘要多、每段窄 → 剪枝准            摘要少、每段宽 → 剪枝粗
索引大、汇总慢                     索引小、汇总快
回表页少                           回表页多

对一个「每页约 100 行」的表：

pages_per_range	一个摘要覆盖的行数	覆盖的时间跨度（按 1000 行/秒估算）
32	约 3200 行	约 3.2 秒
128（默认）	约 12800 行	约 12.8 秒
1024	约 102400 行	约 102 秒

也就是说：默认值 128 意味着「时间跨度小于十几秒的查询，至少要回表读 128 个页」。典型查询是「查 5 分钟」，128 完全够；典型查询是「查 1 秒」，就该调小。

BRIN 与分区：互补而非竞争

很多人以为「分区已经能裁剪时间，BRIN 是多余的」。这个判断要分情况：

分区方式	时间查询能否靠分区裁剪	还需要 BRIN 吗
按时间 RANGE 分区（每月一个）	能，直接甩掉无关分区	分区内通常不需要（时间区间已很窄）
按时间分区但粒度很粗（每年一个）	部分能	分区内值得建 BRIN，细化到天/小时
按 HASH(device_id) / LIST(region) 分区	不能	分区内非常值得，否则每个分区都要全表扫
不分区，单表几 TB	不能	BRIN 的主场

一句话：分区解决「跨分区的粗裁剪」，BRIN 解决「分区内（或单表内）的细裁剪」，两者正交。

时序表的选型决策树

开始：有一张时序表，要优化按时间范围查询
│
├─ Q1：单表还是分区？
│   ├─ 表 < 几百 GB，写入量可控 ──────────────────→ 单表
│   └─ 表 > 几百 GB，或需要按时间快速删旧数据 ────→ 按时间 RANGE 分区
│
├─ Q2（单表路径）：created_at 的 correlation 是多少？
│   ├─ |correlation| > 0.9 ────→ 建 BRIN，先用默认 pages_per_range=128
│   │                             ├─ 典型查询 < 1 分钟跨度 → 调小到 32
│   │                             └─ 典型查询 > 数小时跨度 → 保持 128 或调到 256
│   ├─ 0.5 < |correlation| < 0.9 → BRIN 收益不稳定，先实测再决定
│   └─ |correlation| < 0.5 ────→ 放弃 BRIN，改用 B-Tree 或先治理写入乱序
│
├─ Q3（分区路径）：分区键是时间吗？
│   ├─ 是（月/周分区）──────→ 分区裁剪已足够，不建 BRIN
│   └─ 否（按设备/租户）────→ 每个分区内建 BRIN(created_at)
│
└─ Q4：还需要按主键点查吗？
    ├─ 需要 ──→ 保留主键 B-Tree（BRIN 不能替代点查）
    └─ 不需要 → 只保留 BRIN 即可，省掉一个巨大的 B-Tree

语法与用法

-- 1. 时序表上建 BRIN（最常用）
CREATE INDEX 索引名 ON 表名 USING brin (时间列);

-- 2. 指定块范围大小（查询跨度小就调小）
CREATE INDEX 索引名 ON 表名 USING brin (时间列) WITH (pages_per_range = 32);

-- 3. 高频写入开启自动汇总
CREATE INDEX 索引名 ON 表名 USING brin (时间列)
    WITH (pages_per_range = 128, autosummarize = on);

-- 4. 分区的时序表内建 BRIN（自动递归到各分区）
CREATE INDEX 索引名 ON 分区表 USING brin (时间列);

-- 5. 并行创建（大表推荐）
SET max_parallel_maintenance_workers = 4;
CREATE INDEX CONCURRENTLY 索引名 ON 表名 USING brin (时间列);

-- 6. 手动汇总新范围
SELECT brin_summarize_new_values('索引名');

-- 7. 重建（乱序写入后）
REINDEX INDEX CONCURRENTLY 索引名;

最小可运行示例——给一张 100 万行的指标表建 BRIN：

DROP TABLE IF EXISTS metrics;

CREATE TABLE metrics (
    id          bigserial,
    created_at  timestamptz NOT NULL,
    device_id   int   NOT NULL,
    metric_name text  NOT NULL,
    value       double precision NOT NULL
);

-- 每秒 1 行，灌 100 万秒（约 11.6 天），时间单调递增
INSERT INTO metrics (created_at, device_id, metric_name, value)
SELECT
    timestamptz '2026-01-01 00:00:00+08' + (g || ' seconds')::interval,
    (g % 512)::int,
    (ARRAY['cpu','mem','disk_io','net_rx'])[1 + (g % 4)],
    random() * 100
FROM generate_series(1, 1000000) AS g;

ANALYZE metrics;

CREATE INDEX metrics_created_brin ON metrics USING brin (created_at);

DROP TABLE
CREATE TABLE
INSERT 0 1000000
ANALYZE
CREATE INDEX

查询一个 10 分钟的时间窗口：

EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF, BUFFERS)
SELECT count(*), avg(value)
FROM metrics
WHERE created_at >= timestamptz '2026-01-05 03:00:00+08'
  AND created_at <  timestamptz '2026-01-05 03:10:00+08';

                                    QUERY PLAN
----------------------------------------------------------------------------------
 Aggregate (actual rows=1 loops=1)
   Buffers: shared hit=481
   ->  Bitmap Heap Scan on metrics (actual rows=600 loops=1)
         Recheck Cond: ((created_at >= ...) AND (created_at < ...))
         Rows Removed by Index Recheck: 6660
         Heap Blocks: exact=479
         Buffers: shared hit=479
         ->  Bitmap Index Scan on metrics_created_brin (actual rows=7260 loops=1)
               Index Cond: ((created_at >= ...) AND (created_at < ...))
               Buffers: shared hit=2
(9 rows)

索引扫描 2 个缓冲页，返回 600 行真实命中——这就是时序表上 BRIN 的日常形态。

详细说明

用 correlation 判定时序列的可行性

动手建索引之前，第一件事永远是看 correlation，它是唯一的硬性门槛：

SELECT attname, correlation, null_frac, n_distinct, most_common_vals
FROM pg_stats
WHERE tablename = 'metrics'
ORDER BY abs(correlation) DESC NULLS LAST;

   attname   | correlation | null_frac | n_distinct |   most_common_vals
-------------+-------------+-----------+------------+----------------------
 created_at  |    0.999998 |         0 |    1000000 |
 id          |    0.999998 |         0 |         -1 |
 device_id   |    0.001337 |         0 |        512 | {…}
 metric_name |    0.001902 |         0 |          4 | {cpu,mem,disk_io,net_rx}
(4 rows)

判定标准：

abs(correlation)	结论
> 0.95	强烈推荐 BRIN
0.9 ~ 0.95	推荐，但需实测确认
0.7 ~ 0.9	边界情况，可能不如 B-Tree，必须实测
< 0.7	不建议，BRIN 大概率退化为全表扫描

ANALYZE 之后 correlation 才有值；新表没分析过时 pg_stats 里查不到行：

-- 先确保统计信息是最新的
ANALYZE metrics;

SELECT attname, correlation
FROM pg_stats
WHERE tablename = 'metrics' AND attname = 'created_at';

   attname   | correlation
-------------+-------------
 created_at  |    0.999998
(1 row)

correlation 会随时间漂移

它反映采样那一刻的物理顺序。持续追加写入会让它保持高位，而 UPDATE、DELETE 会拉低它。把它纳入例行巡检，别只在建索引那天看一次。

pages_per_range 调优实测

pages_per_range 是这张表唯一的性能旋钮。做法永远是「建几个不同取值的索引，实测体积与回表页数，再选一个」。

DROP TABLE IF EXISTS ts_tune;

CREATE TABLE ts_tune (
    id         bigserial,
    created_at timestamptz NOT NULL,
    payload    text NOT NULL
);

INSERT INTO ts_tune (created_at, payload)
SELECT
    timestamptz '2026-01-01 00:00:00+08' + (g || ' milliseconds')::interval,
    repeat('x', 60)
FROM generate_series(1, 5000000) AS g;

ANALYZE ts_tune;

DROP TABLE
CREATE TABLE
INSERT 0 5000000
ANALYZE

CREATE INDEX ts_tune_p16   ON ts_tune USING brin (created_at) WITH (pages_per_range = 16);
CREATE INDEX ts_tune_p128  ON ts_tune USING brin (created_at) WITH (pages_per_range = 128);
CREATE INDEX ts_tune_p1024 ON ts_tune USING brin (created_at) WITH (pages_per_range = 1024);
CREATE INDEX ts_tune_btree ON ts_tune USING btree (created_at);

SELECT
    c.relname                                   AS index_name,
    c.reloptions                                AS storage_options,
    pg_size_pretty(pg_relation_size(c.oid))     AS size
FROM pg_class c
JOIN pg_index i ON i.indexrelid = c.oid
WHERE i.indrelid = 'ts_tune'::regclass
ORDER BY pg_relation_size(c.oid);

  index_name   |   storage_options    |  size
---------------+----------------------+--------
 ts_tune_p1024 | {pages_per_range=1024}| 56 kB
 ts_tune_p128  | {pages_per_range=128} | 376 kB
 ts_tune_p16   | {pages_per_range=16}  | 3.0 MB
 ts_tune_btree |                      | 138 MB
(4 rows)

关键结论：即使把 pages_per_range 调到 16（比默认精确 8 倍），BRIN 仍然只有 B-Tree 的 1/44。这个余量意味着「用空间换精度」在时序场景里几乎总是划算的。

把「20 秒窗口查询」在三个 BRIN 上的表现整理成对照表：

索引	索引体积	候选行数	真实命中	剔除的假阳性	回表堆页
p16	3.0 MB	22400	20000	2400	约 272
p128	376 kB	33280	20000	13280	约 400
p1024	56 kB	204800	20000	184800	约 2456
btree	138 MB	20000	20000	0	约 200

（示例读数，实际数值随页填充率浮动。）读法：p16 的回表页数比 p128 少 1/3，代价是索引大 8 倍——但 3 MB 对一张 600 MB 的表微不足道。对「秒级窗口」，p16~p32 往往比默认值更合适；典型查询是「几十分钟」时，p128 已足够。

不要盲目调小

pages_per_range 调小会让索引项数量正比增长，进而推高索引写入维护开销、VACUUM 汇总耗时与缓存占用。对写入极高频的表可能得不偿失。唯一判断方法是在真实数据量和真实查询上实测。

多 TB 数据的体积实测方法

单表几十亿行时，直接 SELECT count(*) 都要跑很久。评估 BRIN 收益可以用「页数 / 索引项数」推算，避免全表扫描：

-- 表与索引的物理大小（读 pg_class，秒级返回，不扫表）
SELECT
    c.relname,
    am.amname                                  AS method,
    c.relpages                                 AS pages_8k,
    pg_size_pretty(pg_relation_size(c.oid))    AS size
FROM pg_class c
LEFT JOIN pg_am am ON am.oid = c.relam
WHERE c.relname IN ('metrics_ts', 'metrics_ts_created_brin', 'metrics_ts_created_btree')
ORDER BY pg_relation_size(c.oid) DESC;

        relname           | method | pages_8k |  size
--------------------------+--------+----------+---------
 metrics_ts               |        | 46873344 | 358 GB
 metrics_ts_created_btree | btree  |  3842160 | 29 GB
 metrics_ts_created_brin  | brin   |     1012 | 7.9 MB
(3 rows)

relpages 是 pg_class 里缓存的页数，读取它不会扫描表，是 TB 级表上最轻量的体积测量方式。第二步是推算理论体积并验证实测是否合理：

BRIN 索引项数 ≈ 表页数 / pages_per_range
              = 46,873,344 / 128
              ≈ 366,198 项

每项约 30~40 字节（timestamptz 的 min/max 两个 8 字节值 + 头部开销）
理论索引体积 ≈ 366,198 × 32 字节 ≈ 11.7 MB

实测 7.9 MB（含页内压缩与未满页）→ 数量级吻合 ✅

第三步：需要页级统计时用 pageinspect 抽样（不要对大表全量调用）：

SELECT
    count(*)                            AS range_items,
    count(*) FILTER (WHERE placeholder) AS unsummarized,
    min(blknum)                         AS first_block,
    max(blknum)                         AS last_block
FROM brin_page_items(get_raw_page('metrics_ts_created_brin', 1), 'metrics_ts_created_brin');

 range_items | unsummarized | first_block | last_block
-------------+--------------+-------------+------------
        1012 |            0 |           0 |     129536
(1 row)

BRIN 没有树结构

第 0 页是元页，第 1 页起才是摘要页。索引很大时摘要页分布在多个页上，需逐页 get_raw_page(index, page_no) 统计。生产上只做抽样体检，不做全量统计。

BRIN 与分区表结合

分区的时序表上，CREATE INDEX ... USING brin 会自动递归到所有分区（和 B-Tree 一样），后续新建的分区也会自动带上同名索引：

DROP TABLE IF EXISTS metrics_part;

CREATE TABLE metrics_part (
    id         bigserial,
    created_at timestamptz NOT NULL,
    device_id  int NOT NULL,
    value      double precision NOT NULL
) PARTITION BY RANGE (created_at);

CREATE TABLE metrics_part_2026_01 PARTITION OF metrics_part
    FOR VALUES FROM ('2026-01-01') TO ('2026-02-01');
CREATE TABLE metrics_part_2026_02 PARTITION OF metrics_part
    FOR VALUES FROM ('2026-02-01') TO ('2026-03-01');

-- 递归建 BRIN：两个分区各得一个索引
CREATE INDEX ON metrics_part USING brin (created_at);

DROP TABLE
CREATE TABLE
CREATE TABLE
CREATE TABLE
CREATE INDEX

验证索引确实递归到了每个分区：

SELECT
    parent.relname      AS parent_table,
    child.relname       AS partition,
    c.relname           AS brin_index,
    pg_size_pretty(pg_relation_size(c.oid)) AS index_size
FROM pg_inherits i
JOIN pg_class parent ON parent.oid = i.inhparent
JOIN pg_class child  ON child.oid  = i.inhrelid
LEFT JOIN pg_index ix ON ix.indrelid = child.oid
LEFT JOIN pg_class c  ON c.oid = ix.indexrelid
WHERE parent.relname = 'metrics_part'
ORDER BY child.relname;

  parent_table |      partition      |       brin_index        | index_size
---------------+---------------------+-------------------------+------------
 metrics_part  | metrics_part_2026_01| metrics_part_2026_01_...| 32 kB
 metrics_part  | metrics_part_2026_02| metrics_part_2026_02_...| 32 kB
(2 rows)

两种组合方式，用途完全不同：

方式 A：按时间分区 + 不建 BRIN
  查询「2026-02 的某小时」→ 分区裁剪直接命中 2026_02 一个分区，
  再在分区内全表扫 → 已经很快，BRIN 收益有限

方式 B：按 device_id 哈希分区 + 分区内建 BRIN(created_at)
  查询「某设备某小时」→ 哈希裁剪命中 1 个分区，
  但该分区跨越全部时间 → 必须靠 BRIN 把时间范围剪出来 ✅

还有一个必须知道的操作限制：

-- 错误！分区表上不能使用 CONCURRENTLY 建索引
CREATE INDEX CONCURRENTLY ON metrics_part USING brin (created_at);

ERROR:  cannot create index on partitioned table "metrics_part" concurrently

在线给分区表加 BRIN 的标准做法是逐分区 CONCURRENTLY 建，再 ATTACH 到父索引：

-- 1. 先在父表上建一个「只有定义、无数据」的索引（不递归）
CREATE INDEX metrics_part_created_brin ON ONLY metrics_part USING brin (created_at);

-- 2. 每个分区上并发建（子索引名随意，挂上父索引后会统一）
CREATE INDEX CONCURRENTLY metrics_part_2026_01_brin
    ON metrics_part_2026_01 USING brin (created_at);
CREATE INDEX CONCURRENTLY metrics_part_2026_02_brin
    ON metrics_part_2026_02 USING brin (created_at);

-- 3. 把子索引挂到父索引上
ALTER INDEX metrics_part_created_brin ATTACH PARTITION metrics_part_2026_01_brin;
ALTER INDEX metrics_part_created_brin ATTACH PARTITION metrics_part_2026_02_brin;

brin_summarize_new_values() 与维护节奏

BRIN 的摘要在第一次被汇总动作处理前一直是 placeholder（未汇总）。未汇总的范围在查询时会被无条件保留，等于这段时间的剪枝能力归零。触发汇总是三个来源：

触发源	时机	是否可控
VACUUM（手动或 autovacuum）	扫描表时顺带汇总	依赖 autovacuum 策略
autosummarize = on	检测到「跨入下一个范围」的插入时排队定向汇总	需要 reloption 开启
手动函数调用	立即执行	完全可控，推荐

-- 汇总索引中所有还没汇总的范围，返回本次处理的范围数
SELECT brin_summarize_new_values('metrics_created_brin');

 brin_summarize_new_values
---------------------------
                       216
(1 row)

批量导入之后立即调用一次是最省事的习惯：

INSERT INTO metrics (created_at, device_id, metric_name, value)
SELECT * FROM staging_metrics;

ANALYZE metrics;
SELECT brin_summarize_new_values('metrics_created_brin');

INSERT 0 4200000
ANALYZE
 brin_summarize_new_values
---------------------------
                        42
(1 row)

检查还有多少范围没汇总（抽样即可）：

SELECT
    count(*) FILTER (WHERE placeholder) AS unsummarized,
    count(*)                            AS total_ranges
FROM brin_page_items(get_raw_page('metrics_created_brin', 1), 'metrics_created_brin');

 unsummarized | total_ranges
--------------+--------------
            0 |          522
(1 row)

未汇总范围会让 BRIN 「静默失效」

它不会报错、不会告警，只是查询计划里 Heap Blocks: lossy 悄悄变大、Rows Removed by Index Recheck 暴涨。定期巡检 placeholder 比例，是 BRIN 运维里最容易被漏掉的一环。

乱序写入导致失效与重建

下面演示「乱序写入把 BRIN 打废」的完整过程与恢复手段。

DROP TABLE IF EXISTS disorder_demo;

CREATE TABLE disorder_demo (
    id         bigserial,
    created_at timestamptz NOT NULL,
    note       text NOT NULL
);

-- 顺序灌入：2026 年 1 月整月
INSERT INTO disorder_demo (created_at, note)
SELECT
    timestamptz '2026-01-01 00:00:00+08' + (g || ' seconds')::interval,
    'seq'
FROM generate_series(1, 1000000) AS g;

CREATE INDEX disorder_demo_brin ON disorder_demo USING brin (created_at);
ANALYZE disorder_demo;

SELECT correlation FROM pg_stats
WHERE tablename = 'disorder_demo' AND attname = 'created_at';

 correlation
-------------
    0.999999
(1 row)

现在回填历史数据——时间戳落在范围两端，物理位置却在表尾：

-- 回填 1 月初与 2 月初的数据（时间跨度极大，物理位置却在末尾）
INSERT INTO disorder_demo (created_at, note)
SELECT
    timestamptz '2026-01-01 00:00:00+08' + ((g % 60) || ' seconds')::interval,
    'backfill'
FROM generate_series(1, 200000) AS g;

INSERT INTO disorder_demo (created_at, note)
SELECT
    timestamptz '2026-02-01 00:00:00+08' + ((g % 60) || ' seconds')::interval,
    'backfill'
FROM generate_series(1, 200000) AS g;

ANALYZE disorder_demo;

SELECT correlation FROM pg_stats
WHERE tablename = 'disorder_demo' AND attname = 'created_at';

 correlation
-------------
    0.241638
(1 row)

correlation 从 0.999999 掉到 0.24，此时 BRIN 几乎失效：

EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF, BUFFERS)
SELECT count(*) FROM disorder_demo
WHERE created_at >= timestamptz '2026-01-15 00:00:00+08'
  AND created_at <  timestamptz '2026-01-15 01:00:00+08';

                                    QUERY PLAN
----------------------------------------------------------------------------------
 Aggregate (actual rows=1 loops=1)
   Buffers: shared hit=11500
   ->  Bitmap Heap Scan on disorder_demo (actual rows=3600 loops=1)
         Recheck Cond: (...)
         Rows Removed by Index Recheck: 1396380
         Heap Blocks: lossy=11429
         Buffers: shared hit=11400
         ->  Bitmap Index Scan on disorder_demo_brin (actual rows=1399980 loops=1)
               Buffers: shared hit=2
(9 rows)

Heap Blocks: lossy=11429 基本等于整表页数——BRIN 已经把全表扫描伪装成了 Bitmap Heap Scan。

恢复手段按代价从低到高：

-- 手段 1：先尝试重新汇总（对「新范围未汇总」有效，对「摘要区间被撑大」无效）
SELECT brin_summarize_new_values('disorder_demo_brin');

-- 手段 2：反汇总 + 重新汇总（强制重算指定范围的摘要）
SELECT brin_desummarize_range('disorder_demo_brin', 0);
VACUUM disorder_demo;

-- 手段 3：重建索引（彻底，可用 CONCURRENTLY 避免阻塞）
REINDEX INDEX CONCURRENTLY disorder_demo_brin;

但重建也救不回已经乱掉的物理顺序：

ANALYZE disorder_demo;

SELECT correlation FROM pg_stats
WHERE tablename = 'disorder_demo' AND attname = 'created_at';

 correlation
-------------
    0.241638
(1 row)

REINDEX 只是按当前物理顺序重新生成摘要，物理顺序本身没变，所以相关性依旧是 0.24。真正要恢复只有两条路：

路线 A（推荐）：让数据重新物理有序
  CLUSTER disorder_demo USING disorder_demo_created_btree;
  -- 或 pg_repack --table=disorder_demo
  之后 REINDEX + ANALYZE，correlation 回到高位

路线 B（更根本）：改写入路径，避免乱序
  - 回填/历史导入走独立临时表，导入前先按时间排序
  - 多 Writer 场景让应用层按时间分桶
  - 用分区表把不同时间的数据物理隔离

CLUSTER 会持锁

CLUSTER 期间持有 ACCESS EXCLUSIVE 锁，生产大表应改用 pg_repack（在线重排）。无论哪种方式，先确认「乱序」是偶发还是一次性的——如果每天都在回填历史数据，重排只是治标。

用 EXPLAIN 定位瓶颈并与 B-Tree 对比成本

时序表上 BRIN 的计划永远是 Bitmap Index Scan + Bitmap Heap Scan。判断它是否真的有效，只看三个数字：

EXPLAIN (ANALYZE, BUFFERS, COSTS OFF, TIMING OFF)
SELECT device_id, avg(value)
FROM metrics
WHERE created_at >= timestamptz '2026-01-03 00:00:00+08'
  AND created_at <  timestamptz '2026-01-03 01:00:00+08'
GROUP BY device_id;

                                    QUERY PLAN
----------------------------------------------------------------------------------
 HashAggregate (actual rows=512 loops=1)
   Group Key: device_id
   Buffers: shared hit=3310
   ->  Bitmap Heap Scan on metrics (actual rows=3600 loops=1)
         Recheck Cond: ((created_at >= ...) AND (created_at < ...))
         Rows Removed by Index Recheck: 37680
         Heap Blocks: exact=3296
         Buffers: shared hit=3296
         ->  Bitmap Index Scan on metrics_created_brin (actual rows=41280 loops=1)
               Index Cond: ((created_at >= ...) AND (created_at < ...))
               Buffers: shared hit=2
(11 rows)

判定标准：

指标 1：Bitmap Index Scan 的 Buffers
  只有 1~3 页 → 索引扫描本身几乎免费 ✅
  上百页      → 索引项太多，考虑调大 pages_per_range

指标 2：Rows Removed by Index Recheck / actual rows
  假阳性率 < 50%  → 摘要质量良好 ✅
  假阳性率 > 90%  → 摘要太粗，调小 pages_per_range，或检查 correlation

指标 3：Heap Blocks / 表总页数
  < 5%   → BRIN 收益显著 ✅
  > 30%  → 已接近全表扫描，考虑分区或 B-Tree

把这三项做成巡检查询：

SELECT
    relname                                     AS index_name,
    idx_scan,
    idx_tup_read,
    idx_tup_fetch,
    pg_size_pretty(pg_relation_size(indexrelid)) AS index_size
FROM pg_stat_user_indexes
WHERE indexrelname LIKE '%brin%'
ORDER BY idx_scan DESC;

     index_name      | idx_scan | idx_tup_read | idx_tup_fetch | index_size
---------------------+----------+--------------+---------------+------------
 metrics_created_brin|     1842 |     13849210 |        912334 | 7.9 MB
(1 row)

idx_tup_read 远大于 idx_tup_fetch 就说明有大量候选行在复检时被丢弃——这是 BRIN 的固有代价，关键看绝对值是否可接受。

为什么宽范围查询上 BRIN 能追平 B-Tree？因为两者读的堆页数量接近，但读的索引页数量差几个数量级：

查询「1 小时窗口」，表中该窗口占 1/744 的数据

B-Tree：
  索引定位到起点  → 读 log(N) 个索引页（N 行 → 约 4 层）
  回表读取 1/744 的数据 → 读 H 个堆页
  总成本 ≈ 4 + H

BRIN：
  扫描全部摘要（约 550 个索引项，全在 2 个页里）→ 读 2 个索引页
  摘要筛选出候选范围 → 命中约 1/744 的堆页
  回表读取命中页 + 少量假阳性页 → 读 H + ε 个堆页
  总成本 ≈ 2 + H + ε

代价几乎相同，但索引本身的维护成本天差地别：

成本项	B-Tree	BRIN
索引体积（1 TB 表）	数十 GB	数 MB ~ 数十 MB
单次 INSERT 的索引维护	随机页写入，可能触发页分裂	通常只更新当前范围摘要，追加式
缓存占用（shared_buffers）	大，常挤占数据缓存	极小，几乎全部常驻
建索引耗时	需要排序，长	顺序扫一遍，短
VACUUM 时的索引清理	遍历所有索引项	只检查摘要

宽范围查询是 BRIN 的主场，窄范围点查不是

把上表反过来看：如果查询是「取某设备最近 1 条」，B-Tree 走 Index Scan 只需读 4 个索引页 + 1 个堆页；BRIN 则要扫摘要找到候选范围，回表读 128 个堆页才能确认。能力边界很清楚：BRIN 优化「读一批页」，B-Tree 优化「读一行」。 时序表的分析型查询属于前者。

实战示例

完整走一遍：一张 2000 万行的访问日志表，从建表、灌数、建索引到计划对比与耗时实测。

第 1 步：建表并灌入 2000 万行

DROP TABLE IF EXISTS access_log;

CREATE TABLE access_log (
    id         bigserial,
    created_at timestamptz NOT NULL,
    client_ip  inet        NOT NULL,
    path       text        NOT NULL,
    status     int         NOT NULL,
    cost_ms    int         NOT NULL
);

-- 20 天，每秒约 12 条，共 2000 万行，时间严格单调递增
INSERT INTO access_log (created_at, client_ip, path, status, cost_ms)
SELECT
    timestamptz '2026-01-01 00:00:00+08' + ((g / 12) || ' seconds')::interval,
    ('10.' || (g % 64) || '.' || ((g / 64) % 256) || '.' || ((g / 16384) % 256))::inet,
    (ARRAY['/api/users','/api/orders','/api/items','/healthz','/static/app.js'])[1 + (g % 5)],
    (ARRAY[200,200,200,404,500])[1 + (g % 5)],
    (g % 900)::int
FROM generate_series(1, 20000000) AS g;

ANALYZE access_log;

SELECT pg_size_pretty(pg_relation_size('access_log')) AS table_size;

DROP TABLE
CREATE TABLE
INSERT 0 20000000
ANALYZE
 table_size
------------
 1817 MB
(1 row)

第 2 步：验证相关性

SELECT attname, correlation, n_distinct
FROM pg_stats
WHERE tablename = 'access_log'
ORDER BY abs(correlation) DESC;

   attname   | correlation | n_distinct
-------------+-------------+------------
 created_at  |    0.999999 |   17279999
 id          |    0.999999 |         -1
 client_ip   |    0.000021 |       4128
 cost_ms     |   -0.000143 |        900
 path        |   -0.000198 |          5
 status      |    0.000301 |          5
(6 rows)

只有 created_at 与 id 符合 BRIN 条件。

第 3 步：建索引

-- BRIN：时间列（本节主角）
CREATE INDEX access_log_created_brin ON access_log USING brin (created_at);

-- BRIN：id（验证「单调主键也可以走 BRIN」）
CREATE INDEX access_log_id_brin ON access_log USING brin (id);

-- B-Tree：时间列（对照）
CREATE INDEX access_log_created_btree ON access_log USING btree (created_at);

-- B-Tree：IP 列（BRIN 不适用的对照）
CREATE INDEX access_log_ip_btree ON access_log USING btree (client_ip);

CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX

第 4 步：体积对比

SELECT
    c.relname                                     AS index_name,
    am.amname                                     AS method,
    pg_size_pretty(pg_relation_size(c.oid))       AS size
FROM pg_class c
JOIN pg_index i ON i.indexrelid = c.oid
JOIN pg_am am   ON am.oid = c.relam
WHERE i.indrelid = 'access_log'::regclass
ORDER BY pg_relation_size(c.oid);

       index_name         | method |  size
--------------------------+--------+---------
 access_log_created_brin  | brin   | 120 kB
 access_log_id_brin       | brin   | 120 kB
 access_log_ip_btree      | btree  | 162 MB
 access_log_created_btree | btree  | 419 MB
(4 rows)

同样一列 created_at：BRIN 120 kB vs B-Tree 419 MB，相差约 3500 倍。两个 BRIN 加起来（240 kB）还不到 B-Tree 的千分之一。

第 5 步：宽范围查询（1 天）

EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF, BUFFERS)
SELECT count(*)
FROM access_log
WHERE created_at >= timestamptz '2026-01-10 00:00:00+08'
  AND created_at <  timestamptz '2026-01-11 00:00:00+08';

                                    QUERY PLAN
----------------------------------------------------------------------------------
 Aggregate (actual rows=1 loops=1)
   Buffers: shared hit=21752
   ->  Bitmap Heap Scan on access_log (actual rows=1036800 loops=1)
         Recheck Cond: ((created_at >= ...) AND (created_at < ...))
         Rows Removed by Index Recheck: 0
         Heap Blocks: exact=21752
         Buffers: shared hit=21752
         ->  Bitmap Index Scan on access_log_created_brin (actual rows=1036800 loops=1)
               Index Cond: ((created_at >= ...) AND (created_at < ...))
               Buffers: shared hit=2
(9 rows)

1 天窗口正好覆盖大片连续范围，假阳性为 0——BRIN 在这个尺度上几乎精确。

第 6 步：中范围查询（10 分钟）的双计划对比

-- 走 BRIN（默认规划器选择）
EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF, BUFFERS)
SELECT count(*) FROM access_log
WHERE created_at >= timestamptz '2026-01-10 12:00:00+08'
  AND created_at <  timestamptz '2026-01-10 12:10:00+08';

                                    QUERY PLAN
----------------------------------------------------------------------------------
 Aggregate (actual rows=1 loops=1)
   Buffers: shared hit=1154
   ->  Bitmap Heap Scan on access_log (actual rows=7200 loops=1)
         Recheck Cond: ((created_at >= ...) AND (created_at < ...))
         Rows Removed by Index Recheck: 42046
         Heap Blocks: exact=1152
         Buffers: shared hit=1152
         ->  Bitmap Index Scan on access_log_created_brin (actual rows=49246 loops=1)
               Buffers: shared hit=2
(8 rows)

-- 强制走 B-Tree 对比
SET enable_bitmapscan = off;
SET enable_seqscan = off;

EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF, BUFFERS)
SELECT count(*) FROM access_log
WHERE created_at >= timestamptz '2026-01-10 12:00:00+08'
  AND created_at <  timestamptz '2026-01-10 12:10:00+08';

RESET enable_bitmapscan;
RESET enable_seqscan;

                                 QUERY PLAN
-----------------------------------------------------------------------------
 Aggregate (actual rows=1 loops=1)
   Buffers: shared hit=1150
   ->  Index Only Scan using access_log_created_btree on access_log
         Index Cond: ((created_at >= ...) AND (created_at < ...))
         Heap Fetches: 0
         Buffers: shared hit=1150
(5 rows)

结论：堆页读取量几乎相同（1152 vs 1150 页）；BRIN 的索引扫描只读 2 页（全部摘要在 2 个页里），B-Tree 要读多层索引页；B-Tree 的 1150 页全是索引页（Index Only Scan），而 B-Tree 索引本身占 419 MB 常驻缓存压力，BRIN 只占 120 kB。Index Only Scan 之所以成立，是因为 B-Tree 索引恰好覆盖了唯一被查询的列、且表刚被 VACUUM 过。BRIN 无法支持覆盖扫描，这点要牢记。

第 7 步：pages_per_range 调小后的效果验证

针对「秒级窗口」这类查询（上面第 6 步的假阳性率高达 85%），减小 pages_per_range 效果明显：

CREATE INDEX access_log_created_brin_p32
    ON access_log USING brin (created_at) WITH (pages_per_range = 32);

SELECT indexrelname,
       pg_size_pretty(pg_relation_size(indexrelid)) AS size,
       reloptions
FROM pg_stat_user_indexes
WHERE relname = 'access_log' AND indexrelname LIKE '%brin%'
ORDER BY pg_relation_size(indexrelid);

         indexrelname          |  size   |         reloptions
-------------------------------+---------+-----------------------------
 access_log_created_brin       | 120 kB  | {pages_per_range=128}
 access_log_id_brin            | 120 kB  | {pages_per_range=128}
 access_log_created_brin_p32   | 480 kB  | {pages_per_range=32}
(3 rows)

480 kB 换来 4 倍精度的剪枝。对 1.8 GB 的表而言，这点空间可以忽略。

第 8 步：汇总与收尾

-- 汇总新增数据产生的未汇总范围
SELECT brin_summarize_new_values('access_log_created_brin');

-- 清理临时对照索引
DROP INDEX access_log_created_brin_p32;
DROP INDEX access_log_id_brin;

SELECT
    indexrelname                                  AS index_name,
    idx_scan,
    pg_size_pretty(pg_relation_size(indexrelid))   AS size
FROM pg_stat_user_indexes
WHERE relname = 'access_log'
ORDER BY pg_relation_size(indexrelid);

 brin_summarize_new_values
---------------------------
                         5
(1 row)
DROP INDEX
DROP INDEX
       index_name        | idx_scan |  size
--------------------------+----------+---------
 access_log_created_brin  |        3 | 120 kB
 access_log_ip_btree      |        1 | 162 MB
 access_log_created_btree |        1 | 419 MB
(3 rows)

idx_scan 是选型的最硬依据：如果某个索引长期 idx_scan = 0，无论它多小都该删掉。

注意事项

• 错误！在 correlation 低的列上建 BRIN 是无效的。 它不报错，只是把全表扫描伪装成 Bitmap Heap Scan。

-- 该列 correlation ≈ 0.0001，建了等于白建
CREATE INDEX access_log_path_brin ON access_log USING brin (path);

• 错误！BRIN 不能替代主键点查。 WHERE id = ? 用 BRIN 会回表扫一整个范围。

-- 错误用法：指望 BRIN 做点查
EXPLAIN ANALYZE SELECT * FROM access_log WHERE id = 1234567;

• 注意：批量导入后必须汇总，否则新数据区间的剪枝能力为零。

INSERT INTO access_log SELECT * FROM staging;
ANALYZE access_log;
SELECT brin_summarize_new_values('access_log_created_brin');  -- 别忘了这句

• 注意：UPDATE 任意列都会破坏物理顺序。 时序表应设计为 append-only。

-- 不推荐：频繁更新会逐步磨掉 correlation
UPDATE access_log SET cost_ms = 0 WHERE created_at < now() - interval '30 days';

• 不推荐在按时间分区的分区内再建 BRIN。 分区裁剪已经命中单个分区，BRIN 收益很小。

-- 收益有限
CREATE INDEX ON access_log_monthly_2026_01 USING brin (created_at);

• 注意：pages_per_range 无法通过 ALTER INDEX 修改。 想改只能 DROP + CREATE。

-- 无效：不会改变已有索引的范围划分
ALTER INDEX access_log_created_brin SET (pages_per_range = 32);

• 注意：分区表上不能 CREATE INDEX CONCURRENTLY。 必须逐分区建再 ATTACH PARTITION。

-- 错误！cannot create index on partitioned table ... concurrently
CREATE INDEX CONCURRENTLY ON access_log_monthly USING brin (created_at);

• REINDEX 不会修复乱序。 它按当前物理顺序重新生成摘要，correlation 不变。

REINDEX INDEX CONCURRENTLY access_log_created_brin;  -- 物理顺序没变，相关性照旧

• 不推荐给「按设备查最近一条」这类查询依赖 BRIN。 这是复合 B-Tree 索引 (device_id, created_at DESC) 的主场。

• autosummarize 默认关闭。 高频写入且不跑定期 VACUUM 的表，务必显式开启。

ALTER INDEX access_log_created_brin SET (autosummarize = on);

最佳实践

• 建索引前先跑「候选列体检」，把 correlation 当作准入门槛：

  SELECT attname, correlation, n_distinct
  FROM pg_stats
  WHERE tablename = 'your_timeseries_table' AND abs(correlation) > 0.9
  ORDER BY abs(correlation) DESC;

• 默认 pages_per_range = 128 起步，按典型查询跨度决定是否调小：

  -- 典型查询为「秒级窗口」→ 调小
  CREATE INDEX idx_ts_brin ON your_table USING brin (created_at)
      WITH (pages_per_range = 32, autosummarize = on);
  
  -- 典型查询为「小时/天级窗口」→ 默认值即可
  CREATE INDEX idx_ts_brin ON your_table USING brin (created_at)
      WITH (autosummarize = on);

• 在 ETL / 批量导入流程末尾固定加一句汇总：

  ANALYZE your_table;
  SELECT brin_summarize_new_values('idx_ts_brin');

• 按时间分区粒度决定是否还需要 BRIN：月/周分区 → 不需要；年分区或按非时间维度分区 → 需要在分区内建 BRIN。

  -- 按设备哈希分区 + 分区内 BRIN（时间查询靠 BRIN 裁剪）
  CREATE TABLE metrics_p (id bigserial, created_at timestamptz, device_id int)
      PARTITION BY HASH (device_id);
  CREATE INDEX ON metrics_p USING brin (created_at);

• 分区表在线加 BRIN 走「ON ONLY + 逐分区 CONCURRENTLY + ATTACH」三步，避免长时间持 ACCESS EXCLUSIVE 锁：

  CREATE INDEX idx_p_brin ON ONLY metrics_p USING brin (created_at);
  CREATE INDEX CONCURRENTLY idx_p_0_brin ON metrics_p_0 USING brin (created_at);
  ALTER INDEX idx_p_brin ATTACH PARTITION idx_p_0_brin;

• 把「相关性」「未汇总比例」「索引使用率」三项做成定期巡检：

  -- 1) 相关性巡检
  SELECT tablename, attname, correlation
  FROM pg_stats
  WHERE tablename = 'your_table' AND attname = 'created_at';
  
  -- 2) 未汇总范围巡检（抽样）
  SELECT count(*) FILTER (WHERE placeholder) AS unsummarized, count(*) AS total
  FROM brin_page_items(get_raw_page('idx_ts_brin', 1), 'idx_ts_brin');
  
  -- 3) 索引使用率巡检
  SELECT indexrelname, idx_scan, idx_tup_read, idx_tup_fetch
  FROM pg_stat_user_indexes
  WHERE relname = 'your_table' AND idx_scan = 0;

• 写入乱序不可控时，改架构而不是反复重建索引：让回填数据先按时间排序再导入。

  CREATE TEMP TABLE backfill_sorted AS
  SELECT * FROM staging_metrics ORDER BY created_at;
  INSERT INTO metrics SELECT * FROM backfill_sorted ORDER BY created_at;

• 大表建 BRIN 时开并行，并注意 CREATE INDEX CONCURRENTLY 在分区表上的限制：

  SET max_parallel_maintenance_workers = 4;
  CREATE INDEX CONCURRENTLY idx_ts_brin ON your_table USING brin (created_at);
  RESET max_parallel_maintenance_workers;

• 同时保留主键 B-Tree 与时间 BRIN，让两类查询各走各的路：

  -- 主键 B-Tree：服务点查/按 id 关联（由 PRIMARY KEY 自动创建）
  -- 时间 BRIN：服务时间范围聚合
  CREATE INDEX idx_ts_brin ON your_table USING brin (created_at);

下一节

继续学习：表达式索引

参考链接

• PostgreSQL 官方文档：BRIN 索引（原理、操作符类、pages_per_range）
• PostgreSQL 官方文档：索引类型总览
• PostgreSQL 官方文档：CREATE INDEX（存储参数与 CONCURRENTLY）
• PostgreSQL 官方文档：表分区
• PostgreSQL 官方文档：索引维护函数（brin_summarize_new_values 等）
• PostgreSQL 官方文档：数据库尺寸函数（pg_relation_size）
• PostgreSQL 官方文档：pg_stats 视图（correlation）
• PostgreSQL 官方文档：pageinspect 扩展（brin_page_items）
• PostgreSQL 官方文档：用 EXPLAIN 分析查询
• PostgreSQL 官方文档：ALTER TABLE（ATTACH PARTITION）