PostgreSQL JSONB: When to Use It and When to Stop

JSONB is PostgreSQL's most seductive feature. It's also the one most likely to ruin your schema if you let it. I've done both — used it well and used it badly. The badly version took three months to untangle.

When JSONB is the right call

Truly unstructured metadata

User preferences are the textbook case: different users enable different features, and you can't know at schema design time which features exist.

CREATE TABLE user_preferences (
  user_id UUID PRIMARY KEY REFERENCES users(id),
  settings JSONB NOT NULL DEFAULT '{}'
);

-- A user's settings might look like:
-- {"theme": "dark", "notifications": {"email": true, "sms": false}}

You can query specific settings:

SELECT user_id FROM user_preferences
WHERE settings->>'theme' = 'dark';

And update nested values without fetching and rewriting the whole object:

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

Event logs and audit trails

The shape of an audit event varies by event type — a user login event has different fields from a payment event. JSONB handles this without a migration every time you add an event type.

CREATE TABLE audit_log (
  id BIGSERIAL PRIMARY KEY,
  event_type TEXT NOT NULL,
  actor_id UUID,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  payload JSONB NOT NULL
);

External API response caching

When you're storing third-party API responses, you often can't control the shape. JSONB handles this without a schema per endpoint.

When JSONB is a trap

Data you query by

If you write WHERE data->>'status' = 'active' in more than one place, that field should be a column.

I worked in a codebase that stored order status in a JSONB metadata column because "we might add more status types later." We did add more status types. We also added an expression index on (metadata->>'status'), then a generated column to make the index usable — and then we had a column anyway, we'd just taken a two-week detour to get there.

Data with relationships

If you store an array of IDs in JSONB and then find yourself writing array containment queries to find related records, you should have used a join table.

-- Don't do this
SELECT * FROM projects WHERE team_members @> '["user-123"]';

-- Do this
SELECT p.* FROM projects p
JOIN project_members pm ON pm.project_id = p.id
WHERE pm.user_id = 'user-123';

The join table version is faster (indexable foreign keys), enforceable (foreign key constraints), and easier to query.

The "schema-free" myth

You still have a schema. It's just in your application code instead of your database — harder to see, harder to enforce, harder to query.

The performance reality

A GIN index on a JSONB column indexes all keys and values. It's fast for containment queries using @>:

-- Uses a GIN index efficiently
SELECT * FROM events WHERE payload @> '{"event_type": "login"}';

Less useful for path queries using ->>. For those, you want an expression index on the specific path:

CREATE INDEX idx_events_user_id ON events ((payload->>'user_id'));

I tested a table with 1 million rows comparing WHERE jsonb_col->>'status' = 'active' (with an expression index) against WHERE status_col = 'active' (with a btree index). The JSONB query was consistently 15–25% slower. Small enough that it doesn't matter for infrequent queries. Matters a lot for queries running thousands of times per minute.

If you find yourself adding multiple expression indexes on a JSONB column, pause and ask whether those paths should just be columns.

The hybrid approach

Use columns for everything you query regularly, JSONB for everything else:

CREATE TABLE products (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  name TEXT NOT NULL,
  category_id INT REFERENCES categories(id),
  price NUMERIC(10, 2) NOT NULL,
  status TEXT NOT NULL DEFAULT 'active',
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),

  -- Relational: things I filter/sort/join on
  -- JSONB: things I display but don't query by
  attributes JSONB NOT NULL DEFAULT '{}'
);

The attributes column holds things like dimensions, colour variants, custom fields. You display them, you update them, but you don't filter rows by them. If a specific attribute becomes something you query by, you extract it to a real column — a one-time migration made deliberately rather than discovered under load.

If you're reaching for jsonb_set() more than once per UPDATE, your JSONB column probably wants to be a table. And if you're using jsonb_extract_path() in WHERE clauses regularly, those paths want to be columns.