PostgreSQL में EAV को JSONB से बदलना

टीएल; डॉ: JSONB क्वेरी प्रदर्शन से समझौता किए बिना डेटाबेस स्कीमा विकास को बहुत सरल बना सकता है।

परिचय

आइए रिलेशनल डेटाबेस (डेटाबेस) की दुनिया में संभवतः सबसे पुराने उपयोग मामलों में से एक का एक उत्कृष्ट उदाहरण दें: हमारे पास एक इकाई है, और हमें इस इकाई के कुछ गुणों (विशेषताओं) को सहेजने की आवश्यकता है। लेकिन सभी उदाहरणों में गुणों का एक ही सेट नहीं हो सकता है, और भविष्य में और अधिक गुण जोड़े जा सकते हैं।

इस समस्या को हल करने का सबसे आसान तरीका प्रत्येक संपत्ति मूल्य के लिए डेटाबेस तालिका में एक कॉलम बनाना है, और बस उन लोगों को भरना है जो एक विशिष्ट इकाई उदाहरण के लिए आवश्यक हैं। महान! समस्या हल हो गई... जब तक कि आपकी तालिका में लाखों रिकॉर्ड न हों और आपको एक नया रिकॉर्ड जोड़ने की आवश्यकता न हो।

ईएवी पैटर्न पर विचार करें (इकाई-विशेषता-मूल्य), ऐसा अक्सर होता है. एक तालिका में इकाइयाँ (रिकॉर्ड) होती हैं, दूसरी तालिका में संपत्ति के नाम (विशेषताएँ) होती हैं, और तीसरी तालिका इकाइयों को उनकी विशेषताओं के साथ जोड़ती है और वर्तमान इकाई के लिए उन विशेषताओं का मूल्य रखती है। यह आपको अलग-अलग ऑब्जेक्ट के लिए गुणों के अलग-अलग सेट रखने की क्षमता देता है, और डेटाबेस संरचना को बदले बिना तुरंत गुण जोड़ने की सुविधा भी देता है।

हालाँकि, यदि ईवीए दृष्टिकोण में कुछ कमियाँ न होतीं तो मैं यह पोस्ट नहीं लिख रहा होता। इसलिए, उदाहरण के लिए, एक या अधिक संस्थाओं को प्राप्त करने के लिए जिनमें प्रत्येक में 1 विशेषता है, क्वेरी में 2 जुड़ाव की आवश्यकता होती है: पहला विशेषता तालिका के साथ जुड़ाव है, दूसरा मान तालिका के साथ जुड़ाव है। यदि किसी इकाई में 2 विशेषताएँ हैं, तो 4 जोड़ की आवश्यकता है! इसके अतिरिक्त, सभी विशेषताओं को आमतौर पर स्ट्रिंग्स के रूप में संग्रहीत किया जाता है, जिसके परिणामस्वरूप परिणाम और WHERE क्लॉज दोनों के लिए टाइप कास्टिंग होती है। यदि आप बहुत सारी क्वेरीज़ लिखते हैं, तो संसाधन उपयोग की दृष्टि से यह काफी बेकार है।

इन स्पष्ट कमियों के बावजूद, इस प्रकार की समस्याओं को हल करने के लिए ईएवी का उपयोग लंबे समय से किया जाता रहा है। ये अपरिहार्य कमियाँ थीं, और इससे बेहतर कोई विकल्प नहीं था।
लेकिन फिर PostgreSQL में एक नई "तकनीक" सामने आई...

PostgreSQL 9.4 से शुरू करके, JSON बाइनरी डेटा को संग्रहीत करने के लिए JSONB डेटा प्रकार जोड़ा गया था। हालाँकि इस प्रारूप में JSON को संग्रहीत करने में आम तौर पर सादे पाठ JSON की तुलना में थोड़ा अधिक स्थान और समय लगता है, लेकिन इस पर संचालन करना बहुत तेज़ है। JSONB इंडेक्सिंग का भी समर्थन करता है, जिससे क्वेरीज़ और भी तेज़ हो जाती हैं।

JSONB डेटा प्रकार हमें हमारी इकाई तालिका में केवल एक JSONB कॉलम जोड़कर बोझिल EAV पैटर्न को बदलने की अनुमति देता है, जिससे डेटाबेस डिज़ाइन बहुत सरल हो जाता है। लेकिन कई लोगों का तर्क है कि इसके साथ उत्पादकता में कमी होनी चाहिए... इसीलिए मैंने यह लेख लिखा है।

एक परीक्षण डेटाबेस स्थापित करना

