JSON or JSONB in PostgreSQL, and how do you index JSONB?

Summary

JSONB stores a parsed binary form: it is slightly slower to write but much faster to query, supports containment and existence operators and can be indexed with GIN. JSON keeps the exact text (whitespace, key order, duplicate keys) and is rarely what you want.

Longer Answer

• CREATE INDEX ON events USING gin (payload); supports @>, ?, ?|, ?&.
• jsonb_path_ops makes a smaller, faster GIN index that supports only @> (containment).
• For a single field used in equality or ranges, an expression B-tree index is better: CREATE INDEX ON events ((payload->>'user_id'));.
• SQL/JSON path queries (jsonb_path_query, @?, @@) and, in PostgreSQL 16+/17, SQL-standard JSON_TABLE and constructors add expressiveness.

Keep frequently filtered, joined or constrained fields as real columns; JSONB statistics are coarse, so the planner often misestimates JSONB predicates.

Code Example

CREATE INDEX events_payload_gin ON events USING gin (payload jsonb_path_ops);
SELECT id FROM events WHERE payload @> '{"type": "checkout", "country": "DE"}';

Key Points

• Use JSONB.
• GIN (default or jsonb_path_ops) for containment.
• B-tree expression indexes for single keys.
• Hot fields belong in columns.