PostgreSQL BRIN 时序索引

示例说明

BRIN 的思路完全不同于 B 树：不索引每一行，而是给每个连续块范围记一对最小最大值。追加型时序天然按时间物理有序——时间范围查询只需排除不相交的块范围，稀疏索引即可完成粗筛。代价是块内要再过滤，但换来的是体积比 B 树小数百倍、写入几乎零负担。

技术要点

• 块范围极值替代逐行索引
• 物理有序是 BRIN 生效前提
• 体积优势换块内再过滤

代码解读

建索引时 pages_per_range 控制粒度——三十二页一个范围是常用平衡；范围查询的 WHERE 落在块范围筛选上，Buffers 输出能看到读页量远小于全表。B 树对照版仅供体积感知。老数据分区的方案让每个分区的时间跨度更窄，块范围判别更锐利。

使用建议

数据乱序写入时 BRIN 退化严重，先 CLUSTER 或分区对齐物理序。点查询仍要 B 树，BRIN 只赢范围扫描。范围大小按块密度调优。

代码示例

-- PostgreSQL 时序表按写入时间自然有序（append-only）
CREATE TABLE metrics (
  observed_at timestamptz NOT NULL,
  sensor_id int NOT NULL,
  value double precision NOT NULL
) WITH (fillfactor = 100);

-- BRIN 索引：按块范围记录最小最大值
CREATE INDEX idx_metrics_observed_brin
  ON metrics USING brin (observed_at)
  WITH (pages_per_range = 32);

-- 范围扫描：块范围过滤掉绝大多数数据块
SELECT sensor_id, avg(value)
FROM metrics
WHERE observed_at >= '2026-10-21 00:00'
  AND observed_at < '2026-10-22 00:00'
GROUP BY sensor_id;

-- 尺寸对照：B 树版本用于体积感知
CREATE INDEX idx_metrics_observed_btree
  ON metrics (observed_at);

-- 验证计划选择
EXPLAIN (ANALYZE, BUFFERS)
SELECT count(*)
FROM metrics
WHERE observed_at >= '2026-10-21'
  AND observed_at < '2026-10-22';

-- 老数据分区后 BRIN 更精准：每分区独立建
CREATE TABLE metrics_old PARTITION OF metrics
  FOR VALUES FROM ('2026-01-01') TO ('2026-10-01');