0
Challenge · 34Correct & Fast

Beyond B-tree: GIN, GiST, BRIN & JSONB

Perfused in ~60% of apps

A B-tree answers one family of question extremely well: "is this equal to X, or between X and Y?" It has nothing useful to say about "does this jsonb column contain {featured: true}", "do these two time slots overlap", or "give me a tiny, cheap index over a billion-row log table." Reach for `create index` on any of those with the default access method and you either get an index Postgres silently never uses, or one so large it costs more than the table it's supposed to speed up.

Core idea. Postgres ships more than one index structure, and each is shaped for a different question, not a different data type. GIN inverts the data — one entry per possible element, pointing back at every row that has it — so it answers "contains" questions over jsonb, arrays and full text. GiST is a general search tree built for "overlaps" and "nearest," which is why it is the only access method an EXCLUDE constraint can use. BRIN stores one min/max summary per block of the table, not per row, trading precision for a fraction of the size on huge, naturally-ordered data. And any of these — including B-tree — can be built on the RESULT of an expression, not just a raw column.
Playground, this isn't a screenshot
the question that routes here

“Give me rows equal to X, or between X and Y, or sorted by this column.”

One value per row, totally ordered. B-tree is a sorted list — exactly what equality, range and ORDER BY need.
create index posts_created_at_idx on posts(created_at);
Tradeoff: Useless for “contains” or “overlaps” questions — those aren't about sort order.
↑ play a little first, then dig in