5TB 日志表实测：BRIN 千分之一体积换多少性能

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

业务日志表通常具有两个鲜明特征：数据按时间持续追加，查询几乎都带时间范围；单表体量却会迅速膨胀。我们维护的一张 PostgreSQL 日志表约 5TB，日增 18 亿行，早期在 created_at 上建了普通 B-Tree 索引。结果是索引体积接近 1.1TB，备份、恢复、VACUUM、分区切换都变得沉重，而真正高频的查询只是“最近 10 分钟错误日志”或“某小时按状态聚合”。

BRIN（Block Range Index）记录一段连续数据块中的最小/最大值，不索引每一行。对于物理顺序与时间顺序基本一致的时序日志，它通常能用千分之一左右的体积帮助规划器跳过大量无关数据块。问题在于：它到底能换回多少查询性能？什么情况下又会失效？下面用可复现实验和线上实测说明。

方案对比：BRIN、B-Tree 与分区表如何选择

先给结论：BRIN 不是 B-Tree 的替代品，而是顺序型宽表的低成本粗筛器。B-Tree 适合高选择性、任意排序、唯一性约束；BRIN 适合追加写入、字段与物理存储强相关、条件能裁剪大范围块的场景；分区表则从物理文件层面管理生命周期，常与 BRIN 配合使用。

在我们的样本中，B-Tree 索引约为表体积的 22%，BRIN 使用默认 128 个块一个区间时只有约 4.2GB，体积约为 B-Tree 的千分之四。若把 pages_per_range 调到 32，BRIN 增大到 16GB，裁剪粒度更细，但仍然只有 B-Tree 的 1.5% 左右。

实测环境与数据构造：先确认物理顺序

以下 SQL 可直接运行，用于构造一张典型追加式日志表。正式测试时可把 generate_series 的规模调大，并在测试前后分别用 \dt+、\di+ 或 pg_relation_size 查看体积。

-- 1. 创建追加式时序日志表
BEGIN;
DROP TABLE IF EXISTS app_log_brin_demo;
CREATE TABLE app_log_brin_demo (
    id          bigint GENERATED ALWAYS AS IDENTITY,
    created_at  timestamptz NOT NULL DEFAULT now(),
    level       text NOT NULL,
    service     text NOT NULL,
    trace_id    uuid NOT NULL DEFAULT gen_random_uuid(),
    message     text NOT NULL
);

-- 2. 模拟按时间顺序写入：这是 BRIN 有效的前提
INSERT INTO app_log_brin_demo(created_at, level, service, message)
SELECT
    now() - (s ||' second')::interval,
    CASE WHEN random() < 0.02 THEN 'ERROR'
         WHEN random() < 0.20 THEN 'WARN'
         ELSE 'INFO' END,
    'svc-' || (s % 20),
    'sequential log message payload '
      || repeat('x', 80)
FROM generate_series(1, 5000000) AS s;
COMMIT;

-- 3. 分别建立 B-Tree 与 BRIN
CREATE INDEX idx_log_btree_time
  ON app_log_brin_demo(created_at);

CREATE INDEX idx_log_brin_time
  ON app_log_brin_demo
  USING brin(created_at) WITH (pages_per_range = 128);

ANALYZE app_log_brin_demo;

-- 4. 对比索引体积，单位为 MB
SELECT
  pg_size_pretty(pg_relation_size('app_log_brin_demo')) AS table_size,
  pg_size_pretty(pg_relation_size('idx_log_btree_time')) AS btree_size,
  pg_size_pretty(pg_relation_size('idx_log_brin_time')) AS brin_size;

判断 BRIN 是否靠谱，不能只看字段类型，而要看列值和堆块物理顺序是否相关。PostgreSQL 提供了相关性统计：越接近 1 或 -1，越适合 BRIN；接近 0 时，最小值和最大值会覆盖很大范围，裁剪能力会明显下降。

-- 查看 created_at 与物理存储顺序的相关性
SELECT attname, correlation
FROM pg_stats
WHERE schemaname = 'public'
  AND tablename = 'app_log_brin_demo'
  AND attname = 'created_at';

查询性能：范围检索、聚合与写入的真实收益

线上 5TB 表的典型结果如下。数值受存储、并发、缓冲区和分区数量影响，重点看数量级与执行计划形态。

场景	B-Tree	BRIN(128)	BRIN(32)	观察
最近 10 分钟错误日志	180ms	210ms	190ms	高选择性点查 B-Tree 略优
最近 1 小时按 level 聚合	4.8s	2.1s	1.6s	BRIN 顺序扫描更少随机 I/O
最近 24 小时导出	41s	9s	8s	大范围读取 BRIN 优势明显
单日批量写入索引开销	基准	+13%	+0.8%	+2%
索引体积	1.1TB	4.2GB	16GB	BRIN 维护成本极低

查看执行计划时，BRIN 的关键不是出现“Index Scan”，而是 Bitmap Heap Scan 配合 Brin Cond，并且 lossy blocks 后仍能通过可见性映射跳过大量块。

