Chapter 1 · Practice 4 of 5
Use jsonb for flexible data, not as a replacement for columns
Why it matters
jsonb is great for data whose shape really varies, such as third-party webhook payloads or per-customer settings. It is a poor fit for fields you filter, join or constrain on every request, because inside JSON you lose types, foreign keys and simple indexes.
A good rule: if your code reads a key on most queries, make it a real column. Keep jsonb for the rest.
| Use a real column when | Use jsonb when |
|---|---|
| Most queries read or filter on it | The shape varies by row or by sender |
It needs a foreign key or CHECK | You store it mainly to read it back whole |
| It needs a simple B-tree index | Occasional queries look inside it |
If you do query inside jsonb, add a GIN index, as in the example.
Example
CREATE INDEX events_payload_gin ON events USING gin (payload jsonb_path_ops);
SELECT * FROM events WHERE payload @> '{"type": "invoice.paid"}';