Databases & Storage
PostgreSQL Index Benchmarks: B-Tree vs BRIN vs GIN
A deep dive benchmark comparing PostgreSQL B-Tree, BRIN, GIN, and GiST indexes on 100M rows for analytics and search workloads.
Test Environment
CPUAWS r6g.2xlarge (8 vCPU)
RAM64GB DDR4
OSAmazon Linux 2023
Toolpgbench
Metrics Comparison
Index Build Time
Unit: SecondsBRIN
12★
B-Tree
85
GIN
240
Storage Size
Unit: MBBRIN
1.5★
B-Tree
2400
GIN
3800
Detailed Analysis
BRIN shines for massive sequential time-series data, while GIN dominates full-text and array searches.
BRIN works by storing min/max values for block ranges, making it exceptionally tiny and fast for append-only data like logs.
Index Scan
B-Tree traverses down to leaf nodes
Bottleneck: B-Tree index size can exceed memory limits.
Architectural Recommendations
- Use BRIN for time-series logs
- Use GIN for JSONB and arrays
Frequently Asked Questions
When should I use BRIN instead of B-Tree?
When your table is large, naturally ordered by the indexed column, and you query by ranges.