0Pricing
PostgreSQL Performance & Query Optimization · Урок

Когда следует нормализовать данные из JSONB

Определяйте шаблоны доступа, при которых перенос полей JSONB в обычные столбцы повышает производительность.

«Когда следует нормализовать данные из JSONB» — бесплатный урок PostgreSQL Performance & Query Optimization на CoddyKit. Это урок 4 из 4. Ты можешь прочитать весь урок бесплатно ниже — а потом практиковать его прямо в браузере с встроенным редактором кода и ИИ-репетитором 24/7. Это часть пути обучения PostgreSQL Performance & Query Optimization, и твой прогресс синхронизируется между веб-версией и приложением CoddyKit. Курс PostgreSQL Performance & Query Optimization содержит 4 уроков всего.

Части этого урока еще не переведены и отображаются на английском.

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.

Часто задаваемые вопросы

Урок «Когда следует нормализовать данные из JSONB» бесплатный?

Да — полный текст урока «Когда следует нормализовать данные из JSONB» бесплатно доступен здесь в веб-версии. Чтобы практиковать его интерактивно (встроенный редактор кода и ИИ-репетитор 24/7) и разблокировать остальной курс PostgreSQL Performance & Query Optimization, подпишись на CoddyKit PRO. Курс PostgreSQL Performance & Query Optimization содержит 4 уроков всего.

Чему я научусь в уроке «Когда следует нормализовать данные из JSONB»?

Определяйте шаблоны доступа, при которых перенос полей JSONB в обычные столбцы повышает производительность. Ты практикуешь PostgreSQL Performance & Query Optimization с помощью реального кода, который запускаешь прямо в браузере, и ИИ-репетитор 24/7 отвечает на твои вопросы во время урока.

Нужен ли мне опыт, чтобы начать PostgreSQL Performance & Query Optimization?

Предыдущий опыт не требуется. PostgreSQL Performance & Query Optimization на CoddyKit структурирован для всех уровней — от новичков до продвинутых, поэтому ты можешь начать отсюда или с самого начала и учиться в своем темпе. Это урок 4 из 4.

Сколько времени занимает урок «Когда следует нормализовать данные из JSONB»?

Большинство уроков CoddyKit занимают около 5–10 минут. Каждый из них компактный и интерактивный, поэтому ты постоянно делаешь прогресс и продолжаешь с того же места в веб-версии и приложении.

Можно ли писать и запускать код в этом уроке PostgreSQL Performance & Query Optimization?

Да. Каждый урок PostgreSQL Performance & Query Optimization включает встроенный редактор кода, поэтому ты пишешь и запускаешь реальный код прямо в браузере и получаешь моментальную обратную связь от AI — локальная установка не требуется.

Все уроки этого курса

  1. Операторы JSONB и запросы на вхождение
  2. GIN и индексы выражений для JSONB
  3. Запросы к JSONB с помощью JSONPath
  4. Когда следует нормализовать данные из JSONB
← Назад к PostgreSQL Performance & Query Optimization