GIN and GIST indexes

B-tree is great for scalars. GIN and GIST exist because real data is messy: documents, JSON, arrays, geographic regions, time ranges. These indexes let Postgres efficiently search inside composite values.

GIN: Generalized Inverted Index

Inverted index. For each item (word, array element, jsonb key) stores the list of rows containing it. Optimized for "does this value contain X" queries.

Use GIN for:

• Full-text search: tsvector columns with @@ operator
• JSONB: WHERE doc @> '{"status": "open"}'
• Arrays: WHERE tags @> ARRAY['urgent']
• Trigrams for fuzzy match: pg_trgm extension, LIKE '%substring%'

CREATE INDEX idx_doc_search ON articles USING GIN (to_tsvector('english', body));
CREATE INDEX idx_metadata ON events USING GIN (metadata jsonb_path_ops);
CREATE INDEX idx_tags ON posts USING GIN (tags);

GIST: Generalized Search Tree

Balanced tree of bounding boxes. Each node summarizes the union of its children. Walk down, prune subtrees whose box does not match.

Use GIST for:

• Geometric types: point, polygon, box (PostGIS uses GIST)
• Range types: tstzrange, int4range
• Exclusion constraints: "no two rows overlap"
• Trigrams (alternative to GIN, slower lookup, faster updates)

CREATE INDEX idx_room_booking ON bookings USING GIST (room_id, during tstzrange);
ALTER TABLE bookings ADD CONSTRAINT no_overlap EXCLUDE USING GIST (room_id WITH =, during WITH &&);

That exclusion constraint prevents double-booking a room.

The trade-off

The interview line

"B-tree only handles scalar equality and range. GIN and GIST exist for composite values. GIN is an inverted index, best for full-text search, JSONB containment, and array operators, with fast reads and slow writes. GIST is a balanced tree of bounding boxes, best for geometry, ranges, and exclusion constraints, with faster writes and slower reads."