---
title: "PostgreSQL BRIN Indexes for Mobile Telemetry: When 128 Pages Beats a 50GB B-Tree"
published: true
description: "Learn how BRIN indexes cut mobile telemetry index size by 99% vs B-trees. Covers correlation requirements, minmax-multi operator class, and the partition boundary decisions that separate a 10x win from a silent query killer."
tags: postgresql, architecture, mobile, performance
canonical_url: https://mvpfactory.co/blog/postgresql-brin-indexes-mobile-telemetry
---
## What You Will Build
By the end of this workshop, you will know how to replace a 50GB B-tree index on a mobile telemetry `created_at` column with a BRIN index measured in kilobytes — and more importantly, you will know when *not* to. We cover correlation checks, operator class selection, and partition boundary alignment. Get any one of these wrong and BRIN becomes a silent query killer that scans every heap block anyway.
## Prerequisites
- PostgreSQL 14+ (for `minmax-multi` operator class)
- An append-heavy table with a timestamp column — typical mobile telemetry schema
- Basic familiarity with `pg_stats` and `EXPLAIN ANALYZE`
---
## The Real Cost of B-Trees at Telemetry Scale
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 are looking at 50–80 GB of index data for a year of history.
Here is the gotcha that will save you hours: **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) | Write Overhead | 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 |
A BRIN index at default `pages_per_range = 128` is not just smaller — it is orders of magnitude smaller.
---
## Step 1: Check Correlation First — Always
BRIN does not index individual row values. It stores the minimum and maximum value within contiguous groups of heap pages (block ranges). At query time, PostgreSQL skips ranges that cannot contain matching rows.
Physical correlation is not optional — it is the entire mechanism.
sql
SELECT correlation
FROM pg_stats
WHERE tablename = 'device_events'
AND attname = 'created_at';
-- You want this above 0.90, ideally > 0.99
For append-only telemetry inserted in timestamp order, correlation is typically 0.99+. 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.
Do not skip this check.
---
## Step 2: Choose the Right Operator Class
PostgreSQL 14 introduced `minmax_multi_ops`. Let me show you a pattern I use in every project.
sql
-- 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` tracks up to 32 distinct sub-ranges per block range instead of one wide min/max pair. This matters in multi-tenant architectures where device data from different customers lands on the same heap page. Standard minmax records a wide range encompassing everything, causing false positives. minmax-multi cuts those significantly.
Default to `minmax-multi` for any multi-tenant or mixed-workload telemetry schema.
---
## Step 3: Align Partition Boundaries With Query Patterns
The docs do not emphasize this enough, but partition granularity directly determines BRIN's selectivity ceiling.
With `pages_per_range = 128` and 8 KB pages, each range covers 1 MB of heap. A 50 GB monthly partition has ~50,000 ranges. A one-day query eliminates 97% of ranges — but still reads ~1.7 GB of heap.
plaintext
Monthly partition: 50 GB → ~50,000 ranges → day query reads ~1.7 GB
Daily partition: ~1.7 GB → planner eliminates via pruning, BRIN handles remainder
Partition at your dominant query granularity. 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.
---
## Gotchas
**Correlation below 0.85**: You will likely see worse performance than a partial B-tree. Measure before you migrate.
**Assuming BRIN is always smaller wins**: BRIN with poor correlation triggers near-full heap scans — the index exists but provides almost no filtering.
**Using standard minmax on interleaved tenant data**: The min/max range becomes too wide, producing excessive false positives. Use `minmax-multi`.
**Misaligned partition boundaries**: Monthly partitions with daily query patterns make BRIN work much harder than necessary. Daily partitions shift the heavy lifting to the planner's partition pruning, which is more efficient.
---
## Conclusion
BRIN is a genuine 10x win for append-heavy, physically ordered telemetry — 128 KB versus 50 GB is not marketing, it is arithmetic. But it earns that win only when correlation is high, the operator class matches your data distribution, and partitions align with your query granularity.
Check `pg_stats.correlation`, default to `minmax-multi` for multi-tenant schemas, and design your partition strategy alongside your index strategy — not as an afterthought.
**Further reading:** [PostgreSQL BRIN documentation](https://www.postgresql.org/docs/current/brin.html) — pay particular attention to the operator class reference and the `pages_per_range` storage parameter.
Top comments (0)