BRIN Index
Introduction BRIN (Block Range Index) works by grouping a cluster of pages in the HEAP together into a single range rather than creating an index for every single data row like a B-Tree. The structure of a data row inside a BRIN Index (called an Index Tuple) consists of the following three core components: blknum (Block Number): The block number starting the range (for example: 0, 128, 256, 512...). min_value: The minimum value of that column within a block range. max_value: The maximum value of that column within a block range. How It Works When executing a query with a BRIN Index, Postgres scans the index and checks whether the value being searched for falls within any [Min, Max] range If not, it skips that entire block range, eliminating a huge amount of redundant data. If yes, Postgres relies on the blknum to load the corresponding Pages in the HEAP up and checks them sequentially to find the exact data row. Advantages Super compact: Because it only stores the Min and Max for an en...