GIN, GiST & BRIN — Series 3

Preview — 3 of 10 questions

Both GIN and GiST can index the same jsonb or array column. What's the general trade-off between them?

AGIN typically builds a more compact index and answers lookups faster, but is more expensive to update on every write; GiST updates are cheaper per write but lookups are generally slower and the index larger for the same containment-style queries — GIN favors read-heavy workloads, GiST favors write-heavy ones

BGiST is always strictly better; GIN is a legacy index type kept only for backward compatibility

CGIN only supports exact-match queries; GiST only supports range queries — they aren't interchangeable for any of the same query types

DThere's no real trade-off; they produce identical performance for every workload

A BRIN index is added to a created_at column on an append-only events table (rows always inserted in increasing time order). Later, the same kind of BRIN index is added to a user_id column thats essentially randomly distributed across the tables physical storage. What's the outcome?

AThe created_at BRIN index works well because BRIN summarizes each block range's min/max value, and physically sequential data means each range's min/max is tight, letting BRIN skip most blocks; the user_id BRIN index is much less effective because randomly scattered values mean nearly every block range's min/max spans almost the entire value range, so BRIN can't rule out many blocks at all

BBoth indexes perform equally well, since BRIN doesn't depend on physical row order

CBRIN indexes require the underlying data to be sorted at all times, and PostgreSQL will refuse to build one on the user_id column

DIt's the opposite — BRIN performs best on randomly distributed columns and worst on sequential ones

A recommendation query needs ORDER BY embedding <-> query_vector LIMIT 10 (nearest-neighbor search). Why must this use a GiST- or specialized-index approach (like pgvector's HNSW/IVFFlat) rather than a GIN index?

AGIN indexes are actually the standard choice for nearest-neighbor queries; this scenario is unusual

BThe <-> operator only exists for GiST-indexed columns as a matter of SQL syntax restriction, unrelated to the index's internal structure

CGIN can do it, but only for arrays of integers, never floating-point vectors

DGIN is fundamentally built around indexing sets of keys per row for containment/existence lookups (@>, ?, full-text @@) — it has no notion of a distance-based ordering between entries, so it simply doesn't support ORDER BY ... <->-style nearest-neighbor queries at all; GiST (and purpose-built vector index types) are designed with distance-ordering traversal in mind

Answer all 10 questions (7 more), see explanations for every answer, and track your score.