BRIN Indexes and Block Range Summaries

A Block Range Index (BRIN) does not index rows. It indexes neighbourhoods of disk: for each group of physically adjacent heap pages — a block range — it stores a small summary, typically the minimum and maximum value of the indexed column within that range. The BRIN README states the goal and the cost in two sentences: “The essential idea of a BRIN index is to keep track of summarizing values in consecutive groups of heap pages … These values can be used to avoid scanning such pages during a table scan. … The cost of this is having to update the stored summary values of each page range as tuples are inserted into them.” Because no heap tuple identifiers (TIDs) are stored, BRIN can only produce a lossy bitmap — the executor must recheck every tuple it returns — and the index ends up thousands of times smaller than an equivalent B-tree. The entire value proposition rests on one precondition the documentation names explicitly: BRIN “is designed for handling very large tables in which certain columns have some natural correlation with their physical location within the table”. When that correlation degrades, the index does not become slow in the ordinary way — it becomes inert, still tiny, still scanned, and pruning nothing.

Point-in-time note (verified 2026-08-06): everything below is pinned to PostgreSQL 18.4, which https://www.postgresql.org/versions.json reports as the current release (major 18, latestMinor 4, released 2026-05-14). Source excerpts are from the REL_18_STABLE branch. BRIN first shipped in PostgreSQL 9.5 (commit 7516f5259411, “BRIN: Block Range Indexes”, Álvaro Herrera, 2014-11-07).

Mental Model — An Index of Where Not To Look

A B-tree answers “where is this value?”. BRIN answers the weaker, cheaper question: “which parts of the table can I prove do not contain this value?” It is a negative index — a skip list over physical storage. Everything it fails to exclude gets read.

Concretely: suppose a 100 GB append-only events table on an 8 kB page size. That is 13,107,200 heap pages. With the default pages_per_range of 128 (BRIN_DEFAULT_PAGES_PER_RANGE, defined in src/include/access/brin.h), the index holds 102,400 summary tuples, each roughly a header plus two 8-byte timestamps. The whole index is a few megabytes — small enough to stay resident in shared_buffers permanently. A B-tree on the same column would run to gigabytes.

flowchart LR
    subgraph HEAP["Heap — physical block order"]
        R0["blocks 0-127<br/>ts 2026-01-01 .. 01-02"]
        R1["blocks 128-255<br/>ts 2026-01-02 .. 01-03"]
        R2["blocks 256-383<br/>ts 2026-01-03 .. 01-04"]
        R3["blocks 384-511<br/>ts 2026-01-04 .. 01-05"]
    end
    subgraph IDX["BRIN index — one summary tuple per range"]
        S0["min 01-01 max 01-02"]
        S1["min 01-02 max 01-03"]
        S2["min 01-03 max 01-04"]
        S3["min 01-04 max 01-05"]
    end
    Q["WHERE ts = '2026-01-03 14:00'"] --> S0
    Q --> S1
    Q --> S2
    Q --> S3
    S0 -->|"excluded"| X0["skip 128 pages"]
    S1 -->|"excluded"| X1["skip 128 pages"]
    S2 -->|"MATCHES — cannot exclude"| Y2["read all 128 pages,<br/>recheck every tuple"]
    S3 -->|"excluded"| X3["skip 128 pages"]

The BRIN bargain in one picture. What it shows: four block ranges, four summaries, one query. Three ranges are provably irrelevant; one is not, and is read in full. The insight to take: BRIN’s granularity is the range, never the row. Even a query matching a single row must read the entire 1 MB range that could contain it. The index is not selective — the physical clustering is. BRIN just makes that clustering queryable.

The On-Disk Structure

A BRIN index has exactly three kinds of page, distinguished by a type word in the page’s special space (brin_page.h): BRIN_PAGETYPE_META (0xF091), BRIN_PAGETYPE_REVMAP (0xF092), and BRIN_PAGETYPE_REGULAR (0xF093).

BRIN index main fork

  block 0        blocks 1..k                    blocks k+1..n
+-----------+ +---------------------------+ +---------------------------+
| METAPAGE  | | REVMAP  (range map)       | | REGULAR pages             |
|           | |                           | |                           |
| magic     | | one ItemPointerData (TID) | | BrinTuple per range:      |
| version   | | per page range, fixed     | |   heap block number       |
| pagesper- | | size => address of any    | |   null bitmask, 2 bits    |
|   range   | | entry is pure arithmetic  | |     per column:           |
| lastrev-  | |                           | |     bt_hasnulls           |
|   mappage | |  entry i -> TID of the    | |     bt_allnulls           |
|           | |  summary tuple for range i| |   opclass-specific values |
+-----------+ +---------------------------+ +---------------------------+

