Postgres JSONB: When to Use It and When to Use Columns

PostgreSQL's jsonb type is one reason "SQL vs NoSQL" is less of a dilemma than it used to be: you can store flexible, schemaless documents inside a relational database, index them, and query them. It's also one of the most over-used features in AI-generated schemas, where metadata jsonb quietly becomes the place every new field goes. (SQL vs NoSQL.)

json vs jsonb

json stores the exact input text — whitespace, key order, duplicate keys — and re-parses it on every access.
jsonb stores a decomposed binary form: slightly slower to write, much faster to query, and indexable. Key order isn't preserved and duplicate keys keep the last value.

Use jsonb unless you specifically need to preserve the original text.

Good uses

Genuinely variable attributes — product specs that differ per category, form builder responses, feature settings per integration.
Payloads from elsewhere — webhook events, API responses you store for audit or replay. (Handling Webhooks Reliably.)
Sparse, rarely queried data — preferences with dozens of optional keys, most unset.
Document-shaped data read and written as a whole — a page layout, a saved editor state.

Bad uses

Fields you filter, sort, or join on constantly — status, user_id, created_at. These should be columns.
Relationships — arrays of IDs pointing at other tables lose foreign keys and cascade rules.
Money and quantities you aggregate — types, constraints, and arithmetic are clumsier inside JSON.
"We'll figure out the schema later" — later arrives, and now every reader must handle every historical shape.

The pragmatic pattern is hybrid: stable, important fields as columns; the variable tail in jsonb.

CREATE TABLE products (
  id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  sku         text NOT NULL UNIQUE,
  category    text NOT NULL,
  price_cents integer NOT NULL CHECK (price_cents >= 0),
  attributes  jsonb NOT NULL DEFAULT '{}'
);

The operators you'll use

attributes -> 'dimensions'            -- jsonb value
attributes ->> 'color'                -- text value
attributes #>> '{dimensions,width}'   -- text at a path
attributes @> '{"color": "red"}'      -- contains
attributes ? 'warranty'               -- key exists
attributes ?| array['usb_c','hdmi']   -- any of these keys exist
jsonb_path_exists(attributes, '$.ports[*] ? (@ == "usb_c")')  -- SQL/JSON path

Since PostgreSQL 14, subscripting works too: attributes['dimensions']['width'].

Remember ->> returns text: cast before comparing numbers — (attributes->>'weight_g')::int > 500 — or you'll get text comparison ("900" > "1000").

Indexing

GIN index for containment and key existence

CREATE INDEX products_attributes_gin ON products USING GIN (attributes);
-- supports @>, ?, ?|, ?&

CREATE INDEX products_attributes_path_gin ON products USING GIN (attributes jsonb_path_ops);
-- smaller and faster, but only supports @> (and jsonpath match operators)

Write queries in the form the index supports: WHERE attributes @> '{"color":"red"}' uses the GIN index; WHERE attributes->>'color' = 'red' does not.

Expression index for one hot key

CREATE INDEX products_color_idx ON products ((attributes->>'color'));

A B-tree on one extracted key — supports equality, ranges, and sorting on that key, and is far smaller than a full GIN index. If you find yourself adding several of these, those keys probably want to be columns. (Database Indexes.)

Generated columns

Promote a JSON key to a real, indexable, typed column without changing writers:

ALTER TABLE products
  ADD COLUMN weight_g integer GENERATED ALWAYS AS ((attributes->>'weight_g')::integer) STORED;

Updating

-- set a nested value
UPDATE products SET attributes = jsonb_set(attributes, '{dimensions,width}', '42') WHERE id = $1;

-- merge keys (shallow)
UPDATE products SET attributes = attributes || '{"warranty": "2y"}' WHERE id = $1;

-- remove a key
UPDATE products SET attributes = attributes - 'legacy_code' WHERE id = $1;

Two things to know:

Updating any part of a jsonb value rewrites the whole value (and the row). Large, frequently updated documents cause write amplification and table bloat. Split hot, frequently changing parts into columns or a separate table.
Read-modify-write in application code (load JSON, change a key, save it back) races with concurrent updates — the classic lost update. Prefer in-database operators as above. (Database Transactions Explained.)

Validate the shape

Schemaless doesn't have to mean unvalidated:

ALTER TABLE products ADD CONSTRAINT attributes_is_object
  CHECK (jsonb_typeof(attributes) = 'object');

ALTER TABLE products ADD CONSTRAINT weight_is_number
  CHECK (NOT attributes ? 'weight_g' OR jsonb_typeof(attributes->'weight_g') = 'number');

And validate at the application boundary with a schema per category or version — Zod in TypeScript, Pydantic in Python. (Validating Input With Zod.) Store a schema_version key if shapes will evolve.

Signs a JSONB field should become columns

You've added expression indexes on three or more of its keys.
Most rows have the same keys.
You need foreign keys, uniqueness, or NOT NULL on something inside it.
Queries cast the same keys over and over.
Bugs come from different rows having different shapes for "the same" field.

Migrate with a generated column or a backfilled real column, switch readers, then stop writing the key. (What Are Database Migrations?.)

EasySpawn gives each project a managed Postgres in its workspace, so Claude Code can prototype JSONB schemas, add indexes, and check query plans with EXPLAIN ANALYZE against real data. See how it works or join the waitlist.