Decision Record · ADR-003

Normalized tables over JSONB in PostgreSQL

Core entities are modeled in normalized form, with constraints and foreign keys. JSONB is used selectively for genuinely schemaless or rarely queried peripheral data.

What is this? · Decision Record

A documented architecture decision in ADR format — context, options considered, rationale and consequences. One concrete decision, not a general guide. Go to overview

Status
Accepted
Identifier
ADR-003
Review
To be revisited when query patterns or data volumes change significantly.

Context

With JSONB, PostgreSQL offers flexible, schemaless storage. For structured data, that raises the question: normalized tables or JSONB columns?

Problem

JSONB is temptingly flexible, but it moves structure and integrity out of the database and into application code. Normalized tables enforce structure but are less flexible when the schema changes frequently.

Options

What we considered.

  1. A

    Normalized tables

    Structure, constraints and indexes in the database; clear relationships.

  2. B

    JSONB columns

    Flexible and schemaless — integrity lives in the code.

  3. C

    Mixed

    Normalized for core entities, JSONB for genuinely peripheral data.

Decision

What we decided.

Core entities are modeled in normalized form, with constraints and foreign keys. JSONB is used selectively for genuinely schemaless or rarely queried peripheral data.

Why this decision

Data integrity belongs in the database, not in scattered application logic. Normalized structures are queryable, indexable and self-documenting. JSONB is the right choice where the structure is genuinely variable — not as a shortcut around modeling.

Consequences

  • Schema changes to core entities require migrations — deliberate, versioned and traceable.
  • Frequently queried JSONB fields need targeted indexes (GIN) and a documented expected shape.
  • The boundary between core and peripheral data is a design decision that has to be justified.

Rejected alternatives

  • JSONB as the defaultMoves integrity into the code and makes queries and migrations harder.
  • Normalization at any costForces structure even where data is genuinely schemaless.
Read on

Related topics.

Guides, decisions, services and case studies connected to this page.

Technologies

Facing a similar decision?

We don't make it on gut feeling. To discuss the decisions in your system, talk directly to our management.