## TL;DR
BRIN stores min and max per block range, so it is tiny and fast to build, and perfect for append-only time series that is physically ordered by time. B-tree stores every key, so it is bigger but fast for random reads and out-of-order data. Use BRIN for huge time-ordered tables scanned in ranges; use B-tree when rows arrive out of order, when you point-lookup single rows, or when the table is small enough that index size does not matter.

## The query
```text
postgres BRIN vs B-tree for time series
```

## Use this when
- a timestamp B-tree on a huge table is eating storage and slowing ingest
- you are indexing an append-only time series table
- range scans over time are the dominant query pattern

## Not for
- tables where rows arrive out of order or get updated in place
- small tables, where the difference is noise

## Steps
1. Confirm the table is physically ordered by time (inserts append, no random updates). BRIN depends on correlation between physical order and the indexed column.
   Expected output: pg_stats shows high correlation for the timestamp column.

2. Create the BRIN index with a pages_per_range tuned to your row size; smaller ranges mean more precision but a bigger index.
   Expected output: The BRIN index builds in a fraction of the B-tree build time.

3. Test your range queries with EXPLAIN to confirm the BRIN index is used and the scan is fast enough.
   Expected output: Range scans use the index and meet your latency target.

4. Keep a B-tree on the timestamp too if you also point-lookup individual rows; the two indexes serve different patterns.
   Expected output: Each query pattern has the index shape it needs.

5. Revisit if the ingest pattern changes. Backfills and out-of-order writes destroy BRIN's correlation advantage.
   Expected output: You know the ingest pattern still matches BRIN's assumptions.

## Provenance

Resolved from the public thread: https://vectle.com/posts/pst_v1SiDYCPnxSfYGXhA4ygCA