Block Range Index

[3][4] Other vendors have described some similar features,[2] including Oracle,[5][6] Netezza 'zone maps',[7] Infobright 'data packs',[8] MonetDB[9] and Apache Hive with ORC/Parquet.

[10] BRIN operate by "summarising" large blocks of data into a compact form, which can be efficiently tested to exclude many of them from a database query, early on.

By reducing the data volume so early on, both by representing large blocks as small tuples, and by eliminating many blocks, BRIN substantially reduce the amount of detailed data that must be examined by the database node on a row-by-row basis.

Some simple benchmarks suggest a five-fold improvement in search performance with an index scan, compared to the unindexed table.

As BRIN only requires a tuple for each block (of many rows), the index becomes sufficiently small to make the difference between disk and memory.

[17] Wong reports that B-tree slowed down additions to an unindexed 10GB table by 85%, but a comparable BRIN only had an overhead of 11%.

[1] BRIN may be created for extremely large data where B-tree would require horizontal partitioning.

BRIN are only efficient if the ordering of the key values follows the organisation of blocks in the storage layer.

[14]: 9 If the data is truly random, or if there is much churn of the key values in a 'hot' database, the assumptions underlying BRIN may break down.

All blocks contain entries "of interest" and so few may be excluded early on by the BRIN range filter.

In other cases, the key value may not be monotonic, but provided that there is still a strong grouping within the record's physical order, BRIN is effective.

[6] Storage Indexes provides data pruning at this layer: efficiently indicating sections that are of no further interest.