PostgreSQL BRIN Index Explained: A Complete Guide

Indexes are the backbone of query performance in PostgreSQL, but not all indexes are created equal. For tables with billions of rows, a traditional B-tree index can become a storage burden, consuming gigabytes of disk space and slowing down writes. PostgreSQL 9.5 introduced a fundamentally different approach: the BRIN index.

A postgresql brin index trades granularity for efficiency. Instead of tracking the location of every row, it summarizes ranges of blocks, recording only the minimum and maximum values found within each range. This design makes BRIN uniquely suited to large table optimization scenarios where storage efficiency matters more than pinpoint accuracy.

B-tree stores a pointer for every row; BRIN stores only min/max summaries per block range, yielding massive space savings on correlated data.

Block Range Structure and Summarization

BRIN stands for Block Range INdex. At its core, a BRIN index divides the table's heap into contiguous ranges of pages—by default, 128 pages per range. For each range, PostgreSQL stores a single summarization tuple containing metadata about the values inside that range.

The most common operator class, minmax, stores exactly two values per column per range: the smallest value and the largest value. When a query asks for rows within a specific range, PostgreSQL checks the BRIN summary. If the target range falls outside the min/max bounds of a block range, that entire range is excluded from the scan. If it overlaps, PostgreSQL reads the blocks and applies the query predicate directly to the heap tuples.

How BRIN Stores Min/Max Values per Range

Unlike a B-tree, which maintains a sorted tree of individual row pointers, a postgresql block range index is essentially a flat array of summaries. The index entry for a range is updated only when new data is inserted into that range or when VACUUM processes it. This append-friendly behavior is why BRIN shines on append-only workloads.

The summarization is stored in a separate relation (the BRIN index itself), and because each range covers many pages, the total index size remains tiny even for terabyte-scale tables.

The Role of Correlation in BRIN Effectiveness

BRIN effectiveness depends heavily on correlation—how closely the logical order of your data matches its physical storage order on disk. When you insert time-series data with a monotonically increasing timestamp, the correlation coefficient approaches 1.0. In this case, each block range contains a tight cluster of values, and the min/max summary is highly selective.

If your data is randomly distributed (for example, UUID primary keys), the min and max values in each block range will span nearly the entire value domain. The query planner will find the summaries unhelpful, and a BRIN index will offer little to no benefit. This is the most important factor in the brin index vs btree decision.

Use this decision tree to determine whether your workload and data patterns favor BRIN over B-tree.

Ideal Workloads: Time-Series and Append-Only Data

The canonical use case for a postgresql brin index is time-series data. Event logs, sensor readings, audit trails, and metrics tables typically receive inserts with ever-increasing timestamps. Because new rows are appended to the end of the table, physical storage order remains highly correlated with the timestamp column.

Range scans on these tables—queries like "find all events between Monday and Friday"—are exactly the access pattern BRIN is designed to accelerate. The index allows the query planner to skip entire block ranges that fall outside the date range, reducing I/O dramatically.

Correlation Requirements and Data Ordering

Before creating a BRIN index, check your data's correlation using pg_stats:

SELECT attname, correlation
FROM pg_stats
WHERE tablename = 'events' AND attname = 'created_at';

A correlation value above 0.9 indicates that BRIN will likely be effective. Values below 0.5 suggest the data is too scattered on disk for BRIN to provide meaningful range exclusion.

You can improve correlation by clustering the table on the target column, though this requires a maintenance window and an exclusive lock:

CLUSTER events USING idx_events_created_at;

When B-Tree Is Still the Better Choice

B-tree indexes remain the right tool for many jobs. Use B-tree when:

• You need exact-match lookups (WHERE id = 42).
• Your data has low correlation (random inserts, UUIDs).
• You require uniqueness constraints.
• Query latency for individual rows is critical.

BRIN is not a replacement for B-tree; it is a complement for specific read-heavy workloads on massive tables.

Basic CREATE INDEX Syntax

Creating a BRIN index is straightforward:

CREATE INDEX idx_events_created_at_brin
ON events USING BRIN (created_at);

This creates a default BRIN index with pages_per_range set to 128. The index will immediately begin summarizing existing data and will update summaries as new rows are inserted.

Choosing pages_per_range

The pages_per_range parameter controls how many heap pages each summary covers. The default is 128, but you can tune it:

CREATE INDEX idx_events_created_at_brin
ON events USING BRIN (created_at)
WITH (pages_per_range = 32);

A smaller value increases index granularity, potentially improving selectivity at the cost of a larger index. A larger value shrinks the index further but may reduce effectiveness if correlation is imperfect. There is no universal ideal; the right value depends on your table size, correlation, and query patterns. Start with the default and adjust based on execution plan analysis.

Available Operator Classes

PostgreSQL ships with several BRIN operator classes:

For most users, minmax is the correct starting point. The inclusion opclass is valuable when indexing native PostgreSQL range types.

Query Planner Behavior with BRIN

When a query uses a BRIN index, PostgreSQL performs a bitmap index scan. The index returns a bitmap of pages that might contain matching rows, and the executor reads those pages sequentially. This is less precise than a B-tree index scan but far more I/O-efficient on large range queries.

You can verify BRIN usage with EXPLAIN (ANALYZE, BUFFERS):

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

Look for Bitmap Index Scan on idx_events_created_at_brin in the output.

Impact of VACUUM and Table Bloat

BRIN indexes rely on accurate summaries. When rows are updated or deleted, the dead tuples remain in the heap until VACUUM reclaims them. If a block range contains many dead tuples, the min/max summary may become less precise, though BRIN is generally more resilient to table bloat than B-tree indexes.

Running VACUUM ANALYZE periodically ensures statistics stay current and summaries remain accurate. For append-only tables, a simple ANALYZE after bulk loads is often sufficient.

Monitoring BRIN Index Efficiency

To assess whether your BRIN index is pulling its weight, compare the index size to the table size:

SELECT
  relname AS index_name,
  pg_size_pretty(pg_relation_size(indexrelid)) AS index_size
FROM pg_stat_user_indexes
WHERE relname LIKE '%brin%';

A well-functioning postgresql brin index should be a tiny fraction of the table size—often kilobytes versus gigabytes. If query performance is poor, verify that the planner is using the index and that your data correlation remains high.