We had an events table that grew by tens of millions of rows a day, almost all of them appended with a created_at of right now. Queries were nearly always time-range filters: give me everything between two timestamps. The btree index on created_at worked, of course, but it was enormous, it had to be maintained on every insert, and it kept a healthy slice of shared_buffers busy just holding its own upper layers. During an incident review I tested a BRIN index on a copy of the table. It was a few megabytes against the btree's tens of gigabytes, and the time-range queries ran at roughly the same speed, because the data arrived in time order and the table's physical layout already matched the assumption the index makes. That table is why I check for BRIN candidates before reaching for btree on any large, append-mostly table.
BRIN is not a better btree. It is a different bet entirely, and this post is about what the bet is, how to check whether your data qualifies, how to tune pages_per_range, and the failure mode that matters most: a BRIN index that exists, costs you write overhead, and silently does nothing.
What a BRIN index actually stores
A Block Range Index does not point at individual rows. It divides the table's heap into ranges of adjacent pages, 128 pages per range by default, and for each range stores a compact summary. For a plain minmax BRIN on a column, that summary is the minimum and maximum value found anywhere in those pages. When a query filters on that column, PostgreSQL can skip any range whose min and max prove it cannot contain a matching row, and only scans the ranges that might. The index is tiny because it stores a couple of values per range instead of one entry per row, and it stays tiny as the table grows.
The cost of that tininess is that BRIN is lossy. A range that might contain matches gets scanned in full, and every row is rechecked against the real predicate. In EXPLAIN output that shows up as a Bitmap Heap Scan with a count of Rows Removed by Index Recheck. Some recheck volume is normal and expected; if the recheck removes nearly everything it touches, your ranges are not selective and the index is earning very little for its keep.
The bet: physical correlation
Minmax BRIN, the default family and the subject of this post, only works when a column's values are correlated with the physical position of rows in the heap. Time-ordered append-only data is the canonical case: row 1 was inserted before row 2, so earlier pages hold earlier timestamps, and each page range covers a narrow time window. A range scan then touches a handful of ranges and skips the rest. The planner even exposes its own measurement of this property: ANALYZE computes a correlation statistic per column, visible in pg_stats, where 1 means perfect ascending correlation, -1 perfect descending, and 0 no correlation at all.
SELECT attname, correlation
FROM pg_stats
WHERE schemaname = 'public'
AND tablename = 'events';My rule of thumb: an absolute correlation above about 0.9 on a big table is a strong BRIN candidate. Between 0.5 and 0.9 it depends on how much you care about index size versus recheck volume. Near zero, do not bother with minmax, because the min and max of every range will span most of the value domain and every range will always "maybe" match. Bloom opclasses, also added in PostgreSQL 14, are the exception for pure equality filters on uncorrelated data, but that is a different tool with its own tradeoffs. Check the statistic after a representative ANALYZE, and remember that correlation describes the current physical order. Bulk loads, restores, and anything that rewrites the table can change it dramatically, usually for the better.
Creating one and tuning pages_per_range
CREATE INDEX events_created_at_brin
ON events USING brin (created_at)
WITH (pages_per_range = 32);pages_per_range is the one knob that matters. The default of 128 pages, one megabyte of heap per range, is a reasonable start. Lowering it, as above to 32, gives each range a narrower min-max window, which makes ranges more selective and cuts recheck work, at the price of a larger index and more summaries to maintain. Raising it shrinks the index further but widens each window. For tables where rows arrive strictly in time order I have found values between 16 and 64 to be a sweet spot, but measure with your own queries: EXPLAIN ANALYZE a representative range filter and watch the recheck line as you vary the knob. PostgreSQL 14 also added minmax-multi operator classes, which store multiple min-max intervals per range and help when a mostly-ordered column has localized outliers that would otherwise stretch a single interval across the whole domain.
One operational detail that bites people: BRIN summaries are not maintained instantly for brand-new data. Newly appended pages sit unsummarized until something summarizes them, and unsummarized ranges are scanned in full by every query. Any VACUUM pass, manual or autovacuum, summarizes the pending ranges it finds; the catch is that an append-mostly table with few dead tuples may not earn an autovacuum visit on its own. Building the index with autosummarize on tells autovacuum to visit specifically to summarize new ranges, and you can always force it by hand after a bulk load:
SELECT brin_summarize_new_values('events_created_at_brin'::regclass);Enable autosummarize at index creation if the table is append-heavy enough that the gap between summarization passes matters, knowing it makes autovacuum do extra visits it would otherwise skip.
When BRIN wins
- Append-only time series and event logs. created_at, logged_at, received_at. This is the flagship case, and where the size difference against btree is most absurd.
- Sequential identifiers on huge tables. An identity column on an append-mostly table correlates with position almost perfectly, and a BRIN on it can serve id-range scans that would otherwise need a btree the size of a small country.
- Batch-loaded fact tables. Data loaded in sorted or partition order tends to have excellent correlation by construction.
- Partitioned tables. A local BRIN per partition on the time column pairs naturally with native partitioning and keeps every partition's index overhead near zero.
When it silently does nothing
The dangerous BRIN is not the one that errors; it is the one that exists and is never used. Random-order values are the classic killer: a UUIDv4 column, or any hash, has correlation near zero, so every range's min-max spans the value space and the planner, correctly, never picks the index. Heavily updated columns degrade too, because updates relocate row versions across pages and erode the physical ordering the index assumed. Small tables are the quieter case: if the heap fits in a few hundred pages, a sequential scan is cheap and the planner will ignore BRIN no matter how good the correlation is. In all three situations the index still costs you maintenance on every write, and it shows up in pg_stat_user_indexes with an idx_scan of zero, which is exactly where to look for it.
The verification loop is simple: EXPLAIN ANALYZE the queries the index exists for, confirm a Bitmap Heap Scan using the BRIN index appears, and read the Rows Removed by Index Recheck number. Then check idx_scan periodically. If the planner refuses the index on well-correlated data, the usual suspects are stale statistics or ranges that were never summarized after a bulk load. If it refuses on uncorrelated data, drop the index and move on; btree exists for a reason, and our EXPLAIN ANALYZE guide walks through reading those plans if the output is unfamiliar.
Sizing up the trade with MonPG
An index swap is a bet you only settle with before-and-after numbers: insert overhead, buffer pressure, range-scan latency. I use MonPG on production PostgreSQL systems for exactly that comparison, because query history, wait events, and index usage statistics in one place let the week after the BRIN swap be judged against the week before instead of argued from theory. The PostgreSQL index advisor page shows how index-level signals surface in the product, and the MonPG blog has more on index hygiene for large tables.