Nahrazení EAV za JSONB v PostgreSQL

TL; DR: JSONB může výrazně zjednodušit vývoj schématu databáze bez obětování výkonu dotazů.

úvod

Uveďme klasický příklad pravděpodobně jednoho z nejstarších případů použití na světě relační databáze (databáze): máme entitu a potřebujeme uložit určité vlastnosti (atributy) této entity. Ne všechny instance však mohou mít stejnou sadu vlastností a v budoucnu mohou být přidány další vlastnosti.

Nejjednodušší způsob, jak tento problém vyřešit, je vytvořit sloupec v tabulce databáze pro každou hodnotu vlastnosti a jednoduše vyplnit ty, které jsou potřebné pro konkrétní instanci entity. Skvělý! Problém vyřešen... dokud vaše tabulka nebude obsahovat miliony záznamů a nebudete muset přidat nový záznam.

Zvažte vzor EAV (Entita-Atribut-Hodnota), vyskytuje se poměrně často. Jedna tabulka obsahuje entity (záznamy), další tabulka obsahuje názvy vlastností (atributy) a třetí tabulka přiřazuje entity k jejich atributům a obsahuje hodnotu těchto atributů pro aktuální entitu. To vám dává možnost mít různé sady vlastností pro různé objekty a také přidávat vlastnosti za běhu bez změny struktury databáze.

Nepsal bych však tento příspěvek, pokud by přístup EVA neměl nějaké nevýhody. Takže například pro získání jedné nebo více entit, které mají každá 1 atribut, jsou v dotazu vyžadována 2 spojení: první je spojení s tabulkou atributů, druhé je spojení s tabulkou hodnot. Pokud má entita 2 atributy, jsou potřeba 4 spojení! Všechny atributy jsou navíc obvykle uloženy jako řetězce, což má za následek přetypování typu pro výsledek i klauzuli WHERE. Pokud píšete hodně dotazů, pak je to z hlediska využití zdrojů docela plýtvání.

Navzdory těmto zjevným nedostatkům se EAV již dlouho používá k řešení těchto typů problémů. To byly nevyhnutelné nedostatky a lepší alternativa prostě neexistovala.
Pak se ale v PostgreSQL objevila nová „technologie“...

Počínaje PostgreSQL 9.4 byl pro ukládání binárních dat JSON přidán datový typ JSONB. Ačkoli uložení JSON v tomto formátu obvykle zabere o něco více místa a času než prostý textový JSON, provádění operací na něm je mnohem rychlejší. JSONB také podporuje indexování, díky kterému jsou dotazy ještě rychlejší.

Datový typ JSONB nám umožňuje nahradit těžkopádný vzor EAV přidáním pouze jednoho sloupce JSONB do naší tabulky entit, což výrazně zjednodušuje návrh databáze. Mnozí ale tvrdí, že by to mělo být doprovázeno poklesem produktivity... Proto jsem napsal tento článek.

Nastavení testovací databáze

Pro toto srovnání jsem vytvořil databázi na čerstvé instalaci PostgreSQL 9.5 na sestavení za 80 $ DigitalOcean Ubuntu 14.04. Po nastavení některých parametrů v postgresql.conf jsem spustil tento skript pomocí psql. Pro prezentaci dat ve formě EAV byly vytvořeny následující tabulky:

CREATE TABLE entity ( 
  id           SERIAL PRIMARY KEY, 
  name         TEXT, 
  description  TEXT
);
CREATE TABLE entity_attribute (
  id          SERIAL PRIMARY KEY, 
  name        TEXT
);
CREATE TABLE entity_attribute_value (
  id                  SERIAL PRIMARY KEY, 
  entity_id           INT    REFERENCES entity(id), 
  entity_attribute_id INT    REFERENCES entity_attribute(id), 
  value               TEXT
);

Níže je tabulka, kde budou uložena stejná data, ale s atributy ve sloupci typu JSONB – vlastnosti.

