Suia EAV ma JSONB i PostgreSQL

TL; DR: E mafai e le JSONB ona fa'afaigofie tele le atina'eina o fuafuaga fa'amaumauga e aunoa ma le ositaulagaina o le fa'atinoga o fesili.

Faatomuaga

Sei o tatou tuʻuina atu se faʻataʻitaʻiga masani o se tasi o faʻaoga pito sili ona tuai i le lalolagi o se faʻamaumauga faʻamaumauga (faʻamaumauga): e iai a tatou faʻalapotopotoga, ma e manaʻomia ona tatou faʻasaoina ni meatotino (uiga) o lenei faʻalapotopotoga. Ae le o fa'ata'ita'iga uma e mafai ona tutusa le seti o meatotino, ma e mafai ona fa'aopoopo nisi meatotino i le lumana'i.

O le auala pito sili ona faigofie e foia ai lenei faʻafitauli o le fatuina lea o se koluma i le laulau faʻamaumauga mo tau o meatotino taʻitasi, ma faʻatumu mea e manaʻomia mo se faʻataʻitaʻiga faʻapitoa. Matagofie! Fa'afitauli foia...seia o'o i lau laulau e iai le faitau miliona o fa'amaumauga ma e mana'omia le fa'aopoopoina o se fa'amaumauga fou.

Mafaufau i le mamanu EAV (Fa'alapotopotoga-Uiga-Taua), e tupu soo. O le tasi laulau o lo'o i ai fa'alapotopotoga (fa'amaumauga), o le isi laulau o lo'o i ai igoa o meatotino (uiga), ma le laulau lona tolu e fa'afeso'ota'i fa'atasi ma o latou uiga ma o lo'o i ai le tau o na uiga mo le fa'alapotopotoga o lo'o iai nei. O lenei mea e te maua ai le malosi e maua ai seti eseese o meatotino mo mea eseese, ma faʻaopoopo foi meatotino i luga o le lele e aunoa ma le suia o le fausaga o faʻamaumauga.

Ae ui i lea, ou te le tusia lenei pou pe ana leai ni faʻaletonu i le auala EVA. O lea, mo se faʻataʻitaʻiga, ina ia maua se tasi pe sili atu faʻalapotopotoga e 1 uiga taʻitasi, 2 faʻatasi e manaʻomia i le fesili: o le muamua o le tuʻufaʻatasia ma le laulau uiga, o le lona lua o le tuʻufaʻatasia ma le laulau faʻatatau. Afai e 2 uiga o se faalapotopotoga, ona manaʻomia lea o le 4 soʻo! E le gata i lea, o uiga uma e masani lava ona teuina e pei o manoa, lea e maua ai le faʻailoga mo le faʻaiʻuga ma le WHERE fuaiupu. Afai e te tusia le tele o fesili, o lona uiga e matua faʻaumatia i tulaga o le faʻaogaina o punaoa.

E ui lava i nei faʻaletonu manino, EAV ua leva ona faʻaaogaina e foia ai nei ituaiga o faʻafitauli. O ni faaletonu e le maalofia, ma e leai lava se isi mea e sili atu.
Ae na aliali mai se "tekinolosi" fou i PostgreSQL ...

Amata i le PostgreSQL 9.4, o le JSONB data type na faʻaopoopoina e teu ai faʻamaumauga binary JSON. E ui o le teuina o le JSON i lenei faatulagaga e masani ona alu sina avanoa ma le taimi nai lo le JSON tusitusiga manino, o le faʻatinoina o gaioiga e sili atu le vave. E lagolagoina foi e le JSONB le faasinoupuina o igoa, lea e sili atu ai le vave o fesili.

O le ituaiga fa'amaumauga a le JSONB e mafai ai ona tatou suia le mamanu EAV lavelave e ala i le fa'aopoopoina na'o le tasi le koluma JSONB i la tatou laulau fa'alapotopotoga, fa'afaigofie tele le mamanu o fa'amaumauga. Ae o le toʻatele o loʻo finau e tatau ona faʻatasi ma le faʻaitiitia o le gaosiga ... O le mea lea na ou tusia ai lenei tusiga.

Fa'atulaga se fa'amaumauga o su'ega

Mo lenei faʻatusatusaga, na ou fatuina le faʻamaumauga i luga o se faʻapipiʻi fou o PostgreSQL 9.5 i luga ole $80 fausia. DigitalOcean Ubuntu 14.04 Ina ua maeʻa ona faʻatulagaina nisi o parakalafa i le postgresql.conf sa ou tamoe lenei tusitusiga faʻaaoga psql. O laulau nei na faia e tu'uina atu ai fa'amaumauga ile EAV fomu:

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