इस तुलना के लिए, मैंने $9.5 बिल्ड पर PostgreSQL 80 की ताज़ा स्थापना पर डेटाबेस बनाया DigitalOcean Ubuntu 14.04. После настройки некоторых параметров в 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
);

यह बहुत सरल लगता है, है ना? फिर इसे इकाई तालिकाओं में जोड़ा गया (सत्ता & इकाई_जेएसओएनबी) 10 मिलियन रिकॉर्ड, और तदनुसार, तालिका को ईएवी पैटर्न और जेएसओएनबी कॉलम के साथ दृष्टिकोण का उपयोग करके समान डेटा से भरा गया था - इकाई_jsonb.गुण. इस प्रकार, हमें संपत्तियों के पूरे सेट के बीच कई अलग-अलग डेटा प्रकार प्राप्त हुए। उदाहरण डेटा:

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

तो अब हमारे पास दोनों विकल्पों के लिए समान डेटा है। आइए कार्यस्थल पर कार्यान्वयन की तुलना करना शुरू करें!

अपने डिज़ाइन को सरल बनाएं

यह पहले कहा गया था कि डेटाबेस डिज़ाइन को बहुत सरल बनाया गया था: EAV के लिए तीन तालिकाओं का उपयोग करने के बजाय, गुणों के लिए JSONB कॉलम का उपयोग करके एक तालिका। लेकिन यह अनुरोधों में कैसे परिलक्षित होता है? एक इकाई संपत्ति को अद्यतन करना इस तरह दिखता है:

-- 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 ऑब्जेक्ट के रूप में पास करना चाहिए। हालाँकि, हमें किसी भी पहचानकर्ता को पहले से जानने की आवश्यकता नहीं है। ईएवी उदाहरण को देखते हुए, अद्यतन करने के लिए हमें इकाई_आईडी और इकाई_विशेषता_आईडी दोनों को जानना होगा। यदि आप ऑब्जेक्ट नाम के आधार पर 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';

मुझे लगता है कि हम इस बात पर सहमत हो सकते हैं कि दूसरा छोटा है (कोई जुड़ाव नहीं!), और इसलिए अधिक पठनीय है। JSONB यहाँ जीत गया! हम JSONB ऑब्जेक्ट से टेक्स्ट मान के रूप में रंग प्राप्त करने के लिए JSON ->> ऑपरेटर का उपयोग करते हैं। @> ऑपरेटर का उपयोग करके 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;

ईएवी के साथ आपको प्रत्येक संपत्ति के लिए 2 जॉइन की आवश्यकता होगी जिसे आप क्वेरी करना चाहते हैं। मेरी राय में, उपरोक्त प्रश्न डेटाबेस डिज़ाइन में एक महान सरलीकरण दिखाते हैं। JSONB क्वेरीज़ लिखने के तरीके के और भी उदाहरण देखें यह डाक।
अब बारी है परफॉर्मेंस की बात करने की.

निष्पादन

प्रदर्शन की तुलना करने के लिए मैंने इसका उपयोग किया व्याख्या विश्लेषण निष्पादन समय की गणना करने के लिए प्रश्नों में। प्रत्येक क्वेरी को कम से कम तीन बार निष्पादित किया गया क्योंकि क्वेरी प्लानर को पहली बार अधिक समय लगता है। सबसे पहले मैंने बिना किसी अनुक्रमणिका के क्वेरीज़ चलायीं। जाहिर है, यह JSONB का एक फायदा था, क्योंकि EAV के लिए आवश्यक जॉइन इंडेक्स का उपयोग नहीं कर सकते थे (विदेशी कुंजी फ़ील्ड अनुक्रमित नहीं थे)। इसके बाद मैंने ईएवी वैल्यू टेबल के 2 विदेशी कुंजी कॉलमों पर एक इंडेक्स बनाया, साथ ही एक इंडेक्स भी बनाया जिन JSONB कॉलम के लिए।

डेटा अद्यतन ने समय के संदर्भ में (एमएस में) निम्नलिखित परिणाम दिखाए। ध्यान दें कि पैमाना लघुगणकीय है:

हम देखते हैं कि यदि आप ऊपर बताए गए कारण से इंडेक्स का उपयोग नहीं करते हैं, तो JSONB EAV की तुलना में बहुत (> 50000-x) तेज़ है। जब हम प्राथमिक कुंजी के साथ कॉलम को अनुक्रमित करते हैं, तो अंतर लगभग गायब हो जाता है, लेकिन JSONB अभी भी EAV से 1,3 गुना तेज है। ध्यान दें कि JSONB कॉलम पर सूचकांक का यहां कोई प्रभाव नहीं है क्योंकि हम मूल्यांकन मानदंड में संपत्ति कॉलम का उपयोग नहीं कर रहे हैं।

