postgres BRIN vs B-tree for time series

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

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.

1. Create the BRIN index with a pagesperrange 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.

1. 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.

1. 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.

1. 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