JSONB Is Not a Schema: When To Reach For It in Postgres, And When To Stop

The first JSONB column on a table is usually a good idea. The third one is usually a sign that something has gone wrong. By the time you have metadata, settings, attributes, and extra_data columns each holding a different shape of dict that nobody documented, the schema is no longer telling you what your data is — the application is, somewhere, in code that runs at request time.

JSONB is one of the most useful features in Postgres. It is also one of the easiest to misuse. The rule for when to use it, the queries that tell you when a key should be promoted to a column, and the indexing pattern that makes querying it fast — that is what this post is. No “JSONB vs JSON” basics, no benchmarking. Just the practical decisions.

The honest rule

Use JSONB when the shape varies legitimately per row and the variation is not something you query against in performance-critical paths.

That is the entire rule. Examples that pass:

• A webhook_events table where the payload shape depends on the event type and the variation is real (Stripe events vs GitHub events vs your internal ones).
• An audit_log table where you store before/after diffs of arbitrary models.
• A user_preferences blob where each user has a slightly different set of toggled features and you only ever read the whole blob to render a settings page.

Examples that fail:

• A users.metadata column that always contains {"plan": "...", "trial_ends_at": "..."}. That is not “varies per row” — that is two columns hiding from a migration.
• An orders.extra blob that the billing query has to filter on. You will need an index on that key, you will not get the planner statistics a column would give you, and you will spend the rest of the project arguing about JSON path syntax.
• A products.spec blob that the search page filters on. You wanted EAV — JSONB is not EAV, it is unindexed-by-default unstructured data.

If a JSONB key is queried in WHERE more than once in your codebase, it should probably be a column. If two of those queries appear in the same hot path, it definitely should.

The four queries that tell you to promote a key

Run these against your real database before deciding whether a JSONB key has earned a column.

1. How many rows have this key?

SELECT count(*) FILTER (WHERE data ? 'plan') AS with_key,
       count(*)                              AS total
FROM accounts;

If 95%+ of rows have the key, it is no longer optional. Make it a column. JSONB is for legitimately optional data — once it is universal, you are paying the JSON parsing cost on every read for nothing.

2. What are the actual values?

SELECT data->>'plan' AS plan, count(*)
FROM accounts
GROUP BY 1
ORDER BY 2 DESC;

If there are five distinct values — free, pro, team, enterprise, legacy — you have an enum, not a free-form blob. Make it a column with a CHECK or a generated plan enum.

3. How often does the key appear in WHERE clauses?

grep -r "data->>'plan'" app/ (or your equivalent). Anywhere the key shows up in a WHERE is a query the planner cannot optimize without help. Each occurrence is an argument for a column.

4. What does the planner do with it?

EXPLAIN ANALYZE
SELECT * FROM accounts WHERE data->>'plan' = 'pro';

If you see a Seq Scan, the JSONB key has no index. You can fix that with a GIN index — but the rows-promoted-to-column version comes with btree statistics, which the planner uses for join ordering. JSONB does not. Bad estimates are how a fast-looking query becomes a slow one in production.

Indexing JSONB the right way

When you do keep something in JSONB, the wrong index is “no index” and the second-wrong index is a btree on (data->>'key') for a column you will eventually rename. The right default is a GIN index — General Inverted iNdex — which lets Postgres look up rows by JSONB key/value containment.

CREATE INDEX events_payload_gin ON events USING GIN (payload);

That index supports the JSONB containment operator @>:

-- Uses the GIN index. Fast.
SELECT * FROM events WHERE payload @> '{"type": "user.signup"}';

-- Does NOT use the GIN index above. Slow.
SELECT * FROM events WHERE payload->>'type' = 'user.signup';

The difference is real. The first version is “find rows where payload contains this object,” which GIN handles natively. The second is “extract the value at this path and compare it as text,” which is a function call the GIN index does not understand.

If you must use the ->> form (because you need the value, not just existence), index the expression directly:

CREATE INDEX events_type_idx ON events ((payload->>'type'));

