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.

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
);

All lessons in this course

← Back to PostgreSQL Performance & Query Optimization