JSONB, deeply: operators, GIN, and the boundary

jsonb vs json: binary wins

Postgres has two JSON types and they are not interchangeable:

• json stores the exact text you gave it — whitespace, key order, and duplicate keys preserved. Every read reparses it. There are no useful indexes on its contents.
• jsonb parses on write into a decomposed binary form: keys are deduplicated (last value wins), order isn’t preserved, and reads are fast because there’s no reparse. Critically, jsonb can be indexed with GIN.

For anything you query, the answer is jsonb. Use json only for the rare case where you must round-trip the exact original text (e.g. preserving a signature over the raw bytes). The write cost of jsonb parsing is paid once; the read and index wins are paid forever.

The operator vocabulary

When you write a query against a jsonb column, the wrong operator choice silently compares the wrong type — and the GIN index you built won’t fire. You navigate jsonb with a small set of operators, and the trap that bites everyone is this: -> returns jsonb, ->> returns text.

SELECT
  payload -> 'user'             AS user_json,    -- jsonb (a nested object)
  payload -> 'user' ->> 'name'  AS user_name,    -- text  (chain -> then ->>)
  payload #> '{user,roles,0}'   AS first_role_j, -- jsonb at a path
  payload #>> '{user,roles,0}'  AS first_role_t  -- text  at a path
FROM events;

• -> get field/element as jsonb; ->> get field/element as text.
• #> get at a path as jsonb; #>> get at a path as text.
• @> containment — does the left document contain the right one? payload @> '{"tenant_id": 7}'.
• ? does the document have this top-level key? payload ? 'tenant_id'.

The text-vs-jsonb distinction matters because ->>'tenant_id' = '7' compares text, while @> '{"tenant_id": 7}' compares structure — and only the containment form uses a GIN index well.

GIN indexing for containment

A plain B-tree can’t index “is this key/value somewhere inside this document.” That’s what GIN (Generalized Inverted Index) is for: it indexes the contents of each jsonb value so containment (@>) and key-exists (?) queries skip the sequential scan.

-- default GIN: supports @>, ?, ?|, ?& — bigger index, more operators
CREATE INDEX ON events USING gin (payload);

-- jsonb_path_ops: supports only @> — smaller and faster for containment
CREATE INDEX ON events USING gin (payload jsonb_path_ops);

-- now this uses the index instead of scanning 90M rows:
SELECT * FROM events WHERE payload @> '{"tenant_id": 7}';

The two GIN flavours are a real tradeoff. The default jsonb_ops indexes every key and value, supporting @>, ?, ?|, ?& — flexible but larger. jsonb_path_ops indexes only hashed paths-to-values, so it supports only @>, but the index is smaller and containment lookups are faster. Pick jsonb_path_ops when containment is all you do (the common case); pick the default when you also need key-existence operators.

The boundary: where JSONB earns its place

This is the senior judgment call. JSONB is right for the sparse, schema-varying long tail — per-event metadata that differs by event type, optional attributes only 2% of rows set, third-party payloads you don’t control. It is wrong as a substitute for columns you filter, sort, join, or constrain on. Those belong as typed columns: a tenant_id bigint, a status text, a created_at timestamptz — typed, NOT NULL-able, CHECK-able, and indexable with a cheap B-tree.

And relationships are not JSONB’s job at all. A list of tag ids inside a document can’t be foreign-keyed, can’t be joined efficiently, and forces a whole-document rewrite to add one tag. A many-to-many belongs in a side table (event_tags(event_id, tag_id)) with real foreign keys. (The databases track’s relational-model chapter has a dedicated lesson on jsonb and arrays from the storage angle.)

Updates: jsonb_set and the whole-document cost

You update a jsonb field with jsonb_set (or the || merge / - delete operators):

UPDATE events
SET payload = jsonb_set(payload, '{status}', '"shipped"', true)  -- true = create if missing
WHERE id = 42;

The cost that surprises people: there is no in-place edit of one key. jsonb is stored as a single value, so changing one field reads the whole document, builds a new version, and writes it back — and MVCC makes that a brand-new row version (dead tuple for vacuum to clean). A 30 KB document updated 100 times a second is 3 MB/s of rewrite churn plus bloat. This is the strongest argument for keeping hot, frequently-mutated fields as their own columns: updating a status text column rewrites the row too, but you avoid serializing and reparsing a fat document on every change.