0Pricing
PostgreSQL Performance & Query Optimization · Lesson

When to Normalize Out of JSONB

Recognize the access patterns where promoting JSONB fields to real columns wins on performance.

When to Normalize Out of JSONB is a free PostgreSQL Performance & Query Optimization 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 PostgreSQL Performance & Query Optimization learning path, one of 4 lessons in the course, and your progress syncs across the web and the CoddyKit app.

JSONB Is Great Until It Isn't

JSONB is wonderful for flexible, schema-less data. But not every field belongs inside the blob. Some fields are accessed so often, filtered so hard, or joined so frequently that keeping them buried in JSONB actively hurts performance.

This lesson is about a single design decision: when do you promote a JSONB field to a real column?

  • A real column has a fixed type, can be NOT NULL, and indexes cheaply.
  • A JSONB field is dynamic, but every read pays a parse/extract cost and indexing it is heavier.

The goal isn't "JSONB bad, columns good" — it's matching the access pattern to the right storage.

What Promoting Actually Means

"Normalizing out of JSONB" means taking a value that currently lives inside the data JSONB column and storing it as its own typed column instead.

You can keep the JSONB for the long tail of rare attributes, and pull out only the hot fields.

-- Before: everything lives in JSONB
CREATE TABLE events (
    id       bigserial PRIMARY KEY,
    data     jsonb NOT NULL
);

-- After: hot fields promoted, rest stays flexible
CREATE TABLE events (
    id          bigserial PRIMARY KEY,
    user_id     bigint NOT NULL,
    event_type  text   NOT NULL,
    created_at  timestamptz NOT NULL,
    data        jsonb NOT NULL  -- the long tail
);

Signal 1: You Filter On It Constantly

The strongest signal to promote a field is a WHERE clause that hits it on nearly every query.

Filtering inside JSONB forces an extraction expression like data->>'status'. That works, but it returns text, needs casting, and a plain B-tree index on the table won't cover it unless you build an expression index.

If the filter is core to your workload, a real typed column with an ordinary index is simpler and faster.

-- Buried in JSONB: needs an expression index to be fast
EXPLAIN ANALYZE
SELECT * FROM events
WHERE data->>'status' = 'active';

-- Expression index that makes the above usable
CREATE INDEX idx_events_status
    ON events ((data->>'status'));

Expression Index vs. Real Column

An expression index on (data->>'status') can match the exact filter, but it has sharp edges:

  • The query predicate must match the indexed expression character for character — data->>'status' indexed won't help data#>>'{status}'.
  • Values come back as text; range and numeric filters need explicit casts that must also match the index.
  • You maintain one index per extracted field, each re-parsing the JSON on write.

A promoted column sidesteps all of this: standard typing, standard indexes, standard planner statistics.

Signal 2: You Need Range or Sort Performance

Numbers and timestamps are common victims. Inside JSONB they're stored as text-ish values, so range scans and ORDER BY need a typed expression index just to behave.

If you sort or range-filter on a field — pagination by created_at, price ranges, amounts — promote it to a real timestamptz / numeric column. The planner gets accurate stats and a clean B-tree.

-- Range + sort on a JSONB number is awkward and cast-heavy
SELECT *
FROM orders
WHERE (data->>'amount')::numeric > 100
ORDER BY (data->>'created_at')::timestamptz DESC
LIMIT 20;

-- With promoted columns it's a plain, index-friendly query
SELECT *
FROM orders
WHERE amount > 100
ORDER BY created_at DESC
LIMIT 20;

Signal 3: You Join Or Group On It

Foreign keys and grouping keys should almost never live in JSONB.

  • You cannot declare a real FOREIGN KEY constraint on data->>'user_id' — referential integrity is lost.
  • Joining on an extracted text value blocks hash/merge join optimizations and forces casts.
  • GROUP BY data->>'category' can't use column statistics well, hurting aggregate plans.

If a field connects rows together, make it a first-class typed column.

-- Fragile: no FK, cast on every join, poor stats
SELECT u.name, count(*)
FROM events e
JOIN users u ON u.id = (e.data->>'user_id')::bigint
GROUP BY u.name;

-- Promoted user_id: real FK, clean join, real stats
-- ALTER TABLE events ADD COLUMN user_id bigint REFERENCES users(id);

When JSONB Should Stay JSONB

