The Memory Exhaustion Hazard of High-Velocity B-Trees
Modern data architectures routinely ingest millions of append-only records per day: financial audit logs, API telemetry events, IoT sensor readings, and clickstream tracking. When operating on tables exceeding hundreds of millions of rows, developers instinctively attach standard B-Tree indexes to primary timestamp and sequential ID columns.
However, B-Trees maintain a dedicated leaf entry for every single row in the table. On a 500-million-row telemetry table, a single B-Tree index on a created_at timestamp consumes 12 to 18 Gigabytes of disk and RAM. When queries execute, PostgreSQL must load these massive B-Tree pages into shared_buffers, evicting frequently accessed relational data and degrading overall database throughput. This is where Block Range Indexes (BRIN) become a game-changer.
1. B-Tree vs. BRIN Architecture & Resource Footprint
Instead of indexing individual rows, a BRIN index summarizes entire physical block ranges on disk. For each range of table blocks (by default, 128 disk pages or 1MB of storage), BRIN stores only two values: the minimum and maximum value found within that block range:
| Metric | Standard B-Tree Index | BRIN Index (pages_per_range = 128) | Efficiency Advantage |
|---|---|---|---|
| Index Size (500M Rows) | 14,350 MB (~14 GB) | 18 MB | 99.87% Size Reduction |
| RAM Required in Cache | 4,000 MB – 14,000 MB | < 20 MB (Fits in L3 cache) | Near-zero buffer pool eviction |
| Write/Insert Amplification | High (Tree rebalancing & page splits) | Near Zero (Updates only min/max) | 5x faster ingestion throughput |
| Point Lookup Speed | Instant (Exact page pointer) | Slightly Slower (Scans 128-page range) | Trade-off for sequential queries |
2. Creating and Tuning BRIN Indexes
BRIN indexes rely on high physical correlation between data values and their physical storage order on disk. Because append-only timestamps naturally advance monotonically, correlation approaches 1.00:
-- 1. Check physical disk correlation before indexing
SELECT
attname,
correlation
FROM pg_stats
WHERE tablename = 'telemetry_events' AND attname = 'created_at';
-- Returns correlation: 0.9984 (Ideal for BRIN)
-- 2. Create the BRIN index with custom range sizing
-- Lower pages_per_range (e.g., 32) tightens range scans at the cost of slight index growth
CREATE INDEX idx_telemetry_created_at_brin
ON telemetry_events
USING BRIN (created_at)
WITH (pages_per_range = 64);
3. Automated Maintenance with brin_summarize_new_values
As new rows are inserted at high velocity, newly appended physical pages remain unsummarized until autovacuum runs. Run this periodic maintenance routine to ensure 100% summary coverage:
-- Force immediate summarization of newly appended unindexed blocks
SELECT brin_summarize_new_values('idx_telemetry_created_at_brin');
Pairing BRIN indexes with our strategies for PostgreSQL Covering Indexes creates an enterprise-grade, memory-efficient persistence tier. Explore our consulting capabilities in High-Throughput Database Architecture.