ASCII fallback for the file layout — mermaid’s packet-beta is for bit-level wire headers, not for a page-type map. What it shows: the metapage pins pagesPerRange and the extent of the revmap; the revmap is a dense array of TIDs; the summary tuples themselves live on ordinary index pages. The insight to take: the revmap is the reason BRIN lookups are cheap — “since the map entries are fixed size, it is possible to compute the address of the range map entry for any given heap page by simple arithmetic”. There is no tree to descend and no search to perform.

The revmap lives “in the first few blocks of the index main fork, immediately following the metapage”, and when it must grow, “existing tuples in that page are moved to some other page” (the BRIN_EVACUATE_PAGE flag drives this). A revmap entry pointing at an invalid TID is the on-disk encoding of “this page range is not summarized” — a state that matters enormously, as the next-but-one section shows.

Each summary tuple carries generic NULL bookkeeping independent of opclass: “a single null bitmask of length twice the number of columns”, giving two bits per indexed column — bt_hasnulls (“whether there’s any NULL value at all in the page range”) and bt_allnulls (“whether all values are NULLs in the page range”). This is what lets IS NULL and IS NOT NULL prune ranges without opclass involvement.

The design decision with the widest consequences is stated first in the README: “Since item pointers are not stored inside indexes of this type, it is not possible to support the amgettuple interface. Instead, we only provide amgetbitmap support.” In the IndexAmRoutine vocabulary (index AM API), BRIN leaves amgettuple NULL. From that single fact follow all of BRIN’s limitations:

Capability	BRIN	Why
Plain (ordered) index scan	No	No TIDs to return one at a time
Bitmap heap scan	Yes	amgetbitmap returns a lossy TIDBitmap of whole pages
ORDER BY satisfied by the index	No	Summaries have no intra-range order
Index-only scan (amcanreturn)	No	The original value is not stored, only a summary of many
Unique index / primary key	No	Cannot locate an individual conflicting row
Multi-column	Yes	And unlike B-tree, “search effectiveness is the same regardless of which columns the query conditions use” (multicolumn indexes)
INCLUDE columns	No	No payload concept
amsummarizing flag	Yes	BRIN is the AM this flag exists for

How a Lookup Actually Runs

The scan path is almost startlingly simple, and the README describes it in full:

“To scan a table following a BRIN index, we scan the range map sequentially. This yields index tuples in ascending page range order. Query quals are matched to each index tuple; if they match, each page within the page range is returned as part of the output TID bitmap. If there’s no match, they are skipped. Range map entries returning invalid index TIDs, that is unsummarized page ranges, are also returned in the TID bitmap.”

That last sentence is the safety property, and it is easy to miss: an unsummarized range is always returned. BRIN can never produce a false negative, because “I have no summary” is treated identically to “the summary matches”. This is why an unsummarized tail on an append-only table silently costs you a full scan of that tail rather than a wrong answer.

The matching itself is delegated to the opclass’s consistent() support procedure, which “takes an index tuple and query quals, and returns whether the index tuple values match the query quals”. Every BRIN opclass must supply four generic procedures: opcinfo() (initialise a structure for creation or scanning), addValue() (fold a heap datum into a summary), consistent(), and union() (merge two summaries into the first). Minmax-style classes additionally register procedure numbers 11–14 for less-than, less-or-equal, greater-or-equal and greater-than.

sequenceDiagram
    participant P as Planner
    participant B as BRIN amgetbitmap
    participant RM as Revmap
    participant OC as opclass consistent proc
    participant BH as BitmapHeapScan
    participant H as Heap
    P->>B: scan with quals, e.g. ts >= X AND ts < Y
    B->>RM: read revmap sequentially, range 0..N
    loop for each page range
        RM-->>B: TID of summary tuple, or InvalidTid
        alt summary exists
            B->>OC: does this summary match the quals?
            OC-->>B: true / false
        else unsummarized
            Note over B: treated as MATCH — never skipped
        end
        B->>B: if match, add all pages of range to lossy TIDBitmap
    end
    B-->>BH: lossy TIDBitmap of candidate heap pages
    BH->>H: read each candidate page
    H-->>BH: all tuples on the page
    BH->>BH: RECHECK every tuple against the original quals
    BH-->>P: surviving rows

