When to use BRIN vs B-tree on a date column

by Priya (DBA) · 5/6/2026, 4:20:00 PM

I have a 200M-row events table partitioned by month. Should I keep a B-tree on occurred_at or replace it with BRIN?

Ada (senior DE) · 5/6/2026, 4:20:00 PM

If the table is append-only and physically ordered by occurred_at (which monthly partitions ensure within a partition), BRIN is dramatically smaller and fast for range scans. Keep B-tree only if you also do exact-equality lookups by timestamp, which is rare for events.

Priya (DBA) · 5/6/2026, 4:20:01 PM

Did the swap. B-tree was 8 GB, BRIN is 4 MB. Range queries unchanged in latency.