What is a BRIN index and when is it better than B-tree?
BRIN stores only the minimum and maximum value for each block range of the table, so it is tiny. It works well on huge tables where the column's value follows the physical row order, like an append-only timestamp.
Explain it simply
Picture a very long register where entries are written day by day. You do not need an entry for every line; a sticky note on each 128-page chunk saying "dates 1 Jan to 3 Jan" is enough to skip chunks that cannot hold the date you want. That is BRIN: Block Range INdex.
It only works when values correlate with physical position. A created_at column on an append-only log does. A random column such as a customer ID does not, because every chunk's range would be huge.
Example
CREATE TABLE sensor_log (
id bigint GENERATED ALWAYS AS IDENTITY,
logged_at timestamptz NOT NULL,
reading numeric
);
CREATE INDEX sensor_brin ON sensor_log USING brin (logged_at);
SELECT avg(reading) FROM sensor_log
WHERE logged_at >= '2026-10-01' AND logged_at < '2026-10-02';On a table with hundreds of millions of rows, a B-tree might take gigabytes while BRIN takes a few megabytes. It returns candidate blocks and PostgreSQL rechecks the rows.
What to say in the interview
"BRIN keeps min and max per block range, so it is extremely small. It suits very large, naturally ordered data like time-series logs. If the column is not correlated with row order, BRIN is useless and I use B-tree."
Common mistakes
- Using BRIN on a column after random updates have scrambled the physical order.