EAV nomaiņa uz JSONB PostgreSQL

TL; DR: JSONB var ievērojami atvieglot datu bāzes shēmas izstrādi, neupurējot vaicājumu veiktspēju.

Ievads

Apskatīsim klasisku piemēru, iespējams, vienu no senākajiem veidiem, kā izmantot relāciju datu bāzes (Datu bāze): mums ir entitāte, un ir nepieciešams saglabāt noteiktus šīs entitātes īpašumus (atribūtus). Tomēr ne visas instances var būt ar vienādu īpašumu kopu, turklāt nākotnē ir iespējams pievienot vēl īpašumus.

Vienkāršākais risinājums šai problēmai ir izveidot kolonu datu bāzes tabulā katram īpašuma vērtībai un aizpildīt tikai tās, kas nepieciešamas konkrētai entitātes instancei. Lieliski! Problēma atrisināta… līdz brīdim, kad jūsu tabulā ir miljoni ierakstu un jums ir nepieciešams pievienot jaunu ierakstu.

Apskatīsim EAV paraugu (Entity-Attribute-Value), tas tiek izmantots diezgan bieži. Viena tabula satur entitātes (ierakstus), otra tabula satur īpašumu (atribūtu) nosaukumus, savukārt trešā tabula sasaista entitātes ar to atribūtiem un satur šo atribūtu vērtības pašreizējai entitātei. Tas dod iespēju uzturēt dažādus īpašumu komplektus dažādām objektiem, kā arī pievienot īpašumus “uz vietas”, nenomainot datu bāzes struktūru.

Tomēr es negribētu rakstīt šo piezīmi, ja nebūtu trūkumu EAV pieejā. Piemēram, lai iegūtu vienu vai vairākas entitātes, kurām ir 1 atribūts, vaicājumā ir nepieciešami 2 join (apvienojumi): pirmais – apvienojums ar atribūtu tabulu, otrais – apvienojums ar vērtību tabulu. Ja entitātei ir 2 atribūti, tad nepieciešami jau 4 join! Turklāt visi atribūti parasti tiek glabāti kā virkne, kas noved pie tipu pieskaņas, gan rezultātā, gan WHERE nosacījumā. Ja jūs rakstāt daudz vaicājumu, tas ir diezgan izšķērdīgi resursu ziņā.

Neskatoties uz šiem acīmredzamajiem trūkumiem, EAV jau sen tiek izmantots šādu problēmu risināšanai. 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 no PostgreSQL 9.4, tika pievienots JSONB datu tips, lai uzglabātu JSON bināros datus. Lai gan uzglabāšana šajā formātā parasti aizņem nedaudz vairāk vietas un laika nekā vienkāršs teksta JSON, operāciju izpilde ir daudz ātrāka. JSONB arī atbalsta indeksošanu, kas padara pieprasījumus vēl ātrākus.

JSONB datu tips ļauj mums nomainīt apgrūtināto EAV modeli, tikai pievienojot vienu JSONB kolonnu mūsu entitāšu tabulai, kas ievērojami vienkāršo datu bāzes projektēšanu. Tomēr daudzi apgalvo, ka tam jāietver veiktspējas samazinājums… Lūk, kāpēc es uzrakstīju šo rakstu.

Testa datu bāzes iestatīšana

Šim salīdzinājumam es izveidoju datu bāzi jaunā PostgreSQL 9.5 instalācijā par 80 dolāriem. DigitalOcean Ubuntu 14.04. Pēc dažādu parametru iestatīšanas postgresql.conf es palaidu šis skriptu, izmantojot psql. Lai attēlotu datus EAV formātā, 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ā – properties.

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 tika pievienoti 10 miljoni ierakstu tabulām (entity & entity_jsonb) un attiecīgi tika aizpildīti ar vienādiem datiem tabulās, kurās tiek izmantots EAV modelis un pieeja ar JSONB kolonnu – entity_jsonb.properties. Tādējādi mēs ieguvām vairākus dažādus datu tipus starp visiem īpašību kopumiem. Datu piemērs:

{
  id:          1
  name:        "Entity1"
  description: "Test entity no. 1"
  properties:  {
    color:        "sarkans"
    length:       120
    width:        3.1882420
    hassomething: true
    country:      "Beļģija"
  } 
}

Tātad, tagad mums ir vienādi dati divām versijām. Sāksim salīdzināt ieviešanu darbībā!

Dizaina vienkāršošana

Iepriekš tika minēts, ka datu bāzes dizains ir ievērojami vienkāršots: viena tabula, izmantojot JSONB kolonnu īpašībām, vietā trīs tabulu izmantošanai EAV. Bet kā tas atspoguļojas vaicājumos? Viens īpašības atjauninājums entitātei izskatās šādi:

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

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

Kā redzam, pēdējais vaicājums neizskatās vienkāršāks. Lai atjauninātu īpašuma vērtību JSONB objektā, mums jādara izmantot funkciju jsonb_set(), un mums jānodod mūsu jaunā vērtība kā JSONB objekts. Tomēr mums iepriekš nav jāzina nekādi identifikatori. Aplūkojot EAV piemēru, mums jāzina gan entity_id, gan entity_attribute_id, lai veiktu atjauninājumu. Ja vēlaties atjaunināt īpašību JSONB kolonnā, pamatojoties uz objekta nosaukumu, – to visu var veikt ar vienkāršu rindiņu.

Tagad izvēlēsimies šo entītiju, kuru mēs 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 = 'zils';

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

Es domāju, ka mēs varam piekrist, ka otrais ir īsāks (bez apvienošanās!), un attiecīgi vieglāk lasāms. Šeit uzvara JSONB! Mēs izmantojam JSON operatoru ->>, lai iegūtu krāsu kā tekstuālo vērtību no JSONB objekta. Pastāv arī otra pieeja, lai sasniegtu to pašu rezultātu JSONB modelī, izmantojot operatoru @>:

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