CREATE TABLE entity_jsonb (
  id          SERIAL PRIMARY KEY, 
  name        TEXT, 
  description TEXT,
  properties  JSONB
);

Vypadá to mnohem jednodušeji, že? Poté byl přidán do tabulek entit (subjekty & entity_jsonb) 10 milionů záznamů, a proto byla tabulka naplněna stejnými daty pomocí vzoru EAV a přístupu se sloupcem JSONB - entity_jsonb.properties. V rámci celé sady vlastností jsme tedy obdrželi několik různých datových typů. Příklad údajů:

{
  id:          1
  name:        "Entity1"
  description: "Test entity no. 1"
  properties:  {
    color:        "red"
    lenght:       120
    width:        3.1882420
    hassomething: true
    country:      "Belgium"
  } 
}

Nyní tedy máme stejná data pro obě možnosti. Začněme porovnávat implementace v práci!

Zjednodušte svůj design

Již dříve bylo uvedeno, že návrh databáze byl značně zjednodušen: jedna tabulka pomocí sloupce JSONB pro vlastnosti namísto použití tří tabulek pro EAV. Jak se to ale odráží v žádostech? Aktualizace jedné vlastnosti entity vypadá takto:

-- EAV
UPDATE entity_attribute_value 
SET value = 'blue' 
WHERE entity_attribute_id = 1 
  AND entity_id = 120;

-- JSONB
UPDATE entity_jsonb 
SET properties = jsonb_set(properties, '{"color"}', '"blue"') 
WHERE id = 120;

Jak vidíte, poslední požadavek nevypadá jednodušeji. K aktualizaci hodnoty vlastnosti v objektu JSONB musíme použít funkci jsonb_set()a měl by předat naši novou hodnotu jako objekt JSONB. Nepotřebujeme však předem znát žádný identifikátor. Když se podíváme na příklad EAV, potřebujeme znát jak entity_id, tak entity_attribute_id, abychom mohli provést aktualizaci. Pokud chcete aktualizovat vlastnost ve sloupci JSONB na základě názvu objektu, pak se to vše provede na jednom jednoduchém řádku.

Nyní vyberte entitu, kterou jsme právě aktualizovali, na základě její nové barvy:

-- EAV
SELECT e.name 
FROM entity e 
  INNER JOIN entity_attribute_value eav ON e.id = eav.entity_id
  INNER JOIN entity_attribute ea ON eav.entity_attribute_id = ea.id
WHERE ea.name = 'color' AND eav.value = 'blue';

-- JSONB
SELECT name 
FROM entity_jsonb 
WHERE properties ->> 'color' = 'blue';

Myslím, že se shodneme na tom, že druhý je kratší (no join!), a tedy čitelnější. JSONB zde vyhrává! K získání barvy jako textové hodnoty z objektu JSONB používáme operátor JSON ->>. Existuje také druhý způsob, jak dosáhnout stejného výsledku v modelu JSONB pomocí operátoru @>:

-- JSONB 
SELECT name 
FROM entity_jsonb 
WHERE properties @> '{"color": "blue"}';

To je trochu složitější: zkontrolujeme, zda objekt JSON ve sloupci vlastností obsahuje objekt, který je napravo od operátoru @>. Méně čitelné, produktivnější (viz níže).

Udělejme si používání JSONB ještě jednodušší, když potřebujete vybrat více vlastností najednou. Zde skutečně přichází na řadu přístup JSONB: jednoduše vybereme vlastnosti jako další sloupce v naší sadě výsledků bez nutnosti spojení:

-- JSONB 
SELECT name
  , properties ->> 'color'
  , properties ->> 'country'
FROM entity_jsonb 
WHERE id = 120;

S EAV budete potřebovat 2 spojení pro každou vlastnost, kterou chcete dotazovat. Podle mého názoru výše uvedené dotazy ukazují velké zjednodušení v návrhu databáze. Podívejte se na další příklady, jak psát dotazy JSONB, také v tohle pošta.
Nyní je čas mluvit o výkonu.

