Summary:

If you’re dealing with huge tables and struggling with B-Tree index bloat or slow bulk inserts, it’s time to look at BRIN (Block Range Indexes).

This article explains:

What BRIN indexes are and how they work internally
How they compare to B-Tree indexes in terms of size, performance, and maintenance
When BRIN is a better choice, especially for:

  • Time-series or append-only data

  • Huge, cold partitions

  • Queries filtering by time or sequential IDs

Key Takeaways:

  • BRIN indexes are tiny and fast to build, ideal for large tables.

  • They’re not a B-Tree replacement, but a powerful companion in the right scenarios.

  • Proper data ordering makes or breaks BRIN performance.

  • Combine BRIN (e.g., on timestamps) with B-Tree (e.g., on IDs) for best results.

Check this post here: https://kiransabne.dev/postgresql-indexing-when-brin-is-a-better-choice-than-b-tree

Production notes (October 2026)

These notes add the diagnostic queries, decision criteria and validation steps the topic needs on a production system.

A large table does not automatically need BRIN

BRIN stores summaries for ranges of heap pages. It is compact when the indexed value follows the table's physical order, as an append-only event timestamp often does. A B-tree stores much more precise lookup information. BRIN may use less space and cost less to maintain, but it can return many candidate pages that PostgreSQL must recheck.

For a time-window query on a large event table, ask how rows arrived. If events were appended roughly in timestamp order, a narrow range may map to a small part of the heap. Backfills, updates or mixed ingestion can weaken that correlation and increase rechecks.

The query shape determines the useful index

  • Large time windows on physically ordered data: BRIN can skip page ranges with a small index while still reading and rechecking rows in matching ranges.
  • Point lookups and narrow tenant filters: a B-tree usually gives a more precise path. The correct composite key may matter more than choosing a smaller index type.
  • ORDER BY occurred_at DESC LIMIT 20: a B-tree can provide ordered access; BRIN does not return rows in index order. Test the endpoint's sort and limit, not only its filter.

These are starting hypotheses. The planner may choose a sequential scan for a broad range, and a BRIN on a poorly correlated column may recheck so many heap pages that its small size is irrelevant.

Compare the two on the actual query

  1. Capture the slow statement, its predicate selectivity and call frequency. Include both common and worst-case time windows.
  2. Check the column's physical correlation in PostgreSQL statistics, then use EXPLAIN (ANALYZE, BUFFERS) on a representative copy or safe read query.
  3. Compare index size, heap blocks visited, lossy bitmap rechecks, execution time and insert cost. Test with both warm and cold cache if I/O is the concern.
  4. Keep a B-tree where point lookups or highly selective ranges need its precision. A BRIN index can complement, rather than replace, it.

Check correlation as a clue, then verify it against the plan. Statistics can be stale after major backfills, and a whole-table correlation value can hide local disorder.

SELECT attname, correlation
FROM pg_stats
WHERE schemaname = 'public'
  AND tablename = 'events'
  AND attname = 'occurred_at';

EXPLAIN (ANALYZE, BUFFERS)
SELECT id FROM events
WHERE occurred_at >= TIMESTAMPTZ '2026-10-01 00:00:00+00'
  AND occurred_at <  TIMESTAMPTZ '2026-10-02 00:00:00+00';

On a production-like copy, compare candidate indexes against the same narrow and broad windows. A BRIN bitmap path exposes lossy heap blocks and rows removed by recheck. Compare shared blocks read or hit, execution time, index size and write overhead, then check whether the choice still holds for recent versus historical data.

The pages_per_range setting trades a smaller index for coarser summaries. Changing it requires a benchmark; a tiny BRIN index is not useful if the query still scans most heap pages. PostgreSQL's BRIN documentation explains the lossy range-summary behavior.

If an index change is warranted on a live system, plan its build and storage cost and validate the affected write workload. A scoped PostgreSQL investigation can rank this against other causes of latency.