PricingLoginStart free
PostgreSQL Performance & Query Optimization · Lekcja

Kiedy normalizować dane z JSONB

Rozpoznawaj wzorce dostępu, w których przeniesienie pól JSONB do rzeczywistych kolumn poprawia wydajność.

Lekcja 4 z 413 kroki

Kiedy normalizować dane z JSONB to bezpłatna lekcja PostgreSQL Performance & Query Optimization na CoddyKit. To lekcja 4 z 4. Możesz przeczytać całą lekcję poniżej za darmo — a potem ćwiczyć ją interaktywnie w przeglądarce z wbudowanym edytorem kodu i tutorem AI dostępnym 24/7. To część ścieżki edukacyjnej PostgreSQL Performance & Query Optimization, a Twój postęp synchronizuje się między webem a aplikacją CoddyKit. Kurs PostgreSQL Performance & Query Optimization zawiera 4 lekcji w sumie.

JSONB jest świetny, dopóki taki nie jest

JSONB świetnie nadaje się do elastycznych danych bez ustalonego schematu. Jednak nie każde pole powinno znajdować się w tym obiekcie. Niektóre pola są odczytywane tak często, tak intensywnie filtrowane lub tak często używane w złączeniach, że przechowywanie ich wewnątrz JSONB aktywnie pogarsza wydajność.

Ta lekcja dotyczy jednej decyzji projektowej: kiedy przenieść pole JSONB do rzeczywistej kolumny?

  • Rzeczywista kolumna ma stały typ, może mieć ograniczenie NOT NULL i można ją tanio indeksować.
  • Pole JSONB jest dynamiczne, ale każdy odczyt wiąże się z kosztem parsowania i wydobywania, a jego indeksowanie jest cięższe.

Celem nie jest zasada „JSONB jest złe, kolumny są dobre” — chodzi o dopasowanie sposobu dostępu do właściwego sposobu przechowywania danych.

Co właściwie oznacza przeniesienie pola

„Normalizacja poza JSONB” oznacza wzięcie wartości, która obecnie znajduje się w kolumnie JSONB data, i przechowywanie jej zamiast tego w osobnej kolumnie o określonym typie.

Mogą Państwo zachować JSONB dla rzadko używanych atrybutów, a wydzielić z niego tylko często używane pola.

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

Sygnał 1: stale filtrują Państwo po tym polu

Najsilniejszym sygnałem wskazującym na potrzebę przeniesienia pola jest klauzula WHERE, która odwołuje się do niego w niemal każdym zapytaniu.

Filtrowanie wewnątrz JSONB wymusza użycie wyrażenia wydobywającego, takiego jak data->>'status'. Działa ono, ale zwraca typ text, wymaga rzutowania, a zwykły indeks B-tree tabeli nie obejmie go, chyba że utworzą Państwo indeks wyrażenia.

Jeśli filtrowanie jest kluczowe dla obciążenia, rzeczywista kolumna o odpowiednim typie ze zwykłym indeksem będzie prostsza i szybsza.

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

Indeks wyrażenia a rzeczywista kolumna

Indeks wyrażeń dla (data->>'status') może obsłużyć dokładnie taki filtr, ale ma istotne ograniczenia:

  • Predykat zapytania musi być identyczny z indeksowanym wyrażeniem — znak w znak. Zindeksowane data->>'status' nie pomoże w przypadku data#>>'{status}'.
  • Wartości są zwracane jako text, dlatego filtry zakresowe i numeryczne wymagają jawnych rzutowań, które również muszą odpowiadać indeksowi.
  • Należy utrzymywać osobny indeks dla każdego wyodrębnianego pola, a każdy z nich ponownie analizuje JSON podczas zapisu.

Wydzielenie pola do osobnej kolumny pozwala uniknąć wszystkich tych problemów: zapewnia standardowe typowanie, standardowe indeksy i standardowe statystyki optymalizatora.

Sygnał 2: potrzebna jest wydajność operacji zakresowych lub sortowania

Liczby i znaczniki czasu często padają ofiarą tego problemu. W JSONB są przechowywane jako wartości przypominające tekst, więc skanowanie zakresów i ORDER BY wymagają indeksu wyrażeń z odpowiednim typem, aby działać prawidłowo.

