Intermediate LevelPostgreSQL
What are Block Range Indexes (BRIN) and why are they ideal for multi-gigabyte time-series tables?
❓ Interview Prompt: Explain BRIN (Block Range Index) architecture, page ranges (`pages_per_range`), min/max summary values, 99% smaller index size, and time-series performance.
BRIN (Block Range Index) is designed for multi-gigabyte tables where data is naturally ordered on disk (e.g. append-only time-series or auto-incrementing IDs):
How BRIN Works:
Divides physical table pages into ranges (default 128 pages = 1MB) and stores ONLY the minimum and maximum value for each range!
•
Tiny Index Size: BRIN indexes take 99% less disk space than B-Trees (e.g. 100KB BRIN vs 2GB B-Tree) and fit entirely in RAM!
Code Implementation Example
sql
-- Create BRIN index on append-only created_at time-series column
CREATE INDEX idx_logs_created_at_brin
ON audit_logs
USING brin (created_at)
WITH (pages_per_range = 64);💡Creating a compact BRIN index on time-series table columns.
🎯 Interview Key Takeaway
Use BRIN indexes on massive append-only time-series tables to save gigabytes of index RAM.
#BRIN#Block Range Index#Time-Series#Index Size#Intermediate
Back to all PostgreSQL Questions→