What is a GIN index and which PostgreSQL features use it?
GIN is an inverted index: it maps each element (a word, an array item, a JSONB key) to the rows that contain it. It powers full-text search, array and JSONB containment queries, and trigram search, with slower writes than B-tree.
Explain it simply
The index at the back of a textbook lists each term with the page numbers where it appears. That is a GIN index: "Generalized Inverted Index". Instead of one entry per row, it has one entry per element found inside the rows, each pointing to many rows.
This suits columns that hold many things at once: documents, arrays, JSON.
Example
-- arrays
CREATE INDEX cand_skills_gin ON candidates USING gin (skills);
SELECT * FROM candidates WHERE skills @> ARRAY['sql'];
-- jsonb
CREATE INDEX events_data_gin ON events USING gin (data);
SELECT * FROM events WHERE data @> '{"type":"login"}';
-- full-text search
CREATE INDEX posts_fts ON posts USING gin (to_tsvector('english', body));
-- trigram LIKE search (needs the pg_trgm extension)
CREATE INDEX names_trgm ON students USING gin (name gin_trgm_ops);GIN is slow to update because one row touches many entries. PostgreSQL softens this with a pending list that is merged in batches (the fastupdate setting).
What to say in the interview
"GIN is an inverted index mapping elements to rows. I use it for full-text search, arrays, JSONB containment and pg_trgm. Reads are fast, writes are heavier than B-tree, so I avoid it on write-hot columns unless the search benefit is worth it."
Common mistakes
- Expecting a GIN index to help with = or ORDER BY; that is B-tree work.