PostgreSQL में JSONB के साथ EAV की जगह

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

परिचय

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

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

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

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

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

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

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

परीक्षण डेटाबेस सेटअप

इस तुलना के लिए, मैंने DigitalOcean Ubuntu 14.04 के $ 80 बिल्ड पर PostgreSQL 9.5 की एक नई स्थापना पर एक डेटाबेस बनाया। 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 कॉलम के साथ दृष्टिकोण का उपयोग करके भरा गया था - Unit_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 ऑब्जेक्ट के रूप में हमारे नए मान को पास करना होगा। हालांकि, हमें पहले से किसी भी पहचानकर्ता को जानने की आवश्यकता नहीं है। ईएवी उदाहरण को देखते हुए, हमें अद्यतन करने के लिए, दोनों Unit_id और unit_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'; 

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

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



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

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



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

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

DB तालिका आकार

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



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

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

परिणाम

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

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