🥇Pag-ilis sa EAV gamit ang JSONB sa PostgreSQL

TL;DR: Ang JSONB makapasayon pag-ayo sa pagpalambo sa database schema nga dili isakripisyo ang performance sa query.

Pasiuna

Atong kuhaon ang usa ka klasiko nga ehemplo, tingali usa sa labing karaan nga mga kaso sa paggamit sa kalibutan sa mga relational database: kita adunay usa ka entity, ug kinahanglan natong tipigan ang pipila ka mga kabtangan (mga hiyas) niini nga entity. Apan dili tanan nga mga instance mahimong adunay parehas nga hugpong sa mga kabtangan, ug daghang mga kabtangan ang mahimong idugang sa umaabot.

Ang pinakasimple nga solusyon niini nga problema mao ang paghimo og kolum sa database table para sa matag property value ug pun-on lang ang mga gikinahanglan para sa usa ka piho nga entity instance. Maayo kaayo! Nasulbad na ang problema... hangtod nga ang imong table adunay minilyon nga mga rekord ug kinahanglan ka nga magdugang og bag-ong rekord.

Atong hisgotan ang sumbanan sa EAV (Bili sa Entidad nga Hiyas), kini komon kaayo. Ang usa ka lamesa adunay mga entidad (mga rekord), ang laing lamesa adunay mga ngalan sa kabtangan (mga hiyas), ug ang ikatulo nga lamesa nagsumpay sa mga entidad ngadto sa ilang mga hiyas ug adunay mga bili niini nga mga hiyas alang sa kasamtangang entidad. Kini nagtugot kanimo sa pagbaton og lain-laing mga hugpong sa mga kabtangan alang sa lain-laing mga butang, ingon man pagdugang og mga kabtangan diha-diha dayon, nga dili usbon ang istruktura sa database.

Apan, dili unta ko magsulat niini nga post kon walay mga disbentaha sa pamaagi sa EVA. Pananglitan, ang pagkuha og usa o daghan pang mga entidad nga adunay usa ka attribute matag usa nagkinahanglan og duha ka join sa query: ang una nga join gamit ang attribute table, ang ikaduha nga join gamit ang value table. Kon ang usa ka entidad adunay duha ka attribute, nan upat ka join ang gikinahanglan! Dugang pa, ang tanang attribute kasagarang gitipigan isip mga string, nga moresulta sa type coercion para sa resulta ug sa WHERE clause. Kon magsulat ka og daghang query, kini usik kaayo sa paggamit sa resources.

Bisan pa niining klaro nga mga kakulangan, ang EAV dugay nang gigamit sa pagsulbad niining mga matang sa problema. Kini dili kalikayan nga mga kakulangan, ug wala nay mas maayong alternatibo.
Apan unya usa ka bag-ong "teknolohiya" ang mitungha sa PostgreSQL…

Sugod sa PostgreSQL 9.4, ang JSONB data type gidugang para sa pagtipig sa binary JSON data. Bisan tuod ang pagtipig sa JSON niini nga format kasagaran magkinahanglan og gamay nga dugang nga espasyo ug oras kaysa sa plain text JSON, ang mga operasyon niini mas paspas. Gisuportahan usab sa JSONB ang indexing, nga naghimo sa mga pangutana nga mas paspas pa.

Ang JSONB data type nagtugot kanato sa pag-ilis sa lisod nga EAV pattern pinaagi sa pagdugang lang og usa ka JSONB column sa atong entity table, nga nagpasayon pag-ayo sa disenyo sa database. Apan, daghan ang nangatarungan nga kini adunay gasto sa performance… Mao kana ang hinungdan nganong gisulat nako kini nga artikulo.

Pag-setup og database sa pagsulay

Para ani nga pagtandi, akong gibuhat ang database sa bag-ong instalasyon sa PostgreSQL 9.5 sa $80 nga build. DigitalOcean Ubuntu 14.04. После настройки некоторых параметров в postgresql.conf я запустил kini script gamit ang psql. Aron ipakita ang datos isip EAV, ang mosunod nga mga lamesa gihimo:

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

Sa ubos mao ang usa ka talaan diin ang parehas nga datos tipigan, apan adunay mga hiyas sa usa ka kolum sa JSONB – mga kabtangan.

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

