开发者日记

外观

从全表扫描到百倍加速：BRIN 索引实战避坑实录 ​

问题背景：TB 级日志表为什么不适合继续堆 B-tree ​

在监控、审计、IoT、网关访问日志等场景中，PostgreSQL 表通常按时间持续追加，查询却集中在“最近 10 分钟”“某小时错误日志”“两个时间点之间的链路记录”。这类表有两个鲜明特征：数据量很快进入亿行甚至 TB 级；时间列与物理存储顺序高度相关。

很多团队的第一反应仍是给 created_at 创建普通 B-tree 索引。短期确实有效，但随着数据增长会遇到三个问题：

  1. 索引体积可能达到表的 15%～30%，TB 级表意味着数百 GB 额外存储；
  2. 高并发写入时，每次插入都要维护庞大的索引结构，WAL 与缓存压力上升；
  3. 查询范围稍大时，优化器可能认为随机回表成本过高，转而放弃索引，重新执行全表扫描。

BRIN（Block Range INdex，块范围索引）不是为任意等值查询设计的。它记录每个或每若干个数据块中某列的最小值和最大值。对于顺序写入的时间列，BRIN 可以用极小的元数据跳过大量不可能包含目标时间范围的数据块，从而以约 0.1% 级别的存储开销，换取范围查询数量级的性能提升。

方案对比：BRIN、B-tree 与分区表分别解决什么问题 ​

BRIN 适合“物理有序”的时序范围查询 ​

BRIN 的核心单位是 block range，例如每 128 个页面形成一个范围，索引中只保存该范围内时间列的最小值、最大值和异常信息。查询条件带有 created_at >= ? AND created_at < ? 时，执行器可以跳过最小值、最大值与查询区间不相交的块范围。

它的优势是小、写入轻、维护便宜；前提是列值与表中物理行位置具有较强相关性。如果同一时间段的数据散落在整张表各处，BRIN 的裁剪能力会明显下降。

B-tree 适合高选择性与多场景访问 ​

B-tree 对随机分布数据、精确匹配、排序和多种操作符都很稳健。例如按 trace_id = ? 查询单条链路，B-tree 通常是最佳选择。但在 TB 级日志表上，仅为时间范围扫描维护 B-tree，往往性价比不高。

分区表适合生命周期管理，而不是替代索引 ​

按天或按月声明式分区，可以直接 DROP PARTITION 清理历史数据，也能做分区裁剪。但分区并不能解决单分区内的物理过滤问题，分区数量过多还会增加元数据与计划时间。实际项目中常见组合是：按月分区承载生命周期，单分区内对时间列建立 BRIN，再对高基数字段如请求 ID 建 B-tree。

时间范围查询09:00-10:00读取 BRIN 摘要跳过无关块范围顺序扫描少量数据块低 I/O、高吞吐无索引全表扫描读取 TB 级冷数据前提：时间列与物理写入顺序强相关

实操步骤：构造可复现的时序日志表 ​

下面的示例可以直接在 PostgreSQL 12 及以上版本执行。为了避免真的写入 TB 级数据，我们使用 generate_series 模拟按时间顺序追加的日志，并通过 pg_relation_size 对比索引体积。

sql
-- 1. 创建时序日志表
DROP TABLE IF EXISTS app_log;

CREATE TABLE app_log (
    id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    created_at  timestamptz NOT NULL,
    level       text NOT NULL,
    service     text NOT NULL,
    message     text NOT NULL
);

-- 2. 模拟顺序写入：每 10 毫秒一条日志
INSERT INTO app_log (created_at, level, service, message)
SELECT
    clock,
    CASE (random() * 100)::int
        WHEN 0 THEN 'ERROR'
        WHEN 1 THEN 'WARN'
        ELSE 'INFO'
    END AS level,
    (ARRAY['order', 'payment', 'gateway', 'search'])[1 + (random() * 3)::int] AS service,
    'synthetic log message for performance test ' || gs
FROM generate_series(
    TIMESTAMPTZ '2026-01-01 00:00:00+08',
    TIMESTAMPTZ '2026-01-07 23:59:59+08',
    INTERVAL '10 milliseconds'
) WITH ORDINALITY AS s(clock, gs);

-- 3. 确保规划器拥有最新统计信息
ANALYZE app_log;

先执行一个典型范围查询，并查看执行计划。此时只有主键索引，时间条件无法有效利用它。

sql
EXPLAIN (ANALYZE, BUFFERS)
SELECT id, created_at, level, service, message
FROM app_log
WHERE created_at >= TIMESTAMPTZ '2026-01-03 09:00:00+08'
  AND created_at <  TIMESTAMPTZ '2026-01-03 10:00:00+08'
  AND level = 'ERROR'