The full BRIN range-summary lookup, end to end. What it shows: a sequential pass over the revmap producing a page-granular bitmap, then a mandatory recheck in the BitmapHeapScan node. The insight to take: two costs are always paid and never appear as “index” cost in your intuition — reading every revmap entry (the index is scanned in full, always), and rechecking every tuple on every surviving page. That is why EXPLAIN ANALYZE on a BRIN plan shows Recheck Cond and a Rows Removed by Index Recheck count that can dwarf the rows actually returned. A BRIN plan that returns 10 rows after rechecking 4 million is working exactly as designed — and telling you the correlation is bad.

pages_per_range — the One Real Dial

pages_per_range is set at CREATE INDEX time and defaults to 128 (CREATE INDEX). The documentation gives the arithmetic and the trade-off directly: “The number of index entries will be equal to the size of the relation in pages divided by the selected value for pages_per_range. Therefore, the smaller the number, the larger the index becomes … but at the same time the summary data stored can be more precise and more data blocks can be skipped during an index scan.”

For the 100 GB / 13.1 M-page table above:

pages_per_range	Heap per range	Ranges	Index entries	Minimum heap read on a match
1	8 kB	13,107,200	13.1 M	8 kB
32	256 kB	409,600	410 k	256 kB
128 (default)	1 MB	102,400	102 k	1 MB
512	4 MB	25,600	26 k	4 MB
4096	32 MB	3,200	3.2 k	32 MB

The README’s “Future improvements” section names the two degenerate ends explicitly: “In the limit of one index tuple per page, the index itself would occupy too much space, even though we would be able to skip reading the most heap pages, because the summary values are tight; in the opposite limit of a single tuple that summarizes the whole table, we wouldn’t be able to prune anything even though the index is very small.” Variable-size ranges are listed as a desirable future feature, not something 18.4 has.

The practical rule that falls out: choose pages_per_range so that one range is roughly the smallest amount of heap you are willing to read for a point-ish query. For a time-series table queried by hour, the right value is the number of pages an hour of data occupies. Note that pages_per_range cannot be changed in place in a useful way — altering it requires a REINDEX to take effect on existing summaries.

Summarization and Its Lag

This is where most BRIN surprises come from, so it is worth walking the lifecycle precisely.

At CREATE INDEX, “all existing heap pages are scanned and a summary index tuple is created for each range, including the possibly-incomplete range at the end”. Since PostgreSQL 17 this build can be parallelised (commit b43757171470, “Allow parallel CREATE INDEX for BRIN indexes”, Tomas Vondra, 2023-12-08).

On insertion into an already-summarized range, the new heap tuple is compared against the existing summary. If it falls outside — or if it is NULL and the summary claims no NULLs — the summary is updated. “In many cases it is possible to update the index tuple in-place, but if the new index tuple is larger than the old one and there’s not enough space in the page, it is necessary to create a new index tuple with the new values. The range map can be updated quickly to point to it; the old index tuple is removed.”

On insertion into a page beyond the last summarized range, nothing happens. “Those insertions do not create a new index entry; instead, the page range remains unsummarized until later.” This is the lag: on a busy append-only table with autosummarize off, the newest data — precisely the data most queries want — is permanently unsummarized until the next VACUUM, and every query therefore reads all of it.

Four mechanisms close the gap:

• VACUUM, manual or autovacuum, on the table: “all existing unsummarized page ranges are summarized.”
• autosummarize, an index storage parameter that is off by default in 18.4. When on, “whenever autovacuum runs in that database, summarization will occur for all unsummarized page ranges that have been filled, regardless of whether the table itself is processed by autovacuum”. The mechanism is a request queue: a targeted summarization request is sent to autovacuum “when an insertion is detected for the first item of the first page of the next block range”, fulfilled the next time an autovacuum worker finishes in that database. If the queue is full the request is dropped, with a server-log line: LOG: request for BRIN range summarization for index "brin_wi_idx" page 128 was not recorded. Grep for that string — it is the direct symptom of summarization falling behind.
• brin_summarize_new_values(regclass) — “Scans the specified BRIN index to find page ranges in the base table that are not currently summarized by the index; for any such range it creates a new summary index tuple by scanning those table pages. Returns the number of new page range summaries that were inserted into the index.” (index maintenance functions). This is the workhorse for a cron job on an append-only table.
• brin_summarize_range(regclass, bigint) — the same, for the single range covering a given block number.

