GIN Index

ELI5 — The Vibe Check

A GIN index is like a book's index on steroids. Instead of pointing to one location per entry, each entry can point to thousands of locations. It's built for data types that contain multiple values, like arrays, JSON fields, and full-text search vectors. Without GIN, searching inside JSON columns is painfully slow.

Real Talk

A Generalized Inverted Index (GIN) is a PostgreSQL index type designed for composite values where a single indexed item can contain multiple keys. It builds an inverted index mapping each key to the set of rows containing it. GIN is optimal for full-text search, JSONB containment, array operations, and trigram matching.

Show Me The Code

-- GIN for JSONB
CREATE INDEX idx_metadata ON products USING GIN(metadata);
SELECT * FROM products WHERE metadata @> '{"color": "red"}';

-- GIN for arrays
CREATE INDEX idx_tags ON articles USING GIN(tags);
SELECT * FROM articles WHERE tags @> ARRAY['database'];

When You'll Hear This

"Add a GIN index on that JSONB column, queries will go from seconds to milliseconds." / "Full-text search without a GIN index is just a sequential scan in disguise."