O loʻo i lalo se laulau o loʻo teuina ai faʻamatalaga tutusa, ae faʻatasi ai ma uiga i se koluma ituaiga JSONB - meatotino.

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

E foliga mai e sili atu ona faigofie, a ea? Ona faʻaopoopoina lea i laulau faʻapitoa (faalapotopotoga & entity_jsonb) 10 miliona faʻamaumauga, ma e tusa ai, na faʻatumu le laulau i faʻamatalaga tutusa e faʻaaoga ai le mamanu EAV ma le auala ma se koluma JSONB - entity_jsonb.properties. O lea la, na matou mauaina le tele o ituaiga faʻamatalaga eseese i totonu o le seti atoa o meatotino. Fa'ata'ita'iga fa'amatalaga:

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

O lea la ua i ai a matou faʻamatalaga tutusa mo filifiliga uma e lua. Tatou amata fa'atusatusa fa'atinoga ile galuega!

Fa'afaigofie lau mamanu

Na taʻua muamua o le mamanu o faʻamaumauga na matua faʻafaigofieina: tasi le laulau, e ala i le faʻaaogaina o se koluma JSONB mo meatotino, nai lo le faʻaaogaina o laulau e tolu mo le EAV. Ae faʻapefea ona atagia lenei mea i talosaga? O le fa'afouina o se tasi o meatotino e fa'apea:

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

E pei ona e vaʻaia, o le talosaga mulimuli e le foliga faigofie. Ina ia faʻafouina le tau o se meatotino i se mea JSONB e tatau ona tatou faʻaogaina le galuega jsonb_set(), ma e tatau ona pasi lo tatou tau fou o se mea JSONB. Ae ui i lea, matou te le manaʻomia le iloa muamua o soʻo se faʻamatalaga. Va'ai i le fa'ata'ita'iga EAV, e mana'omia ona tatou iloa uma le entity_id ma le entity_attribute_id ina ia fa'atino le fa'afouga. Afai e te manaʻo e faʻafou se meatotino i se koluma JSONB e faʻavae i luga o le igoa mea, ona faia uma lea i se laina faigofie e tasi.

Se'i o tatou filifili le vaega lea faatoa tatou fa'afouina e fa'atatau i lona lanu fou:

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

Ou te manatu e mafai ona tatou ioe o le lona lua e puupuu (leai se auai!), Ma o lea e sili atu ona faitau. JSONB malo iinei! Matou te faʻaogaina le JSON ->> operator e maua ai le lanu o se tau o tusitusiga mai se mea JSONB. E iai foʻi se auala lona lua e maua ai le iʻuga tutusa i le JSONB faʻataʻitaʻiga e faʻaaoga ai le @> operator:

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

E sili atu le lavelave lenei: matou te siaki e vaʻai pe o le JSON mea i lona koluma meatotino o loʻo i ai se mea o loʻo i le taumatau o le @> operator. E itiiti le faitau, sili atu ona aoga (silasila i lalo).

Se'i fa'afaigofie le fa'aogaina o le JSONB pe a mana'omia ona e filifilia ni meatotino se tele i le taimi e tasi. O le mea lea e sau moni ai le JSONB: matou te filifilia mea e fai ma koluma faaopoopo i la matou seti faʻaiʻuga e aunoa ma le manaʻomia o le faʻatasi:

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

Faatasi ai ma le EAV e te manaʻomia 2 soʻotaga mo meatotino taʻitasi e te manaʻo e suʻeina. I loʻu manatu, o fesili o loʻo i luga o loʻo faʻaalia ai se faʻafaigofie tele i le faʻasologa o faʻamaumauga. Va'ai nisi fa'ata'ita'iga o le tusiaina o fesili JSONB, fa'apea fo'i i totonu lenei pou.
O lea ua oo i le taimi e talanoa ai e uiga i le faatinoga.

Gaioiga

E faʻatusatusa le faʻatinoga na ou faʻaaogaina FAAMATALA ILOILO i fesili, e fa'atatau le taimi e fa'atino ai. Sa fa'atino su'esu'ega ta'itasi pe a ma le faatolu ona e umi le taimi muamua o le fuafuaga o fesili. Muamua sa ou tamoe i fesili e aunoa ma ni faasino igoa. E manino lava, o se faʻamanuiaga lea o le JSONB, talu ai o faʻatasi e manaʻomia mo le EAV e le mafai ona faʻaogaina faʻamatalaga (faʻailoga autu mai fafo e leʻi faʻasinoina). Ina ua maeʻa lenei mea, na ou faia se faʻailoga i luga o koluma autu e 2 mai fafo o le laulau tau EAV, faʻapea foʻi ma se faʻailoga. GIN mo se koluma JSONB.