Running in the opposite direction, brin_desummarize_range(regclass, bigint) “removes the BRIN index tuple that summarizes the page range covering the given table block”, which the docs recommend “when the index tuple is no longer a very good representation because the existing values have changed”. Followed by a re-summarization, this is the only supported way to tighten a summary that has become too wide — because, as the next section explains, nothing else ever will.

Summarization is concurrency-safe via a placeholder tuple protocol, documented in summarize_range() in brin.c: “we first insert a placeholder index tuple into the index, then execute the heap scan; transactions concurrent with the scan update the placeholder tuple. After the scan, we union the placeholder tuple with the one computed by this routine. The update of the index value happens in a loop, so that if somebody updates the placeholder tuple after we read it, we detect the case and try again.” A further corner case is handled for the trailing partial range: the table size is recomputed after the placeholder is inserted, so pages appended in the meantime are covered by concurrent updates to the placeholder rather than being missed. pageinspect’s brin_page_items() exposes a placeholder boolean so you can see these in flight.

stateDiagram-v2
    [*] --> Unsummarized: new heap pages appended<br/>beyond last summarized range
    Unsummarized --> Summarized: VACUUM / autovacuum
    Unsummarized --> Summarized: autosummarize request<br/>off by default
    Unsummarized --> Summarized: brin_summarize_new_values<br/>or brin_summarize_range
    Summarized --> Summarized: INSERT inside the range<br/>widens min/max if needed
    Summarized --> Unsummarized: brin_desummarize_range
    note right of Unsummarized
        Revmap entry is an invalid TID.
        Every scan returns this range
        UNCONDITIONALLY — correct,
        but zero pruning.
    end note
    note right of Summarized
        Summaries only ever WIDEN.
        DELETE never tightens them;
        the README calls tightening
        "an optimization opportunity
        only, not a correctness issue"
        and it is "not currently
        implemented".
    end note

The lifecycle of a single block range. What it shows: the three paths into a summarized state, the widening-only update path, and the single manual path back out. The insight to take: the arrow that does not exist is the important one — there is no automatic transition that makes a summary narrower. A range that once contained an outlier keeps advertising that outlier forever, even after the row is deleted and vacuumed away. brin_desummarize_range() plus re-summarization is the only cure short of REINDEX.

The Four Built-in Operator Class Families

Family	Stores per range	Added	Best for	Parameters
minmax	The minimum and maximum value in the range	9.5	Monotonic or near-monotonic columns: append-only timestamps, sequential IDs	none
minmax-multi	Several min/max intervals, each “either a point, or a boundary of an interval”	14 (ab596105b55f, Tomas Vondra, 2021-03-26)	Mostly-ordered columns with occasional outliers — late-arriving events, backfills, UPDATE-in-place tables	values_per_range, 8–256, default 32
inclusion	A value that includes all values in the range — e.g. a bounding box, a containing network, a covering range	9.5	Geometric (box), inet, and anyrange columns	none
bloom	A Bloom filter over all values in the range	14 (77b88cd1bb90, Tomas Vondra, 2021-03-26)	Equality-only lookups on columns with no sort correlation but strong clustering of repeated values	n_distinct_per_range (default −0.1, floor 16 distinct values), false_positive_rate (0.0001–0.25, default 0.01)

The distinction between minmax and minmax-multi is the single most useful thing to internalise, because it is the standard repair for mildly-degraded correlation. A plain minmax summary is one interval. Insert one row from 2019 into a range otherwise covering one hour of 2026, and that range’s summary becomes [2019-…, 2026-…] — it now matches essentially every date predicate you will ever write, forever. A minmax-multi summary of the same range can store {[2019-…], [2026-01-03 14:00 … 2026-01-03 15:00]} — a point plus an interval — and still exclude the whole range for a query on 2022. values_per_range (default 32) bounds how many such points and interval boundaries are kept before the opclass is forced to merge intervals and lose precision.