Jeśli sortują Państwo po danym polu lub filtrują po jego zakresie — na przykład podczas stronicowania według created_at albo filtrowania cen i kwot — należy wydzielić je do rzeczywistej kolumny typu timestamptz / numeric. Optymalizator otrzyma dokładne statystyki i przejrzysty indeks 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;

Sygnał 3: wykonywane są na nim złączenia lub grupowanie

Klucze obce i klucze grupowania niemal nigdy nie powinny znajdować się w JSONB.

  • Nie można zadeklarować rzeczywistego ograniczenia FOREIGN KEY dla data->>'user_id' — integralność referencyjna zostaje utracona.
  • Złączenia po wyodrębnionej wartości tekstowej blokują optymalizacje złączeń haszujących i scalających oraz wymuszają rzutowania.
  • GROUP BY data->>'category' nie może skutecznie korzystać ze statystyk kolumn, co pogarsza plany agregacji.

Jeśli dane pole łączy ze sobą wiersze, należy utworzyć dla niego pełnoprawną kolumnę o określonym typie.

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

Kiedy JSONB powinien pozostać JSONB

Wydzielenie pola do osobnej kolumny nie jest bezpłatne, dlatego należy pozostawić je w JSONB, gdy:

  • jest rzadkie — występuje tylko w niewielkiej części wierszy (wydzielenie utworzyłoby kolumnę w większości wypełnioną wartościami NULL);
  • jest rzadko używane w filtrach — jest odczytywane jako część całego dokumentu i nigdy nie jest używane w WHERE/JOIN;
  • jest nieprzewidywalne — klucze różnią się zależnie od dzierżawcy lub typu zdarzenia i nie można ich wyliczyć;
  • tworzy zagnieżdżoną strukturę, pobieraną jako jedna całość (np. obiekt ustawień).

To właśnie dla takiego długiego ogona zastosowań zaprojektowano JSONB. Nie należy go spłaszczać tylko dlatego, że spłaszczyli Państwo często używane pola.

GIN: druga możliwość

Przed wydzieleniem pola do osobnej kolumny warto sprawdzić, czy problemu nie rozwiązuje już indeks GIN na JSONB. GIN doskonale sprawdza się w zapytaniach sprawdzających zawieranie i istnienie kluczy wśród wielu nieprzewidywalnych kluczy.

GIN jest świetnym rozwiązaniem, gdy doraźnie wyszukują Państwo po wielu różnych kluczach JSON. Jest przesadą (i zwiększa koszt zapisu), gdy wszystkie zapytania korzystają z jednego lub dwóch konkretnych pól — w takim przypadku należy wydzielić te pola do osobnych kolumn.

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

Wskazówka ułatwiająca podjęcie decyzji

Szybka reguła dla każdego pola:

  • Często używane + selektywne + typowane (filtrowane, sortowane, używane w złączeniach lub jako klucz obcy) → należy wydzielić do rzeczywistej kolumny.
  • Doraźnie używane w wielu kluczach → należy pozostawić w JSONB i dodać indeks GIN.
  • Rzadkie / odczytywane jako dokument / rzadko wyszukiwane → należy pozostawić w JSONB bez dodatkowego indeksu.

Większość rzeczywistych schematów ma ostatecznie charakter hybrydowy: kilka wydzielonych kolumn oraz kolumna JSONB na całą resztę.

Bezpieczne wydzielanie pola

Aby wydzielić istniejące pole JSONB do osobnej kolumny, należy najpierw uzupełnić nową kolumnę jego wartościami, a następnie ją zindeksować. Wykonanie tych operacji etapami pozwala skrócić blokady i najpierw zweryfikować dane.

Należy pamiętać o rzutowaniu: tekst JSONB musi zostać przekonwertowany na typ docelowy. Trzeba też zdecydować, co zrobić z wierszami, w których brakuje klucza (w tym przypadku przyjmą wartość 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);

Utrzymywanie zgodności (albo usunięcie duplikatu)

Po wydzieleniu pola do osobnej kolumny mają Państwo dwie możliwości: usunąć to pole z JSONB, aby istniało jedno źródło prawdy, albo zachować oba i zagwarantować ich zgodność.

Jeśli oba pola pozostaną, najczystszym rozwiązaniem jest kolumna generowana: jest automatycznie wyliczana z JSONB i nie może się z nim rozjechać.

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

