NoSQLIntermediate
JSONB column or real columns?
“When do you store data as JSONB versus typed columns in Postgres?”
What this tests
- Schema flexibility vs structure
- Indexing JSONB
Answers by level
Read the beginner answer first and notice what is missing.
JSONB for genuinely variable data whose shape you do not control — per-integration settings, event payloads, user-defined fields. Typed columns for data you know the shape of: they are smaller, constrained, typed, joinable and visible to the planner.
JSONB is indexable (GIN for containment, expression index for a hot path), but promote a key to a column the moment you filter or join on it regularly.
Green flags · Red flags
Strong green flag · Warns against JSONB becoming the schema.
Green flags
- Variable-shape → JSONB; known-shape → columns
- Knows GIN and expression indexes on JSONB
- Promotes hot keys to columns
Red flags
- "JSONB to avoid migrations" as a default
- Unaware JSONB can be indexed
Follow-up questions
F1
How do you index WHERE data->>'tenant' = ? efficiently?
Scenario
A settings table is one big JSONB blob and reports on it are slow. What do you change?