PostgreSQL jsonb: operátory, GIN indexy a omezení

Používej jsonb, ne json, a jen pro data, jejichž tvar se skutečně mění: payloady webhooků, nastavení jednotlivých zákazníků, atributy produktů, které se liší podle kategorie. Pole vytáhneš jako text přes ->>, zda dokument obsahuje daný fragment, zjistíš přes @> a index postavíš jako GIN s jsonb_path_ops, pokud většinou používáš @>. Zbytek průvodce jsou detaily, které rozhodují, jestli toto řešení dobře zestárne, a případy, kdy by obyčejný sloupec byl lepší.

Varování zkráceně: jsonb je dobrý druhý nástroj a špatný první. Cokoli, podle čeho filtruješ, joinuješ, řadíš nebo co musí být unikátní, patří do skutečného sloupce. Sloupec s dokumentem je pro dlouhý chvost polí, která čteš celá a jen zřídka v nich hledáš.

json a jsonb: v čem je rozdíl#

PostgreSQL má dva typy JSON a chovají se jinak. json uloží přesný text, který mu dáš, a při každém přístupu ho znovu parsuje. jsonb parsuje jednou, ukládá rozloženou binární podobu a podporuje indexy. Téměř vždy chceš jsonb.

Čím za jsonb platíš:

• Mezery a pořadí klíčů se neuchovávají. {"b": 1, "a": 2} se vrátí jako {"a": 2, "b": 1}, klíče jsou seřazené nejdřív podle délky a pak abecedně.
• Duplicitní klíče se sloučí. Pokud se klíč objeví dvakrát, vyhrává poslední hodnota.
• Vkládání je o něco pomalejší kvůli převodu.

Důvody zvolit json jsou jen dva: potřebuješ původní text bajt po bajtu (auditní log přesně toho, co poslala třetí strana), nebo hodnotu nikdy nečteš. I tehdy je surové tělo často srozumitelnější uložit do sloupce text, protože je z něj jasnější záměr.

Čísla si zaslouží zmínku. jsonb ukládá čísla jako PostgreSQL numeric, takže velká celá čísla a desetinná čísla přežijí beze ztrát, lépe, než zvládne JavaScript. Háček je na výstupu: klient, který výsledek parsuje do typu double, ztratí přesnost nad 2^53, a to je chyba klienta, ne databáze.

Sloupec se deklaruje jako každý jiný:

CREATE TABLE app.events (  id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,  tenant_id   bigint      NOT NULL,  kind        text        NOT NULL,  created_at  timestamptz NOT NULL DEFAULT now(),  payload     jsonb       NOT NULL DEFAULT '{}');INSERT INTO app.events (tenant_id, kind, payload) VALUES  (7, 'signup',  '{"plan":"pro","source":"ad","tags":["beta","eu"],"user":{"id":41,"email":"a@example.com"}}'),  (7, 'invoice', '{"total":19.90,"currency":"USD","paid":false}');

Všimni si, jak je tabulka postavená: tenant_id, kind a created_at jsou sloupce, protože je používá každý dotaz, a v payload je jen proměnlivá část. Celý návrh stojí na tomto rozdělení.

Operátory, které budeš používat denně#

Jsou dvě rodiny: operátory, které hodnotu vytáhnou, a operátory, které ji testují. Snadno se pletou, protože vypadají podobně.

Šipka s jedním hrotem nechá hodnotu jako jsonb, takže můžeš řetězit dál. Šipka se dvěma hroty dá obyčejný text, který chceš na konci řetězu nebo v klauzuli WHERE. Celé číslo vpravo vybere prvek pole, počítá se od nuly, a záporné číslo počítá od konce.

