0Pricing
SQL Academy · Lesson

Modelling: When JSONB Beats Normalisation

Decide between a JSONB column and a separate table — flexible attributes vs strict schemas.

Modelling: When JSONB Beats Normalisation is a free SQL Academy lesson on CoddyKit — lesson 4 of 4. You can read the complete lesson below for free — then practise it hands-on in the browser with a built-in code editor and a 24/7 AI tutor. It is part of the SQL Academy learning path, one of 4 lessons in the course, and your progress syncs across the web and the CoddyKit app.

The Choice

For each piece of data you might store: column or JSONB key? The answer drives schema design, query performance, and maintainability.

When Columns Win

Use real columns when data is:

  • Known and stable
  • Queried frequently with indexes
  • Validated by CHECK / FK constraints
  • Reported on or joined

When JSONB Wins

Use JSONB when:

  • Shape varies per row (tenant-defined fields, plugin metadata)
  • Schema evolves rapidly (avoid migrations)
  • Whole-document reads / writes are the norm
  • Storage of opaque payloads (webhook bodies)

The Hybrid Pattern

Best of both: structured columns for the stable core, JSONB for the variable tail:

CREATE TABLE events (
  id BIGSERIAL PRIMARY KEY,
  ts TIMESTAMPTZ NOT NULL,
  event_type TEXT NOT NULL,
  user_id BIGINT,
  data JSONB NOT NULL DEFAULT '{}'::JSONB
);

EAV Trap

The classic "entity-attribute-value" model — one table with rows of (key, value) — used to be the only choice for sparse data. JSONB replaces EAV with one document column:

-- Old EAV:
CREATE TABLE user_attrs (
  user_id BIGINT,
  attr TEXT,
  val TEXT
);

-- Modern:
ALTER TABLE users ADD COLUMN attrs JSONB NOT NULL DEFAULT '{}';

-- JSONB queries are faster and easier than the EAV joins.

Migration Strategy

Add a column to a hot table → 5 minutes of downtime on a big table. With JSONB, adding a field is just an app code change.

Constraints on JSONB

Add CHECK constraints to enforce minimum structure:

ALTER TABLE events
  ADD CONSTRAINT data_has_type CHECK (data ? 'type'),
  ADD CONSTRAINT type_is_string CHECK (jsonb_typeof(data->'type') = 'string');

Promote Hot Fields

If a JSONB field becomes frequently queried, promote it to a real column. A UPDATE script + new index + new column makes it fast.

JSONB Patch Operations

Update a single field:

UPDATE users
SET attrs = jsonb_set(attrs, '{plan}', '"pro"')
WHERE id = 1;

Don't Store Money in JSONB

Financial values need exact NUMERIC types and constraints. Always real columns.

When JSONB Hurts

If you find yourself extracting and casting the same JSONB key in every query, that's a sign it should be a column. The cumulative cost of (data->>'k')::INT across millions of rows adds up.

Schema Documentation

JSONB columns are easy to write but hard to document. Maintain an external schema (JSON Schema, app code) so collaborators know what to expect.

Recap

JSONB is a powerful tool — use it for the variable parts, real columns for the stable core. Promote hot fields when patterns emerge.

Quick Check

You're storing audit events. The event type is always present; some events have an array of "actors" that varies in shape. How do you model it?

Frequently asked questions

Is the “Modelling: When JSONB Beats Normalisation” lesson free?

Yes — the full text of “Modelling: When JSONB Beats Normalisation” is free to read here on the web, and the SQL Academy course includes 4 lessons in total. To practise it interactively (a built-in code editor and a 24/7 AI tutor) and unlock the rest of the SQL Academy course, upgrade to CoddyKit PRO.

What will I learn in “Modelling: When JSONB Beats Normalisation”?

Decide between a JSONB column and a separate table — flexible attributes vs strict schemas. You practise SQL Academy with hands-on code you run directly in the browser, and a 24/7 AI tutor answers your questions as you work through the lesson.

Do I need any experience to start SQL Academy?

No prior experience is required. SQL Academy on CoddyKit is structured for beginners through advanced learners; this is — lesson 4 of 4, so you can start here or from the beginning and move at your own pace.

How long does the “Modelling: When JSONB Beats Normalisation” lesson take?

Most CoddyKit lessons take about 5–10 minutes. Each one is bite-sized and interactive, so you make steady progress and pick up exactly where you left off across the web and the app.

Can I write and run code in this SQL Academy lesson?

Yes. Every SQL Academy lesson includes a built-in code editor, so you write and run real code right in your browser and get instant AI feedback — no local setup required.

All lessons in this course

  1. JSONB vs JSON: When to Use Each
  2. Path Operators: -> ->> @>
  3. Indexing JSONB with GIN
  4. Modelling: When JSONB Beats Normalisation
← Back to SQL Academy