PostgreSQL BRIN 索引应用场景_MsSqlserver

postgresql brin 索引应用场景

核心适用条件（先判断能不能用）

✅ 表非常大（千万行以上，亿级最佳）
✅ 列值与数据写入的物理顺序高度相关
✅ 查询以"范围过滤"为主，不需要精确定位单行
✅ 磁盘空间或写入性能敏感

场景一：时间序列日志表 ⭐ 最典型

-- 系统访问日志，按时间顺序写入，数据量极大
create table access_log (
    id         bigserial primary key,
    user_id    bigint,
    url        text,
    status     int,
    created_at timestamptz default now()
);

-- ✅ 用 brin，索引只有几百kb，哪怕表有10亿行
create index idx_access_log_brin on access_log using brin (created_at);

-- 查某天的日志
select * from access_log
where created_at between '2024-03-01' and '2024-03-02';

为什么有效：日志按时间顺序写入，物理块1 = 最早的数据，物理块n = 最新数据，brin 能精准跳过无关块。

场景二：iot / 传感器数据

create table sensor_data (
    sensor_id   int,
    temperature float,
    humidity    float,
    recorded_at timestamptz default now()
);

create index idx_sensor_brin on sensor_data using brin (recorded_at);

-- 查某段时间内某传感器的数据
select * from sensor_data
where recorded_at > now() - interval '7 days'
and sensor_id = 42;

传感器数据天然按时间堆积，brin 几乎是最优选择。

场景三：金融流水 / 订单表

create table order_flow (
    id          bigserial primary key,
    order_no    varchar(32),
    amount      numeric(18,2),
    created_at  timestamptz default now()
);

-- 自增id 和 created_at 都可以建 brin
create index idx_order_id_brin   on order_flow using brin (id);
create index idx_order_time_brin on order_flow using brin (created_at);

-- 按月查账单
select sum(amount) from order_flow
where created_at between '2024-01-01' and '2024-02-01';

场景四：数据仓库 / 历史归档表

-- 历史数据归档表，只追加写入，几亿行
create table dw_sales_history (
    sale_date   date,
    region      varchar(50),
    product_id  bigint,
    revenue     numeric(18,2)
);

-- brin 索引极小，配合按 sale_date 查询非常高效
create index idx_dw_sales_brin on dw_sales_history using brin (sale_date);

-- 统计某季度数据
select region, sum(revenue)
from dw_sales_history
where sale_date between '2024-01-01' and '2024-03-31'
group by region;

场景五：自增主键的超大表辅助过滤

-- 超大表上，主键 b+tree 已经很大了
-- 如果查询是大范围 id 过滤（比如分片处理）
create index idx_events_id_brin on events using brin (id);

-- 批量处理：每次处理一段id区间
select * from events where id between 1000000 and 2000000;

brin 参数调优：pages_per_range

-- 默认每128个块作为一组
-- 数据量极大时可以调大，索引更小但精度更低
-- 数据量较小时可以调小，精度更高
create index idx_log_brin on access_log
using brin (created_at)
with (pages_per_range = 64);   -- 更精细
-- 或
with (pages_per_range = 256);  -- 更省空间

❌ brin 不适合的场景（踩坑预防）

-- ❌ 随机写入的字段（没有物理顺序）
where email = 'xxx@xxx.com'      -- 用 b+tree

-- ❌ 需要精确查单行
where id = 12345                 -- 用 b+tree

-- ❌ 数据会被大量 update（破坏物理顺序）
update users set score = ...     -- 用 b+tree

-- ❌ 小表（brin 优势不明显，b+tree 更好）
-- 表只有几十万行 → 直接用 b+tree

各场景索引选型速查

场景	推荐索引
系统日志、访问记录（按时间写入）	brin
iot / 传感器时序数据	brin
金融流水、订单（时间范围查询）	brin
数据仓库历史归档表	brin
普通业务表等值查询	b+tree
全文检索、数组、jsonb	gin
模糊查询 like '%xx%'	gin + pg_trgm