PostgreSQL BRIN Index

Description

BRIN (Block Range INdex) is a PostgreSQL index type designed primarily for very large tables where the indexed values are correlated with the physical order of rows in the table.

It is particularly effective for append-only and time-series data, where values such as timestamps, transaction dates, event times, or sequential identifiers naturally increase as new rows are inserted.

The main difference between BRIN and traditional indexes such as B-tree is that BRIN does not maintain an index entry for every row.

Instead, PostgreSQL divides the physical table into groups of consecutive heap pages called block ranges. BRIN stores compact summary information about the values contained in each range.

For the commonly used minmax BRIN strategy, PostgreSQL stores the minimum and maximum values found in each block range.

Conceptually:

PostgreSQL Heap

Pages 0–127
MIN date = Jan 01
MAX date = Jan 05

Pages 128–255
MIN date = Jan 05
MAX date = Jan 10

Pages 256–383
MIN date = Jan 10
MAX date = Jan 15

When a query searches for a particular date range, PostgreSQL checks these BRIN summaries.

If a block range cannot possibly contain the requested values, PostgreSQL can skip the entire range.

Query: Jan 06 – Jan 07

Pages 0–127       Jan 01–05   → SKIP
Pages 128–255     Jan 05–10   → READ
Pages 256–383     Jan 10–15   → SKIP

This is the fundamental purpose of BRIN:

Instead of finding the exact location of every matching row, BRIN helps PostgreSQL identify which large physical portions of the table can be skipped.

BRIN Is a Lossy Index

BRIN is generally a lossy index.

When a BRIN summary indicates that a block range may contain the requested value, it does not mean that every row in that range matches.

It means only:

This block range MAY contain matching rows.

PostgreSQL must therefore read the candidate heap pages and recheck the actual rows against the query condition.

The process is approximately:

Query condition
      ↓
BRIN summaries
      ↓
Eliminate impossible block ranges
      ↓
Candidate block ranges
      ↓
Read heap pages
      ↓
Recheck actual rows
      ↓
Return matching rows

This lossiness is an intentional part of BRIN's design.

BRIN sacrifices row-level precision in exchange for:

• extremely small index size;

• low maintenance overhead;

• efficient elimination of large physical portions of a table.

Why Physical Correlation Is Important

BRIN performs best when indexed values are strongly correlated with their physical position in the table.

For example, an append-only table containing chronological data may physically look like:

Beginning of table
        ↓
January
February
March
April
May
        ↓
End of table

In this situation, each physical block range contains a relatively narrow date range.

BRIN can therefore eliminate many ranges very efficiently.

If values are randomly distributed:

Range 1 → Jan, Aug, Mar, Dec
Range 2 → Nov, Feb, Sep, Apr
Range 3 → Jul, Jan, Oct, May

the minimum and maximum values of many ranges may cover a large portion of the complete value domain.

BRIN then cannot eliminate many block ranges and becomes much less effective.

Therefore:

Table size alone does not make BRIN appropriate. The indexed column should also have good correlation with physical row order.

Why BRIN Is Good for Append-Only and Time-Series Tables

Append-only and time-series tables naturally tend to maintain the physical correlation BRIN needs.

As new records are appended:

Old data
   ↓
Older heap pages

New data
   ↓
Newer heap pages

If the indexed timestamp or identifier also increases, physical location and indexed value remain strongly correlated.

Typical BRIN candidates therefore include:

• time-series data;

• sensor and IoT measurements;

• application logs;

• database logs;

• audit history;

• event tables;

• transaction history;

• large historical tables;

• large fact tables loaded in date order.

However, append-only does not automatically mean BRIN is appropriate.

An append-only table containing randomly distributed customer_id values may be a poor candidate for a BRIN index on customer_id.

The important factor is the relationship between the indexed value and physical row order.

pages_per_range

BRIN groups consecutive heap pages into block ranges.

The size of those ranges is controlled by:

pages_per_range

For many standard BRIN operator classes, the default is:

pages_per_range = 128

With PostgreSQL's usual 8 KB heap page size, one range therefore represents approximately:

128 × 8 KB ≈ 1 MB

A single BRIN summary can consequently describe a large number of rows.

This is one of the main reasons BRIN indexes can remain extremely small even when the underlying table contains hundreds of millions or billions of rows.

Smaller pages_per_range

Smaller ranges
      ↓
More block ranges
      ↓
More BRIN summaries
      ↓
Higher precision
      ↓
Potentially fewer unnecessary heap pages
      ↓
Larger BRIN index

