Pinapalitan ang EAV ng JSONB sa PostgreSQL

TL; DR: Ang JSONB ay maaaring lubos na gawing simple ang pagbuo ng database schema nang hindi sinasakripisyo ang pagganap ng query.

Pagpapakilala

Magbigay tayo ng isang klasikong halimbawa ng malamang na isa sa mga pinakalumang kaso ng paggamit sa mundo ng isang relational database (database): mayroon tayong entity, at kailangan nating i-save ang ilang partikular na katangian (attribute) ng entity na ito. Ngunit hindi lahat ng mga instance ay maaaring may parehong hanay ng mga katangian, at higit pang mga katangian ang maaaring idagdag sa hinaharap.

Ang pinakamadaling paraan upang malutas ang problemang ito ay ang lumikha ng isang column sa talahanayan ng database para sa bawat value ng property, at punan lamang ang mga kailangan para sa isang partikular na halimbawa ng entity. Malaki! Nalutas ang problema... hanggang sa ang iyong talahanayan ay naglalaman ng milyun-milyong talaan at kailangan mong magdagdag ng bagong tala.

Isaalang-alang ang EAV pattern (Entity-Attribute-Value), madalas itong nangyayari. Ang isang talahanayan ay naglalaman ng mga entity (mga talaan), ang isa pang talahanayan ay naglalaman ng mga pangalan ng ari-arian (mga katangian), at ang isang ikatlong talahanayan ay nag-uugnay ng mga entidad sa kanilang mga katangian at naglalaman ng halaga ng mga katangiang iyon para sa kasalukuyang entity. Nagbibigay ito sa iyo ng kakayahang magkaroon ng iba't ibang hanay ng mga katangian para sa iba't ibang mga bagay, at magdagdag din ng mga katangian nang mabilisan nang hindi binabago ang istraktura ng database.

Gayunpaman, hindi ko isusulat ang post na ito kung walang ilang mga downsides sa EVA diskarte. Kaya, halimbawa, upang makakuha ng isa o higit pang mga entity na mayroong 1 attribute bawat isa, 2 join ang kinakailangan sa query: ang una ay isang join sa attribute table, ang pangalawa ay isang join sa values ​​table. Kung may 2 attribute ang isang entity, kailangan ng 4 na pagsali! Bilang karagdagan, ang lahat ng mga katangian ay karaniwang naka-imbak bilang mga string, na nagreresulta sa uri ng pag-cast para sa parehong resulta at ang sugnay na WHERE. Kung sumulat ka ng maraming mga query, kung gayon ito ay lubos na aksaya sa mga tuntunin ng paggamit ng mapagkukunan.

Sa kabila ng mga halatang pagkukulang na ito, matagal nang ginagamit ang EAV upang malutas ang mga ganitong uri ng problema. Ang mga ito ay hindi maiiwasang mga pagkukulang, at walang mas mahusay na alternatibo.
Ngunit pagkatapos ay lumitaw ang isang bagong "teknolohiya" sa PostgreSQL...

Simula sa PostgreSQL 9.4, idinagdag ang uri ng data ng JSONB upang mag-imbak ng binary data ng JSON. Bagama't ang pag-iimbak ng JSON sa format na ito ay karaniwang tumatagal ng kaunting espasyo at oras kaysa sa simpleng text na JSON, ang pagsasagawa ng mga operasyon dito ay mas mabilis. Sinusuportahan din ng JSONB ang pag-index, na ginagawang mas mabilis ang mga query.

Ang uri ng data ng JSONB ay nagbibigay-daan sa amin na palitan ang masalimuot na pattern ng EAV sa pamamagitan ng pagdaragdag lamang ng isang column ng JSONB sa aming talahanayan ng entity, na lubos na nagpapasimple sa disenyo ng database. Ngunit marami ang nangangatwiran na ito ay dapat na sinamahan ng pagbaba ng produktibidad... Kaya nga sinulat ko ang artikulong ito.

Pag-set up ng database ng pagsubok

Para sa paghahambing na ito, nilikha ko ang database sa isang bagong pag-install ng PostgreSQL 9.5 sa $80 build DigitalOcean Ubuntu 14.04. После настройки некоторых параметров в postgresql.conf я запустил ito script gamit ang psql. Ang mga sumusunod na talahanayan ay ginawa upang ipakita ang data sa EAV form:

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

