EAV aizstāšana ar JSONB pakalpojumā PostgreSQL

TL; DR: JSONB var ievērojami vienkāršot datu bāzes shēmas izstrādi, nezaudējot vaicājuma veiktspēju.

Ievads

Sniegsim klasisku piemēru vienam no senākajiem relāciju datu bāzes (datu bāzes) lietošanas gadījumiem pasaulē: mums ir entītija, un mums ir jāsaglabā noteikti šīs entītijas rekvizīti (atribūti). Taču ne visiem gadījumiem var būt vienāda rekvizītu kopa, un nākotnē var tikt pievienoti vairāki rekvizīti.

Vienkāršākais veids, kā atrisināt šo problēmu, ir izveidot kolonnu datu bāzes tabulā katrai rekvizīta vērtībai un vienkārši aizpildīt tos, kas nepieciešami konkrētai entītijas instancei. Lieliski! Problēma atrisināta... līdz jūsu tabulā ir miljoniem ierakstu un jums ir jāpievieno jauns ieraksts.

Apsveriet EAV modeli (Vienība-atribūts-vērtība), tas notiek diezgan bieži. Vienā tabulā ir entītijas (ieraksti), citā tabulā ir rekvizītu nosaukumi (atribūti), un trešā tabula saista entītijas ar to atribūtiem un satur šo atribūtu vērtību pašreizējai entītijai. Tas dod iespēju dažādiem objektiem izmantot dažādas rekvizītu kopas, kā arī pievienot rekvizītus, nemainot datu bāzes struktūru.

Tomēr es nerakstītu šo ziņu, ja EVA pieejai nebūtu negatīvu pusi. Tātad, piemēram, lai iegūtu vienu vai vairākas entītijas, kurām katrai ir 1 atribūts, vaicājumā ir nepieciešami 2 savienojumi: pirmais ir savienojums ar atribūtu tabulu, otrais ir savienojums ar vērtību tabulu. Ja entītijai ir 2 atribūti, tad nepieciešami 4 savienojumi! Turklāt visi atribūti parasti tiek saglabāti kā virknes, kā rezultātā tiek veikta tipa apraide gan rezultātam, gan klauzulai WHERE. Ja jūs rakstāt daudz vaicājumu, tas ir diezgan izšķērdīgs resursu izmantošanas ziņā.

Neskatoties uz šiem acīmredzamajiem trūkumiem, EAV jau sen ir izmantots, lai atrisinātu šāda veida problēmas. Tie bija neizbēgami trūkumi, un labākas alternatīvas vienkārši nebija.
Bet tad PostgreSQL parādījās jauna “tehnoloģija”...

Sākot ar PostgreSQL 9.4, JSONB datu tips tika pievienots JSON bināro datu glabāšanai. Lai gan JSON glabāšana šajā formātā parasti aizņem nedaudz vairāk vietas un laika nekā vienkārša teksta JSON, darbību veikšana ar to ir daudz ātrāka. JSONB atbalsta arī indeksēšanu, kas padara vaicājumus vēl ātrākus.

JSONB datu tips ļauj mums aizstāt apgrūtinošo EAV modeli, pievienojot tikai vienu JSONB kolonnu mūsu entītiju tabulai, ievērojami vienkāršojot datu bāzes dizainu. Bet daudzi iebilst, ka tam vajadzētu būt kopā ar produktivitātes samazināšanos... Tāpēc es arī uzrakstīju šo rakstu.

Testu datu bāzes iestatīšana

Šim salīdzinājumam es izveidoju datu bāzi, izmantojot jaunu PostgreSQL 9.5 instalāciju ar 80 $ būvējumu DigitalOcean Ubuntu 14.04 Pēc dažu parametru konfigurēšanas postgresql.conf failā es palaidu šis skripts, izmantojot psql. Lai sniegtu datus EAV formā, tika izveidotas šādas tabulas:

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