-- 典型时间范围查询
EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
SELECT id, created_at, level, service, message
FROM app_log_brin_demo
WHERE created_at >= now() - interval '1 hour'
  AND level = 'ERROR'
ORDER BY created_at DESC
LIMIT 200;

-- 典型聚合查询：BRIN 裁剪时间范围，再做顺序聚合
EXPLAIN (ANALYZE, BUFFERS)
SELECT level, count(*)
FROM app_log_brin_demo
WHERE created_at >= date_trunc('hour', now())
  AND created_at <  date_trunc('hour', now()) + interval '1 hour'
GROUP BY level;

如果查询只返回极少几行，B-Tree 可以沿树快速定位；BRIN 即使裁剪掉 99% 的块，仍可能在剩余区间内做回表过滤。因此“最近一条错误日志”这类查询未必更快。反过来，当查询读取一个小时甚至一天的数据时，B-Tree 会产生大量随机堆页访问，BRIN 的顺序 I/O 反而更稳。

pages_per_range 调优：索引粒度不是越小越好

BRIN 的核心参数是 pages_per_range。值越大，索引越小，但每个摘要覆盖的块越多，容易把不满足条件的块也包含进来；值越小，摘要更精确，索引和维护成本上升。日志表建议从 128 开始，按查询窗口和单页行数压测 32、64、128、256，而不是盲目照搬默认值。

-- 建立更细粒度的 BRIN，适合查询窗口很短且写入非常密集的表
CREATE INDEX idx_log_brin_time_fine
ON app_log_brin_demo
USING brin(created_at)
WITH (pages_per_range = 32);

-- 已有索引可安全重建；生产大表建议使用非阻塞方式
BEGIN;
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_log_brin_time_v2
  ON app_log_brin_demo
  USING brin(created_at)
  WITH (pages_per_range = 64);
DROP INDEX IF EXISTS idx_log_brin_time;
ALTER INDEX idx_log_brin_time_v2 RENAME TO idx_log_brin_time;
COMMIT;
ANALYZE app_log_brin_demo;

需要注意，CREATE INDEX CONCURRENTLY 不能放在事务块中执行；上面的事务写法仅用于演示改名流程，生产中应分步骤执行，并在失败后用 DROP INDEX CONCURRENTLY 清理无效索引。

避坑总结：BRIN 最常见的 4 个失效点

1. 数据物理顺序乱了，BRIN 摘要仍会“看起来正常”

常见现象是 created_at 上确实有 BRIN，执行计划也显示 Brin Cond，但 shared read blocks 很高，查询接近全表扫描。根因通常是多生产者乱序写入、UPDATE 导致行迁移、从历史文件恢复后顺序被打乱。先用 pg_stats.correlation 检查；若相关性低于 0.7，应修正写入通道，或按时间分区，让每个分区内部保持追加顺序。不要指望在随机分布字段上靠 BRIN 替代 B-Tree。

2. 查询条件对列做函数包装，导致摘要无法裁剪

错误写法是 WHERE date_trunc(''hour'', created_at) >= ''2026-01-01''，或者对 created_at 做时区表达式计算。规划器难以利用原始列范围，BRIN 可能不生效。应改写为闭合区间：created_at >= ''2026-01-01 00:00:00+08'' AND created_at < ''2026-01-02 00:00:00+08''。若业务必须按表达式查询，可建立表达式 BRIN，但仍要保证表达式结果与物理顺序相关。

3. autovacuum 没跟上，摘要和可见性映射变旧

大量插入、更新后，如果统计信息陈旧，规划器可能错误估计行数，或者 BRIN 不能有效跳过不可见元组。现象是第一次查询很慢，手工 ANALYZE 或 VACUUM 后恢复。对超大日志表应单独调大 autovacuum 频率，并确保自动清理不被长事务阻塞。可查询 pg_stat_user_tables 的 n_tup_ins、n_dead_tup、last_autovacuum，必要时对表设置 autovacuum_vacuum_scale_factor = 0.01、autovacuum_analyze_scale_factor = 0.005。

4. 用 BRIN 承担唯一性和高频点查

BRIN 不支持唯一约束，也不适合按 trace_id、user_id 精确定位单行。曾经遇到团队只保留时间 BRIN 后，按 trace_id 查询从毫秒级退化到几十秒。正确做法是组合设计：时间列使用 BRIN 服务大范围报表与日志扫描，trace_id 这类高选择性字段使用 B-Tree；若 trace_id 查询总是限定最近时间，也可以评估哈希分区或按服务拆分索引。

最终，在这张 5TB 时序日志表上，BRIN 用约千分之几的索引体积，把大范围时间查询提升到原来的 2 到 5 倍，并显著降低写入放大与索引维护成本；但它换来的不是“万能加速”，而是建立在物理顺序、范围谓词和持续维护之上的稳定收益。

免责声明

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