Cards/DatabasesSystem Design · Day 44Sep 15, 2026

Not Every Index Is a Tree

B-tree, GIN, BRIN, partial and covering indexes compared by the question each one answers, and a billion-row table where BRIN costs 300 KB against a B-tree's 5 GB

B-trees answer one question well: where is this value, or this range of values? Exact match, range queries, ordered results. Plenty of questions aren't that shape, and Postgres has an index for most of them.

GIN handles multi-valued data — which rows contain this word, this tag, this JSON key. One row maps to many values, which is exactly what full-text search and arrays need.

BRIN stores nothing but the minimum and maximum per block, so it only works when the data is physically ordered. On a billion-row table with a timestamp column it can be a few hundred kilobytes, where a B-tree on the same column runs to gigabytes. Same data, different question.

A partial index covers only the rows you care about. A covering index answers from the index alone and never touches the table.

Reach for a different tool only after you've named the question precisely. The name usually contains the answer.

Reach for a different tool only after you've named the question precisely. The name usually contains the answer.