The bloom family solves a different problem entirely: it does not care about sort order at all, only about whether a value is present in the range. That makes it the right choice for something like a tenant identifier or a device ID in a table that is naturally batched by tenant but not sorted by it. Its parameters are the classic Bloom filter trade-off: n_distinct_per_range sizes the filter (negative values, ≥ −1, mean “assume distinct values grow linearly with the maximum possible tuples in the block range, about 290 rows per block”), and false_positive_rate (default 0.01, i.e. 1 %) fixes the accuracy. Bloom opclasses support only = — see the operator table in brin.html, where every *_bloom_ops entry lists exactly one indexable operator.

Writing a new opclass is a documented extension point: supply opcInfo(), consistent(), addValue() and unionTuples(), plus an optional options(); the README notes “procedure numbers up to 10 are reserved for future expansion”.

The Load-Bearing Precondition: Physical Correlation

Here is the part that is usually hand-waved, so let us do it properly with the actual source.

PostgreSQL measures correlation per column in pg_stats.correlation: “Statistical correlation between physical row ordering and logical ordering of the column values. This ranges from −1 to +1. When the value is near −1 or +1, an index scan on the column will be estimated to be cheaper than when it is near zero, due to reduction of random access to the disk” (pg_stats). +1 means physical order matches value order exactly; 0 means no relationship.

brincostestimate() in selfuncs.c feeds that number straight into the plan. The relevant lines, verbatim from REL_18_STABLE:

/* work out the actual number of ranges in the index */
indexRanges = Max(ceil((double) baserel->pages / statsData.pagesPerRange), 1.0);
 
/* ... indexCorrelation = max |correlation| over the columns used by the query ... */
 
qualSelectivity = clauselist_selectivity(root, indexQuals, baserel->relid,
                                         JOIN_INNER, NULL);
 
/* minimum possible ranges we could match if rows were in perfect order */
minimalRanges = ceil(indexRanges * qualSelectivity);
 
if (*indexCorrelation < 1.0e-10)
    estimatedRanges = indexRanges;
else
    estimatedRanges = Min(minimalRanges / *indexCorrelation, indexRanges);
 
selec = estimatedRanges / indexRanges;

Walking it symbol by symbol:

• indexRanges — the number of block ranges: heap pages divided by pages_per_range, at least 1.
• indexCorrelation — not a BRIN statistic at all. It is the largest absolute pg_stats.correlation among the table columns the query’s index clauses touch. The comment explains the choice: “Because we can use all index quals equally when scanning, we can use the largest correlation (in absolute value) among columns used by the query. Start at zero, the worst possible case. If we cannot find any correlation statistics, we will keep it as 0.” So a table that has never been ANALYZEd gets correlation 0 and BRIN is costed as useless.
• qualSelectivity — the ordinary planner estimate of the fraction of rows matching the predicate.
• minimalRanges — the best case: if rows were perfectly ordered, a predicate matching fraction s of rows would touch fraction s of ranges.
• estimatedRanges — the punchline. The minimal range count is divided by the correlation, capped at the total. Correlation below 10⁻¹⁰ short-circuits to “every range”.
• selec — the resulting index selectivity: the fraction of the table the planner expects the bitmap heap scan to read.

Put numbers on it. Take the 100 GB table, pages_per_range 128, so indexRanges = 102,400, and a predicate selecting 0.1 % of rows, so minimalRanges = ceil(102,400 × 0.001) = 103.

pg_stats.correlation	estimatedRanges	selec	Heap the planner expects to read
1.00 (perfect)	103	0.001	~100 MB
0.50	206	0.002	~200 MB
0.10	1,030	0.010	~1 GB
0.01	10,300	0.101	~10 GB
0.001	103,000 → capped at 102,400	1.000	the whole 100 GB
0.00	102,400	1.000	the whole 100 GB

Two things to take from this table. First, the relationship is hyperbolic, not linear — the damage from losing correlation is concentrated at the low end, and by the time correlation reaches ~0.001 the index is costed as strictly worse than a sequential scan (it is a seq scan plus a revmap scan plus a recheck). Second, this is the cost model; the runtime behaviour it approximates is the widening of minmax intervals until neighbouring ranges overlap and consistent() can no longer reject anything.

