0Pricing
PostgreSQL Performance & Query Optimization · Leçon

Quand normaliser les données hors de JSONB

Repérez les modes d’accès pour lesquels transformer les champs JSONB en véritables colonnes améliore les performances.

Quand normaliser les données hors de JSONB est une leçon PostgreSQL Performance & Query Optimization gratuite sur CoddyKit. Ceci est la leçon 4 sur 4. Tu peux lire la leçon complète ci-dessous gratuitement — puis la pratiquer en direct dans le navigateur avec un éditeur de code intégré et un tuteur IA 24/7. Elle fait partie du parcours d'apprentissage PostgreSQL Performance & Query Optimization, et ta progression se synchronise sur le web et l'application CoddyKit. Le cours PostgreSQL Performance & Query Optimization comprend 4 leçons au total.

Certaines parties de cette leçon n'ont pas encore été traduites et s'affichent en anglais.

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.

Questions Fréquemment Posées

La leçon « Quand normaliser les données hors de JSONB » est-elle gratuite ?

Oui — le texte complet de « Quand normaliser les données hors de JSONB » est gratuit à lire ici sur le web. Pour la pratiquer de manière interactive (un éditeur de code intégré et un tuteur IA 24/7) et déverrouiller le reste du cours PostgreSQL Performance & Query Optimization, passe à CoddyKit PRO. Le cours PostgreSQL Performance & Query Optimization comprend 4 leçons au total.

Qu'est-ce que j'apprendrai dans « Quand normaliser les données hors de JSONB » ?

Repérez les modes d’accès pour lesquels transformer les champs JSONB en véritables colonnes améliore les performances. Tu pratiques PostgreSQL Performance & Query Optimization avec du code pratique que tu exécutes directement dans le navigateur, et un tuteur IA 24/7 répond à tes questions au fur et à mesure que tu avances dans la leçon.

Dois-je avoir de l'expérience pour commencer PostgreSQL Performance & Query Optimization ?

Aucune expérience préalable n'est requise. PostgreSQL Performance & Query Optimization sur CoddyKit est structuré pour les débutants jusqu'aux apprenants avancés, donc tu peux commencer ici ou depuis le début et avancer à ton rythme. Ceci est la leçon 4 sur 4.

Combien de temps prend la leçon « Quand normaliser les données hors de JSONB » ?

La plupart des leçons CoddyKit prennent environ 5–10 minutes. Chacune est courte et interactive, tu progresses régulièrement et tu repiques exactement où tu t'es arrêté sur le web et l'app.

Peux-tu écrire et exécuter du code dans cette leçon PostgreSQL Performance & Query Optimization ?

Oui. Chaque leçon PostgreSQL Performance & Query Optimization inclut un éditeur de code intégré, tu écris et exécutes du vrai code directement dans ton navigateur et tu reçois des retours IA instantanés — aucune configuration locale requise.

Toutes les leçons de ce cours

  1. Opérateurs JSONB et requêtes de contenance
  2. GIN ou index d’expression sur JSONB
  3. Interroger JSONB avec JSONPath
  4. Quand normaliser les données hors de JSONB
← Retour à PostgreSQL Performance & Query Optimization