JSONB vs Tables: A Decision Framework for Postgres Schema Design

"Should this be a column, or should it live inside a jsonb blob?" is one of the most common schema questions in Postgres-based applications. The wrong answer either produces 200 columns nobody can navigate, or a JSON blob that GROUP BY won't touch. The right answer is almost always "some of each", and there's a framework for deciding which goes where.

The question, sharpened#

The decision isn't binary. There are actually four resting states for a piece of structured data in Postgres:

1. A column on the row's main table.
2. A column on a separate but related table (1:1 or 1:N).
3. A key inside a jsonb column on the main table.
4. A row in an EAV-style key/value table (rare, almost always a mistake).

Most of the day-to-day choices are between options 1 and 3.

A three-question framework#

For any new field, ask:

1. Will I query it in a WHERE clause? If yes, it wants to be a real column with an index. If no, it can live in JSONB.
2. Does it have a stable shape across rows? If yes (every row has it, with the same type), it's a column. If no (it varies per row, or it's populated by tenants who can add arbitrary keys), it's JSONB.
3. Will I aggregate over it? SUM, AVG, COUNT, GROUP BY all favour real columns. JSONB aggregates are possible but painful.

If all three answers point the same way, the decision is easy. If they conflict, you have a hybrid case and you're probably looking at the hybrid pattern below.

Where JSONB is the right answer#

JSONB earns its keep when:

1. Tenant-supplied data with no shape contract

Webhook payloads, third-party integration responses, user-defined custom fields. You don't control what comes in. You'll usually pull it out with a ->> operator when you need it, and you'll rarely WHERE against it.

CREATE TABLE webhook_events (
  id          uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  source      text NOT NULL,                       -- stripe, github, etc.
  event_type  text NOT NULL,                       -- 'invoice.paid'
  payload     jsonb NOT NULL,
  received_at timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX webhook_source_type_idx ON webhook_events (source, event_type);

2. Wide, sparsely-populated metadata

A user_metadata field that's 30 possible keys and 4 are populated for any given row. Promoting them all to columns creates a sparse, hard-to-evolve table. JSONB nests them comfortably.

3. Append-only audit / analytics payloads

Anything you write once and read in aggregate later. JSON compression and storage costs are reasonable; you can always promote a frequently-queried key to a column later.

Where a real table beats JSONB#

The cases where you'll regret the JSONB choice:

1. Anything you filter on

WHERE metadata->>'status' = 'active' can be indexed (jsonb_path_ops, GIN), but the query planner is worse at estimating cardinality through JSONB than through a column. A status column with a btree index beats the JSONB equivalent every time.

2. Foreign keys

You cannot put a FK constraint on a JSONB key. If metadata-> >'owner_id' is logically a foreign key to users.id, the database can't enforce it and you'll eventually have an orphan reference. Always promote FKs out of JSONB.

3. Anything you display in the UI

Building a list view, a filter UI, or an export from JSONB is irritating in every framework. Column-driven UIs (including our admin views) need real columns to give users typed filters, sorting, and inline editing.

4. Anything you join through

JSONB joins are technically possible (lateral joins with jsonb_array_elements) but the query plans are slow and unintuitive. If a piece of data is the basis for a join, it wants its own table.

The hybrid pattern that's usually correct#

For most real tables, the right shape is:

CREATE TABLE orders (
  id            uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id     uuid NOT NULL REFERENCES tenants(id),
  customer_id   uuid REFERENCES customers(id),

  -- Promoted: anything queried, filtered, joined, or aggregated.
  status        text NOT NULL,
  total_cents   bigint NOT NULL,
  currency      text NOT NULL DEFAULT 'USD',
  placed_at     timestamptz NOT NULL DEFAULT now(),

  -- Kept in JSONB: provider-specific payloads, free-form notes, anything
  -- we don't have a query for *today*.
  provider_data jsonb NOT NULL DEFAULT '{}'::jsonb,
  metadata      jsonb NOT NULL DEFAULT '{}'::jsonb
);

The rule of thumb: promote on demand. Start everything in JSONB. The first time you write a WHERE against a key, promote it to a column with a migration. The audit-log structure of Postgres makes this safe (see the migrations guide).

Indexing implications#

Quick reference of what's available:

• Columns: btree (the default), brin (for sorted time-series), hash (rarely useful), gin/gist for fulltext + array.
• Whole JSONB: GIN with default ops class lets you query "does this row's payload contain X anywhere?" via the @> operator. Slow to build, big on disk.
• JSONB with jsonb_path_ops: smaller index, faster to maintain, only supports @> queries (no existence-of-key checks). The right choice 90% of the time.
• Functional indexes on a JSONB expression: CREATE INDEX ON t ((payload->>'status')). The single best technique for "I want column-like indexing on this one JSONB key".

-- Want to filter on `payload->'status'` like a column?
CREATE INDEX orders_payload_status_idx
  ON orders ((provider_data ->> 'status'));

-- Now this query is as fast as if status were a column.
SELECT count(*) FROM orders WHERE provider_data ->> 'status' = 'paid';

Functional indexes are a great escape hatch when you didn't promote a JSONB key but suddenly need to query it.

The bug patterns#

1. The silent type drift

JSONB doesn't enforce types. metadata->>'amount' returns text. Comparing it to a number works in some Postgres contexts and silently fails in others. Always cast explicitly: (metadata->>'amount')::numeric.

2. The null vs missing key trap

payload->>'foo' returns NULL whether foo is missing or foo: null. payload ? 'foo' is the existence check. If those two cases mean different things in your domain, write the queries explicitly.

3. The accidental schema proliferation

Teams that lean on JSONB too hard end up with three different spellings of the same key (userId, user_id, uid) across rows because nothing enforces consistency. The fix is a CHECK constraint with jsonb_typeof or a function that runs on INSERT, or just promote it to a column.

4. The TOAST chunking surprise

Large JSONB values get TOAST-ed (compressed and stored out-of-line). That makes reads of them slow because the page needs to be decompressed. A SELECT * on a table with multi-KB JSONB per row is much slower than a SELECT col1, col2. Be explicit about which columns you fetch.

Used well, JSONB is one of Postgres's superpowers. Used as a bag-of-everything, it's the source of an entire class of bugs. The framework above is what we use day-to-day to keep the line clear.