JSONB in PostgreSQL: When to Use It and When Not To

The Best Feature You Can Misuse

JSONB is one of PostgreSQL’s genuinely great features — and one of its most abused. It stores JSON in a binary, indexable form with a rich operator set, so you can keep semi-structured data right next to your relational data and query it well. The trap is enthusiasm: the moment it clicks, people start pouring entire schemas into a single jsonb column and throw away everything the database is good at.

jsonb vs json

Use jsonb essentially always. json stores the raw text (preserves whitespace and duplicate keys, no indexing); jsonb parses to a binary form that is indexable and supports containment. The operators you’ll live in:

data -> 'user'          -- get field as jsonb
data ->> 'email'        -- get field as text
data #> '{a,b}'         -- get nested by path (jsonb)
data @> '{"active": true}'   -- containment (does data contain this?)
jsonb_path_query(data, '$.items[*].sku')   -- SQL/JSON path

When to use it

• Genuinely variable attributes — per-tenant custom fields, product specs that differ by category.
• External payloads — store the API/webhook body verbatim, extract what you need.
• Sparse data that would be a sea of NULL columns or an EAV table.

When NOT to use it

Don’t migrate a relational schema into one big jsonb blob. You lose column types, NOT NULL/CHECK constraints, foreign keys, and — quietly the most expensive — the planner statistics that make joins and filters fast. Anything you regularly filter, join or aggregate by should be a real, typed column. Reserve JSONB for the dynamic tail, not the core.

Indexing it properly

A jsonb column isn’t magically fast — index it for how you query:

-- containment / key-existence queries → GIN
CREATE INDEX ix_doc_gin ON events USING GIN (data jsonb_path_ops);
--   uses:  WHERE data @> '{"type":"signup"}'

-- equality/range on ONE hot field → B-tree expression index
CREATE INDEX ix_doc_email ON users ((data ->> 'email'));
--   uses:  WHERE data ->> 'email' = 'a@b.com'

-- promote a hot field to a real, stat-tracked column
ALTER TABLE users
  ADD COLUMN email text GENERATED ALWAYS AS (data ->> 'email') STORED;

That last move — a STORED generated column pulling a hot field out of the JSON — gives the planner real statistics on it and is often the difference between a JSONB design that scales and one that crawls.