jsonb stores structured JSON data in PostgreSQL in a representation designed for efficient processing and indexing. It is a strong fit when attributes are genuinely unpredictable or sparsely populated, such as large-scale ingestion whose fields and presence cannot be known in advance. GIN and GiST indexes make it substantially more useful for searchable structured data than XML, whose PostgreSQL form is not indexable. section 3.8 section 5.3
The tempting mistake is treating jsonb as a universal replacement for relational design. Putting an entire customer or sale into one JSONB column maximizes apparent flexibility, but it removes ordinary column-level structure. Once application data has stable fields and relationships, queries must extract and cast values repeatedly, and PostgreSQL cannot enforce referential integrity or reliably ensure the stored values have the intended form. section 5.3
Relational JSON turns a normal join into extraction work
A JSON-only accounts table and a JSON-only sales table require joins on extracted account identifiers, numeric casts for balances and amounts, and special predicates for nested migration metadata. The query is possible, but its complexity makes mistakes and poor performance easier. A need to join JSON-stored entities is a warning that the data may fit a relational pattern better. section 5.3
SELECT a.json_account ->> 'name' AS "Name"
FROM test.accounts a
JOIN test.sales s
ON (s.json_sale ->> 'account_id')::int = (a.json_account ->> 'id')::int
WHERE (a.json_account ->> 'balance')::numeric < 20000
AND (s.json_sale ->> 'amount')::numeric > 10000
AND (NOT a.json_account['migrated_account'] ->> 'system' = 'legacy'
OR a.json_account -> 'migrated_account' IS NULL);
This query exposes the cost of relational JSON: every join and filter reaches into documents, converts values, and handles optional nested fields before PostgreSQL can evaluate the business condition. section 5.3
Use jsonb deliberately:
- Prefer it for flexible, sparse, or incompletely known attributes, especially during ingestion. section 5.3
- Keep naturally relational entities and relationships in relational form rather than recreating joins through document extraction. section 5.3
- Choose JSON(B), rather than XML, when structured documents must be queried or manipulated in PostgreSQL. section 3.8 section 3.9
- Measure actual stored values with pg_column_size() when comparing representation overhead. section 3.8
jsonb enables a productive combination of relational and schemaless data, not an excuse to blur their boundary. Its value is flexibility where the shape is truly variable; its risk is allowing stable business facts, keys, and relationships to become opaque documents that demand increasingly elaborate SQL to recover. section 5.3