Now the planner uses it, but you have a single-purpose index. If you have five hot keys, you have five indexes. At that point — see above — those keys want to be columns.

For partial GIN indexes, Postgres supports the jsonb_path_ops operator class, which is smaller and faster but supports only @>:

CREATE INDEX events_payload_gin
  ON events USING GIN (payload jsonb_path_ops);

Use it if @> is your only access pattern and the index size matters (it usually does once the table grows past a few million rows).

The query patterns nobody tells you about

A few JSONB tricks that pay back the investment.

Update a nested key without overwriting the whole blob:

UPDATE settings
SET data = jsonb_set(data, '{notifications,email}', 'false', true)
WHERE user_id = $1;

The fourth argument (true) means “create the key if it does not exist.” Without it, the update silently no-ops on rows missing notifications.email — which is exactly the bug pattern you do not want.

Remove a key:

UPDATE settings SET data = data - 'deprecated_flag' WHERE data ? 'deprecated_flag';

The - operator removes a top-level key. For nested removal, #- takes a path:

UPDATE settings SET data = data #- '{billing,old_card}';

Expand JSONB to rows, joining-style:

SELECT u.id, k.key, k.value
FROM users u, jsonb_each_text(u.preferences) k
WHERE k.value::boolean IS TRUE;

jsonb_each_text turns a JSONB object into a (key, value) set. This is the right tool when you really do want to query any key without indexing each one — but if you find yourself doing this in a hot path, the data model needs work.

Aggregate rows back into a JSONB object:

SELECT user_id, jsonb_object_agg(pref_key, pref_value) AS preferences
FROM user_preferences
GROUP BY user_id;

Useful when migrating out of JSONB into a properly normalized user_preferences table — you can run both representations side by side and verify they match before flipping reads.

The migration nobody plans for

Every team I have seen that overused JSONB ends up doing the same migration three years later: extract the keys that have become universal into proper columns. It is annoying because it is mostly text manipulation, and it is annoying because the JSONB schema was never written down anywhere. Save yourself by writing this migration as you go, not three years later:

-- Add the column nullable, populate it, then enforce NOT NULL.
ALTER TABLE accounts ADD COLUMN plan text;
UPDATE accounts SET plan = data->>'plan' WHERE data ? 'plan';
ALTER TABLE accounts ALTER COLUMN plan SET NOT NULL;
ALTER TABLE accounts ADD CHECK (plan IN ('free','pro','team','enterprise'));

-- Optional: drop the now-redundant key from JSONB.
UPDATE accounts SET data = data - 'plan' WHERE data ? 'plan';

Three-step migrations like this are easy if the column was used in a handful of places. They turn into a quarter-long project if every API endpoint, every analytics query, every export job has its own copy of data->>'plan'. The fix is to read JSONB through one helper module from day one, so that when you promote a key the change is in one place.

The takeaway

JSONB is a sharp tool. It is genuinely the right answer for variable-shape per-row data — webhook payloads, audit diffs, user-defined attributes — and the wrong answer for “I do not feel like writing a migration today.” The trap is that the wrong answer feels great for six months and then you cannot un-tangle the schema.

Use the four queries — coverage, distinct values, code occurrences, planner behavior — to decide when a key has graduated to a column. Index JSONB with GIN, query it with @>, and route every read through one helper so future migrations do not require sed-across-the-codebase. And the moment a JSONB key shows up in a join condition, treat that as a sign you skipped the data-modeling step.

---

A note from Yojji

Choosing the right data model for a feature — the boring decision between “ship a JSONB blob now” and “spend two hours writing the schema” — is one of those calls that is invisible if you make it well and three quarters of refactor work if you do not. It is the kind of judgment Yojji’s backend teams bring to client work.

Yojji is an international custom software development company founded in 2016, with offices across Europe, the US, and the UK. Their dedicated teams ship full-cycle product engineering across the JavaScript stack and the major cloud platforms — including the unglamorous database design that decides whether a product still feels good a year later.