Postgres Indexing: When Does BRIN Win?

BRIN (Block Range INdex) is a type of index in Postgres that is used for large tables with a large number of rows and a high degree of clustering. BRIN is an efficient way to store and retrieve data from tables that have a large number of rows and a high degree of clustering.

BRIN wins when:

  1. The data is stored in a large table with a large number of rows.
  2. The data is highly clustered, meaning that the data is stored in blocks that are frequently used together.
  3. The queries being performed on the data are range-based, meaning that they are looking for data within a specific range of values.
  4. The table size is too large to store an index in memory, so an index that can be partially stored on disk is necessary.
  5. The data is stored in large blocks, making it difficult for a traditional index to provide efficient access to the data.

In conclusion, BRIN is most effective when the data is stored in large tables with a large number of rows and a high degree of clustering, and the queries being performed on the data are range-based.

About Shiv Iyer 456 Articles
Open Source Database Systems Engineer with a deep understanding of Optimizer Internals, Performance Engineering, Scalability and Data SRE. Shiv currently is the Founder, Investor, Board Member and CEO of multiple Database Systems Infrastructure Operations companies in the Transaction Processing Computing and ColumnStores ecosystem. He is also a frequent speaker in open source software conferences globally.