BRIN pages_per_range Tuning #

A BRIN index on a 1.4 TB events table is 2 MB and answers a one-day range query by reading 68,000 heap pages, of which 5% contain no matching row. Halving the block range size doubles the index to 4 MB and removes most of that waste. Shrinking it further makes the index 32 MB and saves almost nothing more. The right setting is a measurement, not a default.

BRIN’s structure is covered in BRIN and hash indexes. This page is about its one important storage parameter.

The Index Condition #

pages_per_range sets how many heap pages each BRIN summary covers, default 128 (1 MB of heap per summary). It determines two things in opposite directions:

The waste is concentrated at the edges of the queried range and around any out-of-order values. For perfectly ordered data and a query spanning many ranges, only the first and last ranges contain non-matching rows, so the waste is small at any setting. For data with occasional stragglers — a backfilled row from last month landing in today’s pages — the affected range’s summary widens to span both values, and every query in that whole span must read it.

That last case is what minmax_multi operator classes (PostgreSQL 14+) address: instead of one interval per range, they store several disjoint intervals, so one stray value no longer makes the range match a month of queries.

Summaries for newly written pages are created by VACUUM, by brin_summarize_new_values(), or automatically with autosummarize = on. Unsummarized ranges are assumed to match everything.

Trading index size against rechecked rows Left, 128 pages per range: the index is 2 megabytes, each summary covers a megabyte of heap, and a day query rechecks 402 thousand non-matching rows at the range edges. Right, 8 pages per range: the index is 32 megabytes, summaries are sixteen times finer, and only 26 thousand rows are rechecked. pages_per_range = 128 (default) index 2 MB 1 MB of heap per summary 402,118 rows rechecked 68,224 heap blocks read pages_per_range = 8 index 32 MB 64 kB of heap per summary 26,004 rows rechecked 61,208 heap blocks read both indexes are negligible next to a 1.4 TB table

Annotated EXPLAIN Evidence #

EXPLAIN (ANALYZE, BUFFERS)
SELECT count(*) FROM events
WHERE occurred_at >= '2026-09-16' AND occurred_at < '2026-09-17';

Default ranges:

Bitmap Heap Scan on events  (actual time=2.1..742.6 rows=8412004 loops=1)
  Recheck Cond: ((occurred_at >= '2026-09-16'::date) AND (occurred_at < '2026-09-17'::date))
  Rows Removed by Index Recheck: 402118
  Heap Blocks: lossy=68224
  ->  Bitmap Index Scan on events_occurred_brin  (actual time=2.0..2.0 rows=682240 loops=1)
        Buffers: shared hit=18
Execution Time: 814.1 ms
-- recheck removed 402k rows: 4.8% of the rows read did not match

Finer ranges:

CREATE INDEX CONCURRENTLY events_occurred_brin8 ON events USING brin (occurred_at) WITH (pages_per_range = 8, autosummarize = on);
Bitmap Heap Scan on events  (actual time=6.4..688.2 rows=8412004 loops=1)
  Rows Removed by Index Recheck: 26004
  Heap Blocks: lossy=61208
  ->  Bitmap Index Scan on events_occurred_brin8  (actual time=6.2..6.2 rows=612080 loops=1)
        Buffers: shared hit=284
Execution Time: 702.6 ms
-- 8% fewer heap blocks, 94% fewer rechecks, index still tiny

A table with stragglers, where range size is not the answer:

Rows Removed by Index Recheck: 41204110
Heap Blocks: lossy=1804220
-- rechecks larger than the result: out-of-order values widened many summaries
-- fix: minmax_multi, not smaller ranges
The two numbers that decide the setting Three items are annotated. Rows Removed by Index Recheck compared with rows returned gives the waste ratio. Heap Blocks lossy shows how many pages the bitmap covered. A recheck count far above the result size points at out-of-order values rather than range size. Rows Removed by Index Recheck: 402118 waste: 4.8% of rows read Heap Blocks: lossy=68224 pages the bitmap covered rechecks ≫ rows returned stragglers, not range size compare rechecked rows with returned rows, not with the table

