Замена EAV на JSONB у PostgreSQL

TL; DR: JSONB можа значна спрасціць распрацоўку схемы БД без шкоды прадукцыйнасці ў запытах.

Увядзенне

Прывядзем класічны прыклад, мусіць, аднаго з найстарэйшых варыянтаў выкарыстання ў свеце рэляцыйных БД (база дадзеных): у нас ёсць сутнасць, і неабходна захаваць вызначаныя ўласцівасці (атрыбуты) гэтай сутнасці. Але не ўсе асобнікі могуць маюць аднолькавы набор уласцівасцяў, да таго ж у будучыні, магчымае даданне яшчэ ўласцівасцяў.

Самы просты шлях рашэння гэтай праблемы - гэта стварэнне слупка ў табліцы БД для кожнага значэнне ўласцівасці, і проста запаўняць тыя, якія патрэбныя для вызначанага асобніка сутнасці. Выдатна! Праблема вырашана… да таго моманту, пакуль ваша табліца не змяшчае мільёны запісаў і ў вас не ўзнікне неабходнасць дадаць новы запіс.

Разгледзім патэрн EAV (Entity-Attribute-Value), ён сустракаецца дастаткова часта. Адна табліца ўтрымоўвае сутнасці (запісы), іншая табліца ўтрымоўвае імёны ўласцівасцяў (атрыбутаў), а трэцяя табліца злучае сутнасці з іх атрыбутамі і ўтрымоўвае значэнне гэтых атрыбутаў для бягучай сутнасці. Гэта дае вам магчымасць мець розныя наборы ўласцівасцяў для розных аб'ектаў, а таксама дадаваць уласцівасці "на ляту", не змяняючы структуры БД.

Тым не менш, я б не пісаў гэтую нататку, калі б не было недахопаў у падыходзе з выкарыстаннем EVA. Так, напрыклад, для атрымання адной або некалькіх сутнасцяў, якія маюць па 1 атрыбуту патрабуецца 2 join'а (аб'яднанні) у запыце: першы - аб'яднанне з табліцай атрыбутаў, другі - аб'яднанне з табліцай значэнняў. Калі сутнасць маюць 2 атрыбуты, тое трэба ўжо 4 join'а! Акрамя таго, усе атрыбуты звычайна захоўваюцца ў выглядзе радкоў, што прыводзіць да прывядзення тыпаў як для выніку, так і для ўмовы WHERE. Калі вы пішаце шмат запытаў, то гэта дастаткова марнатраўна, з пункту гледжання выкарыстання рэсурсаў.

Нягледзячы на ​​гэтыя відавочныя недахопы, EAV ужо даўно выкарыстоўваецца для вырашэння такога роду праблем. Гэта былі непазбежныя недахопы, і лепшай альтэрнатывы проста не было.
Але затым у PostgreSQL з'явілася новая "тэхналогія"…

Пачынаючы з PostgreSQL 9.4, быў дададзены тып дадзеных JSONB для захоўвання двайковых дадзеных JSON. Хоць захоўванне JSON у гэтым фармаце звычайна займае крыху больш месца і часу, чым просты тэкставы JSON, выкананне аперацый з ім адбываецца нашмат хутчэй. Таксама JSONB падтрымлівае індэксаванне, што робіць запыты да іх яшчэ хутчэй.

Тып дадзеных JSONB дазваляе нам замяніць грувасткі патэрн EAV шляхам дадання ўсяго толькі аднаго слупка JSONB у нашу табліцу сутнасцяў, што значна спрашчае праектаванне базы дадзеных. Але многія сцвярджаюць, што гэта павінна суправаджацца зніжэннем прадукцыйнасці… Вось з гэтай прычыны я і з'явіўся гэты артыкул.

Настройка тэставай базы даных

Для гэтага параўнання я стварыў базу дадзеных на новай усталёўцы PostgreSQL 9.5 на 80-даляравай зборцы DigitalOcean Ubuntu 14.04/XNUMX. Пасля наладкі некаторых параметраў у postgresql.conf я запусціў гэты скрыпт з дапамогай psql. Для прадстаўлення дадзеных у выглядзе EAV былі створаны наступныя табліцы:

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

