BRIN Indexes for Large Sequential Data
Discover BRIN (Block Range INdexes), a tiny index type ideal for huge tables whose data is naturally ordered, such as time-series and append-only logs.
BRIN Indexes for Large Sequential Data is a free PostgreSQL Performance & Query Optimization 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 PostgreSQL Performance & Query Optimization learning path, one of 4 lessons in the course, and your progress syncs across the web and the CoddyKit app.
What is a BRIN Index?
A BRIN (Block Range INdex) stores summary information about ranges of physical table blocks instead of pointing at individual rows. Each entry covers many pages, so the index is extremely small.
How BRIN Differs from B-tree
A B-tree has one entry per row and can be large. A BRIN keeps just the min and max value for each block range.
- B-tree: precise, big, great for random lookups
- BRIN: approximate, tiny, great for range scans on ordered data
When BRIN Shines
BRIN works best when the column's values correlate with physical storage order. Classic cases:
- Time-series tables ordered by inserted timestamp
- Append-only logs
- Large fact tables loaded in key order
Creating a BRIN Index
Use the USING brin clause. Notice how small and fast it is to build compared to a B-tree on the same column.
CREATE INDEX idx_events_ts_brin
ON events USING brin (created_at);Querying Through a BRIN
A range filter lets the planner skip block ranges whose min/max cannot match, reading only the relevant pages.
EXPLAIN ANALYZE
SELECT * FROM events
WHERE created_at >= '2026-01-01'
AND created_at < '2026-02-01';The pages_per_range Option
You can tune how many pages each summary entry covers. Smaller ranges make the index more precise but larger.
CREATE INDEX idx_events_ts_brin
ON events USING brin (created_at)
WITH (pages_per_range = 32);Why Correlation Matters
If the column is not physically ordered, almost every block range will overlap the search value and BRIN will scan the whole table. Check correlation with the stats view.
SELECT attname, correlation
FROM pg_stats
WHERE tablename = 'events';Summarizing New Data
BRIN entries for freshly inserted blocks may be unsummarized. Run this to summarize them so queries can prune those ranges.
SELECT brin_summarize_new_values('idx_events_ts_brin');Size Comparison
On a billion-row table a B-tree might be tens of gigabytes while a BRIN is a few megabytes. Compare them directly.
SELECT pg_size_pretty(pg_relation_size('idx_events_ts_brin'));Trade-offs to Remember
BRIN is not a free win:
- Useless on randomly ordered columns
- Slower for exact single-row lookups than B-tree
- Needs periodic summarization of new blocks
Pick it when the table is huge and naturally ordered.
Maintaining BRIN over Updates
If existing rows are heavily updated, a block range's min/max may widen and lose precision over time. For volatile data, periodically rebuild the index to restore tight ranges.
REINDEX INDEX idx_events_ts_brin;Quick Check
Test your BRIN knowledge.
Recap
You learned BRIN indexes:
- They store min/max summaries per block range — tiny footprint
- Ideal for large, physically ordered data like time-series
- Created with
USING brin; tunepages_per_range - Check
correlationfirst; summarize new blocks - Avoid them on randomly ordered columns
Frequently asked questions
Is the “BRIN Indexes for Large Sequential Data” lesson free?
Yes — the full text of “BRIN Indexes for Large Sequential Data” is free to read here on the web, and the PostgreSQL Performance & Query Optimization 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 PostgreSQL Performance & Query Optimization course, upgrade to CoddyKit PRO.
What will I learn in “BRIN Indexes for Large Sequential Data”?
Discover BRIN (Block Range INdexes), a tiny index type ideal for huge tables whose data is naturally ordered, such as time-series and append-only logs. You practise PostgreSQL Performance & Query Optimization 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 PostgreSQL Performance & Query Optimization?
No prior experience is required. PostgreSQL Performance & Query Optimization 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 Sequential Data” 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 PostgreSQL Performance & Query Optimization lesson?
Yes. Every PostgreSQL Performance & Query Optimization 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
- Hash, GIN, and GiST Indexes
- Partial and Expression Indexes
- Covering Indexes and Index-Only Scans
- BRIN Indexes for Large Sequential Data