O le fa'afouga o fa'amaumauga na fa'aalia ai fa'ai'uga o lo'o mulimuli mai ile taimi (i le ms). Manatua o le fua e logarithmic:

Matou te vaʻaia o le JSONB e tele (> 50000-x) vave nai lo le EAV pe afai e te le faʻaogaina faʻasino igoa, mo le mafuaʻaga o loʻo taʻua i luga. A matou fa'asinoina koluma ma ki autu, e toetoe lava a mou atu le eseesega, ae o le JSONB e 1,3 taimi sili atu nai lo le EAV. Manatua o le faasino igoa i luga o le koluma JSONB e leai se aoga iinei talu ai tatou te le o faʻaogaina le koluma o meatotino i le faʻatonuga o iloiloga.

Mo le filifilia o faʻamaumauga e faʻavae i luga o le tau o meatotino, matou te maua iʻuga nei (fua masani):

E mafai ona e maitauina o le JSONB e toe galue vave nai lo le EAV e aunoa ma ni fa'asinomaga, ae a EAV ma fa'asino igoa, e sili atu lona galue nai lo le JSONB. Ae na ou vaaia o taimi mo fesili a le JSONB e tutusa, na mafua ai ona ou iloa le mea moni e le aoga le GIN index. E foliga mai pe a e faʻaogaina se GIN index i luga o se koluma ma mea faʻapitoa, e naʻo le aoga pe a faʻaaogaina le faʻaogaina o le operator @>. Na ou faʻaogaina lenei mea i se suʻega fou ma sa i ai se aafiaga tele i le taimi: naʻo 0,153ms! Ole 15000 taimi sili atu nai lo le EAV ma le 25000 taimi sili atu nai lo le ->> operator.

Ou te manatu ua lava le vave!

Tele laulau fa'amaumauga

Se'i o tatou fa'atusatusa lapo'a laulau mo auala uma e lua. I le psql e mafai ona tatou faʻaalia le tele o laulau uma ma faʻasino igoa e faʻaaoga ai le poloaiga dti+

Mo le fa'aogaina o le EAV, lapopo'a laulau e tusa ma le 3068 MB ma fa'asino igoa i le 3427 MB mo le aofa'i o le 6,43 GB. O le JSONB auala e faʻaaoga ai le 1817 MB mo le laulau ma le 318 MB mo faʻamatalaga, o le 2,08 GB. E foliga mai e 3 taimi e itiiti ifo! O lenei mea moni na faateia ai aʻu ona matou te teuina igoa o meatotino i mea JSONB uma.

Ae ui i lea, o fuainumera e tautala mo i latou lava: i le EAV matou te teuina 2 integer mai fafo ki i le tau o uiga, ma maua ai le 8 bytes o faʻamatalaga faaopoopo. E le gata i lea, o le EAV o loʻo teuina uma tau o meatotino e fai ma tusitusiga, ae o le JSONB o le a faʻaogaina numera ma le boolean i totonu pe a mafai, ma maua ai se tulagavae laʻititi.

O taunuʻuga

I le aotelega, ou te manatu o le faasaoina o meatotino a le faalapotopotoga i le JSONB format e mafai ona faafaigofieina ai le mamanuina ma le tausia o lau database. Afai o loʻo e faʻatautaia le tele o fesili, ona tuʻu lea o mea uma i le laulau tutusa e pei o le faʻalapotopotoga o le a sili atu ona lelei le galue. Ma o le mea moni e faʻafaigofieina le fegalegaleaiga i le va o faʻamaumauga ua uma ona faʻaopoopo, ae o le faʻamaumauga faʻamaumauga e 3 taimi laʻititi i le voluma.

E le gata i lea, e faʻavae i luga o suʻega na faia, e mafai ona tatou faʻamaonia o le gau o faʻatinoga e matua le taua. I nisi tulaga, e sili atu le vave o le JSONB nai lo le EAV, e sili atu le lelei. Ae ui i lea, o lenei faʻataʻitaʻiga o le mea moni e le o aofia uma ai vaega (eg faʻalapotopotoga ma le tele o mea totino, o se faʻaopoopoga taua i le numera o meatotino o faʻamaumauga o loʻo i ai nei, ...), o lea afai ei ai ni au fautuaga ile auala e faʻaleleia ai. , fa'amolemole lagona le saoloto e tu'u i fa'amatalaga!

puna: www.habr.com