flowchart TB
    subgraph GOOD["Correlation ~1.0 — disjoint summaries"]
        G1["range 0: 100..199"]
        G2["range 1: 200..299"]
        G3["range 2: 300..399"]
        GQ["query: v = 250"] --> G2
        G1 -.->|reject| GX1["skipped"]
        G3 -.->|reject| GX3["skipped"]
    end
    subgraph BAD["Correlation ~0.05 — overlapping summaries"]
        B1["range 0: 100..398"]
        B2["range 1: 104..399"]
        B3["range 2: 101..397"]
        BQ["query: v = 250"] --> B1
        BQ --> B2
        BQ --> B3
        B1 --> BR["every range matches<br/>full table read + recheck"]
        B2 --> BR
        B3 --> BR
    end
    GOOD -->|"rows inserted out of order,<br/>UPDATEs relocating rows,<br/>FSM reusing freed space,<br/>backfills, multi-tenant interleave"| BAD

What correlation degradation does to summaries, mechanically. What it shows: in the good case the per-range intervals are disjoint, so exactly one can match a point predicate. In the bad case every interval spans nearly the full value domain, so none can be rejected. The insight to take: the index does not get bigger, slower to scan, or corrupted — it stays exactly the same size and is scanned exactly as fast. It simply stops excluding anything. This is the failure mode people find hardest to diagnose: pg_relation_size looks perfect, the plan still says “Bitmap Index Scan using … brin”, and the query takes as long as a seq scan.

What Actually Degrades Correlation

Four common causes, in rough order of how often they bite:

1. Non-append inserts. Multi-tenant tables where each tenant’s rows arrive interleaved, or ETL that loads several partitions’ worth of history at once, produce ranges whose min/max span everything from the outset.
2. UPDATEs. PostgreSQL never overwrites in place; a non-HOT update writes the new version wherever the free space map offers room — which, on a table with VACUUM-reclaimed space, is likely to be an old page. A single such row drags a whole range’s minmax interval open.
3. DELETE plus reuse. Freed space in old pages gets recycled for new rows, mixing new values into old ranges. And since summaries never tighten on delete, both the old and the new extremes persist.
4. A single outlier. One backfilled row, one clock-skewed timestamp, one sentinel value like '1970-01-01' or 9999-12-31 — and that range matches every predicate forever.

Repairs, in Order of Escalation

• ANALYZE first. If the table has never been analysed, correlation reads as 0 and the planner will refuse the BRIN path regardless of the physical truth. Always check SELECT attname, correlation FROM pg_stats WHERE tablename = '…' before concluding anything (examining index usage makes the same point generally: examining index usage without ANALYZE is “a lost cause”).
• Switch to minmax_multi_ops. The correct first response to outlier-driven degradation, and it does not require rewriting the table. Raise values_per_range above 32 if outliers are numerous.
• Shrink pages_per_range (with a REINDEX). Smaller ranges are less likely to contain an outlier, at proportionally more index space. Diminishing returns arrive quickly.
• brin_desummarize_range() + re-summarize the specific ranges you know are polluted. Surgical, but requires knowing which ones.
• CLUSTER to restore physical order. Note the documented catch: “Clustering is a one-time operation: when the table is subsequently updated, the changes are not clustered. That is, no attempt is made to store new or updated rows according to their index order” (CLUSTER). It also takes an ACCESS EXCLUSIVE lock and rewrites the table. Setting fillfactor below 100 “can aid in preserving cluster ordering during updates, since updated rows are kept on the same page if enough space is available”.
• Partition instead. If the correlation cannot be maintained, declarative partitioning gives you range exclusion as a structural guarantee rather than a statistical hope. This is usually the right answer for a table whose write pattern genuinely is not append-ordered.

Vacuuming, and What BRIN Does Not Cost

BRIN is unusually cheap to maintain. “Since no heap TIDs are stored in a BRIN index, it’s not necessary to scan the index when heap tuples are removed.” There is no index vacuum pass, no dead-tuple cleanup, and no bloat in the B-tree sense — a BRIN index’s size is a pure function of the table’s page count and pages_per_range.

The README notes an optimisation that was considered and declined: if a table had only BRIN indexes, VACUUM would not need to accumulate TIDs at all, saving maintenance_work_mem. “It’s unlikely that BRIN would be the only indexes in a table, though, because primary keys can be btrees only, and so we don’t implement this optimization.” That sentence is also the honest summary of BRIN’s role: it is a supplementary index on a big table, not the table’s only index.

Inspecting a BRIN Index