Szybkie sprawdzenie

Które pole jest najlepszym kandydatem do normalizacji i WYDZIELENIA z kolumny JSONB data?

Odpowiedz w interaktywnej lekcji

Podsumowanie

Wydzielają Państwo pole JSONB do rzeczywistej kolumny, gdy wymaga tego jego sposób użycia:

  • Ciągłe filtrowanie → kolumna o określonym typie i zwykły indeks są lepsze niż indeks wyrażeń.
  • Przeszukiwanie zakresu lub sortowanie → rzeczywisty typ numeric/timestamptz zapewnia przejrzyste indeksy B-tree i dokładne statystyki.
  • Złączenia / grupowanie / klucz obcy → tylko rzeczywista kolumna obsługuje klucze obce i dobre plany złączeń.

Pola należy pozostawić w JSONB, gdy są rzadkie, używane doraźnie w wielu kluczach lub odczytywane jako dokument — jeśli potrzebują Państwo wyszukiwania zawartości, należy dodać indeks GIN. Najlepszy projekt jest zwykle hybrydowy: kilka wydzielonych, często używanych kolumn oraz kolumna JSONB na całą resztę. Przy wydzielaniu pola należy ostrożnie uzupełnić dane, a następnie usunąć zduplikowany klucz albo użyć kolumny generowanej STORED, aby wartości nigdy się nie rozeszły.

Bezpłatny start

Ucz się SQL dzięki korepetycjom AI — za darmo

Pisz i uruchamiaj kod w przeglądarce, otrzymuj natychmiastową pomoc od korepetytora AI dostępnego 24/7 i kontynuuj naukę w sieci lub w aplikacji.

Kursy
22
Lekcje
88
Zacznij naukę bezpłatnieWyświetl pełny kurs

Często zadawane pytania

Czy lekcja „Kiedy normalizować dane z JSONB” jest bezpłatna?

Tak — pełny tekst „Kiedy normalizować dane z JSONB” jest dostępny za darmo tutaj w sieci. Aby ćwiczyć ją interaktywnie (wbudowany edytor kodu i tutor AI dostępny 24/7) i odblokować resztę kursu PostgreSQL Performance & Query Optimization, przejdź na CoddyKit PRO. Kurs PostgreSQL Performance & Query Optimization zawiera 4 lekcji w sumie.

Co nauczysz się w „Kiedy normalizować dane z JSONB”?

Rozpoznawaj wzorce dostępu, w których przeniesienie pól JSONB do rzeczywistych kolumn poprawia wydajność. Ćwiczysz PostgreSQL Performance & Query Optimization z praktycznym kodem, który uruchamiasz bezpośrednio w przeglądarce, a tutor AI dostępny 24/7 odpowiada na Twoje pytania podczas pracy nad lekcją.

Czy potrzebuję doświadczenia, aby zacząć PostgreSQL Performance & Query Optimization?

Nie wymagamy żadnego doświadczenia. PostgreSQL Performance & Query Optimization w CoddyKit jest strukturyzowany dla początkujących i zaawansowanych użytkowników, więc możesz zacząć tutaj lub od początku i uczyć się w swoim tempie. To lekcja 4 z 4.

Ile czasu zajmuje lekcja „Kiedy normalizować dane z JSONB”?

Większość lekcji CoddyKit trwa około 5–10 minut. Każda lekcja to mały, interaktywny krok, dzięki czemu robisz systematyczne postępy i zawsze wracasz dokładnie do tego samego miejsca — na webie i w aplikacji.

Czy mogę pisać i uruchamiać kod w tej lekcji PostgreSQL Performance & Query Optimization?

Tak. Każda lekcja PostgreSQL Performance & Query Optimization zawiera wbudowany edytor kodu, więc piszesz i uruchamiasz prawdziwy kod bezpośrednio w przeglądarce i od razu otrzymujesz sprzężenie zwrotne od AI — bez konfiguracji na komputerze.

Wszystkie lekcje w tym kursie

  1. Operatory JSONB i zapytania zawierania
  2. Indeksy GIN a indeksy wyrażeń dla JSONB
  3. Odpytywanie JSONB za pomocą JSONPath
  4. Kiedy normalizować dane z JSONB
← Powrót do PostgreSQL Performance & Query Optimization