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

2026-04-20

BRIN (Block Range Index) indexes in PostgreSQL store summary information for ranges of physical table blocks rather than indexing individual rows. They are 100-1000x smaller than equivalent B-tree indexes, making them ideal for large, physically ordered tables like time-series data, log tables, and append-only workloads where the indexed column correlates strongly with the row's physical position on disk.

The Problem

Large tables create large indexes. A B-tree index on a created_at column in a 500-million-row events table can easily consume 10-15 GB of disk. The index must store a pointer to every single row, sorted by the indexed value. For tables that grow by millions of rows per day, this index grows proportionally, consuming memory in shared_buffers, increasing backup sizes, and slowing down INSERT operations.

The irony is that for many of these tables, a full B-tree index is wildly overbuilt. Time-series data, log tables, and event streams are naturally append-only — new rows have higher timestamps than old rows, and the physical order on disk closely matches the logical order of the indexed column. When there is a strong correlation between the column value and the physical row position, you do not need to index every row individually. You can summarize entire blocks of rows and still efficiently locate the relevant data.

This is exactly what BRIN does. Instead of indexing each row, a BRIN index stores the minimum and maximum values for each range of consecutive physical blocks (default: 128 pages, about 1 MB). To find rows where created_at = '2026-01-15', PostgreSQL reads the BRIN index, identifies which block ranges could contain that date, and scans only those ranges. Block ranges that cannot contain the target value are skipped entirely.

The trade-off is precision. A B-tree points directly to the exact rows. A BRIN points to block ranges that might contain matching rows — PostgreSQL must then scan those blocks sequentially to find the actual matches. This is acceptable when the correlation is high (most matching rows are clustered together in a few ranges) but performs poorly when the indexed column has no physical ordering (random values scattered across the table).

The failure mode is a developer creating a BRIN index on a column with low physical correlation — like a user_id column on a table where inserts come from many users in random order. The BRIN index ends up with overlapping ranges (every block range contains every user_id), and PostgreSQL must scan nearly the entire table anyway, wasting the index lookup time on top of the sequential scan.

How to Detect It

Before creating a BRIN index, check the physical correlation between the column values and row positions:

-- Check correlation for candidate columns
-- Values close to 1.0 or -1.0 indicate strong physical ordering
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;

A correlation above 0.9 (or below -0.9) is ideal for BRIN. Values between 0.7 and 0.9 can still benefit. Below 0.7, a BRIN index will scan too many irrelevant blocks and a B-tree is likely better.

Identify large tables where BRIN indexes would save significant space:

-- Find large tables with B-tree indexes that might be replaced by BRIN
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  -- tables > 1 GB
ORDER BY pg_relation_size(i.indexrelid) DESC
LIMIT 20;

Tables over 1 GB with B-tree indexes consuming 10%+ of the table size are prime candidates for BRIN replacement, if the indexed columns have high physical correlation.

Compare the actual scan behavior of existing indexes:

-- Check how many blocks a range query actually needs to read
EXPLAIN (ANALYZE, BUFFERS)
SELECT count(*)
FROM events
WHERE created_at BETWEEN '2026-01-01' AND '2026-01-31';

If the EXPLAIN output shows a sequential scan or a B-tree index scan reading thousands of buffers for a range query, a BRIN index may be able to eliminate most of that I/O by skipping non-matching block ranges.

How myDBA.dev Shows It

myDBA.dev's Index Advisor and Storage page together identify BRIN index opportunities. The Index Advisor flags large B-tree indexes on append-only tables, where the index size is disproportionate to the query benefit. When the underlying data has high physical correlation, the recommendation suggests replacing the B-tree with a BRIN index, including the expected size reduction.

Index Advisor showing BRIN index recommendations for large time-series tables

The Storage page provides the broader context — showing table and index sizes across the database. Large tables with proportionally large indexes stand out visually, and you can drill down to see which indexes consume the most space. For time-series tables growing by gigabytes per day, switching from B-tree to BRIN can reduce index storage by 99%.

Storage page showing large table and index sizes with growth trends

How to Fix It

Create a BRIN index to replace an oversized B-tree on a physically correlated column:

-- Create a BRIN index on the timestamp column
CREATE INDEX CONCURRENTLY idx_events_created_brin
    ON events USING brin (created_at);

-- 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';

The default pages_per_range is 128 (each BRIN entry summarizes 128 consecutive 8KB pages, or about 1 MB of table data). This is a good starting point, but you can tune it:

-- Smaller pages_per_range = more granular = larger index but fewer false positives
CREATE INDEX CONCURRENTLY idx_events_created_brin_fine
    ON events USING brin (created_at)
    WITH (pages_per_range = 32);