Ніжэй прадстаўлена табліца, дзе будуць захоўвацца тыя ж дадзеныя, але з атрыбутамі ў слупку тыпу JSONB – ўласцівасці.

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

Выглядае нашмат прасцей, ці не так? Затым было дададзена ў табліцы сутнасцяў (сутнасць & entity_jsonb) 10 мільёнаў запісаў, і адпаведна, было запоўнена аднолькавымі дадзенымі табліцы дзе выкарыстоўваецца EAV патэрн і падыход з JSONB слупком – entity_jsonb.properties. Такім чынам, мы атрымалі некалькі розных тыпаў дадзеных сярод усяго набору ўласцівасцяў. Прыклад дадзеных:

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

Такім чынам, зараз у нас ёсць аднолькавыя дадзеныя, для двух варыянтаў. Давайце пачнем параўноўваць рэалізацыі ў працы!

Спрашчэнне дызайну

Раней ужо было сказана, што дызайн БД быў значна спрошчаны: адна табліца, за рахунак выкарыстання слупка JSONB для ўласцівасцяў, замест выкарыстання трох табліц для EAV. Але як гэта адлюстроўваецца ў запытах? Абнаўленне адной уласцівасці сутнасці выглядае наступным чынам:

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

Як бачым, апошні запыт не выглядае прасцей. Каб абнавіць значэнне ўласцівасці ў аб'екце JSONB, мы павінны выкарыстоўваць функцыю jsonb_set(), і павінны перадаць нашае новае значэнне як аб'ект JSONB. Тым не менш, нам не трэба ведаць які-небудзь ідэнтыфікатар загадзя. Паглядзеўшы на прыклад з EAV, нам трэба шляхта і entity_id, і entity_attribute_id, каб выканаць абнаўленне. Калі вы жадаеце абнавіць уласцівасць у слупку JSONB на аснове імя аб'екта, - тое гэта ўсё робіцца адным простым радком.

Цяпер давайце выберам тую сутнасць, якую мы толькі што абнавілі, па ўмове яе новага колеру:

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

Я думаю, што мы можам пагадзіцца з тым, што другое з'яўляецца карацейшым (без join!), і адпаведна больш чытэльным. Тут перамога JSONB! Мы выкарыстоўваем аператар JSON ->>, каб атрымаць колер як тэкставае значэнне з аб'екта JSONB. Існуе таксама другі спосаб дасягнення таго ж выніку ў мадэлі JSONB з выкарыстаннем аператара @>:

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

Гэта крыху складаней: мы правяраем, ці ўтрымоўвае аб'ект JSON у слупку уласцівасцяў аб'ект які знаходзіцца справа ад аператара @>. Менш чытэльны, больш прадукцыйны (гл. далей).

Спрасцім выкарыстання JSONB яшчэ мацней, калі вам трэба абраць некалькі ўласцівасцяў адначасова. Вось дзе сапраўды падыходзіць JSONB-падыход: мы проста выбіраемы ўласцівасці ў якасці дадатковых слупкоў у нашым наборы вынікаў без неабходнасці аб'яднанняў:

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

З EAV вам спатрэбіцца 2 аб'яднанні для кожнага ўласцівасці, якое вы хочаце запытаць. На мой погляд, прыведзеныя вышэй запыты паказваюць вялікае спрашчэнне ў дызайне базы дадзеных. Паглядзець больш прыкладаў таго, як пісаць запыты да JSONB, магчыма таксама ў гэтым пасце.
Цяпер прыйшоў час пагаварыць аб прадукцыйнасці.

Proizvoditelnost

Каб параўнаць прадукцыйнасць, я выкарыстаў EXPLAIN ANALYZE у запытах, для падліку часу выканання. Кожны запыт выконваўся як мінімум тры разы, таму што ў першы раз планавальніку запытаў патрабуецца больш часу. Спачатку я выканаў запыты без якіх-небудзь азначнікаў. Відавочна, гэта служыла перавагай JSONB, бо join, неабходныя для EAV, не маглі выкарыстоўваць азначнікі (палі вонкавага ключа не індэксаваліся). Пасля гэтага я стварыў індэкс для 2-х слупкоў знешніх ключоў табліцы значэнняў EAV, а таксама індэкс GIN для слупка JSONB.