Nasa ibaba ang isang talahanayan kung saan iimbak ang parehong data, ngunit may mga katangian sa isang hanay ng uri ng JSONB - mga katangian.

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

Mukhang mas simple, hindi ba? Pagkatapos ay idinagdag ito sa mga talahanayan ng entity (entity & entity_jsonb) 10 milyong mga tala, at naaayon, ang talahanayan ay napuno ng parehong data gamit ang EAV pattern at ang diskarte na may JSONB column - entity_jsonb.properties. Kaya, nakatanggap kami ng ilang iba't ibang uri ng data sa buong hanay ng mga katangian. Halimbawa ng data:

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

Kaya ngayon mayroon kaming parehong data para sa parehong mga pagpipilian. Simulan natin ang paghahambing ng mga pagpapatupad sa trabaho!

Pasimplehin ang iyong disenyo

Nauna nang sinabi na ang disenyo ng database ay lubos na pinasimple: isang table, sa pamamagitan ng paggamit ng JSONB column para sa mga property, sa halip na gumamit ng tatlong table para sa EAV. Ngunit paano ito makikita sa mga kahilingan? Ang pag-update ng isang entity property ay ganito ang hitsura:

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

Tulad ng nakikita mo, ang huling kahilingan ay hindi mukhang mas simple. Upang i-update ang halaga ng isang property sa isang JSONB object kailangan naming gamitin ang function jsonb_set(), at dapat na pumasa sa aming bagong halaga bilang JSONB object. Gayunpaman, hindi namin kailangang malaman ang anumang identifier nang maaga. Sa pagtingin sa halimbawa ng EAV, kailangan nating malaman pareho ang entity_id at ang entity_attribute_id upang maisagawa ang pag-update. Kung gusto mong i-update ang isang property sa isang column ng JSONB batay sa pangalan ng object, tapos ang lahat ng ito sa isang simpleng linya.

Ngayon piliin natin ang entity na kaka-update lang natin batay sa bagong kulay nito:

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

Sa tingin ko maaari tayong sumang-ayon na ang pangalawa ay mas maikli (bawal sumali!), at samakatuwid ay mas nababasa. Panalo ang JSONB dito! Ginagamit namin ang operator ng JSON ->> para makuha ang kulay bilang isang text value mula sa isang JSONB object. Mayroon ding pangalawang paraan upang makamit ang parehong resulta sa modelong JSONB gamit ang @> operator:

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

Ito ay medyo mas kumplikado: sinusuri namin kung ang object ng JSON sa column ng properties nito ay naglalaman ng object na nasa kanan ng operator na @>. Hindi gaanong nababasa, mas produktibo (tingnan sa ibaba).

Gawing mas madali natin ang paggamit ng JSONB kapag kailangan mong pumili ng maraming property nang sabay-sabay. Dito talaga pumapasok ang diskarte ng JSONB: pinipili lang namin ang mga property bilang karagdagang mga column sa aming set ng resulta nang hindi nangangailangan ng mga pagsali:

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

Sa EAV kakailanganin mo ng 2 pagsali para sa bawat property na gusto mong i-query. Sa aking opinyon, ang mga query sa itaas ay nagpapakita ng isang mahusay na pagpapasimple sa disenyo ng database. Tingnan ang higit pang mga halimbawa ng kung paano magsulat ng mga query sa JSONB, din sa Ito post.
Ngayon ay oras na upang pag-usapan ang tungkol sa pagganap.

Pagiging Produktibo

Upang ihambing ang pagganap na ginamit ko Ipaliwanag ang PAGSUSURI sa mga query, upang kalkulahin ang oras ng pagpapatupad. Ang bawat query ay naisakatuparan nang hindi bababa sa tatlong beses dahil ang query planner ay mas matagal sa unang pagkakataon. Una kong pinatakbo ang mga query nang walang anumang mga index. Malinaw, ito ay isang kalamangan ng JSONB, dahil ang mga pagsali na kinakailangan para sa EAV ay hindi maaaring gumamit ng mga index (ang mga foreign key field ay hindi na-index). Pagkatapos nito, gumawa ako ng index sa 2 foreign key column ng EAV value table, pati na rin ang index. Alak para sa isang column ng JSONB.

Ang pag-update ng data ay nagpakita ng mga sumusunod na resulta sa mga tuntunin ng oras (sa ms). Tandaan na ang sukat ay logarithmic:

