Use JSONB without giving up relational shape

The Goal

Use JSONB for the part of the row that is still changing.

Do not hide the whole record inside JSONB. Keep IDs, ownership, timestamps, status, and common filters as normal columns.

Start With A Mixed Table

create table product_events (
  id bigserial primary key,
  account_id bigint not null,
  user_id bigint,
  event_type text not null,
  happened_at timestamptz not null default now(),
  payload jsonb not null default '{}'
);

This table has a clear relational shape. The flexible part is only payload.

That lets you ask normal questions:

select *
from product_events
where account_id = 42
  and event_type = 'checkout.completed'
order by happened_at desc;

Query JSONB When You Need It

Use containment when you want rows where the JSON includes a shape.

select id, payload
from product_events
where payload @> '{"source":"stripe"}';

Use ->> when you need a text value from a key.

select payload->>'plan' as plan
from product_events
where event_type = 'subscription.changed';

Add The First Index

If containment queries are real, add a GIN index.

create index product_events_payload_gin
  on product_events using gin (payload);

Do this after you know the query exists. JSONB indexes are useful, but they are not free.

Promote Important Fields

When a JSON key becomes part of the product, move it out.

alter table product_events
  add column plan text;

update product_events
set plan = payload->>'plan'
where payload ? 'plan';

Now plan can have a normal index, constraint, and type.

Good Rules

Normal columns for identity, ownership, permissions, status, and time.
JSONB for changing attributes and external payloads.
Index only the keys you actually query.
Promote fields when they become important.
Do not use JSONB to avoid thinking about the model.

JSONB is best when it gives you room to learn, not when it becomes a junk drawer.

References

PostgreSQL JSON types