PostgreSQL BRIN indexes: 128-page vs 50GB B-tree
Meta description: Learn how BRIN indexes cut mobile telemetry index size by 99% vs B-trees. Covers correlation, minmax-multi, operator class selection, and partition boundary pitfalls.
Tags: backend architecture mobile microservices productengineering
TL;DR
Block Range INdexes (BRIN) can reduce your telemetry index from gigabytes to kilobytes — but only if your data has high physical correlation. Get the correlation wrong, partition boundaries wrong, or operator class wrong, and BRIN becomes a silent query killer that scans every heap block anyway. This post walks through the math, the tradeoffs, and the production decisions that separate a 10x win from a 10x regression.
The problem: B-trees don’t scale for append-heavy telemetry
Mobile telemetry tables are among the most append-heavy workloads in production. A fleet of 500,000 active devices emitting events every 30 seconds generates roughly 1 million rows per minute. Index that created_at column with a standard B-tree and you’re looking at 50–80 GB of index data for a year of history — data that must be maintained on every write, vacuumed, and kept in shared buffers.
What most teams get wrong: the index size is not the real cost. The real cost is write amplification. Every INSERT touches the B-tree leaf page, potentially triggers a page split, and forces WAL entries for the index itself. At telemetry scale, that overhead compounds fast.
| Index Type | Size (1B rows, timestamptz) | Write Overhead | Seq Scan Avoidance |
|---|---|---|---|
| B-tree | ~45–55 GB | High (page splits, WAL) | Excellent |
| BRIN (128 pages/range) | ~128 KB | Minimal | Good (high correlation) |
| BRIN (1 page/range) | ~3–5 MB | Minimal | Excellent (low gaps) |
| No index | 0 | None | None |
A BRIN index at default pages_per_range = 128 is not just smaller — it is orders of magnitude smaller.
How BRIN actually works
BRIN does not index individual row values. It indexes block ranges — contiguous groups of heap pages — storing the minimum and maximum value of the indexed column within that range. At query time, PostgreSQL checks which ranges cannot contain matching rows and skips those heap blocks entirely.
Physical correlation is not optional — it is the entire mechanism.
-- Check correlation before creating a BRIN index
SELECT correlation
FROM pg_stats
WHERE tablename = 'device_events'
AND attname = 'created_at';
-- You want this above 0.90, ideally > 0.99For append-only telemetry tables where rows are inserted in timestamp order, correlation is typically 0.99+. Perfect. For tables with bulk backfills, out-of-order device clocks, or partition merges, correlation can collapse to 0.3 — at which point BRIN degrades to a near-full heap scan.
Operator class selection: minmax vs minmax-multi
PostgreSQL 14 introduced minmax_multi_ops, and it matters specifically for sparse or multi-tenant telemetry schemas.
-- Standard minmax (default)
CREATE INDEX idx_events_brin ON device_events
USING brin (created_at timestamptz_minmax_ops)
WITH (pages_per_range = 64);
-- minmax-multi: tracks multiple min/max pairs per range
CREATE INDEX idx_events_brin_multi ON device_events
USING brin (created_at timestamptz_minmax_multi_ops)
WITH (pages_per_range = 64);minmax-multi is valuable when a single block range contains data from multiple non-contiguous time windows — common in multi-tenant architectures where device data from different customers lands in the same heap page. Standard minmax records a wide range that encompasses everything, causing false positives. minmax-multi tracks up to 32 distinct sub-ranges per block range, cutting those false positives significantly.
Use minmax-multi when tenant data interleaves within pages, or when you have periodic bulk inserts of older data alongside live inserts.
Partition boundaries: the decision that makes or breaks BRIN
Partition granularity directly determines BRIN’s selectivity ceiling. Most teams skip this analysis and pay for it later.
If you partition by month and query for a single day, BRIN must scan the entire month’s partition minus the excluded ranges. With pages_per_range = 128 and 8 KB pages, each range covers 1 MB of heap. A 30-day partition might have 50 GB of data — that is 50,000 ranges. Even with perfect correlation, a one-day query eliminates roughly 29/30 of ranges, but still reads ~1.7 GB.
Partition size: 50 GB (monthly)
pages_per_range: 128 pages × 8 KB = 1 MB per range
Ranges total: ~50,000
Day query coverage: ~3.3% of data → 97% range exclusion
Heap blocks read: ~1.7 GB (still significant)
Partition size: ~1.7 GB (daily)
Ranges total: ~1,700
Day query coverage: 100% → full partition pruning by planner
Heap blocks read: ~1.7 GB (same — but now via partition elimination, zero BRIN needed)Partition at the query granularity boundary. Daily partitions for daily queries, weekly for weekly analytics. BRIN then handles sub-partition range filtering.
Before you ship
Check pg_stats.correlation on your timestamp column before creating a BRIN index. Below 0.85, you will likely see worse performance than a partial B-tree. BRIN is not a universal win — it is a win for physically ordered data.
For multi-tenant or mixed-workload telemetry, default to minmax-multi. The overhead is negligible (slightly larger index, marginally slower build) and the reduction in false-positive range matches is meaningful when data from different sources interleaves within the same heap.
Align partition boundaries with your dominant query pattern. BRIN and partition pruning are complementary, not redundant. Partitions eliminate entire tables from the planner’s consideration; BRIN handles intra-partition range filtering. Design them together.