page_title: Block Range Index
section_title: Design
hierarchical_section_title: Block Range Index / Design
context_page_description: A Block Range Index or BRIN is a database indexing technique. They are intended to improve performance with extremely large tables.
  BRIN indexes provide similar benefits to horizontal partitioning or sharding but without needing to explicitly declare partitions.
  A BRIN is applicable to an index on a table that is large and where the index key value is easily sorted and evaluated with a MinMax function.
  BRIN were originally proposed by Alvaro Herrera of 2ndQuadrant in 2013 as 'Minmax indexes'. Implementations thus far are tightly coupled to internal implementation and storage techniques for the database tables. This makes them efficient, but limits them to particular vendors. So far PostgreSQL is the only vendor to have announced a live product with this specific feature, in PostgreSQL 9.5. Other vendors have described some similar features, including Oracle, Netezza 'zone maps', Infobright 'data packs', MonetDB and Apache Hive with ORC/Parquet.
context_section_description: 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. These tests exclude a large block of data for each comparison. 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.
  Data storage in large databases is layered and chunked, with the table storage arranged into 'blocks'. Each block contains perhaps 1MB in each chunk and they are retrieved by requesting specific blocks from a disk-based storage layer. BRIN are a lightweight in-memory summary layer above this: each tuple in the index summarises one block as to the range of the data contained therein: its minimum and maximum values, and if the block contains any non-null data for the column(s) of interest.
  Unlike a traditional index which locates the regions of the table containing values of interest, BRIN act as "negative indexes", showing the blocks that are definitely not of interest and thus do not need to be processed further.
  Some simple benchmarks suggest a five-fold improvement in search performance with an index scan, compared to the unindexed table. Compared to B-trees, they avoid their maintenance overhead.
  As BRIN are so lightweight, they may be held entirely in memory, thus avoiding disk overhead during the scan. The same may not be true of B-tree: B-tree requires a tree node for every approximately N rows in the table, where N is the capacity of a single node, thus the index size is large. 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. For a 'narrow' table the B-tree index volume approaches that of the table itself; the BRIN may be only 5-15% of it.
caption: B-tree index structure