PostgreSQL行修改时间戳列用BRIN替代BTREE索引是否合适？

绝大多数追加写为主的业务场景下，BRIN索引完全可以作为BTREE索引的高性价比替代，只有部分更新频繁、点查为主的场景不适合替换，核心判断依据是modified_at字段的取值和表数据物理存储顺序的相关性。

场景适配性分析 #

你定义的modified_at字段有两个明确的写入特性：

• 新插入行默认取插入时刻的CURRENT_TIMESTAMP，天然按时间顺序追加写入
• 行被更新时，触发器会把modified_at更新为当前时刻的最新值

BRIN索引的本质是块范围索引，它不会为每一行存独立索引条目，只会为连续的一组数据块（默认128个块为一个范围）记录对应字段的最小值、最大值，索引体积通常只有同字段BTREE索引的1/100到1/1000，写入和维护开销极低。

如果你的表是日志、流水、监控这类追加写占绝对多数、极少修改历史老数据的表，数据在磁盘上的物理存储顺序和modified_at的时间顺序几乎完全一致：越老的数据块里存的modified_at值越小，越新的块里值越大。这时候用modified_at做时间范围查询，BRIN可以快速过滤掉所有不满足时间范围的块，扫描效率非常接近BTREE，存储和维护成本还低得多。
尤其是你经常需要查大范围时间数据（比如查最近1天的全量记录）时，BRIN的连续IO特性甚至比BTREE随机寻址的表现更好。

不适合替换的场景 #

出现以下情况时，BRIN的查询性能会明显差于BTREE，不建议替换：

• 频繁更新很早之前的历史老数据：PostgreSQL的UPDATE本质是写入新的行版本，如果经常修改几个月前甚至更早的老记录，这些带着最新modified_at值的新行版本会散落在表的不同数据块里，导致很多数据块内的modified_at跨度极大（同一块里既有几个月前的旧值，也有当前时间的新值），BRIN没法有效过滤无效块，扫描时会读取大量无关数据，性能骤降。
• 查询以点查、极小范围查询为主：比如你经常需要按modified_at查某一秒的单条记录、或者取最新的10条记录，BRIN只能定位到对应的数据块范围，无法精确定位到具体行，必须在块内逐行遍历匹配，性能远不如可以直接定位行位置的BTREE。
• 表的fillfactor设置较低、HOT更新占比高：如果你为了支持HOT更新把表的fillfactor设到了90以下，更新产生的新行版本会留在原数据块内，同样会打乱块内的时间顺序，降低BRIN的过滤效率。

快速验证适配性的方法 #

你可以直接在现有表上执行如下SQL，计算modified_at取值和数据物理块号的相关性：

SELECT corr(block_no, modified_at) AS time_order_correlation
FROM (
    SELECT (ctid::text::point)[0]::bigint AS block_no, modified_at 
    FROM foo
) t;

返回结果范围在-1到1之间：

• 结果越接近1，说明时间顺序和物理存储顺序正相关性越强，BRIN效果越好，一般大于0.9就非常适合替换
• 结果低于0.7时，BRIN的过滤效率会很差，不建议替代BTREE

内容的提问来源于stack exchange，提问作者slsy