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

I recently replaced a 14 GB B-tree index with a 90 KB BRIN index on a time-series table. Same query, same results, 99.9% less disk usage. Insert performance improved by 15% because the BRIN index barely needs updating on each write. The catch? BRIN only works this well when your data has a specific physical property -- and understanding that property is the difference between a 1000x improvement and a useless index.

The Physical Correlation Requirement

BRIN stores min/max value summaries for ranges of consecutive physical table blocks. The default is 128 pages (~1 MB) per range entry. To find rows matching a condition, PostgreSQL checks which block ranges could contain matching values and scans only those blocks, skipping everything else.

This only works when the indexed column's values correlate with the physical row position on disk. Time-series data is the textbook example -- new rows have higher timestamps, they're appended to the end of the table, and all timestamps for a given day are physically clustered together. BRIN can skip 95%+ of the table on a date-range query.

The critical check:

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;

• Correlation > 0.9: excellent for BRIN
• 0.7 - 0.9: marginal benefit, test with EXPLAIN
• Below 0.7: do not use BRIN -- every block range overlaps, and PostgreSQL scans the whole table anyway

The failure mode is instructive: a BRIN index on user_id in a table where many users insert in random order. Every block range contains every user_id. BRIN scans everything. Worse than no index because you pay the index lookup overhead for nothing.

Spotting BRIN Opportunities

Find oversized B-tree indexes on large tables:

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
ORDER BY pg_relation_size(i.indexrelid) DESC
LIMIT 20;

Tables > 1 GB with B-tree indexes at 10%+ of table size are candidates, provided the indexed column has high physical correlation.

The ideal BRIN table:

• Append-only (inserts only, no updates to the indexed column)
• Time-series, log, or event data
• Larger than 1 GB
• Queried primarily with range conditions (BETWEEN, >=, <=)
• Low update/delete frequency

Creating and Configuring BRIN

Basic creation:

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

Autosummarize (Important)

New blocks aren't reflected in the BRIN index until vacuum runs. Without autosummarize, recent data may trigger sequential scans:

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

For append-heavy workloads, always enable this.

pages_per_range Tuning

The default of 128 pages (~1 MB per entry) works for most cases. Trade-offs:

-- Finer granularity: larger index, fewer false positives
CREATE INDEX CONCURRENTLY idx_events_brin_fine
    ON events USING brin (created_at)
    WITH (pages_per_range = 32);

-- Coarser granularity: tiny index, more blocks scanned per query
CREATE INDEX CONCURRENTLY idx_events_brin_coarse
    ON events USING brin (created_at)
    WITH (pages_per_range = 256);

Multi-Column BRIN

When multiple columns correlate with physical order (e.g., timestamp and auto-incrementing ID):

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

Each column is summarized independently within each block range.

Verifying It Works

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

Look for Bitmap Heap Scan with Bitmap Index Scan on idx_events_created_brin. Buffer count should be dramatically lower than a sequential scan.

Compare sizes:

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

Expect 100-1000x smaller than the equivalent B-tree.

Keeping BRIN Effective

Monitor correlation over time. Operations that break physical ordering degrade BRIN effectiveness:

• UPDATEs that change the indexed column
• CLUSTER on a different column
• Bulk DELETEs followed by inserts (new rows fill gaps)

If correlation drops below 0.7, the BRIN index becomes ineffective. Run CLUSTER on the indexed column to restore physical order -- but note that CLUSTER takes an exclusive lock.

After creation, verify with EXPLAIN ANALYZE that the BRIN index reduces buffer reads by at least 50%. If it doesn't, correlation is too low and a B-tree would serve better.

For time-series tables growing by gigabytes per day, BRIN is often the only practical indexing strategy. A B-tree that grows proportionally with the data will eventually consume unmanageable resources. BRIN stays tiny regardless of table size.

---

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