ORDER BY created_at;

在未建立时间索引时，计划通常表现为 Seq Scan，并伴随较高的 Buffers: shared read。如果缓存未命中，还会产生大量实际磁盘读取。

创建 BRIN 索引：用摘要替代完整键树 ​

为时间列创建默认 BRIN 索引：

sql
CREATE INDEX app_log_created_at_brin
ON app_log
USING BRIN (created_at);

ANALYZE app_log;

再次查看执行计划：

sql
EXPLAIN (ANALYZE, BUFFERS)
SELECT id, created_at, level, service, message
FROM app_log
WHERE created_at >= TIMESTAMPTZ '2026-01-03 09:00:00+08'
  AND created_at <  TIMESTAMPTZ '2026-01-03 10:00:00+08'
  AND level = 'ERROR'
ORDER BY created_at;

理想计划会出现类似以下节点：

text
Bitmap Heap Scan on app_log
  Recheck Cond: ...
  Rows Removed by Block Summary: ...
  ->  Bitmap Index Scan on app_log_created_at_brin

其中 Rows Removed by Block Summary 或块范围过滤相关计数越高，说明 BRIN 跳过的数据越多。需要强调的是，BRIN 不是像 B-tree 一样精确定位每条元组，而是先缩小堆表块范围，再在这些块中应用过滤条件。

使用下面的 SQL 对比表、B-tree 与 BRIN 的体积：

sql
-- 创建一个 B-tree 时间索引用于对比
CREATE INDEX app_log_created_at_btree
ON app_log (created_at);

SELECT
    pg_size_pretty(pg_relation_size('app_log')) AS table_size,
    pg_size_pretty(pg_relation_size('app_log_created_at_brin')) AS brin_size,
    pg_size_pretty(pg_relation_size('app_log_created_at_btree')) AS btree_size;

在严格顺序写入的日志表上，BRIN 通常只有若干个数据页，而 B-tree 会随行数线性增长。所谓“0.1% 存储开销”，本质上来自 BRIN 只保存块范围摘要，而不是为每行保存一个可导航的键值指针。

pages_per_range 调优：裁剪能力与索引体积的平衡 ​

pages_per_range 是 BRIN 最关键的参数，表示每个摘要覆盖多少个堆页面。默认值通常是 128。值越小，摘要越精细，裁剪能力越强，但索引更大、维护频率更高；值越大，索引更小，但每个范围覆盖的数据更杂，容易出现“一个范围跨很长时间”的问题。

对于 8KB 页面、单行约数百字节的日志表，可以先在测试环境比较 32、64、128 三档：

sql
DROP INDEX IF EXISTS app_log_created_at_brin;

CREATE INDEX app_log_created_at_brin
ON app_log
USING BRIN (created_at) WITH (
    pages_per_range = 64,
    autosummarize = on
);

ANALYZE app_log;

调优时不要只看索引大小，应同时观察 EXPLAIN (ANALYZE, BUFFERS) 中的共享块读取、执行时间和被块摘要过滤的行数。常见经验是：

  • 数据严格追加、单表极大、查询范围较宽：可保留默认 128；
  • 查询常落在几分钟级别、I/O 成本高：尝试 32 或 64；
  • 数据延迟到达、同一批文件乱序导入严重：先解决物理相关性，再盲目调小参数。

让 BRIN 稳定生效的写入与维护方式 ​

BRIN 对持续追加表很友好，但新建数据页在被摘要记录前，可能处于未汇总状态。PostgreSQL 会在 autovacuum 时汇总 BRIN 索引，也可以开启 autosummarize，让检测到新增块范围时自动调度汇总。

sql
ALTER TABLE app_log SET (
    fillfactor = 90,
    autovacuum_vacuum_scale_factor = 0.02,
    autovacuum_analyze_scale_factor = 0.01
);

ALTER INDEX app_log_created_at_brin SET (
    autosummarize = on
);

如果刚完成大批量导入，需要立即执行报表查询，可以手动汇总：

sql
-- 手动维护 BRIN 摘要，适合批量导入或数据回填后执行
SELECT brin_summarize_new_values('app_log_created_at_brin'::regclass);

ANALYZE app_log;

对于历史数据修复或乱序灌入，更推荐按时间排序重写表，使物理顺序重新与时间列一致：

sql
-- 警告：大表重写会产生大量 I/O 与锁，应在低峰期或使用 pg_repack 等方案
-- VACUUM FULL 会持有强锁，禁止直接在繁忙生产库执行
-- VACUUM FULL app_log;

