What BRIN Indexes Are Good For (and Why the Use Case Matters)

BRIN stands for Block Range INdex. It stores the minimum and maximum value for each 128KB range of pages in a table. That is it. The entire index is a list of (block range, min value, max value) tuples.

It is tiny. A B-tree index on a million-row table might be 50MB. A BRIN index on the same table might be 100KB. It is also nearly free to maintain. When you insert or update a row, PostgreSQL just updates the min/max values for that page range, which is one lookup and one write.

This sounds amazing. It is also nearly useless unless your data is ordered the way you query it.

How BRIN Works

When you query WHERE created_at > '2024-01-01', PostgreSQL uses the index to figure out which page ranges could possibly contain rows matching that condition. A page range is relevant if its max value is greater than '2024-01-01'. A page range is irrelevant if its min value is less than '2024-01-01'.

If your data is randomly scattered, this narrows things down to almost nothing. If a page range has min='2024-01-01' and max='2024-12-31', and you query for WHERE created_at > '2024-06-01', PostgreSQL cannot prove the page range is irrelevant. It could contain anything between '2024-01-01' and '2024-12-31', which includes values both above and below '2024-06-01'. So PostgreSQL scans the page range anyway.

In the worst case, a BRIN index is worse than useless. It costs nothing to maintain and also does nothing. The planner picks it, checks the ranges, finds that every range could possibly match, and you end up scanning the entire table anyway.

The Use Case That Actually Works

BRIN is useful when your data has strong physical correlation with the indexed column. The canonical example is an append-only log table. Rows are inserted in time order. Earlier rows are on earlier pages, later rows are on later pages. When you query for recent entries, PostgreSQL can prove that old pages are irrelevant because their max value is less than your query value.

Another example: a time-series table where data is ingested in time order and partitioned by day. Rows from Monday are on pages 1-100, rows from Tuesday are on pages 101-200, etc. A query for "data from last 7 days" can use BRIN to skip pages that are older than 7 days.

In both cases, the key property is that the data is written in roughly the order you query it by. If rows are randomly inserted, shuffled, updated, or if your queries do not follow the physical order, BRIN does nothing.

How to Check If It Is Right for Your Table

Run the query:

SELECT attname, correlation
FROM pg_stats
WHERE tablename = 'your_table'
ORDER BY abs(correlation) DESC;

This returns the correlation between each column's logical order and its physical order on disk. A correlation of 1.0 means perfectly ordered. A correlation of -1.0 means perfectly reverse ordered. A correlation close to 0 means random.

For BRIN on a column to be useful, that column needs a correlation close to 1.0 or -1.0. A correlation of 0.5 is not good enough. A correlation of 0.05 is definitely not good enough.

Try creating a BRIN index and running an EXPLAIN ANALYZE on a query that should benefit from it. If BRIN reduces the planner's estimate of rows to scan, it is working. If BRIN does not reduce it, do not use it.

Why This Matters

I have seen people add BRIN indexes to tables because they "seemed cheap" without checking whether the data was actually correlated with the index column. The index adds no value and takes up disk space. More importantly, it adds a candidate to the planner's search space. The planner has to evaluate the BRIN index and a B-tree index and potentially a sequential scan. If the BRIN index is useless, the planner might pick it anyway, leading to slower queries.

The right use case for BRIN is narrow and specific. It is not a general-purpose index type. It is specialized. If your data is not specialized in the right way, do not use it.

Use BRIN on an append-only table where you query by recency. Use BRIN on a time-series table where data is ingested in time order and queries are for recent data. Do not use BRIN on a table where rows are inserted, updated, and queried without regard to physical order.

A B-tree index is the default because it works well on almost everything. BRIN is the exception because it works brilliantly on exactly the right thing and does nothing on everything else.