0Pricing
PostgreSQL Performance & Query Optimization · Ders

JSONB'den Ne Zaman Normalleştirme Yapılmalı

JSONB alanlarını gerçek sütunlara dönüştürmenin performans kazandırdığı erişim örüntülerini tanıyın.

JSONB'den Ne Zaman Normalleştirme Yapılmalı, CoddyKit'te ücretsiz bir PostgreSQL Performance & Query Optimization dersidir. Bu, 4 dersinin 4. dersidir. Aşağıdan dersin tamamını ücretsiz okuyabilir, sonra tarayıcıda yerleşik kod editörü ve 7/24 yapay zeka koçu ile uygulamalı olarak pratik yapabilirsin. Bu, PostgreSQL Performance & Query Optimization öğrenme yolunun bir parçasıdır ve ilerlemeniz web ve CoddyKit uygulaması arasında senkronize olur. PostgreSQL Performance & Query Optimization kursu toplamda 4 dersten oluşur.

Bu dersin bazı bölümleri henüz çevrilmemiş olup İngilizce olarak gösterilmektedir.

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.

Sıkça Sorulan Sorular

“JSONB'den Ne Zaman Normalleştirme Yapılmalı” dersi ücretsiz mi?

Evet — “JSONB'den Ne Zaman Normalleştirme Yapılmalı” dersin tüm metni burada web'de ücretsiz olarak okunabilir. Etkileşimli olarak pratik yapmak (yerleşik kod editörü ve 7/24 yapay zeka koçu) ve PostgreSQL Performance & Query Optimization kursunun geri kalanını açmak için CoddyKit PRO'ya yükselt. PostgreSQL Performance & Query Optimization kursu toplamda 4 dersten oluşur.

“JSONB'den Ne Zaman Normalleştirme Yapılmalı” dersinde ne öğreneceğim?

JSONB alanlarını gerçek sütunlara dönüştürmenin performans kazandırdığı erişim örüntülerini tanıyın. PostgreSQL Performance & Query Optimization ile uygulamalı kodu tarayıcıda doğrudan çalıştırarak pratik yaparsın ve 7/24 yapay zeka koçu dersi çalışırken sorularını yanıtlar.

PostgreSQL Performance & Query Optimization öğrenmeye başlamak için deneyim gerekli mi?

Önceden deneyim gerekmez. CoddyKit'te PostgreSQL Performance & Query Optimization, başlangıçtan ileri seviyeye kadar yapılandırıldığı için buradan başlayabilir veya başından başlayıp kendi hızında ilerleme yapabilirsin. Bu, 4 dersinin 4. dersidir.

“JSONB'den Ne Zaman Normalleştirme Yapılmalı” dersi ne kadar sürer?

Çoğu CoddyKit dersi yaklaşık 5–10 dakika sürer. Her biri kısa ve etkileşimli olduğu için sabit ilerleme yaparsın ve web ile uygulama arasında tam olarak bıraktığın yerden devam edebilirsin.

Bu PostgreSQL Performance & Query Optimization dersinde kod yazıp çalıştırabilir miyim?

Evet. Her PostgreSQL Performance & Query Optimization dersi yerleşik bir kod editörü içerir, bu sayede tarayıcıda gerçek kod yazıp çalıştırabilir ve anlık yapay zeka geri bildirimi alırsın — yerel kurulum gerekli değildir.

Bu kursun tüm dersleri

  1. JSONB İşleçleri ve İçerme Sorguları
  2. JSONB'de GIN ve İfade Dizinlerini Karşılaştırma
  3. JSONPath ile JSONB Sorgulama
  4. JSONB'den Ne Zaman Normalleştirme Yapılmalı
← PostgreSQL Performance & Query Optimization Sayfasına Dön