-- 更安全的思路：按分区迁移、新建有序表后切换，或使用在线重排工具

如果业务同时需要按错误级别过滤，不建议简单建立 USING brin (created_at, level) 就期待它解决所有问题。level 只有 INFO、WARN、ERROR 几个值，块范围摘要几乎无法排除大部分块。更实际的写法是结合部分索引：

sql
-- 只给错误日志建立较小的 B-tree，适合低频但高价值的精确排查
CREATE INDEX app_log_error_created_at_idx
ON app_log (created_at)
WHERE level = 'ERROR';

这样，时间扫描由 BRIN 负责粗筛，错误日志的精确定位由部分索引承担，写入与存储成本也低于全量 B-tree。

避坑总结：BRIN 不是“万能小索引” ​

坑 1：列没有物理相关性，却期待 BRIN 像 B-tree 一样精准 ​

常见现象是创建 BRIN 后仍然走 Seq Scan，或者 Bitmap Heap Scan 读取了绝大多数页面。原因通常是表经过长期 UPDATE、删除后又插入、多线程写入不同时间分区，或者历史数据无序导入。此时每个块范围的最小值和最大值跨度很大，摘要无法排除块。

修正方法是先检查相关性，再决定索引策略：

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

correlation 接近 1 或 -1 时，BRIN 通常更有优势；接近 0 时应优先使用 B-tree、分区整理或按时间重写数据。

坑 2：批量导入后没有汇总，查询计划突然退化 ​

典型现象是白天查询很快，凌晨导入后第一批报表变慢；手动 ANALYZE 后也没有明显改善。原因是统计信息更新不等于 BRIN 新块摘要已经完成，未汇总范围可能被扫描以保证结果正确性。

修正方法是大批量加载后执行 brin_summarize_new_values，或在索引上开启 autosummarize。同时不要完全禁用 autovacuum，否则不仅摘要滞后，死元组与统计信息也会一起恶化。

坑 3：盲目把 pages_per_range 调到 1 ​

有些团队发现 BRIN 很小，就把 pages_per_range = 1 当作“索引越细越快”。在超大表上，这会显著增加索引条目、WAL 量和每次汇总的成本，写入放大可能抵消查询收益。

修正方法是以真实查询窗口做 A/B 测试，分别比较 32、64、128 的实际缓冲区读取和 P95 延迟，而不是只比较索引体积。大多数顺序日志表从 128 调整到 32 或 64 就能获得明显收益，不必直接走到极端值。

坑 4：对低基数列滥用 BRIN 多列索引 ​

给状态、级别、布尔标志这类低基数字段建立 BRIN，常常无法裁剪块，因为同一个块内本来就包含几乎所有状态值。现象是执行计划虽然使用了 BRIN，但过滤行数很少，查询仍然读取大量堆页。

修正方法是把 BRIN 留给与物理顺序相关的时间、自增 ID 或序列号；对高选择性字段使用 B-tree，对低频条件使用部分索引，对 JSONB、数组、全文检索等复杂包含关系再考虑 GIN。

坑 5：只看单次执行时间，忽略缓存与回表成本 ​

BRIN 后处理的是堆表块，命中大量缓存时可能表现平平；缓存冷启动或范围跨越大量历史数据时，优势才会非常明显。反过来，如果查询选择列很宽、结果集很大，即便块裁剪有效，网络传输和排序也可能成为瓶颈。

修正方法是使用 EXPLAIN (ANALYZE, BUFFERS) 对比共享命中、磁盘读取和执行时间，并配合时间分区、冷热表拆分、列裁剪以及 LIMIT，不要把 BRIN 当作整体报表性能的唯一手段。

结语 ​

BRIN 的价值不在于替代所有索引，而在于精准利用时序日志表“持续追加、物理有序、按时间范围扫描”的特征。TB 级场景下，一个设计合理的 BRIN 可以让数据库跳过绝大多数堆块，同时把索引存储和写入维护成本压到极低。真正落地时，请先用数据验证相关性，再调整 pages_per_range，最后补齐批量导入后的摘要维护；这三步做到位，百倍范围查询加速才不是偶然，而会成为稳定的生产表现。

免责声明

本工具与文章内容仅用于学习测试场景，优化建议仅供参考，请勿直接用于生产环境，由此产生的风险自行承担。

本文来自开发者日记（devdiary.cn）· 记录开发者实战笔记与免费在线工具箱

免责声明

本工具与文章内容仅用于学习测试场景，优化建议仅供参考，请勿直接用于生产环境，由此产生的风险自行承担。