BRIN Indexes for Large Partitioned Tables
Learn when Block Range Indexes outperform B-trees on huge, naturally ordered partitioned tables.
BRIN Indexes for Large Partitioned Tables is a free Advanced PostgreSQL: Indexing, Partitioning, Replication lesson on CoddyKit — lesson 4 of 4. You can read the complete lesson below for free — then practise it hands-on in the browser with a built-in code editor and a 24/7 AI tutor. It is part of the Advanced PostgreSQL: Indexing, Partitioning, Replication learning path, one of 4 lessons in the course, and your progress syncs across the web and the CoddyKit app.
The Scale Problem
On tables with billions of rows, a B-tree index can grow huge and consume a lot of memory. BRIN (Block Range Index) offers a tiny alternative for naturally ordered data.
How BRIN Works
BRIN stores the min and max value per block range instead of one entry per row. A query checks which ranges could contain matching rows and scans only those blocks.
Correlation Is Key
BRIN shines when the column's physical order correlates with its value order, such as an inserted_at timestamp on an append-only table. Random ordering makes BRIN nearly useless.
Creating a BRIN Index
You declare it with USING brin. It is dramatically smaller than a B-tree on the same column.
CREATE INDEX ON measurements USING brin (recorded_at);pages_per_range
The pages_per_range storage parameter controls granularity. Smaller values give more precise (but larger) indexes.
CREATE INDEX ON measurements USING brin (recorded_at)
WITH (pages_per_range = 32);Size Comparison
A B-tree might be many gigabytes on a billion-row table; the equivalent BRIN can be just a few megabytes. The trade-off is BRIN does coarser filtering.
BRIN on Partitions
On a range-partitioned table, create the BRIN on the parent; it propagates to each partition. Within a partition the data is usually well-correlated, so BRIN works great.
CREATE INDEX ON events USING brin (created_at);Keeping BRIN Fresh
New blocks must be summarized for BRIN to cover them. Autovacuum does this, or you can force it.
SELECT brin_summarize_new_values('measurements_recorded_at_idx');Verifying with EXPLAIN
Look for a Bitmap Index Scan on the BRIN index followed by a recheck. The recheck confirms candidate blocks really contain matches.
EXPLAIN SELECT * FROM measurements
WHERE recorded_at > now() - interval '1 day';When Not to Use BRIN
Avoid BRIN for:
- Columns with random physical order
- Point lookups needing exact, fast access (use B-tree)
- Uniqueness enforcement (BRIN cannot)
Combining Strategies
At scale, a common pattern is: range-partition by time, BRIN on the time column for cheap range scans, and a small B-tree on a frequently filtered foreign key.
Quick Check
When is BRIN a strong choice?
Recap
You learned BRIN indexes: tiny block-range summaries ideal for huge, well-correlated partitioned tables. Tune pages_per_range, keep summaries fresh, verify with EXPLAIN, and combine BRIN with selective B-trees.
Frequently asked questions
Is the “BRIN Indexes for Large Partitioned Tables” lesson free?
Yes — the full text of “BRIN Indexes for Large Partitioned Tables” is free to read here on the web, and the Advanced PostgreSQL: Indexing, Partitioning, Replication course includes 4 lessons in total. To practise it interactively (a built-in code editor and a 24/7 AI tutor) and unlock the rest of the Advanced PostgreSQL: Indexing, Partitioning, Replication course, upgrade to CoddyKit PRO.
What will I learn in “BRIN Indexes for Large Partitioned Tables”?
Learn when Block Range Indexes outperform B-trees on huge, naturally ordered partitioned tables. You practise Advanced PostgreSQL: Indexing, Partitioning, Replication with hands-on code you run directly in the browser, and a 24/7 AI tutor answers your questions as you work through the lesson.
Do I need any experience to start Advanced PostgreSQL: Indexing, Partitioning, Replication?
No prior experience is required. Advanced PostgreSQL: Indexing, Partitioning, Replication on CoddyKit is structured for beginners through advanced learners; this is — lesson 4 of 4, so you can start here or from the beginning and move at your own pace.
How long does the “BRIN Indexes for Large Partitioned Tables” lesson take?
Most CoddyKit lessons take about 5–10 minutes. Each one is bite-sized and interactive, so you make steady progress and pick up exactly where you left off across the web and the app.
Can I write and run code in this Advanced PostgreSQL: Indexing, Partitioning, Replication lesson?
Yes. Every Advanced PostgreSQL: Indexing, Partitioning, Replication lesson includes a built-in code editor, so you write and run real code right in your browser and get instant AI feedback — no local setup required.
All lessons in this course
- Scaling Indexes with Partitioning
- Choosing Index/Partition Strategies
- Real-world Case Studies
- BRIN Indexes for Large Partitioned Tables