PostgreSQL BRIN索引在行更新后性能急剧退化，体积优势随数据变动丧失

前Oracle查询引擎团队成员Venkat Sakamuri发布了一项关于PostgreSQL BRIN索引性能的测试分析，揭示了该索引在数据更新后存在的严重性能退化问题 1。在PostgreSQL 17.9环境下的测试表明，对于跨越90天、按时间戳顺序插入的10,000,000行物理有序新表，BRIN索引的体积仅为48 KB，而等效的B-tree索引大小达到224,641,024字节，BRIN体积仅为后者的1/4570 1。测试环境配置为x86_64架构，shared_buffers设为256MB，work_mem为32MB，关闭了fsync和autovacuum，堆表大小为976 MB，BRIN索引的pages_per_range配置为128 1。

然而，当表中5%的行被更新后，BRIN索引的性能出现断崖式下跌 1。由于行迁移导致页范围摘要变宽，BRIN索引触碰的页面数从1,806页增加到51,268页，I/O增长28倍，查询执行时间增长23倍 1。分析指出，在5%的数据变动（churn）下，pg_stats.correlation仍为0.921，但剪枝能力已崩溃，这表明该统计信息无法可靠反映BRIN的退化 1。文章还对比了Oracle Zonemaps的显式过期机制与BRIN的隐式失效问题，并指出CLUSTER命令可恢复物理顺序，但需要ACCESS EXCLUSIVE锁和全表重写 1。

Venkat Sakamuri from DeepSQL R&D, an ex-Oracle Query Engine Team member associated with YC and CMU, has published an analysis on the performance degradation of PostgreSQL BRIN indexes following row updates 1. Testing on a newly created table with 10,000,000 rows spanning 90 days and inserted in timestamp order, he found that the BRIN index occupied only 48 KB 1. In stark contrast, the equivalent B-tree index required 224,641,024 bytes, making the BRIN index 1/4570th the size of the B-tree 1. The test environment utilized PostgreSQL 17.9 on an x86_64 architecture with a 976 MB heap size, configured with shared_buffers=256MB, work_mem=32MB, fsync=off, and autovacuum=off 1. The BRIN index was specifically configured with pages_per_range=128 1.

However, this massive size advantage quickly vanishes when data churn occurs 1. After just 5% of the rows were updated, row migration caused the page range summaries to widen significantly 1. Consequently, the number of pages touched by the BRIN index surged from 1,806 to 51,268 1. This led to a 28-fold increase in I/O and a 23-fold increase in query execution time 1. Notably, the pg_stats.correlation metric failed to reliably reflect this degradation; at 5% churn, the correlation remained at 0.921 even though the index's pruning capability had already collapsed 1.

To address the physical disorder, the CLUSTER command can be used to restore the physical order, but it necessitates an ACCESS EXCLUSIVE lock and a full table rewrite 1. The analysis also contrasts this implicit invalidation issue in BRIN with the explicit expiration mechanism found in Oracle Zonemaps 1.