Абнаўленні дадзеных паказала наступныя вынікі па часе (у мс). Звярніце ўвагу, што маштаб з'яўляецца лагарыфмічным:

Бачым што JSONB нашмат (> 50000-x) хутчэй, чым EAV, калі не выкарыстоўваць індэксы, з прычыны, названай вышэй. Калі мы індэксуем слупкі c першаснымі ключамі, розніца амаль знікае, але JSONB усё яшчэ ў 1,3 разы хутчэй, чым EAV. Звярніце ўвагу, што індэкс у слупку JSONB тут не аказвае ніякага ўплыву, бо мы не выкарыстоўваем слупок уласцівасцяў ва ўмовах адзнакі.

Для выбару дадзеных на аснове значэння ўласцівасці мы атрымліваем наступныя вынікі (звычайны маштаб):

Можна заўважыць, што JSONB зноў працуе хутчэй, чым EAV без індэксаў, але калі EAV з індэксамі - ён усё ж працуе хутчэй чым JSONB. Але потым я ўбачыў, што час для JSONB-запытаў быў аднолькавы, гэта падштурхнула мяне на той факт, што GIN-індэкс не спрацоўваюць. Мабыць, калі вы карыстаецеся індэкс GIN для слупка з запоўненымі ўласцівасцямі, ён дзейнічае толькі пры выкарыстанні аператара ўключэння @>. Я выкарыстаў гэта ў новым тэсце, што аказала вялікі ўплыў на час: усяго 0,153 мс! Гэта ў 15000 разоў хутчэй, чым EAV, і ў 25000 разоў хутчэй, чым аператар ->>.

Думаю, гэта было дастаткова хутка!

Памер табліц БД

Давайце параўнаем памеры табліц пры абодвух падыходаў. У psql мы можам паказаць памер усіх табліц і азначнікаў з дапамогай каманды dti+

Для падыходу EAV памеры табліц складаюць каля 3068 МБ, а індэксы - да 3427 МБ, што ў суме дае 6,43 ГБ. Пры выкарыстанні падыходу з JSONB выкарыстоўваецца 1817 МБ для табліцы і 318 МБ для азначнікаў, што складае 2,08 ГБ. Атрымліваецца ў 3 разы менш! Гэты факт крыху здзівіў мяне, таму што мы захоўваем імёны уласцівасцяў у кожным аб'екце JSONB.

Але ўсё ж лічбы кажуць самі за сябе: у EAV мы захоўваем 2 цэлалікіх вонкавых ключа на значэнне атрыбуту, у выніку чаго атрымліваем 8 байт дадатковых дадзеных. Акрамя таго, у EAV усе значэння ўласцівасцяў захоўваюцца ў выглядзе тэксту, у той час як JSONB будзе выкарыстоўваць лікавыя і лагічныя значэння ўнутры, дзе гэта магчыма, у выніку чаго атрымліваецца меншы аб'ём.

Вынікі

У цэлым, я думаю, што захаванне ўласцівасцяў сутнасцяў у фармаце JSONB можа значна спрасціць праектаванне і абслугоўванне вашай базы дадзеных. Калі вы выконваеце шмат запытаў, тое ўсё, што захоўваецца ў адной табліцы з сутнасцю, сапраўды будзе працаваць больш эфектыўна. І той факт, што гэта спрашчае ўзаемадзеянне паміж дадзенымі, ужо з'яўляецца плюсам, але і выніковая БД у 3 разы меншая па аб'ёме.

Таксама, па зробленым тэстам, можна зрабіць вынік, што страты прадукцыйнасці вельмі малаважныя. У некаторых выпадках JSONB нават працуе хутчэй, чым EAV, што робіць яго яшчэ лепей. Аднак гэты эталонны тэст, вядома, не ахоплівае ўсе аспекты (напрыклад, сутнасці з вельмі вялікай колькасцю уласцівасцяў, значным павелічэннем колькасці ўласцівасцяў існуючых дадзеных,…), таму, калі ў вас ёсць якія-небудзь прапановы аб тым, як іх палепшыць, калі ласка, не саромейцеся пакідаць у каментарах!

Крыніца: habr.com