Larger pages_per_range

Larger ranges
      ↓
Fewer block ranges
      ↓
Fewer BRIN summaries
      ↓
Lower precision
      ↓
Potentially more unnecessary heap pages
      ↓
Smaller BRIN index

Therefore, pages_per_range represents a trade-off between index size and range-elimination precision.

BRIN Operator Classes

BRIN is not limited to storing simple minimum and maximum values.

PostgreSQL uses operator classes to determine what kind of summary information BRIN maintains and which operators can use that summary.

The major BRIN summary strategies include:

minmax
minmax_multi
bloom
inclusion

Availability depends on the indexed data type and the corresponding BRIN operator class.

1. minmax

minmax is the traditional and easiest BRIN strategy to understand.

For every block range, PostgreSQL stores information based on the:

Minimum value
Maximum value

Conceptually:

Block Range 1
MIN = 100
MAX = 500

Block Range 2
MIN = 501
MAX = 900

Block Range 3
MIN = 901
MAX = 1300

When a query searches for a value, PostgreSQL can determine whether that value could possibly exist inside each range.

If it falls outside the stored minimum and maximum, the entire block range can be skipped.

minmax is particularly effective when values are strongly correlated with physical table order.

Typical candidates include:

• timestamps;

• dates;

• sequential IDs;

• monotonically increasing values.

2. minmax_multi

minmax_multi extends the basic min/max concept.

A simple minmax summary describes an entire block range using one broad interval:

MIN ---------------- MAX

This can become inefficient when a range contains several separated groups of values.

minmax_multi can represent multiple smaller intervals instead of reducing everything to one minimum and one maximum.

Conceptually:

Simple minmax:

1 ------------------------------- 10000


minmax_multi:

1 ---- 100

                 5000 ---- 5100

                               9900 ---- 10000

This can allow PostgreSQL to eliminate more ranges when the values are somewhat clustered but not perfectly sequential.

The trade-off is a more complex and potentially larger summary than basic minmax.

3. bloom

The BRIN bloom strategy uses a Bloom filter to summarize values appearing in a block range.

Instead of describing the range primarily with minimum and maximum values, it maintains a probabilistic representation of the values present.

Conceptually:

Block Range
      ↓
Values contained in the range
      ↓
Bloom summary
      ↓
Could this value exist here?

A Bloom filter can say:

Definitely not present

or:

Possibly present

It cannot guarantee that a value reported as possible is actually present.

Therefore, false positives are possible and the actual heap rows still need to be checked.

This strategy can be useful for supported equality-oriented workloads where min/max information does not represent the data distribution well.

4. inclusion

The inclusion strategy is intended for data types where relationships such as:

contains
contained by
overlaps

are more meaningful than ordinary scalar minimum/maximum comparisons.

Instead of summarizing a range simply as:

MIN / MAX

it maintains a summary capable of representing inclusion relationships for supported data types and operators.

This makes BRIN useful beyond ordinary numeric and chronological values.

Why Operator Classes Matter

The operator class determines how PostgreSQL summarizes each block range.

Conceptually:

                     BRIN
                       |
        ┌──────────────┼──────────────┐
        ↓              ↓              ↓
     minmax      minmax_multi       bloom
        ↓              ↓              ↓
    MIN/MAX       Multiple ranges   Bloom filter

                       |
                       ↓
                   inclusion
                       ↓
             Inclusion relationships

Therefore, BRIN should not be thought of simply as a min/max index.

A more accurate description is:

BRIN is a framework for storing compact summaries about physical ranges of heap pages. The selected operator class determines what type of summary is stored.

How BRIN Uses Bitmap Scans

BRIN has an important relationship with PostgreSQL's bitmap scan mechanism.

Unlike a B-tree, BRIN does not normally identify an exact tuple location for each matching indexed value.

It identifies physical block ranges that may contain matching rows.

Therefore, the normal BRIN access pattern is:

BRIN Index
     ↓
Bitmap Index Scan
     ↓
Bitmap of candidate heap pages
     ↓
Bitmap Heap Scan
     ↓
Read candidate heap pages
     ↓
Recheck rows
     ↓
Return matching rows

A typical execution-plan shape is:

Bitmap Heap Scan
    Recheck Cond
    ↓
    Bitmap Index Scan
        Index Cond

Bitmap Index Scan

The Bitmap Index Scan accesses the BRIN index and evaluates its summaries.

Its purpose is not to return the table rows directly.

Instead, it determines which physical heap pages or ranges may contain matching rows and constructs a bitmap representing those candidate locations.