Zemāk ir tabula, kurā tiks glabāti tie paši dati, bet ar atribūtiem JSONB tipa kolonnā - īpašības.

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

Izskatās daudz vienkāršāk, vai ne? Pēc tam tas tika pievienots entītiju tabulām (organizācija & entity_jsonb) 10 miljoni ierakstu, un attiecīgi tabula tika aizpildīta ar tiem pašiem datiem, izmantojot EAV modeli un pieeju ar JSONB kolonnu - entity_jsonb.properties. Tādējādi mēs saņēmām vairākus dažādus datu tipus no visas rekvizītu kopas. Datu piemēri:

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

Tagad mums ir vienādi dati par abām iespējām. Sāksim salīdzināt implementācijas darbā!

Vienkāršojiet savu dizainu

Iepriekš tika teikts, ka datu bāzes dizains tika ievērojami vienkāršots: viena tabula, rekvizītu noteikšanai izmantojot JSONB kolonnu, nevis trīs tabulas EAV. Bet kā tas tiek atspoguļots pieprasījumos? Viena entītijas īpašuma atjaunināšana izskatās šādi:

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

Kā redzat, pēdējais pieprasījums nešķiet vienkāršāks. Lai atjauninātu rekvizīta vērtību JSONB objektā, mums ir jāizmanto funkcija jsonb_set(), un tai vajadzētu nodot mūsu jauno vērtību kā JSONB objektu. Tomēr mums nav iepriekš jāzina neviens identifikators. Aplūkojot EAV piemēru, mums ir jāzina gan entity_id, gan entity_attribute_id, lai veiktu atjaunināšanu. Ja vēlaties atjaunināt rekvizītu JSONB kolonnā, pamatojoties uz objekta nosaukumu, tas viss tiek darīts vienā vienkāršā rindā.

Tagad atlasīsim entītiju, kuru tikko atjauninājām, pamatojoties uz tās jauno krāsu:

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

Es domāju, ka varam piekrist, ka otrais ir īsāks (nav pievienoties!), tāpēc lasāmāks. Šeit uzvar JSONB! Mēs izmantojam operatoru JSON ->>, lai iegūtu krāsu kā teksta vērtību no JSONB objekta. Ir arī otrs veids, kā sasniegt tādu pašu rezultātu JSONB modelī, izmantojot @> operatoru:

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

Tas ir nedaudz sarežģītāk: mēs pārbaudām, vai JSON objekts tā rekvizītu kolonnā satur objektu, kas atrodas pa labi no operatora @>. Mazāk lasāms, produktīvāks (skatīt zemāk).

Padarīsim JSONB lietošanu vēl vienkāršāku, ja jums būs jāatlasa vairāki rekvizīti vienlaikus. Šeit patiešām parādās JSONB pieeja: mēs vienkārši atlasām rekvizītus kā papildu kolonnas mūsu rezultātu kopā, neizmantojot savienojumus:

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

Izmantojot EAV, jums būs nepieciešami 2 savienojumi katram īpašumam, kuru vēlaties vaicāt. Manuprāt, iepriekš minētie vaicājumi parāda lielu datu bāzes dizaina vienkāršošanu. Skatiet citus piemērus, kā rakstīt JSONB vaicājumus, arī iekšā šis pastu.
Tagad ir pienācis laiks runāt par sniegumu.

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

Lai salīdzinātu veiktspēju, es izmantoju IZSKAIDROT ANALIZĒT vaicājumos, lai aprēķinātu izpildes laiku. Katrs vaicājums tika izpildīts vismaz trīs reizes, jo vaicājumu plānotājam pirmajā reizē ir nepieciešams ilgāks laiks. Vispirms es izpildīju vaicājumus bez indeksiem. Acīmredzot tā bija JSONB priekšrocība, jo EAV nepieciešamajos savienojumos nevarēja izmantot indeksus (svešās atslēgas lauki netika indeksēti). Pēc tam es izveidoju indeksu 2 ārējo atslēgu kolonnās EAV vērtību tabulā, kā arī indeksu Džins JSONB kolonnai.