Step-by-Step Resolution #

  1. Measure the current waste ratio: Rows Removed by Index Recheck ÷ rows returned. Below about 0.1 there is little to gain.

  2. Estimate heap pages per unit of the column. For time-series data, how many pages does an hour occupy? If a day spans 68,000 pages, a 128-page range is 0.2% of a day — already fine. If a day spans 200 pages, the default range covers most of it and finer ranges will help.

  3. Build a candidate index concurrently with a smaller range and compare on the real queries:

    CREATE INDEX CONCURRENTLY events_occurred_brin8
  ON events USING brin (occurred_at) WITH (pages_per_range = 8, autosummarize = on);

  4. If rechecks are dominated by stragglers, switch operator class instead:

    CREATE INDEX CONCURRENTLY events_occurred_brin_mm
  ON events USING brin (occurred_at timestamptz_minmax_multi_ops) WITH (autosummarize = on);

  5. Enable autosummarize on append-only tables so the newest ranges are covered without waiting for vacuum, and verify:

    SELECT * FROM brin_page_items(get_raw_page('events_occurred_brin', 2), 'events_occurred_brin') LIMIT 5;
-- or simply: SELECT brin_summarize_new_values('events_occurred_brin');

  6. Drop the loser and keep one index; two BRIN indexes on the same column cost little but confuse future readers.

  7. Re-measure after bulk backfills, which are the usual cause of correlation damage.

Diminishing returns below 32 pages per range At 128 pages per range the index is 2 megabytes and rechecks 402 thousand rows. At 32 pages it is 8 megabytes and rechecks 98 thousand. At 8 pages it is 32 megabytes and rechecks 26 thousand. At 1 page it is 256 megabytes and rechecks 3 thousand, with little further gain in query time. 128 pages (default) 402k rechecked, 2 MB 32 pages 98k rechecked, 8 MB 8 pages 26k rechecked, 32 MB 1 page 3k rechecked, 256 MB query time improved 14% from 128 to 8, and 1% from 8 to 1

Before and After #

-- BEFORE: pages_per_range = 128
Rows Removed by Index Recheck: 402118  Heap Blocks: lossy=68224          Execution Time: 814.1 ms

-- AFTER: pages_per_range = 8, autosummarize on
Rows Removed by Index Recheck: 26004   Heap Blocks: lossy=61208          Execution Time: 702.6 ms

When the answer is not BRIN at all #

BRIN reduces how much of a table a range scan reads; it cannot make a scan selective. A query returning eight million rows will read those rows whatever the index, so the ceiling on improvement is the fraction of read pages that contain no match — a few percent in a well-correlated table. If a query needs to be a hundred times faster, BRIN tuning is the wrong lever: the answer is partitioning (so the query touches one partition), a summary table (so it reads precomputed aggregates), or a B-tree if the query is actually selective.

Where BRIN shines is in making a full-table scan unnecessary at negligible storage cost, especially alongside partitioning: each partition’s BRIN index narrows the scan within the partition, and the two mechanisms compose well. On a partitioned table, set pages_per_range per partition if their sizes differ markedly, since the parameter is about pages, not time.

Common Pitfalls #

Tuning range size when stragglers are the problem. Smaller ranges do not fix widened summaries. Diagnostic signal: rechecks far exceeding returned rows. Fix: minmax_multi.

Forgetting summarization. New pages match everything until summarized. Diagnostic signal: recent-data queries reading the whole table tail. Fix: autosummarize = on.

Very small ranges. The index grows without meaningful gains. Diagnostic signal: index size in hundreds of megabytes with little time improvement. Fix: stop at the knee of the curve.

Comparing rechecks with table size. The ratio that matters is rechecked rows versus returned rows. Diagnostic signal: dismissing a 400k recheck on an 8-million-row result. Fix: compute the ratio.

Frequently Asked Questions #

What is pages_per_range in a BRIN index? #

It is the number of heap pages summarised by each BRIN entry, 128 by default. Smaller values make the index larger and more precise; larger values make it smaller and cause more rows to be rechecked.

How do I know whether to lower pages_per_range? #

Compare Rows Removed by Index Recheck with the rows the query returns. A high ratio means the bitmap includes many pages without matches, which finer ranges can reduce.

What is autosummarize for? #

It creates BRIN summaries for newly written page ranges automatically instead of waiting for vacuum. Without it, recently inserted data is treated as matching every query until a vacuum summarises it.

Up: BRIN and Hash Indexes