B-tree, GIN, GiST, BRIN and hash

SQL, indexing and PostgreSQL Senior Python 3.13+ PostgreSQL 17+ Reviewed 31 Oct 2026 By Best Latest Apps

Pick the fix

Queries filter events with WHERE payload @> '{"type": "signup"}' on a jsonb column. Which index helps?

OptionsCREATE INDEX ON events USING gin (payload jsonb_path_ops);CREATE INDEX ON events (payload);CREATE INDEX ON events USING brin (payload);CREATE INDEX ON events USING hash (payload);

Hint 1 of 1

Which index type looks inside a value?

Choose an answer, then open Show answer below to check it.

Show answer

Answer: A CREATE INDEX ON events USING gin (payload jsonb_path_ops);

Correct. GIN indexes jsonb contents; jsonb_path_ops is compact and supports @>.

@> asks whether a document contains a fragment, which a B-tree can't answer. GIN indexes each key and value inside the document, and jsonb_path_ops makes a smaller index aimed at containment.

Why not the others?

B CREATE INDEX ON events (payload);. A B-tree on whole jsonb values only helps equality on the entire document.
C CREATE INDEX ON events USING brin (payload);. BRIN summarises ranges of ordered values; jsonb containment isn't that.
D CREATE INDEX ON events USING hash (payload);. Hash is equality on the whole value only.

30-second answer

B-tree is the default for equality, ranges and sorting. GIN indexes the elements inside a value, so it suits jsonb containment (@>), arrays and full-text search. GiST supports overlap and nearest-neighbour queries: geometry, ranges, exclusion constraints. BRIN stores a summary per block range and is tiny, which works when values follow the physical order, like timestamps in an append-only log. Hash indexes only do equality.

Practise saying it · 35 s. No recording, just a timer

Match index types to queries.

Say your answer out loud, then check which of these points you covered:

B-tree: equality, range, sort
GIN: jsonb, arrays, full text
GiST: overlap, exclusion
BRIN: huge ordered tables
Hash: equality only

Deep dive

GIN is slower to update than B-tree; fastupdate batches pending entries.

An exclusion constraint with GiST prevents overlapping bookings: EXCLUDE USING gist (room WITH =, during WITH &&) (needs btree_gist for the equality part).

BRIN can be orders of magnitude smaller than a B-tree on a huge time-ordered table, but only helps when values correlate with physical location.

Vector similarity (pgvector) adds HNSW and IVFFlat index types as an extension.

No double bookings, enforced by the database

SQL · PostgreSQL 17

CREATE EXTENSION IF NOT EXISTS btree_gist;
CREATE TABLE bookings (
  room int NOT NULL,
  during tstzrange NOT NULL,
  EXCLUDE USING gist (room WITH =, during WITH &&)
);
INSERT INTO bookings VALUES (1, '[2026-11-01 10:00, 2026-11-01 11:00)');
INSERT INTO bookings VALUES (1, '[2026-11-01 10:30, 2026-11-01 12:00)');

Common mistakes

Using a B-tree on a jsonb column and expecting @> queries to use it.
Using BRIN on a table whose rows are updated randomly.
Choosing hash indexes for range queries.

Senior take & follow-ups

Map each index type to a query shape, and mention exclusion constraints as a senior touch.

Follow-up questions

Think of your answer first, then open the hint.

How do you prevent double-booking a room in the database?
Show hint
A GiST exclusion constraint on room and a time range.
When does BRIN beat B-tree?
Show hint
Very large tables whose indexed column follows insertion order.

Suggest a fix if something here is wrong or out of date.