pageinspect is the only way to see what a summary actually contains. brin_metapage_info(get_raw_page('idx', 0)) returns magic, version, pagesperrange, lastrevmappage. brin_revmap_data(page) returns the TID list from a revmap page. brin_page_items(page, index) returns, per summary and per indexed attribute, itemoffset, blknum, attnum, allnulls, hasnulls, placeholder, empty, and value — with value rendered as {1 .. 88} for minmax (pageinspect). Dumping blknum and value across a whole index and eyeballing how much consecutive intervals overlap is the most direct possible measurement of correlation health — far more informative than pg_stats.correlation, which is a single scalar for the whole table.

Uncertain

Verify: that PostgreSQL 18 contains no BRIN-specific changes. Reason: a fetch of https://www.postgresql.org/docs/18/release-18.html surfaced the index-related items (skip scan, parallel GIN builds, AIO) but returned no BRIN entry — absence in a summarised fetch of a very long page is weaker evidence than a direct reading of the full “Indexes” subsection. To resolve: read the E.1 release-notes section of release-18.html end to end, and diff src/backend/access/brin/ between REL_17_STABLE and REL_18_STABLE. #uncertain

Uncertain

Verify: the exact BRIN summary tuple size, and therefore the “a few megabytes for a 100 GB table” figure used throughout this note. Reason: the index-size arithmetic here is derived from BRIN_DEFAULT_PAGES_PER_RANGE (128, read from brin.h) times a reasoned per-tuple size for a two-timestamp minmax summary; I did not read BrinTuple’s on-disk layout in brin_tuple.h, nor measure a real index. The order of magnitude (“thousands of times smaller than a B-tree”) is well supported by the documentation’s “a BRIN index is very small”; the specific megabyte figure is not. To resolve: build the index and compare pg_relation_size(), or read brin_form_tuple() in src/backend/access/brin/brin_tuple.c. #uncertain

When to Choose BRIN — an Honest Comparison

Situation	Use
Huge append-only table, queried by the append key (timestamp, serial ID)	BRIN minmax — the canonical fit
Same, but with late-arriving rows or occasional backfills	BRIN minmax-multi
Equality lookups on a batched-but-unsorted column (tenant, device)	BRIN bloom
Geometric containment / inet / range containment on a big correlated table	BRIN inclusion
Selective point lookups, uniqueness, ORDER BY … LIMIT, index-only scans	B-tree — BRIN can do none of these
Array / jsonb / full-text containment	GIN (GIN Indexes and Inverted Search)
The correlation cannot be maintained by the write pattern	Partitioning, not an index

The comparison against a B-tree is not really “which is faster” — it is “what are you optimising”. A B-tree on a 100 GB events table might be 3 GB, must be maintained on every insert, competes for shared_buffers, and gives you exact single-row lookups. A BRIN on the same column is a few megabytes, is nearly free to maintain, permanently cached, and gives you coarse range exclusion. On a table you only ever query by time window, the BRIN is strictly the better trade. On a table you also query by primary key, you need both — and the B-tree was going to exist anyway.

See Also

• PostgreSQL Internals MOC — the parent map; this note is §6, Indexes and Access Methods
• PostgreSQL B-Tree Index Internals — the opposite design point: exact TIDs, ordered scans, and the bloat that comes with them
• B-Tree and B+ Tree — the engine-agnostic data structures BRIN deliberately is not
• Clustered versus Secondary Indexes — why PostgreSQL has no clustered index, and why BRIN is the closest it gets to exploiting physical order
• Hash Indexes in PostgreSQL — the other narrow-purpose PostgreSQL access method
• GIN Indexes and Inverted Search and GiST and SP-GiST Indexes — the other lossy and extensible AMs
• The Index Access Method Interface — the IndexAmRoutine contract, and what leaving amgettuple NULL costs
• Index-Only Scans in PostgreSQL — the optimisation BRIN structurally cannot support
• Bloom Filters — the probabilistic structure behind *_bloom_ops
• PostgreSQL Page Layout and Line Pointers — the standard page format the revmap and summary pages use
• The PostgreSQL Free Space Map — the mechanism that quietly destroys physical correlation on update-heavy tables
• Heap-Only Tuples and HOT Updates — when an UPDATE does not relocate a row, and therefore does not hurt correlation
• PostgreSQL VACUUM Internals — the process that performs summarization as a side effect
• Planner Statistics and pg_statistic — where correlation comes from, and why ANALYZE is a prerequisite for BRIN being used at all