-- Larger pages_per_range = less granular = smaller index but more false positives
CREATE INDEX CONCURRENTLY idx_events_created_brin_coarse
    ON events USING brin (created_at)
    WITH (pages_per_range = 256);

The trade-off: smaller pages_per_range means the index is more precise (fewer unnecessary blocks scanned) but larger. Larger pages_per_range means a tiny index but more blocks scanned. For most time-series tables, the default of 128 works well.

Enable Autosummarize

BRIN indexes need to be summarized — when new blocks are written, the BRIN index must be updated to include them. By default, this happens during vacuum. Enable autosummarize to keep the index current between vacuum runs:

-- Enable autosummarize so new data is indexed promptly
CREATE INDEX CONCURRENTLY idx_events_created_brin
    ON events USING brin (created_at)
    WITH (autosummarize = on);

Without autosummarize, newly inserted rows are not reflected in the BRIN index until the next vacuum. This means recent data may trigger sequential scans even though the index exists. For append-heavy workloads, autosummarize = on is strongly recommended.

Multi-Column BRIN Indexes

BRIN indexes support multiple columns, which is useful for tables with several correlated columns:

-- Multi-column BRIN: both columns must have high physical correlation
CREATE INDEX CONCURRENTLY idx_events_multi_brin
    ON events USING brin (created_at, event_id);

Each column is summarized independently within each block range. This is efficient when both columns correlate with physical order (e.g., both created_at and an auto-incrementing event_id increase together).

Verify the Improvement

After creating the BRIN index, drop the old B-tree (if it was only used for range queries on the same column) and verify:

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

You should see Bitmap Heap Scan with Bitmap Index Scan on idx_events_created_brin. The buffer count will be much lower than a full sequential scan — only the matching block ranges are read.

How to Prevent It

Build BRIN awareness into your index strategy for large tables. Any table that meets these criteria is a BRIN candidate:

| Characteristic | Why It Matters | |---------------|---------------| | Append-only or mostly-append | Physical order matches logical order | | Time-series or log data | Timestamp correlates with insertion order | | Table size > 1 GB | B-tree overhead becomes significant | | Range queries are primary access pattern | BRIN excels at range filtering | | Low update/delete frequency | Updates break physical correlation |

Always check pg_stats.correlation before creating a BRIN index. A BRIN index on a column with 0.3 correlation is worse than useless — it costs maintenance time and fools you into thinking the data is indexed when the planner may ignore it entirely.

Monitor BRIN index effectiveness after creation. Compare the buffer counts in EXPLAIN ANALYZE between the BRIN-indexed query and a sequential scan. If the BRIN index is not reducing buffer reads by at least 50%, the physical correlation is too low and a B-tree would serve better.

Watch for operations that break physical correlation. UPDATE statements that change the indexed column, CLUSTER operations on a different column, or bulk DELETE operations followed by new inserts can all degrade the physical ordering over time. If you rely on BRIN indexes, periodically re-check correlation and consider running CLUSTER to restore physical order on critical tables.

FAQ

When should I use BRIN indexes?

Use BRIN indexes on large (> 1 GB) tables where the indexed column has strong physical correlation (> 0.9 in pg_stats). Ideal candidates are time-series tables, log tables, and append-only event streams where the indexed column (typically a timestamp or auto-incrementing ID) naturally increases with insertion order. BRIN indexes are 100-1000x smaller than B-tree indexes on the same column.

What is the difference between B-tree and BRIN indexes?

B-tree indexes store a pointer to every individual row, sorted by the indexed value. BRIN indexes store minimum/maximum value summaries for ranges of consecutive physical blocks. B-tree indexes are larger but point directly to exact rows. BRIN indexes are tiny but point to block ranges that may contain matching rows, requiring a follow-up scan of those blocks. B-tree works on any data distribution; BRIN requires physical correlation.

What are the different index types in PostgreSQL?

PostgreSQL supports seven index types: B-tree (default, for equality and range), Hash (equality only), GiST (geometric, full-text, nearest-neighbor), GIN (arrays, JSONB, full-text), BRIN (block range summaries for physically ordered data), SP-GiST (partitioned search spaces like quad-trees), and the upcoming Bloom index type (for multi-column equality with lossy filtering). Each type is optimized for different data distributions and query patterns.

Why doesn't PostgreSQL use my BRIN index?

The planner skips a BRIN index when the expected number of matching block ranges is too high relative to a sequential scan. This happens when physical correlation is low (the indexed values are scattered randomly across blocks), when the query matches a large fraction of the table (a range covering 80% of rows is faster as a sequential scan), or when the BRIN index is not yet summarized for recently inserted blocks. Check pg_stats.correlation and ensure autosummarize is enabled.