Nakikita namin na ang JSONB ay mas mabilis (> 50000-x) kaysa sa EAV kung hindi ka gumagamit ng mga index, para sa kadahilanang nakasaad sa itaas. Kapag nag-index kami ng mga column na may mga pangunahing key, halos mawala ang pagkakaiba, ngunit ang JSONB ay 1,3 beses pa ring mas mabilis kaysa sa EAV. Tandaan na ang index sa column ng JSONB ay walang epekto dito dahil hindi namin ginagamit ang column ng property sa pamantayan sa pagsusuri.

Para sa pagpili ng data batay sa halaga ng ari-arian, nakukuha namin ang mga sumusunod na resulta (normal na sukat):

Mapapansin mong gumagana muli ang JSONB nang mas mabilis kaysa sa EAV na walang mga index, ngunit kapag ang EAV ay may mga index, mas mabilis pa rin itong gumagana kaysa sa JSONB. Ngunit pagkatapos ay nakita ko na ang mga oras para sa mga query sa JSONB ay pareho, ito ang nagtulak sa akin sa katotohanan na ang mga index ng GIN ay hindi gumagana. Tila kapag gumamit ka ng GIN index sa isang column na may mga populated na property, magkakabisa lang ito kapag ginagamit ang operator na kasama @>. Ginamit ko ito sa isang bagong pagsubok at nagkaroon ito ng malaking epekto sa oras: 0,153ms lang! Ito ay 15000 beses na mas mabilis kaysa sa EAV at 25000 beses na mas mabilis kaysa sa ->> operator.

Sa tingin ko ito ay sapat na mabilis!

Laki ng talahanayan ng database

Ihambing natin ang mga sukat ng talahanayan para sa parehong mga diskarte. Sa psql maaari naming ipakita ang laki ng lahat ng mga talahanayan at index gamit ang command dti+

Para sa diskarte sa EAV, ang mga sukat ng talahanayan ay humigit-kumulang 3068 MB at nag-i-index ng hanggang 3427 MB para sa kabuuang 6,43 GB. Gumagamit ang diskarte ng JSONB ng 1817 MB para sa talahanayan at 318 MB para sa mga index, na 2,08 GB. Ito ay lumiliko ng 3 beses na mas mababa! Medyo nagulat ako sa katotohanang ito dahil nag-iimbak kami ng mga pangalan ng property sa bawat object ng JSONB.

Ngunit gayon pa man, ang mga numero ay nagsasalita para sa kanilang sarili: sa EAV nag-iimbak kami ng 2 integer na foreign key bawat halaga ng katangian, na nagreresulta sa 8 byte ng karagdagang data. Bilang karagdagan, iniimbak ng EAV ang lahat ng mga halaga ng ari-arian bilang teksto, habang ang JSONB ay gagamit ng mga numeric at boolean na halaga sa loob kung saan posible, na magreresulta sa isang mas maliit na bakas ng paa.

Mga resulta ng

Sa pangkalahatan, sa tingin ko ang pag-save ng mga katangian ng entity sa JSONB na format ay maaaring gawing mas madali ang pagdidisenyo at pagpapanatili ng iyong database. Kung nagpapatakbo ka ng maraming query, kung gayon ang pagpapanatiling lahat sa parehong talahanayan bilang entity ay talagang gagana nang mas mahusay. At ang katotohanan na pinapasimple nito ang pakikipag-ugnayan sa pagitan ng data ay isang plus, ngunit ang resultang database ay 3 beses na mas maliit sa dami.

Gayundin, batay sa mga pagsubok na isinagawa, maaari nating tapusin na ang pagkawala ng pagganap ay napakaliit. Sa ilang mga kaso, ang JSONB ay mas mabilis pa kaysa sa EAV, na ginagawa itong mas mahusay. Gayunpaman, ang benchmark na ito siyempre ay hindi sumasaklaw sa lahat ng aspeto (hal. mga entity na may napakalaking bilang ng mga katangian, isang makabuluhang pagtaas sa bilang ng mga katangian ng umiiral na data,...), kaya kung mayroon kang anumang mga mungkahi sa kung paano pagbutihin ang mga ito , mangyaring huwag mag-atubiling umalis sa mga komento!

Pinagmulan: www.habr.com