Tas ir nedaudz sarežģītāk: mēs pārbaudām, vai JSON objekts kolonnā satur objektu, kas atrodas labajā pusē no operatora @>. Mazāk lasāms, bet efektīvāks (skat. tālāk).

Vienkāršosim JSONB izmantošanu vēl vairāk, kad jums ir nepieciešams izvēlēties vairākus īpašumus vienlaikus. Šeit tiešām piemērots ir JSONB pieejas: mēs vienkārši izvēlamies īpašības kā papildu kolonnas mūsu rezultātu kopā bez nepieciešamības pēc apvienošanās:

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

Ar EAV jums būs nepieciešamas 2 apvienošanās katram īpašumam, ko vēlaties pieprasīt. Manuprāt, iepriekš minētie vaicājumi parāda lielu simplifikāciju datubāzes dizainā. Skatiet vairāk piemēru tam, kā rakstīt vaicājumus uz JSONB, iespējams, arī this rakstā.
Tagad ir pienācis laiks runāt par veiktspēju.

Veiktspēja

Lai salīdzinātu veiktspēju, es izmantoju EXPLAIN ANALYZE va pieprasījumos, lai aprēķinātu izpildes laiku. Katrs pieprasījums tika izpildīts vismaz trīs reizes, jo pirmajā reizē pieprasījumu plānotājam nepieciešams vairāk laika. Sākumā es veicu pieprasījumus bez jebkādiem indeksiem. Acīmredzot, tas deva priekšrocību JSONB, jo saistīšanās, kas nepieciešamas EAV, nevarēja izmantot indeksus (ārzemju atslēgu lauki nebija indeksēti). Pēc tam es izveidoju indeksu diviem ārējo atslēgu kolonnām EAV vērtību tabulā, kā arī indeksu GIN JSONB kolonnai.

Datu atjauninājumi parādīja sekojošus rezultātus pēc laika (ms). Lūdzu, ņemiet vērā, ka mērogs ir logaritmisks:

Redzam, ka JSONB ir daudz (> 50000-x) ātrāks nekā EAV, kad indeksi netiek izmantoti, kā minēts iepriekš. Kad mēs indeksējam kolonnas ar primārajām atslēgām, atšķirība gandrīz izzūd, bet JSONB joprojām ir 1,3 reizes ātrāks nekā EAV. Lūdzu, ņemiet vērā, ka indekss JSONB kolonnā šeit neietekmē kaut ko, jo mēs neizmantojam īpašību kolonnu vērtēšanas kritērijos.

Lai atlasītu datus pēc īpašības vērtības, mēs iegūstam šādus rezultātus (parasts mērogs):

Var pamanīt, ka JSONB atkal strādā ātrāk nekā EAV bez indeksiem, bet kad EAV ir ar indeksiem – tas tomēr darbojas ātrāk nekā JSONB. Bet tad es pamanīju, ka laiks JSONB pieprasījumiem bija vienāds, kas pamudināja mani domāt, ka GIN indekss nedarbojas. Izskatās, ka, izmantojot GIN indeksu kolonnā ar aizpildītām īpašībām, tas darbojas tikai, izmantojot iekļaušanas operatoru @>. Es to izmantoju jaunā testā, kas būtiski ietekmēja laiku: tikai 0,153 ms! Tas ir 15000 reizes ātrāk nekā EAV, un 25000 reizes ātrāk nekā operators ->>.

Domāju, ka tas bija pietiekami ātri!

DB tabulu izmērs

Salīdzināsim tabulu izmērus abos pieejas veidos. psql mēs varam parādīt visu tabulu un indeksu izmērus ar komandu dti+

EAV pieejas tabulu izmēri ir apmēram 3068 MB, bet indeksi – līdz 3427 MB, kas kopā veido 6,43 GB. Izmantojot pieeju ar JSONB, tiek izmantoti 1817 MB tabulai un 318 MB indeksiem, kopā 2,08 GB. Tas ir 3 reizes mazāk! Šis fakts mani nedaudz pārsteidza, jo mēs glabājam īpašību nosaukumus katrā JSONB objektā.

Tomēr skaitļi runā paši par sevi: EAV mēs uzglabājam 2 veselus ārējos atslēgas atribūtu vērtībai, kā rezultātā iegūstam 8 baitus papildu datu. Turklāt EAV visus atribūtu vērtības uzglabā tekstā, kamēr JSONB izmantos skaitliskas un loģiskas vērtības tur, kur tas ir iespējams, kā rezultātā iegūstam mazāku apjomu.

Secinājumi

Kopumā es domāju, ka īpašību saglabāšana JSONB formātā var ievērojami vienkāršot jūsu datu bāzes projektēšanu un apkopi. Ja jūs veicat daudz vaicājumu, tad viss, kas ir uzglabāts vienā tabulā ar entitāti, tiešām darbosies efektīvāk. Un tas, ka tas vienkāršo datu mijiedarbību, jau ir plus, bet arī rezultātā iegūtā datu bāze ir 3 reizes mazāka pēc apjoma.

Tāpat pēc veiktajiem testiem var izdarīt secinājumu, ka veiktspējas zudumi ir ļoti nenozīmīgi. Dažos gadījumos JSONB darbojas pat ātrāk nekā EAV, padarot to vēl labākus. Tomēr šis standarta tests, protams, neaptver visus aspektus (piemēram, entitātes ar ļoti lielu īpašību skaitu, būtisks esošo datu īpašību skaita pieaugums...), tādēļ, ja jums ir kādi ieteikumi par to, kā tos uzlabot, lūdzu, nevilcinieties atstāt komentārus!

Avots: habr.com