Murag mas simple, di ba? Dayon gidugang kini sa mga entity table (kompaniya & entidad_jsonb) 10 milyon nga mga rekord, ug sumala niana, ang lamesa napuno sa parehas nga datos diin gigamit ang sumbanan sa EAV ug ang pamaagi gamit ang kolum sa JSONB – entity_jsonb.propertiesBusa, nakakuha kami og daghang lain-laing mga tipo sa datos sa tibuok hugpong sa mga kabtangan. Pananglitan sa datos:

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

Mao nga, karon parehas na ang atong datos para sa duha ka opsyon. Sugdan nato ang pagtandi sa mga implementasyon sa tinuod nga kinabuhi!

Pagpasimple sa disenyo

Nahisgotan na ganina nga ang disenyo sa database gipasimple pag-ayo: usa ka lamesa, gamit ang kolum sa JSONB para sa mga kabtangan, imbes nga tulo ka lamesa para sa EAV. Apan unsaon man kini paghubad ngadto sa mga pangutana? Ang pag-update sa usa ka kabtangan sa usa ka entidad mao kini 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;

Sama sa imong makita, ang katapusang pangutana dili tan-awon nga mas sayon. Aron ma-update ang bili sa usa ka property sa usa ka JSONB object, kinahanglan natong gamiton ang function jsonb_set(), ug kinahanglan nga ipasa ang atong bag-ong bili isip usa ka JSONB object. Bisan pa, dili kinahanglan nga mahibal-an nato daan ang bisan unsang identifier. Sa pagtan-aw sa ehemplo sa EAV, kinahanglan natong mahibal-an ang entity_id ug entity_attribute_id aron mahimo ang pag-update. Kung gusto nimong i-update ang usa ka property sa usa ka JSONB column base sa ngalan sa object, tanan kini mahimo sa usa ka yano nga linya.

Karon atong pilion ang entity nga atong gi-update base sa bag-ong kolor niini:

-- 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 akong hunahuna magkauyon kita nga ang ikaduha mas mubo (kung walay join!) ug busa mas mabasa. Modaog ang JSONB dinhi! Atong gamiton ang JSON ->> operator aron makuha ang kolor isip text value gikan sa JSONB object. Adunay usab ikaduhang paagi aron makab-ot ang parehas nga resulta sa JSONB model gamit ang @> operator:

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

Kini mas komplikado gamay: atong susihon kung ang JSON object sa properties column adunay sulod nga object sa tuo nga bahin sa @> operator. Kon dili kaayo mabasa, mas maayo ang performance (tan-awa sa ubos).

Atong pasimplehon pa ang paggamit sa JSONB kung kinahanglan nimo nga mopili og daghang mga kabtangan sa usa ka higayon. Dinhi gyud modan-ag ang pamaagi sa JSONB: atong pilion lang ang mga kabtangan isip dugang nga mga kolum sa atong resulta, nga dili na kinahanglan ang mga join:

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

Sa EAV, kinahanglan nimo ang duha ka join para sa matag property nga gusto nimong i-query. Sa akong opinyon, ang mga query sa ibabaw nagpakita sa usa ka dakong pagpasimple sa disenyo sa database. Makita usab nimo ang dugang mga ehemplo kung unsaon pagsulat og mga JSONB query sa kini poste.
Karon panahon na aron hisgutan ang bahin sa performance.

Pag-uswag

Para itandi ang performance nga akong gigamit IPAHIBALO, ANALISAHA sa mga pangutana aron makalkulo ang oras sa pagpatuman. Ang matag pangutana gipatuman labing menos tulo ka beses, tungod kay ang query planner mas dugay sa unang higayon. Una, gipadagan nako ang mga pangutana nga walay bisan unsang mga indeks. Kini klaro nga nagsilbing bentaha sa JSONB, tungod kay ang mga join nga gikinahanglan alang sa EAV dili makagamit og mga indeks (ang mga foreign key field wala ma-index). Pagkahuman niana, naghimo ako og indeks sa duha ka foreign key column sa EAV value table, ingon man usa ka indeks Gin para sa kolum sa JSONB.

Ang mga pag-update sa datos nagpakita sa mosunod nga mga resulta sa oras (sa ms). Timan-i nga ang sukdanan kay logarithmic:

Pag-ilis sa EAV sa JSONB sa PostgreSQL

Atong makita nga ang JSONB mas paspas (>50000x) kay sa EAV nga walay mga index, tungod sa rason nga nahisgotan sa ibabaw. Kung atong i-index ang mga primary key column, halos mawala ang kalainan, apan ang JSONB 1,3x gihapon nga mas paspas kay sa EAV. Timan-i nga ang index sa JSONB column walay epekto dinhi, tungod kay wala nato gigamit ang property column sa evaluation criteria.

Para sa pagpili sa datos base sa bili sa kabtangan, atong makuha ang mosunod nga mga resulta (normal nga sukdanan):

Pag-ilis sa EAV sa JSONB sa PostgreSQL

Makita nimo nga ang JSONB mas paspas kay sa EAV nga walay index, apan kon ang EAV gi-index, mas paspas gihapon kini kay sa JSONB. Apan nakita nako nga ang mga oras para sa mga pangutana sa JSONB parehas ra, nga nagdala kanako sa kamatuoran nga ang mga GIN index dili ma-trigger. Dayag nga, kon mogamit ka og GIN index sa usa ka kolum nga adunay mga populated properties, kini mo-epekto lang kon mogamit sa @> inclusion operator. Gigamit nako kini sa usa ka bag-ong pagsulay, ug kini adunay dako nga epekto sa oras: 0,153 ms lang! Kana 15000 ka pilo nga mas paspas kay sa EAV ug 25000 ka pilo nga mas paspas kay sa ->> operator.

Sa akong hunahuna paspas ra kaayo!

Gidak-on sa mga lamesa sa database

Atong itandi ang gidak-on sa mga lamesa para sa duha ka pamaagi. Sa psql, atong mapakita ang gidak-on sa tanang mga lamesa ug mga indeks gamit ang sugo dti+

Pag-ilis sa EAV sa JSONB sa PostgreSQL

Sa pamaagi sa EAV, ang gidak-on sa lamesa gibana-bana nga 3068 MB, ug ang mga indeks hangtod sa 3427 MB, para sa kinatibuk-an nga 6,43 GB. Gamit ang pamaagi sa JSONB, ang lamesa naggamit og 1817 MB ug mga indeks 318 MB, para sa kinatibuk-an nga 2,08 GB. Kana ikatulo nga bahin sa gidak-on! Kini nga kamatuoran nakurat kanako gamay, tungod kay among gitipigan ang mga ngalan sa kabtangan sa matag butang sa JSONB.

Apan ang mga numero nagsulti alang sa ilang kaugalingon: sa EAV, nagtipig kami og duha ka integer foreign keys matag attribute value, nga moresulta sa 8 bytes nga dugang nga datos. Dugang pa, sa EAV, ang tanan nga property values gitipigan isip text, samtang ang JSONB mogamit og numeric ug logical values sa internal kon mahimo, nga moresulta sa mas gamay nga footprint.

Mga resulta

Sa kinatibuk-an, sa akong hunahuna ang pagtipig sa mga kabtangan sa entidad sa JSONB format makapasayon pag-ayo sa disenyo ug pagmentinar sa imong database. Kung maghimo ka og daghang mga pangutana, ang pagtipig sa tanan sa parehas nga lamesa sama sa entidad mahimong mas episyente. Ang kamatuoran nga gipasayon niini ang mga interaksyon sa datos usa na ka bentaha, apan ang resulta nga database tulo ka pilo nga mas gamay sa gidak-on.

Usab, base sa mga resulta sa benchmark, makahinapos kita nga ang silot sa performance gamay ra kaayo. Sa pipila ka mga kaso, ang JSONB mas paspas pa gani nga mo-perform kay sa EAV, nga naghimo niini nga mas maayo pa. Bisan pa, kini nga benchmark siguradong dili makatabon sa tanang aspeto (pananglitan, mga entidad nga adunay daghang gidaghanon sa mga kabtangan, usa ka dakong pagtaas sa gidaghanon sa mga kabtangan sa kasamtangang datos, ug uban pa), busa kung naa kay mga sugyot alang sa pagpaayo, palihug ayaw pagpanuko sa pagbilin niini sa mga komento!

Source: www.habr.com