🐘 PostgreSQL Intermediate

How do you index JSONB columns in PostgreSQL?

Answer

JSONB columns can be indexed using GIN (Generalized Inverted Index) or expression indexes. Full GIN index: CREATE INDEX idx_gin ON table USING GIN (jsonb_column); — supports @>, ?, ?|, ?& operators. GIN with jsonb_path_ops: CREATE INDEX idx_gin ON table USING GIN (jsonb_col jsonb_path_ops); — smaller, faster for @> queries only. Expression index on a specific field: CREATE INDEX idx_city ON profiles((data->>'city')); — used when querying a specific key frequently. CREATE INDEX ON events((data->'user'->'id')); for nested access. Expression indexes are smaller and more selective than full GIN indexes when only one or few fields are queried.