中文
devexamples

PostgreSQL BRIN Index Time Series

Index append-only time series with tiny BRIN blocks, comparing size against B-tree for range scans over billions of rows.

SqlData Integrity

Code

snippet
-- 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');

Related Examples