Datu atjauninājums uzrādīja šādus rezultātus laika izteiksmē (ms). Ņemiet vērā, ka skala ir logaritmiska:

Mēs redzam, ka JSONB ir daudz (> 50000-x) ātrāks par EAV, ja neizmantojat indeksus iepriekš minētā iemesla dēļ. Kad mēs indeksējam kolonnas ar primārajām atslēgām, atšķirība gandrīz pazūd, taču JSONB joprojām ir 1,3 reizes ātrāks nekā EAV. Ņemiet vērā, ka indeksam kolonnā JSONB šeit nav nekādas ietekmes, jo mēs neizmantojam rekvizītu kolonnu vērtēšanas kritērijos.

Lai atlasītu datus, pamatojoties uz īpašuma vērtību, mēs iegūstam šādus rezultātus (parastā mērogā):

Varat pamanīt, ka JSONB atkal darbojas ātrāk nekā EAV bez indeksiem, bet, kad EAV ar indeksiem, tas joprojām darbojas ātrāk nekā JSONB. Bet tad es redzēju, ka JSONB vaicājumu laiki bija vienādi, un tas mani pamudināja uz faktu, ka GIN indeksi nedarbojas. Acīmredzot, ja izmantojat GIN indeksu kolonnā ar aizpildītiem rekvizītiem, tas stājas spēkā tikai tad, ja tiek izmantots operators include @>. Es to izmantoju jaunā testā, un tam bija milzīga ietekme uz laiku: tikai 0,153 ms! Tas ir 15000 25000 reižu ātrāk nekā EAV un XNUMX XNUMX reižu ātrāk nekā ->> operators.

Es domāju, ka tas bija pietiekami ātri!

Datu bāzes tabulas lielums

Salīdzināsim tabulu izmērus abām pieejām. Psql mēs varam parādīt visu tabulu un indeksu lielumu, izmantojot komandu dti+

EAV pieejai tabulu izmēri ir aptuveni 3068 MB un indeksēti līdz 3427 MB, kopā 6,43 GB. JSONB pieeja izmanto 1817 MB tabulai un 318 MB indeksiem, kas ir 2,08 GB. Izrādās 3 reizes mazāk! Šis fakts mani nedaudz pārsteidza, jo mēs glabājam īpašumu nosaukumus katrā JSONB objektā.

Tomēr skaitļi runā paši par sevi: EAV mēs uzglabājam 2 veselas ārējās atslēgas katrai atribūta vērtībai, kā rezultātā tiek iegūti 8 baiti papildu datu. Turklāt EAV saglabā visas īpašuma vērtības kā tekstu, savukārt JSONB, ja iespējams, iekšēji izmantos skaitliskās un Būla vērtības, tādējādi radot mazāku nospiedumu.

Rezultāti

Kopumā es domāju, ka entītijas rekvizītu saglabāšana JSONB formātā var ievērojami atvieglot datu bāzes projektēšanu un uzturēšanu. Ja izpildāt daudz vaicājumu, tad, turot visu vienā tabulā ar entītiju, tas faktiski darbosies efektīvāk. Un tas, ka tas vienkāršo datu mijiedarbību, jau ir pluss, bet iegūtā datubāze ir 3 reizes mazāka apjoma ziņā.

Tāpat, pamatojoties uz veiktajām pārbaudēm, varam secināt, ka veiktspējas zudumi ir ļoti niecīgi. Dažos gadījumos JSONB ir pat ātrāks par EAV, padarot to vēl labāku. Tomēr šis etalons, protams, neaptver visus aspektus (piemēram, entītijas ar ļoti lielu rekvizītu skaitu, ievērojams esošo datu rekvizītu skaita pieaugums...), tādēļ, ja jums ir kādi ieteikumi, kā tos uzlabot , lūdzu, nekautrējieties atstāt komentāros!

Avots: www.habr.com