JSONB

JSONB stores binary JSON with efficient containment queries. Use it for semi-structured attributes that change shape, not as a substitute for relational invariants.

					-- Containment (@>) uses GIN well
CREATE INDEX idx_meta_gin ON products USING GIN (metadata jsonb_path_ops);

SELECT id, metadata->>'color' AS color
FROM products
WHERE metadata @> '{"category": "shoes"}';

-- jsonb_set for partial updates
UPDATE products
SET metadata = jsonb_set(metadata, '{tags}', '["sale"]'::jsonb, true)
WHERE id = 42;
				

Keep queryable fields explicit: promote stable keys to typed columns or generated columns when they drive joins, constraints, or reporting. JSONB shines for optional attributes, event payloads, and config blobs — not for every field in a core entity.

On interviews: explain when JSONB beats EAV tables, how GIN indexes help @> and ? operators, and why schema discipline still matters (migrations, validation, ownership).

Common pitfalls: no indexes on hot JSON paths; storing money or dates only as strings; unbounded document growth; hiding foreign keys inside JSON without integrity checks.

The trade-off is schema flexibility versus query predictability — JSONB defers structure cost to application validation and index design.

Checklist:

• Index paths that appear in WHERE/ORDER BY.
• Promote stable keys to columns when they are join keys.
• Validate shape at write boundaries.
• Measure update size and TOAST overhead on large documents.