Promotion isn't free, so keep fields in JSONB when:

  • They're sparse — present on only a small fraction of rows (promoting creates a mostly-NULL column).
  • They're rarely filtered — read back as a whole document, never used in WHERE/JOIN.
  • They're unpredictable — keys vary per tenant or per event type and you can't enumerate them.
  • They form a nested structure you fetch as one unit (e.g. a settings object).

This long tail is exactly what JSONB was designed for. Don't flatten it just because you flattened the hot fields.

GIN: The Other Option

Before promoting, ask whether a GIN index on the JSONB already solves it. GIN shines for containment and key-existence queries across many unpredictable keys.

GIN is great when you query many different JSON keys ad hoc. It's overkill (and write-heavy) when one or two specific fields drive every query — that's the promotion case.

-- GIN supports @>, ?, ?| ?& on the whole document
CREATE INDEX idx_events_data_gin
    ON events USING gin (data);

-- Containment query the GIN index can serve
SELECT * FROM events
WHERE data @> '{"status": "active"}';

-- jsonb_path_ops: smaller/faster, supports only @>
CREATE INDEX idx_events_data_pathops
    ON events USING gin (data jsonb_path_ops);

Decision Heuristic

A quick rule of thumb for each field:

  • Hot + selective + typed (filtered, sorted, joined, FK) → promote to a real column.
  • Ad-hoc across many keys → keep in JSONB, add a GIN index.
  • Sparse / read-as-document / rarely queried → keep in JSONB, no extra index.

Most real schemas end up hybrid: a handful of promoted columns plus a JSONB column for everything else.

Migrating a Field Out, Safely

To promote an existing JSONB field, backfill it into a new column, then index it. Doing it in steps keeps locks short and lets you validate the data first.

Note the cast: JSONB text must be coerced to the target type, and you should decide what to do with rows missing the key (here they become NULL).

ALTER TABLE events ADD COLUMN created_at timestamptz;

UPDATE events
SET created_at = (data->>'created_at')::timestamptz
WHERE created_at IS NULL
  AND data ? 'created_at';

CREATE INDEX idx_events_created_at ON events (created_at);

Keep Them In Sync (Or Drop The Duplicate)

After promoting, you have two choices: remove the field from the JSONB so there's a single source of truth, or keep both and guarantee they agree.

If you keep both, a generated column is the cleanest: it's derived from JSONB automatically and can't drift.

-- Option A: drop the duplicated key from the blob
UPDATE events
SET data = data - 'created_at';

-- Option B: a STORED generated column stays in sync by design
ALTER TABLE events
    ADD COLUMN status text
    GENERATED ALWAYS AS (data->>'status') STORED;

CREATE INDEX idx_events_status_gen ON events (status);

Quick Check

Which field is the strongest candidate to normalize OUT of a JSONB data column?

Recap

Promote a JSONB field to a real column when its access pattern demands it:

  • Filtered constantly → typed column + ordinary index beats an expression index.
  • Range-scanned or sorted → real numeric/timestamptz gives clean B-trees and accurate stats.
  • Joined / grouped / FK → only a real column supports foreign keys and good join plans.

Keep fields in JSONB when they're sparse, ad-hoc across many keys, or read as a document — add a GIN index if you need containment search. The winning design is usually hybrid: a few promoted hot columns plus a JSONB column for the long tail. When you do promote, backfill carefully and either drop the duplicate key or use a STORED generated column so the two never drift.

Frequently asked questions

Is the “When to Normalize Out of JSONB” lesson free?

Yes — the full text of “When to Normalize Out of JSONB” is free to read here on the web, and the PostgreSQL Performance & Query Optimization 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 PostgreSQL Performance & Query Optimization course, upgrade to CoddyKit PRO.

What will I learn in “When to Normalize Out of JSONB”?

Recognize the access patterns where promoting JSONB fields to real columns wins on performance. You practise PostgreSQL Performance & Query Optimization 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 PostgreSQL Performance & Query Optimization?

No prior experience is required. PostgreSQL Performance & Query Optimization 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 “When to Normalize Out of JSONB” 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 PostgreSQL Performance & Query Optimization lesson?

Yes. Every PostgreSQL Performance & Query Optimization 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 Operators and Containment Queries
  2. GIN vs Expression Indexes on JSONB
  3. Querying JSONB with JSONPath
  4. When to Normalize Out of JSONB
← Back to PostgreSQL Performance & Query Optimization