SELECT payload -> 'user' ->> 'email'      AS email,       payload -> 'tags' ->> 0            AS first_tag,       (payload #>> '{user,id}')::bigint  AS user_idFROM app.eventsWHERE kind = 'signup';

Dvě chování lidi překvapí. Zaprvé, chybějící klíč dá SQL NULL, ne chybu, což je pohodlné, ale schovává překlepy: payload ->> 'plna' tiše nevrátí nic. Zadruhé, JSON null a chybějící klíč jsou dvě různé věci. '{"a": null}'::jsonb -> 'a' vrací hodnotu jsonb null, což není SQL NULL; ->> na ní už SQL NULL vrací. Pokud tvoje logika ten rozdíl řeší, testuj operátorem ? z tabulky níže.

PostgreSQL 14 přidal subscripting, takže funguje i payload['user']['email'] a v UPDATE se čte přirozeněji. Vrací jsonb, stejně jako ->.

Testovací operátory

Vracejí boolean a používají se ve WHERE.

Obsažení (containment) se vyplatí pořádně pochopit, protože indexy jsou postavené kolem tohoto operátoru:

-- Events whose payload includes this fragmentSELECT id FROM app.events WHERE payload @> '{"plan":"pro"}';-- Nested: the fragment must have the same structureSELECT id FROM app.events WHERE payload @> '{"user":{"id":41}}';-- Arrays: contains the element, in any positionSELECT id FROM app.events WHERE payload @> '{"tags":["beta"]}';-- A key exists, whatever its valueSELECT id FROM app.events WHERE payload ? 'currency';

Obsažení porovnává strukturu, ne text. Fragment {"tags":["beta"]} sedí na dokument s ["eu","beta","x"], protože na pořadí v poli ani na dalších prvcích nezáleží. Nesedí ale na dokument, kde je "beta" zanořené o úroveň hlouběji. A operátor ? se dívá jen na klíče nejvyšší úrovně; email uvnitř user nenajde. Otazník se také může střetnout se zástupnými znaky parametrů v některých driverech - známý případ je JDBC driver pro Javu - a pak pomůžou funkce jsonb_exists, jsonb_exists_any a jsonb_exists_all.

Úprava dokumentu#

Změna jednoho pole je místo, kde se jsonb nejméně podobá obyčejnému sloupci. Úprava na místě neexistuje; sestavíš novou hodnotu.

-- Add or overwrite top-level keys (right side wins)UPDATE app.events SET payload = payload || '{"paid":true}' WHERE id = 2;-- Set a nested path; the last argument creates the key if it is missingUPDATE app.eventsSET payload = jsonb_set(payload, '{user,email}', '"new@example.com"', true)WHERE id = 1;-- Remove a key, or a pathUPDATE app.events SET payload = payload - 'source' WHERE id = 1;UPDATE app.events SET payload = payload #- '{user,email}' WHERE id = 1;

Nová hodnota v jsonb_set musí být sama platné jsonb, proto má řetězec výše dvě vrstvy uvozovek: vnitřní z něj dělají JSON řetězec. Zapomenout na to je nejčastější chyba s touto funkcí. || je povrchní - nahradí celý vnořený objekt místo toho, aby se s ním sloučil - takže na hluboké slučování potřebuješ jsonb_set nebo kód aplikace.

Dokumenty skládej z řádků pomocí jsonb_build_object a jsonb_agg a opačným směrem je rozbaluj funkcemi, které vracejí množiny řádků:

-- One row per tagSELECT e.id, t.tagFROM app.events e,     jsonb_array_elements_text(e.payload -> 'tags') AS t(tag)WHERE e.kind = 'signup';-- One row per top-level keySELECT key, value FROM app.events e, jsonb_each(e.payload) WHERE e.id = 2;-- Build a document from a querySELECT jsonb_build_object('tenant', tenant_id, 'events', jsonb_agg(kind))FROM app.events GROUP BY tenant_id;

Na složitější struktury umožňuje jsonpath (jsonb_path_query, @?, @@) zapsat filtr jako $.items[*] ? (@.qty > 5) jedním výrazem. Je to silný nástroj a stojí za to vědět, že existuje, ale čitelný je jen pro ty, kdo s ním pracovali, takže sáhni po něm, když se řetězy -> stanou nepřehlednými, ne ve výchozím stavu. PostgreSQL 17 přidal také standardní SQL JSON_TABLE, který promění dokument v řádky jedinou klauzulí; než se na něj spolehneš, zkontroluj verzi serveru.

Indexování jsonb pomocí GIN#

Bez indexu čte každý filtr payload @> ... celou tabulku. GIN index (generalised inverted) to řeší tím, že indexuje klíče a hodnoty uvnitř dokumentu, stejně jako rejstřík v knize přiřazuje slova ke stránkám. Chceš-li nejdřív obecný přehled, PostgreSQL indexes: types, order and cost ukazuje, kde GIN mezi typy indexů stojí.

Existují dvě třídy operátorů a na výběru záleží:

-- Default: also answers key-existence queriesCREATE INDEX events_payload_gin ON app.events USING gin (payload);-- Smaller and quicker, containment and jsonpath onlyCREATE INDEX events_payload_path ON app.events USING gin (payload jsonb_path_ops);

Praktické pravidlo: pokud jsou všechny tvoje dotazy @>, použij jsonb_path_ops. Hashuje celou cestu k hodnotě místo toho, aby indexoval klíče a hodnoty zvlášť, takže je výrazně menší a lépe zvládá obsažení na častém klíči. Pokud potřebuješ ?, abys zjistil, zda klíč existuje, potřebuješ výchozí třídu. Obě najednou jdou, ale dva GIN indexy na jednom sloupci jsou vysoká cena za pohodlí.

Ověř, že ho plánovač skutečně použije:

EXPLAIN (ANALYZE, BUFFERS)SELECT id FROM app.events WHERE payload @> '{"plan":"pro"}';

Chceš vidět Bitmap Index Scan nad GIN indexem, který zásobuje Bitmap Heap Scan. GIN vždy dává bitmapové skeny, nikdy obyčejný index scan. EXPLAIN ANALYZE on a slow query čte takové plány řádek po řádku.

Co GIN index nedělá

Odtud pochází většina zklamání.

• Nepomáhá porovnáním přes `->>`. WHERE payload ->> 'plan' = 'pro' nemůže použít GIN index výše, protože to není test obsažení. Přepiš to na payload @> '{"plan":"pro"}' nebo vytvoř index nad výrazem (další oddíl).
• Nepomáhá rozsahům ani řazení. GIN index se nedá zeptat na „total větší než 100“ ani podle pole řadit pomocí ORDER BY.
• Pomalu se zapisuje. GIN používá čekající seznam (fastupdate) pro dávkové vkládání, což průměr vyhladí, ale občasné vložení zaplatí za vyprázdnění a vacuum má víc práce. Tabulka s intenzivním vkládáním a velkým GIN indexem to ukáže na latenci zápisu.
• Může být velký. Několikanásobek velikosti ekvivalentního B-tree je běžný. Na malém serveru soupeří o paměť s tvými horkými daty.
• Plánovač nemá statistiky o vnitřku dokumentu. U @> odhaduje selektivitu, takže odhady bývají špatné a špatný odhad může vybrat špatný plán. Je to známé omezení, ne chyba konfigurace.

GIN index nad jsonb je univerzální nástroj, který dobře odpovídá na otázku „najdi dokumenty obsahující X“. Pro pole, podle kterého neustále filtruješ nebo řadíš, chceš něco přesnějšího.

Indexy nad výrazem pro pole, na která se opravdu ptáš#

Když jedno nebo dvě pole nesou většinu provozu, indexuj je přímo B-tree nad výrazem. Je menší, levnější na údržbu, umí rozsahy a řazení a dává plánovači skutečné statistiky.

-- Equality on a text fieldCREATE INDEX events_plan ON app.events ((payload ->> 'plan'));-- Range on a numeric field: cast inside the indexCREATE INDEX events_total ON app.events (((payload ->> 'total')::numeric))  WHERE kind = 'invoice';-- Unique within a tenantCREATE UNIQUE INDEX events_user_once ON app.events (tenant_id, (payload #>> '{user,id}'))  WHERE kind = 'signup';

Dvojité závorky jsou povinné: vnější dvojice je seznam sloupců indexu, vnitřní označuje výraz. Dotaz musí výraz zopakovat přesně, včetně přetypování. Pokud je index na ((payload ->> 'total')::numeric), pak WHERE payload ->> 'total' > '100' porovnává text a nepoužije nic. Je to stejná past, jaká je popsaná pro obyčejné funkce v průvodci indexy, a částečná podmínka WHERE kind = 'invoice' zmenší index na řádky, které dané pole vůbec mají.

Indexy nad výrazem se dobře doplňují s omezením CHECK, aby typ, na který přetypováváš, byl opravdu zaručený:

ALTER TABLE app.events  ADD CONSTRAINT invoice_total_numeric  CHECK (kind <> 'invoice' OR jsonb_typeof(payload -> 'total') = 'number');

Bez něj stačí jediný řádek s "total": "n/a" a přetypování selže, a to uprostřed dotazu, ne při vložení. Omezení jsou nejblíž schématu uvnitř dokumentu, jak se dá dojít, a jsou levná.

Povýšení pole na skutečný sloupec#

Jakmile je pole v každém dotazu, přestaň ho vytahovat. PostgreSQL 12 a novější umí držet skutečný sloupec v souladu s dokumentem:

ALTER TABLE app.events  ADD COLUMN plan text GENERATED ALWAYS AS (payload ->> 'plan') STORED;CREATE INDEX events_plan_col ON app.events (plan);

Sloupec se počítá při zápisu, ukládá se na disk, indexuje se jako každý jiný a objeví se v SELECT *. Přímo do něj psát nejde; měníš dokument a on ho následuje. Na existující velké tabulce přidání uloženého generovaného sloupce tabulku přepíše, takže ber to jako migraci se zámkem, ne jako běžný příkaz - bezpečný postup je v schema migrations without downtime.

Alternativou je obyčejný sloupec plněný aplikací nebo backfillem, kdy dokument drží kopii, dokud ji neodstraníš. Je to víc práce a víc flexibility: můžeš měnit typy, přidat NOT NULL, přidat cizí klíč. Generované sloupce cizími klíči být nemohou.

Uživatelé MySQL ten vzor poznají - jedině takhle MySQL JSON vůbec indexuje - a MySQL JSON columns popisuje obdobu. V PostgreSQL je to volitelné, protože existují indexy nad výrazem, ale generovaný sloupec dělá schéma upřímným v tom, která pole jsou důležitá.

Co jsonb stojí na výkonu#

jsonb má vlastnost, která lidi překvapí při první aktualizaci vytížené tabulky: každá změna dokumentu přepíše celou hodnotu. PostgreSQL nikdy neupravuje jsonb po částech. Přepnutí jedné booleovské hodnoty ve 40 KB dokumentu zapíše novou 40 KB verzi řádku a stará čeká na vacuum.

Úložiště funguje takto:

• Hodnoty nad zhruba 2 KB se komprimují a pokud jsou pořád velké, přesunou se z řádku do tabulky TOAST. Dotaz, který čte jedno pole velkého dokumentu, ho musí přesto načíst a rozbalit celý.
• Aktualizace hodnoty jsonb v TOAST zapíše novou hodnotu do TOAST. Stará zůstává, dokud ji vacuum neodstraní.
• Aktualizace kteréhokoli sloupce řádku, i toho, který s dokumentem nesouvisí, může řádek také přepsat; heap-only aktualizace jsou možné jen tehdy, když se nezměnil žádný indexovaný sloupec.

Důsledky v pořadí, jak často kousnou:

1. Horké čítače uvnitř dokumentu jsou špatný nápad. jsonb_set(payload, '{views}', ...) při každém zobrazení stránky vytváří bloat nepřiměřený uložené informaci. Čítače drž v samostatném celočíselném sloupci nebo tabulce.
2. Velké dokumenty, které se často mění, patří do více řádků. Rozděl 200 KB blob, kde se na požadavek změní jedno pole, na řádky podle podentit.
3. Široké dokumenty škodí čtení, které potřebuje jen jedno pole. Úzká tabulka s často čtenými poli jako sloupci a zbytkem v payload dopadne líp než tabulka, která je celá payload.
4. Vacuum má víc práce. V vacuum and bloat se dozvíš, jak poznat, že se mrtvé tuply hromadí rychleji, než je autovacuum uklízí; tabulky jsonb s častými aktualizacemi jsou běžným zdrojem.

Pevný limit je 1 GB na hodnotu, ke kterému bys se neměl ani přiblížit. Praktický je mnohem nižší: nad několik set kilobajtů je dokument soubor a správným domovem pro soubor je úložiště souborů.

Kdy je jsonb špatný nástroj#

Poctivý test: pokud umíš vypsat pole, jejich typy a to, která jsou povinná, máš schéma, a tabulky PostgreSQL jsou nejlepší nástroj na schéma, jaký máš. jsonb je špatná volba v těchto případech.

• Relační data v přestrojení. Pole jsonb s řádky objednávky uvnitř orders nemůže mít cizí klíč na produkty, nedá se joinovat bez rozbalení a nezaručí, že produkt existuje. Použij podřízenou tabulku.
• Pole, podle kterých filtruješ, řadíš nebo agreguješ. Sloupec má statistiky, B-tree, typ a omezení. Cesta uvnitř dokumentu nemá nic z toho bez další práce a SUM((payload ->> 'total')::numeric) přes miliony řádků je pomalejší než SUM(total).
• Cokoli, co musí být unikátní nebo povinné. NOT NULL, UNIQUE, REFERENCES a CHECK na sloupci jsou jeden řádek. Na poli v dokumentu jsou to indexy nad výrazem a ručně psané kontroly a aplikace na jednu zapomene.
• Často aktualizované malé hodnoty. Čítače, příznaky, časová razítka a stavy, které se mění skoro při každém požadavku, platí výše popsané přepisování bez jakéhokoli užitku.
• Velký binární obsah. Obrázky v base64 uvnitř JSONu jsou tři čtvrtě megabajtu zbytečného parsování. Soubor dej do objektového úložiště a URL do řádku.
• Celá aplikace bez schématu. Pokud má tabulka jeden id a jeden sloupec data, postavil jsi dokumentovou databázi uvnitř relační, s horšími nástroji než dokumentová a bez záruk relační. Může to být vědomá volba; PostgreSQL or MongoDB rozebírá, kdy se hodí spíš druhý engine, a MongoDB indexes and schema design ukazuje, jak vypadá alternativa.

A případy, kdy je to správný nástroj:

• Payloady od třetích stran, jejichž tvar neovládáš, uložené tak, jak přišly, a čtené většinou celé.
• Uživatelsky definovaná pole nebo nastavení jednotlivých tenantů, kde má každý řádek jiné klíče.
• Řídké atributy: katalog, kde má každá kategorie tucet vlastností a žádné dvě kategorie je nesdílejí.
• Záznamy událostí a auditu, zapsané jednou a čtené při ladění, se stabilními poli (tenant_id, kind, created_at) jako obyčejnými sloupci.
• Prototypy, s plánem povýšit pole, která přežijí, na sloupce.

Návyk, který funguje: začni sloupci pro vše, co znáš, přidej jeden sloupec jsonb pojmenovaný podle obsahu (attributes, metadata, raw_payload) a pole z něj přesuň v den, kdy se podruhé objeví ve WHERE.

jsonb na malém serveru#

Na malé instanci se výše uvedené náklady projeví v paměti a na disku, ne v CPU. GIN index plus TOAST plus bloat z aktualizací mohou velikost tabulky zdvojnásobit a nadbytečné stránky vytlačí tvoji pracovní sadu ze shared_buffers. Zkontroluj velikosti před přidáním indexu a po něm:

SELECT pg_size_pretty(pg_table_size('app.events'))   AS table_and_toast,       pg_size_pretty(pg_indexes_size('app.events')) AS indexes;SELECT relname, pg_size_pretty(pg_relation_size(indexrelid)) AS size, idx_scanFROM pg_stat_user_indexes WHERE relid = 'app.events'::regclass;

Indexy na naplněné tabulce vytvářej přes CREATE INDEX CONCURRENTLY, aby se nezablokoval zápis, a pro session nejdřív zvyš maintenance_work_mem - budování GIN je náročné na paměť. Pokud držíš výchozí GIN index i indexy nad výrazem, po týdnu zkontroluj idx_scan a zahoď ten, který plánovač ignoruje. PostgreSQL tuning for small servers popisuje nastavení paměti, na kterém záleží, zda to všechno zůstane v cache, a PostgreSQL with pg_stat_statements ti ukáže, které dotazy na jsonb stojí nejvíc, než přidáš index ne tam, kam patří.

Na RE:NODE je server PostgreSQL samostatný tarif s vlastními přihlašovacími údaji, takže vytvořit a vyzkoušet index na kopii stojí malý tarif, ne riziko pro ostrou databázi. Před velkým budováním indexu nebo migrací, která přepisuje tabulku, udělej backup; sloty jsou součástí tarifu a postup ukazuje průvodce databázemi a zálohami.

FAQ#

Mám v PostgreSQL použít json, nebo jsonb?

Téměř vždy jsonb. Dá se indexovat, rychleji se dotazuje a odstraní duplicitní klíče. json použij, jen když musíš zachovat původní text včetně mezer a pořadí klíčů.

Proč můj dotaz na jsonb nepoužívá GIN index?

Nejčastěji proto, že dotaz používá ->> nebo -> s = místo @>. GIN index odpovídá na testy obsažení a existence klíče, ne na porovnání vytažených hodnot. Přepiš dotaz na @> nebo vytvoř B-tree index nad výrazem, který přesně odpovídá výrazu v dotazu.

jsonb_ops, nebo jsonb_path_ops?

jsonb_path_ops, pokud používáš jen @> a operátory jsonpath: je menší a obvykle rychlejší. Výchozí jsonb_ops zvol, pokud potřebuješ i ?, ?| nebo ?& na testování klíčů.

Je jsonb pomalejší než obyčejné sloupce?

U polí, podle kterých filtruješ a agreguješ, ano: chybí statistiky po polích, řádky jsou širší a každá aktualizace přepíše celou hodnotu. Na uložení a vrácení dokumentu vcelku je rozdíl malý. Cena je v tom, co po něm chceš.

Dá se dát cizí klíč nebo unikátní omezení na pole uvnitř jsonb?

Přímo ne. Unikátnost zajistí unikátní index nad výrazem a tvar dokumentu může hlídat omezení CHECK. Cizí klíč potřebuje skutečný sloupec, takže pole vytáhni generovaným sloupcem nebo ho přesuň do samotné tabulky.

Jak velká může být hodnota jsonb?

Limit je 1 GB, ale výkon klesá dlouho předtím. Hodnoty nad zhruba 2 KB se komprimují nebo přesouvají do úložiště TOAST a každá aktualizace přepíše celou hodnotu. Často aktualizované dokumenty drž malé a soubory ukládej do objektového úložiště.