A PostgreSQL BRIN index does not store one index entry for every indexed row. It stores summary data for consecutive ranges of heap blocks. That distinction gives BRIN a very different cost and selectivity profile from a B-tree.

The access method fits large tables where indexed values tend to follow heap location. Timestamped append-heavy data is a common shape: older values tend to occupy earlier blocks and newer values tend to occupy later blocks. A range predicate can then eliminate many block ranges using compact summary data.

BRIN summarizes heap ranges

For data types using the standard minmax operator class, a BRIN summary records minimum and maximum values found in a block range. An index on an event timestamp can be created with:

CREATE INDEX events_recorded_at_brin_idx
    ON events USING brin (recorded_at);

Suppose one summarized range contains timestamps from 2026-08-01 through 2026-08-03. A query restricted to September can reject that entire range from the summary alone. PostgreSQL does not need an index tuple for every event to make that decision.

This compression is also the source of BRIN’s lossy behavior. A summary that overlaps the query condition only establishes that matching rows may exist in the range. PostgreSQL reads candidate heap pages and rechecks individual tuples against the query condition.

A B-tree answers a different question. Its ordered row-level entries can locate matching tuples with much finer granularity. BRIN instead identifies heap regions that may contain matches. The smaller structure is useful only when those summaries can exclude enough table blocks to reduce heap work.

Physical correlation controls pruning quality

BRIN minmax summaries become selective when values within each block range occupy a narrow interval. Data inserted in timestamp order naturally tends toward that shape.

Consider a table receiving events in increasing recorded_at order. Early heap ranges contain early timestamps, later ranges contain later timestamps, and a query for a recent interval can reject older ranges quickly.

If rows with timestamps from many years are distributed throughout every range, each minimum-to-maximum interval becomes broad. A recent-time query may overlap nearly every summary. The BRIN index can remain small while providing little block elimination.

This makes heap layout part of the index’s practical behavior. SQL-level cardinality alone does not describe BRIN selectivity. Two tables with the same values and row counts can produce different pruning behavior when their physical ordering differs.

pages_per_range sets summary granularity

The pages_per_range storage parameter controls how many heap blocks each BRIN range covers. PostgreSQL uses 128 pages per range by default. A different value can be set when the index is created:

CREATE INDEX events_recorded_at_brin_idx
    ON events USING brin (recorded_at)
    WITH (pages_per_range = 32);

Smaller ranges produce more index entries and more precise summaries. A query can reject blocks at a finer granularity, but the index grows and maintains more summary tuples. Larger ranges reduce index size further while combining more heap values into each summary.

There is no universally suitable range size. The useful point depends on table size, value correlation, row width, query intervals, and the amount of extra heap scanning acceptable for the workload.

New ranges require summarization

BRIN maintenance differs from row-level index maintenance. When an index is created, PostgreSQL scans existing heap pages and creates summaries for their ranges. New tuples added to an already summarized range update its summary as needed.

A newly formed range can remain unsummarized until a summarization operation processes it. VACUUM, including autovacuum processing of the table, summarizes unsummarized ranges. PostgreSQL also exposes functions for explicit maintenance:

SELECT brin_summarize_new_values('events_recorded_at_brin_idx');

The autosummarize index parameter can request targeted summarization as new ranges fill:

CREATE INDEX events_recorded_at_brin_idx
    ON events USING brin (recorded_at)
    WITH (autosummarize = on);

autosummarize is disabled by default. Its requests are handled through autovacuum activity rather than synchronously turning every new range into a summary at insertion time.

Candidate ranges still require heap checks

A BRIN scan commonly feeds a bitmap scan. The index identifies ranges consistent with the scan condition, and the executor examines heap pages represented by those ranges. Tuple conditions are rechecked because the summary describes a range rather than exact row membership.

For a predicate such as:

SELECT id, recorded_at, payload
FROM events
WHERE recorded_at >= TIMESTAMPTZ '2026-09-01 00:00:00+00'
  AND recorded_at <  TIMESTAMPTZ '2026-09-02 00:00:00+00';

minmax summaries whose intervals cannot overlap September 1 can be discarded. Summaries that do overlap remain candidates even if only a small fraction of their tuples satisfy the predicate.

That boundary keeps BRIN distinct from a compact B-tree substitute. Its benefit comes from avoiding irrelevant heap regions, not from representing every matching tuple precisely.

BRIN fits data with locality

BRIN is most coherent when a large relation has stable locality between indexed values and heap blocks. Append-oriented timestamps, monotonically increasing identifiers, and other naturally clustered values can produce narrow summaries without maintaining a row-level index entry for each tuple.

The same index can lose much of its pruning value as updates or insertion patterns scatter values across the heap. Range size and summarization settings can tune the structure, but they cannot create physical correlation that the table does not have.

BRIN therefore exposes a property that conventional index discussions can hide: data placement can be query metadata. When block ranges carry meaningful value boundaries, compact summaries can exclude large regions of a table before tuple-level checks begin.

PostgreSQL Database Indexes BRIN