Производительность

Pro srovnání výkonu jsem použil VYSVĚTLIT ANALÝZU v dotazech pro výpočet doby provedení. Každý dotaz byl proveden alespoň třikrát, protože plánovač dotazů poprvé trvá déle. Nejprve jsem spustil dotazy bez jakýchkoli indexů. Je zřejmé, že to byla výhoda JSONB, protože spojení požadovaná pro EAV nemohla používat indexy (pole cizích klíčů nebyla indexována). Poté jsem vytvořil index na 2 sloupcích cizího klíče tabulky hodnot EAV a také index GIN pro sloupec JSONB.

Aktualizace dat ukázala následující výsledky z hlediska času (v ms). Všimněte si, že měřítko je logaritmické:

Vidíme, že JSONB je mnohem (> 50000-x) rychlejší než EAV, pokud nepoužíváte indexy z výše uvedeného důvodu. Když indexujeme sloupce primárními klíči, rozdíl téměř zmizí, ale JSONB je stále 1,3krát rychlejší než EAV. Všimněte si, že index ve sloupci JSONB zde nemá žádný vliv, protože v hodnotících kritériích nepoužíváme sloupec vlastností.

Pro výběr dat na základě hodnoty vlastnosti získáme následující výsledky (normální měřítko):

Můžete si všimnout, že JSONB opět funguje rychleji než EAV bez indexů, ale když EAV s indexy, stále funguje rychleji než JSONB. Ale pak jsem viděl, že časy pro JSONB dotazy byly stejné, to mě přivedlo k tomu, že GIN indexy nefungují. Pokud použijete index GIN na sloupec s naplněnými vlastnostmi, projeví se pouze při použití operátoru include @>. Použil jsem to v novém testu a mělo to obrovský dopad na čas: pouze 0,153 ms! To je 15000 25000krát rychlejší než EAV a XNUMX XNUMXkrát rychlejší než operátor ->>.

Myslím, že to bylo dost rychlé!

Velikost tabulky databáze

Porovnejme velikosti tabulek pro oba přístupy. V psql můžeme pomocí příkazu zobrazit velikost všech tabulek a indexů dti+

Pro přístup EAV jsou velikosti tabulek kolem 3068 MB a indexy až 3427 MB, celkem 6,43 GB. Přístup JSONB využívá 1817 MB pro tabulku a 318 MB pro indexy, což je 2,08 GB. Vyjde to 3x méně! Tato skutečnost mě trochu překvapila, protože názvy vlastností ukládáme do každého objektu JSONB.

Čísla však stále mluví sama za sebe: v EAV ukládáme 2 celočíselné cizí klíče na hodnotu atributu, což má za následek 8 bajtů dodatečných dat. EAV navíc ukládá všechny hodnoty vlastností jako text, zatímco JSONB bude interně používat číselné a booleovské hodnoty, kde je to možné, což má za následek menší půdorys.

Výsledky

Celkově si myslím, že uložení vlastností entity ve formátu JSONB může značně usnadnit navrhování a údržbu vaší databáze. Pokud spouštíte velké množství dotazů, pak udržování všeho ve stejné tabulce jako entita bude ve skutečnosti fungovat efektivněji. A to, že se tím zjednoduší interakce mezi daty je už plus, ale výsledná databáze je objemově 3x menší.

Také na základě provedených testů můžeme usoudit, že ztráty výkonu jsou velmi nevýznamné. V některých případech je JSONB dokonce rychlejší než EAV, takže je ještě lepší. Tento benchmark však samozřejmě nepokrývá všechny aspekty (např. entity s velmi velkým počtem vlastností, výrazný nárůst počtu vlastností existujících dat,...), takže pokud máte nějaké návrhy, jak je zlepšit , klidně zanechte v komentářích!

Zdroj: www.habr.com