What changed
On August 27, 2026, DeepSQL published a fully reproducible PostgreSQL benchmark examining how BRIN indexes behave when a time-ordered table accumulates randomized updates. On a 10-million-row PostgreSQL 17.9 table, the fresh BRIN index was about 48 KB versus roughly 214 MB for a B-tree and scanned 1,536 lossy heap pages for the test range. After randomized updates to 5% of rows, PostgreSQL still reported column correlation around 0.921, but the BRIN plan touched 51,268 lossy heap pages, rechecked about 3.8 million rows and took hundreds of milliseconds rather than tens. The complete Docker and SQL harness is published inline, and clustering the table restored physical locality and near-original BRIN behavior.
Why it matters
BRIN is attractive for very large append-oriented PostgreSQL tables because the index can be orders of magnitude smaller than a B-tree. The benchmark exposes an operational trap: a familiar planner statistic such as column correlation can remain reassuring while the page-range summaries BRIN depends on have become much less selective. Builders running time-series, event or log-style tables with later updates therefore need to monitor actual lossy-block behavior and recheck volume rather than assuming an initially good BRIN index will remain cheap indefinitely. The exact 5% crossover is workload-specific, but the failure mode is practical and reproducible.
Fresh BRIN delivers the expected size advantage
DeepSQL's test table contains 10 million timestamp-ordered rows and occupies about 976 MB. With `pages_per_range=128`, the BRIN index is roughly 48 KB while the comparison B-tree is around 214 MB. A one-day range query on the fresh layout touches 1,536 lossy heap pages and completes in roughly the low-tens-of-milliseconds range in the published environment.
Five percent randomized updates destroy much of the page locality
After the harness updates 5% of rows to move their indexed timestamp values around the key space, the same BRIN range query expands to 51,268 lossy heap pages and rechecks about 3.8 million rows. The captured plans show query time moving from roughly 20–25 ms fresh into the hundreds of milliseconds. The key point is the shape of the degradation, not a universal threshold: different row widths, update patterns, ranges, hardware and `pages_per_range` values will move the crossover.
Column correlation can hide the degradation
PostgreSQL's reported correlation remains around 0.921 in the 5% churn case. That is still a seemingly strong value, yet BRIN must inspect vastly more pages because min/max summaries now overlap the query range across much of the table. DeepSQL argues that `Heap Blocks: lossy` and rows removed by index recheck are more direct indicators of this failure mode than correlation alone.
Reclustering demonstrates that physical order is the underlying variable
The harness uses `CLUSTER` to restore table order after heavy churn and shows the BRIN query returning close to its original page count and latency. That is strong evidence for the mechanism, but `CLUSTER` itself requires operational care and locking. Production teams may instead evaluate partitioning, rewrite strategies, `pg_repack`-style maintenance or a B-tree depending on write patterns and availability requirements.
The benchmark is intentionally narrow and unusually inspectable
DeepSQL disables some production-oriented behavior such as autovacuum and forces the indexed plan to isolate BRIN mechanics. The author explicitly warns against turning the observed 5% figure into a general rule. Because the complete environment and SQL are published, teams can rerun the experiment with their row widths, update patterns, PostgreSQL version and storage stack rather than relying on the headline number.