PostgreSQL Indexing: When BRIN Is a Better Choice Than B-Tree

If you’re indexing huge, append-only tables with naturally ordered data — BRIN can give you massive performance and space benefits compared to B-Tree.

What is a BRIN Index?

BRIN = Block Range Index

A BRIN index doesn't index every row, unlike B-Tree. Instead, it stores summary data (min, max) about physical blocks of rows.

Think of BRIN as a metadata guide: it narrows down where to search, not what to return.

How BRIN Works Internally

When you create a BRIN index on a column, PostgreSQL organizes the index by summarizing values over physical block ranges in the table.

For each block range, the BRIN index stores summary metadata, including:

• Minimum value in the range

• Maximum value in the range

• And other summary data, depending on the BRIN operator class

During Index Creation

• PostgreSQL scans the table in fixed-size block ranges.

• For each range, it stores a tuple in the BRIN index with the min/max values (and possibly other stats).

During Query Execution

1. When a query has a search condition (e.g., WHERE column = 42):

2. PostgreSQL consults the BRIN index to check the min/max values for each range.

3. If the search value falls outside a block range’s min/max → the range is skipped.

4. If it falls inside the range → PostgreSQL performs a heap scan on that range (similar to a sequential scan but limited to that range).

BRIN does not point to individual rows—just ranges. It’s extremely space-efficient, but depends heavily on data correlation (e.g., time-ordered inserts).

BRIN vs. B-Tree: Feature Comparison

Benchmark: BRIN vs. B-Tree with Real Data

Dataset: 100 million records

CREATE TABLE sensor_data (
    id BIGSERIAL PRIMARY KEY,
    device_id INT,
    temperature NUMERIC,
    event_time TIMESTAMP
);

Populate with realistic data:

INSERT INTO sensor_data (device_id, temperature, event_time)
SELECT
  (random()*1000)::int,
  round(random()*50, 2),
  now() - (random() * interval '365 days')
FROM generate_series(1, 100000000);

Create Indexes

-- BRIN
CREATE INDEX brin_sensor_event_time ON sensor_data USING brin (event_time);

-- B-Tree
CREATE INDEX btree_sensor_event_time ON sensor_data USING btree (event_time);

Performance Test

Query:

SELECT * FROM sensor_data
WHERE event_time BETWEEN '2023-01-01' AND '2023-01-10';

BRIN is ~250x smaller, but slower for highly selective queries

When to Use BRIN (Best Use-Cases)

• Append-only or time-series data

• Large tables: 100M+ rows

• Columns with monotonic or naturally ordered data:

  • Timestamps

  • IDs

  • Dates

• Use with partitioned tables for cheap indexing

• Excellent for archival or rarely queried historical data

When Not to Use BRIN

• Highly randomized or non-sequential data

• High update/delete activity (BRIN won’t self-tune)

• You need fast, pinpoint lookups (use B-Tree instead)

Creating & Tuning BRIN

Basic Syntax

CREATE INDEX idx_brin_column
ON your_table USING brin(your_column);

Tuning pages_per_range

-- Lower = finer granularity (but larger index)
CREATE INDEX idx_brin_custom
ON your_table USING brin(your_column)
WITH (pages_per_range = 32);

Default = 128; use lower for sparse data, higher for dense data

Measuring BRIN Effectiveness

Check index size

SELECT pg_size_pretty(pg_relation_size('idx_brin_column'));

View BRIN summary info

SELECT * FROM pg_brin_bloom_summary;
-- Or use pg_visibility module for deeper inspection

REINDEX or VACUUM

If data distribution changed a lot:

REINDEX INDEX idx_brin_column;
VACUUM ANALYZE your_table;

Pairing BRIN with Other Indexes

Yes! Combine strategies:

• BRIN on event_time

• B-Tree on device_id

Allows PostgreSQL to combine filters effectively.

SELECT * FROM sensor_data
WHERE event_time > now() - interval '30 days'
AND device_id = 42;

Use multicolumn BRIN only if both columns are sequentially increasing.

Other BRIN Index Types

Available operator classes:

• brin_minmax (default)

• brin_bloom (Postgres 14+): bitset-based

• brin_inclusion (Postgres 15+): for ranges

• Custom extensions like brin_lov (local dictionary)

Pros and Cons

Pros

• Tiny disk footprint

• Fast to build

• Great for bulk time-based ingestion

• Easy maintenance

Cons

• Slower than B-Tree for pinpoint queries

• Sensitive to data distribution

• No enforcement of uniqueness

• Doesn't help much on UPDATE-heavy workloads

Final Thoughts

BRIN indexes are an absolute gem for large-scale, time-based, or append-only tables where:

• You care about index size and creation time

• Queries are range-based (e.g., logs, sensor data)

• You don’t need millisecond response times for every query

For more details refer the official doc at PostgreSQL: Documentation: 18: 65.5. BRIN Indexes