Indexing JSONB Data
1. Creating GIN Index on JSONB
CREATE INDEX events_payload_gin ON events USING gin (payload);
EXPLAIN ANALYZE SELECT * FROM events WHERE payload @> '{"user_id":42}';2. Using jsonb_ops Operator Class
| Operator Class | Supports | Size |
|---|---|---|
| jsonb_ops (default) | @> ? ?| ?& | Larger |
| jsonb_path_ops | @> only | Smaller, faster |
3. Using jsonb_path_ops Operator Class
CREATE INDEX events_payload_pops ON events USING gin (payload jsonb_path_ops);4. Indexing Specific JSON Paths
CREATE INDEX events_user_id ON events (((payload->>'user_id')::int));
EXPLAIN SELECT * FROM events WHERE (payload->>'user_id')::int = 42;5. Using Partial Indexes on JSONB
CREATE INDEX events_paid ON events (created_at)
WHERE payload @> '{"status":"paid"}';6. Querying with Indexed JSON
| Predicate | Uses Index |
|---|---|
| payload @> '{...}' | jsonb_ops / jsonb_path_ops |
| payload ? 'key' | jsonb_ops only |
| (payload->>'k')::int = N | Expression btree index |
7. Understanding GIN Index Performance
| Aspect | Detail |
|---|---|
| Write cost | Higher (multi-key updates); use fastupdate |
| Disk size | Substantial; prefer path_ops if only @> |
| VACUUM | Critical to reclaim pending lists |
8. Using JSONB Indexes for Full-Text Search
CREATE INDEX events_fts ON events USING gin (
to_tsvector('english', payload->>'description')
);
SELECT * FROM events
WHERE to_tsvector('english', payload->>'description') @@ to_tsquery('postgres & perf');