PostgreSQL BRIN Indexes: When & How to Use Block Range Indexes

You have a 500-million-row events table. The B-tree index on created_at consumes 12 GB. Every insert must update that 12 GB index. Backups include 12 GB of index data. The buffer cache is full of index pages. And all you ever do is range queries: "give me events from last week." There's a better way. A BRIN index on the same column would be roughly 100 KB -- not 12 GB -- and for your query pattern, it works just as well.

How BRIN Works

Instead of indexing every individual row (like B-tree), BRIN stores the minimum and maximum values for ranges of consecutive physical blocks. The default is 128 pages (~1 MB of table data) per range entry.

To find rows where created_at = '2026-01-15', PostgreSQL reads the BRIN index, identifies which block ranges could contain that date (any range where min <= '2026-01-15' <= max), and scans only those ranges. Block ranges that can't contain the target value are skipped entirely.

The trade-off is precision. B-tree points to exact rows. BRIN points to block ranges that might contain matching rows, then scans those blocks sequentially. This is fine when matching rows are clustered together (time-series data), but terrible when values are scattered randomly across the table.

When BRIN Works (and When It Doesn't)

The key metric is physical correlation -- how closely the column values track with the physical row position on disk.

-- Check correlation for candidate columns
-- Values close to 1.0 or -1.0 = good for BRIN
SELECT
    attname AS column_name,
    correlation,
    n_distinct,
    null_frac
FROM pg_stats
WHERE schemaname = 'public'
  AND tablename = 'events'
  AND attname IN ('created_at', 'event_id', 'user_id')
ORDER BY abs(correlation) DESC;

• Above 0.9: ideal for BRIN. Matching rows are tightly clustered in a few block ranges.
• 0.7 to 0.9: can still benefit, but more false-positive blocks scanned.
• Below 0.7: BRIN will scan too many irrelevant blocks. Use B-tree.

The ideal BRIN candidate:

Characteristic	Why It Matters
Append-only or mostly-append	Physical order matches logical order
Time-series or log data	Timestamp correlates with insertion order
Table size > 1 GB	B-tree overhead becomes significant
Range queries are primary access	BRIN excels at range filtering
Low update/delete frequency	Updates break physical correlation

The failure mode: creating a BRIN index on user_id in a table where inserts come from many users in random order. Every block range contains every user_id, and PostgreSQL must scan the entire table anyway.

Finding BRIN Candidates

Identify large tables with oversized B-tree indexes:

SELECT
    t.schemaname,
    t.relname AS table_name,
    pg_size_pretty(pg_relation_size(t.relid)) AS table_size,
    i.indexrelname AS index_name,
    pg_size_pretty(pg_relation_size(i.indexrelid)) AS index_size,
    round(100.0 * pg_relation_size(i.indexrelid) / NULLIF(pg_relation_size(t.relid), 0), 1)
        AS index_to_table_pct
FROM pg_stat_user_tables t
JOIN pg_stat_user_indexes i ON i.relid = t.relid
WHERE pg_relation_size(t.relid) > 1073741824  -- tables > 1 GB
ORDER BY pg_relation_size(i.indexrelid) DESC
LIMIT 20;

Tables over 1 GB with B-tree indexes consuming 10%+ of the table size are prime candidates -- if the indexed columns have high correlation.

Creating and Tuning BRIN Indexes

Basic BRIN index:

CREATE INDEX CONCURRENTLY idx_events_created_brin
    ON events USING brin (created_at);

Tune pages_per_range

The default of 128 pages summarizes ~1 MB of table data per entry. You can tune this:

-- More granular: larger index, fewer false positives
CREATE INDEX CONCURRENTLY idx_events_created_brin_fine
    ON events USING brin (created_at)
    WITH (pages_per_range = 32);

-- Less granular: tiny index, more false positives
CREATE INDEX CONCURRENTLY idx_events_created_brin_coarse
    ON events USING brin (created_at)
    WITH (pages_per_range = 256);

For most time-series tables, the default of 128 works well.

Enable Autosummarize

By default, new blocks are not reflected in the BRIN index until vacuum runs. This means recent data might trigger sequential scans:

CREATE INDEX CONCURRENTLY idx_events_created_brin
    ON events USING brin (created_at)
    WITH (autosummarize = on);

For append-heavy workloads, autosummarize = on is strongly recommended.

Multi-Column BRIN

BRIN indexes support multiple columns when both correlate with physical order:

CREATE INDEX CONCURRENTLY idx_events_multi_brin
    ON events USING brin (created_at, event_id);

Both created_at and an auto-incrementing event_id increase together, so both have high correlation.

Verify the Improvement

EXPLAIN (ANALYZE, BUFFERS)
SELECT count(*)
FROM events
WHERE created_at BETWEEN '2026-01-01' AND '2026-01-31';

You should see Bitmap Heap Scan with Bitmap Index Scan on idx_events_created_brin. Buffer count should be much lower than a full sequential scan.

Compare index sizes:

SELECT
    indexrelname AS index_name,
    pg_size_pretty(pg_relation_size(indexrelid)) AS index_size
FROM pg_stat_user_indexes
WHERE relname = 'events';

The size difference should be dramatic -- often 100-1000x smaller.

Prevention

Always check pg_stats.correlation before creating a BRIN index. A BRIN on a low-correlation column is worse than useless -- it costs maintenance time and fools you into thinking data is indexed.

Monitor BRIN effectiveness after creation. Compare buffer counts in EXPLAIN ANALYZE between BRIN-indexed queries and sequential scans. If BRIN isn't reducing buffer reads by at least 50%, the correlation is too low.

Watch for operations that break physical correlation: UPDATEs that change the indexed column, CLUSTER on a different column, or bulk DELETEs followed by new inserts. If correlation degrades, consider running CLUSTER to restore physical order.

For time-series tables growing by gigabytes per day, switching from B-tree to BRIN can reduce index storage by 99% -- and your insert performance will thank you.

---

Originally published at mydba.dev/blog/postgres-brin-index