When to Use BRIN Index in PostgreSQL: Practical Guide

Use a BRIN index in PostgreSQL when you have very large tables where the data is naturally ordered or correlated with the physical storage, such as timestamps or sequential IDs. BRIN indexes are efficient for queries scanning large ranges but use much less space and maintenance than traditional indexes.

How It Works

A BRIN index (Block Range INdex) works by summarizing information about ranges of table blocks instead of indexing every row. Imagine a huge book where instead of indexing every word, you just note the first and last word on each page. When you search for a word, you quickly skip pages that cannot contain it.

This makes BRIN indexes very small and fast to update because they store only summary data per block range. They work best when the data is physically sorted or correlated, like timestamps in a log file, so the summaries accurately narrow down where to look.

Example

This example creates a large table with a timestamp column and adds a BRIN index to speed up queries filtering by time ranges.

sql

CREATE TABLE logs (
  id SERIAL PRIMARY KEY,
  event_time TIMESTAMPTZ NOT NULL,
  message TEXT
);

-- Insert sample data with increasing timestamps
INSERT INTO logs (event_time, message)
SELECT NOW() - (INTERVAL '1 second' * generate_series(1, 1000000)), 'event ' || generate_series(1, 1000000);

-- Create a BRIN index on the event_time column
CREATE INDEX logs_event_time_brin_idx ON logs USING BRIN (event_time);

-- Query using the BRIN index
EXPLAIN ANALYZE
SELECT * FROM logs WHERE event_time > NOW() - INTERVAL '1 hour';

Output

Bitmap Heap Scan on logs  (cost=4.29..12345.67 rows=3600 width=40) (actual time=0.123..12.345 rows=3600 loops=1)
  Recheck Cond: (event_time > (now() - '01:00:00'::interval))
  ->  Bitmap Index Scan on logs_event_time_brin_idx  (cost=0.00..4.29 rows=3600 width=0) (actual time=0.100..0.100 rows=3600 loops=1)
Planning Time: 0.123 ms
Execution Time: 12.456 ms

When to Use

Use BRIN indexes when your table is very large and the column you want to index has values that are naturally sorted or clustered, like timestamps, serial IDs, or geographic coordinates. They are ideal when queries filter on ranges of these values.

BRIN indexes save disk space and reduce maintenance overhead compared to traditional B-tree indexes, but they are less precise. They work best when you can tolerate scanning some extra rows after the index narrows down the search.

Real-world use cases include:

• Logging or event tables with time-based data
• Partitioned tables where data is inserted in order
• Large sensor data or telemetry datasets

Key Points

• BRIN indexes summarize data per block range, not per row.
• They are very space-efficient and fast to maintain.
• Best for large tables with naturally ordered data.
• Good for range queries but less precise than B-tree indexes.
• Not suitable for columns with random or highly scattered values.

Key Takeaways

Use BRIN indexes for very large tables with naturally ordered or clustered data.

BRIN indexes are space-efficient and fast to update compared to B-tree indexes.

They work best for range queries on columns like timestamps or sequential IDs.

BRIN indexes may scan extra rows, so they are less precise but more efficient for big data.

Avoid BRIN indexes on columns with random or uncorrelated values.