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.
Previous
How does JSONB differ from JSON in PostgreSQL?
Next
What is the ON CONFLICT clause (upsert) in PostgreSQL?