संपत्ति के मूल्य के आधार पर डेटा का चयन करने पर, हमें निम्नलिखित परिणाम (सामान्य पैमाने) मिलते हैं:

आप देख सकते हैं कि JSONB फिर से इंडेक्स के बिना EAV की तुलना में तेजी से काम करता है, लेकिन जब EAV इंडेक्स के साथ होता है, तब भी यह JSONB की तुलना में तेजी से काम करता है। लेकिन फिर मैंने देखा कि JSONB प्रश्नों का समय वही था, इससे मुझे यह तथ्य पता चला कि GIN इंडेक्स काम नहीं करते हैं। जाहिरा तौर पर जब आप पॉपुलेटेड गुणों वाले कॉलम पर GIN इंडेक्स का उपयोग करते हैं, तो यह केवल शामिल ऑपरेटर @> का उपयोग करते समय प्रभावी होता है। मैंने इसे एक नए परीक्षण में उपयोग किया और उस समय इसका बहुत बड़ा प्रभाव पड़ा: केवल 0,153 एमएस! यह ईएवी से 15000 गुना तेज और ->> ऑपरेटर से 25000 गुना तेज है।

मुझे लगता है कि यह काफी तेज़ था!

डेटाबेस तालिका का आकार

आइए दोनों दृष्टिकोणों के लिए तालिका आकारों की तुलना करें। Psql में हम कमांड का उपयोग करके सभी तालिकाओं और इंडेक्स का आकार दिखा सकते हैं डीटीआई+

ईएवी दृष्टिकोण के लिए, तालिका का आकार लगभग 3068 एमबी है और कुल 3427 जीबी के लिए 6,43 एमबी तक अनुक्रमित है। JSONB दृष्टिकोण तालिका के लिए 1817 एमबी और इंडेक्स के लिए 318 एमबी का उपयोग करता है, जो 2,08 जीबी है। यह 3 गुना कम निकला! इस तथ्य ने मुझे थोड़ा आश्चर्यचकित कर दिया क्योंकि हम प्रत्येक JSONB ऑब्जेक्ट में संपत्ति के नाम संग्रहीत करते हैं।

लेकिन फिर भी, संख्याएँ अपने लिए बोलती हैं: ईएवी में हम प्रति विशेषता मान पर 2 पूर्णांक विदेशी कुंजियाँ संग्रहीत करते हैं, जिसके परिणामस्वरूप 8 बाइट्स अतिरिक्त डेटा प्राप्त होता है। इसके अतिरिक्त, ईएवी सभी संपत्ति मूल्यों को पाठ के रूप में संग्रहीत करता है, जबकि जेएसओएनबी जहां संभव हो आंतरिक रूप से संख्यात्मक और बूलियन मूल्यों का उपयोग करेगा, जिसके परिणामस्वरूप एक छोटा पदचिह्न होगा।

परिणाम

कुल मिलाकर, मुझे लगता है कि इकाई संपत्तियों को JSONB प्रारूप में सहेजने से आपके डेटाबेस को डिज़ाइन करना और बनाए रखना बहुत आसान हो सकता है। यदि आप बहुत सारी क्वेरीज़ चला रहे हैं, तो इकाई के रूप में सब कुछ एक ही तालिका में रखने से वास्तव में अधिक कुशलता से काम होगा। और तथ्य यह है कि यह डेटा के बीच इंटरैक्शन को सरल बनाता है, पहले से ही एक प्लस है, लेकिन परिणामी डेटाबेस वॉल्यूम में 3 गुना छोटा है।

इसके अलावा, किए गए परीक्षणों के आधार पर, हम यह निष्कर्ष निकाल सकते हैं कि प्रदर्शन हानि बहुत महत्वहीन है। कुछ मामलों में, JSONB EAV से भी तेज़ है, जो इसे और भी बेहतर बनाता है। हालाँकि, यह बेंचमार्क निश्चित रूप से सभी पहलुओं को कवर नहीं करता है (उदाहरण के लिए बहुत बड़ी संख्या में संपत्तियों वाली इकाइयाँ, मौजूदा डेटा की संपत्तियों की संख्या में उल्लेखनीय वृद्धि,...), इसलिए यदि आपके पास उन्हें सुधारने के बारे में कोई सुझाव है , कृपया बेझिझक टिप्पणी छोड़ें!

स्रोत: www.habr.com