Conceptually:

Heap Pages

0     → skip
1     → skip
2     → candidate
3     → candidate
4     → candidate
5     → skip
6     → skip
7     → candidate

The bitmap represents approximately:

0 0 1 1 1 0 0 1

PostgreSQL can then concentrate heap access on the candidate areas.

Bitmap Heap Scan

The Bitmap Heap Scan uses the bitmap generated from the index information to access the actual table.

Conceptually:

Bitmap
   ↓
Candidate Heap Pages
   ↓
Read Those Pages
   ↓
Check Actual Tuples

Because BRIN summaries are lossy, PostgreSQL generally has to recheck the actual condition against tuples from candidate pages.

This is why BRIN execution plans commonly contain:

Recheck Cond

and may also show:

Rows Removed by Index Recheck

These are normal consequences of BRIN's block-range summarization.

Why BRIN Uses Bitmap Access

The bitmap mechanism fits BRIN naturally because BRIN's job is essentially:

Identify groups of heap pages that might contain matching data.

BRIN does not normally support the same precise access patterns as B-tree.

Conceptually:

B-tree

Value
  ↓
Precise index navigation
  ↓
Tuple location


BRIN

Condition
  ↓
Range summary
  ↓
Candidate pages
  ↓
Bitmap
  ↓
Heap access
  ↓
Row recheck

For this reason, BRIN query plans normally use:

Bitmap Index Scan
        +
Bitmap Heap Scan

rather than an ordinary B-tree-style:

Index Scan

or:

Index Only Scan

Why BRIN Indexes Are So Small

The exceptional size efficiency of BRIN comes from its range-based architecture.

A traditional index may maintain information associated with individual table rows.

BRIN maintains one compact summary for an entire group of physical pages.

Conceptually:

B-tree

Row 1     → index entry
Row 2     → index entry
Row 3     → index entry
Row 4     → index entry
...
Row N     → index entry


BRIN

Thousands of rows
       ↓
128 heap pages
       ↓
One block range
       ↓
Compact summary

The number of BRIN entries therefore grows according to the number of block ranges, not directly according to the number of table rows.

This can make BRIN dramatically smaller than a B-tree index on the same very large table.

BRIN vs B-tree

BRIN and B-tree use fundamentally different strategies.

The fundamental difference is:

B-tree
   ↓
Where are the matching rows?


BRIN
   ↓
Which physical parts of the table
can be skipped?

Advantages of BRIN

The main advantages of BRIN are:

• extremely small index size;

• low storage requirements;

• low index maintenance overhead;

• excellent suitability for very large tables;

• particularly effective for physically correlated data;

• excellent fit for append-only and time-series workloads;

• ability to eliminate large physical portions of a table;

• configurable block-range size;

• support for multiple summary strategies through operator classes;

• natural integration with PostgreSQL bitmap scans.

Limitations of BRIN

BRIN also has important limitations:

• it is generally lossy;

• it does not provide precise row-level navigation;

• candidate heap pages require row rechecks;

• effectiveness depends strongly on physical correlation;

• randomly distributed values can make BRIN ineffective;

• it is usually not the best choice for highly selective point lookups;

• large pages_per_range values can reduce precision;

• poor summaries can cause unnecessary heap-page reads;

• a very small index does not automatically mean faster queries.

When BRIN Is a Good Choice

BRIN is a strong candidate when:

1. The table is very large.

2. The indexed values correlate strongly with physical row order.

3. The workload frequently filters on that correlated column.

4. Queries can eliminate substantial portions of the table.

5. New rows generally preserve the existing physical ordering.

6. Keeping index size and maintenance overhead low is important.

Common candidates include:

• timestamps;

• transaction dates;

• event times;

• creation dates;

• business dates;

• sequential identifiers;

• other naturally increasing or decreasing values.

Core Idea

The complete BRIN architecture can be summarized as:

Very Large Table
       ↓
Physical Heap Pages
       ↓
Block Ranges
       ↓
BRIN Operator Class
       ↓
Range Summaries
       ↓
Bitmap Index Scan
       ↓
Eliminate Irrelevant Ranges
       ↓
Bitmap of Candidate Pages
       ↓
Bitmap Heap Scan
       ↓
Recheck Actual Rows
       ↓
Result

The most important principle is:

BRIN does not try to precisely index every row. It stores compact summaries about physical ranges of the table so PostgreSQL can determine which portions of a very large table do not need to be read.

This makes BRIN particularly effective for very large, physically correlated, append-only and time-series datasets.