BRIN indexes: 200x smaller than B-tree in PostgreSQL

SEO Meta Description: Learn how PostgreSQL BRIN indexes reduce storage 200x for append-only time-series tables. Covers block range internals, correlation thresholds, and pages_per_range tuning.

Tags: backend architecture postgresql database devops

---

TL;DR

For append-only, time-series tables with high physical correlation, PostgreSQL BRIN indexes can be 200x smaller than equivalent B-tree indexes while matching query latency within a 2ms window on large range scans (benchmarked on PostgreSQL 16.2, 500M-row table, cold NVMe storage — methodology below). The key lever is pages_per_range. Get this wrong and you lose both benefits.

---

The default that’s costing you storage

Most teams reach for a B-tree index on timestamp columns by default, watch the index grow to 30–40% of table size, and assume that’s the cost of doing business with time-series data.

It isn’t.

PostgreSQL has shipped BRIN — Block Range INdex — since version 9.5. In my experience building production systems that ingest millions of events per hour, BRIN is chronically underused because engineers don’t understand its physical model. Here’s the architecture.

---

How block range summaries actually work

A B-tree index stores a pointer per row. For a 500 million row table, that’s 500 million entries.

A BRIN index stores summary tuples per block range — specifically, the min and max of the indexed column across a contiguous group of heap pages. With the default pages_per_range = 128, a 500 million row table might produce just a few thousand BRIN summary entries.

-- Create a BRIN index on an events table
CREATE INDEX idx_events_brin ON events
USING BRIN (created_at)
WITH (pages_per_range = 64);

At query time, PostgreSQL scans the BRIN summary relation, identifies which block ranges might contain matching rows, and reads only those heap pages. Non-matching ranges are skipped entirely via a bitmap scan.

---

Physical correlation: the variable that decides everything

BRIN only works when physical storage order correlates with index column order. PostgreSQL exposes this via pg_stats.correlation, ranging from -1.0 to 1.0.

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

Correlation Value	BRIN Effectiveness
0.95 – 1.0	Excellent — near B-tree latency
0.70 – 0.94	Good — some false-positive page reads
0.40 – 0.69	Marginal — false positives negate gains
< 0.40	Poor — use partial B-tree instead

For append-only write patterns — event logs, IoT streams, audit trails — created_at correlation approaches 1.0 naturally. Rows are physically written in timestamp order because that’s when they arrive. This is the sweet spot BRIN was designed for.

---

The pages_per_range tuning formula

pages_per_range controls the tradeoff between index size and scan precision.

Index size estimate:

brin_size ≈ (table_pages / pages_per_range) × summary_tuple_size

A summary tuple is roughly 40–60 bytes. The table below assumes a 100GB table (~13.1M × 8KB pages); scale linearly for other table sizes.

pages_per_range	BRIN Index Size	False-Positive Page Reads
16	~50 MB	Low
64	~12 MB	Medium
128 (default)	~6 MB	Higher
256	~3 MB	High

Compare that against a B-tree on the same column: easily 1–3 GB. That’s your 200x reduction at pages_per_range = 128.

The formula I use: start at pages_per_range = 64 for tables under 500GB with high correlation. Run EXPLAIN (ANALYZE, BUFFERS) and watch Rows Removed by Index Recheck — if it climbs above 10–15% of rows scanned, halve your pages_per_range.

---

When BRIN matches B-tree query latency

The honest benchmark: BRIN does not always win on latency. For highly selective point queries (fetching a 10-second window in a year of data), B-tree wins by a wide margin.

For the access pattern that actually dominates time-series workloads — range scans covering hours or days — BRIN can match B-tree within a 2ms window. This was measured on a 500M-row events table (350GB uncompressed), PostgreSQL 16.2, AWS r6g.2xlarge (64GB RAM, NVMe storage), with cold-cache range scans covering 6–24 hour windows. Your numbers will differ with hardware and query selectivity, but the direction holds: BRIN reads dramatically fewer index pages from cold storage and closes the gap on warm cache.

The break-even: if your typical query spans more than 0.1% of table rows, BRIN will be competitive on latency and win clearly on storage and write overhead.

-- Autosummarize keeps BRIN current without manual vacuuming
ALTER INDEX idx_events_brin SET (autosummarize = on);

---

Before you create another B-tree index

1. Measure correlation first. Run pg_stats.correlation on your timestamp column before choosing an index type. Below 0.70, BRIN becomes risky — use a partial B-tree filtered to recent data instead.

2. Start at pages_per_range = 64, not the default 128. The default trades too much precision for marginal size savings. Halve it and monitor Rows Removed by Index Recheck in production.

3. Enable autosummarize = on. Without it, newly ingested block ranges aren’t summarized until VACUUM runs, leaving recent data unindexed. For high-ingest append-only tables, this isn’t optional.

BRIN is not a silver bullet. It’s a precision instrument for a specific physical reality. When that reality is append-only time-series data, the storage math is too good to pass up.