Postgres JSONB Best Practice

When to use JSONB.

Overview

Postgres JSONB is powerful and over-used. JSONB shines for genuinely schema-flexible data (per-tenant custom fields, third-party API payloads, evolving event shapes); it hurts when used for fields that have stable schema and frequent access, because every read pays the parsing cost and most queries cannot use simple indexes. The discipline is per-column rationale: JSONB only when the schema flexibility is real, structured columns for everything else.

• When to use JSONB. Per-column the JSONB rationale; schema-flexible data only, not as a general-purpose escape hatch.
• Schema-flexible data. Per-column flexible schema; per-tenant custom fields, third-party payloads, evolving event shapes.
• GIN indexes. Per-JSONB-column GIN index for containment and key existence queries; the index makes JSONB queries actually scan-free.
• Avoid JSONB for hot fields plus per-query path expression. Hot fields with stable schema belong in proper columns; per-query JSONB path expressions (jsonb_path_ops) match modern access patterns.

The approach

The practical approach is per-column rationale required (JSONB only when schema flexibility is real), GIN indexes per JSONB column for queryable access patterns, structured columns for hot fields with stable schema, jsonb_path_ops for per-query path expressions, and documented per-table JSONB rationale committed to the schema documentation so the choice is reviewable.

• Per-column rationale. Per-column documented JSONB rationale; the default is structured columns, JSONB requires justification.
• GIN indexes. Per-JSONB-column GIN index for containment and key queries; without the index, JSONB queries scan the full table.
• Avoid JSONB for hot fields. Per-hot-field proper column; the parsing cost compounds with every read.
• Per-query path expression plus documented schema. Per-query jsonb_path_ops; per-table JSONB rationale committed for operational review.

Why this compounds

JSONB discipline compounds across the schema lifetime. Each correctly-used JSONB column preserves the schema flexibility the workload actually needs; each structured column kept out of JSONB preserves query performance and indexability; the team builds intuition for when JSONB pays off versus when it costs.

• Schema flexibility. Right JSONB matches workload; per-tenant custom fields stay flexible without polluting the structured schema.
• Query performance. Right indexes support queries; GIN-indexed JSONB queries scan-free against millions of rows.
• Operational fit. Right column type matches use; structured fields query like structured fields, JSONB fields query like JSONB.
• Institutional knowledge. Each schema decision teaches Postgres patterns; the team learns when JSONB earns its parsing cost.

JSONB discipline is a database discipline that pays off across years. Nova AI Ops integrates with Postgres telemetry, surfaces JSONB patterns, and supports the team’s schema design discipline.