🥇PostgreSQL मध्ये EAV ची जागा JSONB ने घेणे

टीएल; DR: JSONB क्वेरी कामगिरीचा त्याग न करता डेटाबेस स्कीमा विकास मोठ्या प्रमाणात सुलभ करू शकते.

परिचय

रिलेशनल डेटाबेस (डेटाबेस) च्या जगातील सर्वात जुन्या वापर प्रकरणांपैकी एक उत्कृष्ट उदाहरण देऊ: आमच्याकडे एक अस्तित्व आहे आणि आम्हाला या घटकाचे विशिष्ट गुणधर्म (विशेषता) जतन करणे आवश्यक आहे. परंतु सर्व उदाहरणांमध्ये गुणधर्मांचा एकच संच असू शकत नाही आणि भविष्यात आणखी गुणधर्म जोडले जाऊ शकतात.

या समस्येचे निराकरण करण्याचा सर्वात सोपा मार्ग म्हणजे प्रत्येक मालमत्ता मूल्यासाठी डेटाबेस टेबलमध्ये एक स्तंभ तयार करणे आणि विशिष्ट घटक उदाहरणासाठी आवश्यक असलेले फक्त भरणे. छान! समस्या सोडवली... जोपर्यंत तुमच्या टेबलमध्ये लाखो रेकॉर्ड्स असतील आणि तुम्हाला नवीन रेकॉर्ड जोडण्याची आवश्यकता असेल.

EAV नमुना विचारात घ्या (अस्तित्व-विशेषता-मूल्य), हे बऱ्याचदा उद्भवते. एका टेबलमध्ये संस्था (रेकॉर्ड) असतात, दुसऱ्या टेबलमध्ये प्रॉपर्टीची नावे (विशेषता) असतात आणि तिसऱ्या टेबलमध्ये संस्थांना त्यांच्या विशेषतांशी जोडले जाते आणि सध्याच्या घटकासाठी त्या विशेषतांचे मूल्य असते. हे तुम्हाला वेगवेगळ्या वस्तूंसाठी गुणधर्मांचे वेगवेगळे संच ठेवण्याची आणि डेटाबेस संरचना न बदलता फ्लायवर गुणधर्म जोडण्याची क्षमता देते.

तथापि, जर ईव्हीए पध्दतीमध्ये काही तोटे नसतील तर मी हे पोस्ट लिहिणार नाही. म्हणून, उदाहरणार्थ, प्रत्येकी 1 विशेषता असलेल्या एक किंवा अधिक घटक प्राप्त करण्यासाठी, क्वेरीमध्ये 2 जोडणे आवश्यक आहे: प्रथम गुणधर्म सारणीसह जोडणे आहे, दुसरे मूल्य सारणीसह जोडणे आहे. एखाद्या घटकामध्ये 2 गुणधर्म असल्यास, 4 जोडणे आवश्यक आहेत! याव्यतिरिक्त, सर्व विशेषता सामान्यत: स्ट्रिंग म्हणून संग्रहित केल्या जातात, ज्यामुळे परिणाम आणि WHERE क्लॉज दोन्हीसाठी टाइप कास्टिंग होते. जर तुम्ही अनेक प्रश्न लिहित असाल, तर संसाधनांच्या वापराच्या दृष्टीने हे खूप व्यर्थ आहे.

या स्पष्ट उणीवा असूनही, या प्रकारच्या समस्यांचे निराकरण करण्यासाठी EAV चा दीर्घकाळ वापर केला जात आहे. या अपरिहार्य उणीवा होत्या आणि यापेक्षा चांगला पर्याय नव्हता.
पण नंतर PostgreSQL मध्ये एक नवीन "तंत्रज्ञान" दिसू लागले...

PostgreSQL 9.4 सह प्रारंभ करून, JSON बायनरी डेटा संचयित करण्यासाठी JSONB डेटा प्रकार जोडला गेला. जरी JSON या फॉरमॅटमध्ये साठवण्यासाठी सामान्यत: साध्या मजकुराच्या JSON पेक्षा थोडी जास्त जागा आणि वेळ लागतो, तरीही त्यावर ऑपरेशन्स करणे खूप जलद आहे. JSONB अनुक्रमणिकाला देखील समर्थन देते, जे क्वेरी आणखी जलद करते.

JSONB डेटा प्रकार आम्हाला आमच्या घटक सारणीमध्ये फक्त एक JSONB स्तंभ जोडून अवजड EAV पॅटर्न बदलण्याची परवानगी देतो, डेटाबेस डिझाइन मोठ्या प्रमाणात सुलभ करतो. पण अनेकांचा असा युक्तिवाद आहे की यासोबत उत्पादकता कमी झाली पाहिजे... म्हणूनच मी हा लेख लिहिला आहे.

चाचणी डेटाबेस सेट करत आहे

या तुलनेसाठी, मी $9.5 बिल्डवर PostgreSQL 80 च्या नवीन इंस्टॉलेशनवर डेटाबेस तयार केला आहे. डिजिटल महासागर Ubuntu १४.०४ 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';

मला वाटते की आम्ही सहमत होऊ शकतो की दुसरा लहान आहे (सामील नाही!), आणि म्हणून अधिक वाचनीय आहे. 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;

EAV सह तुम्हाला प्रत्येक मालमत्तेसाठी 2 जॉइन्सची आवश्यकता असेल ज्याची तुम्हाला क्वेरी करायची आहे. माझ्या मते, वरील प्रश्न डेटाबेस डिझाइनमध्ये एक उत्कृष्ट सरलीकरण दर्शवतात. JSONB क्वेरी कशा लिहायच्या याची आणखी उदाहरणे पहा हे पोस्ट.
आता कामगिरीबद्दल बोलण्याची वेळ आली आहे.

उत्पादकता

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

डेटा अपडेटने खालील परिणाम वेळेनुसार (ms मध्ये) दाखवले. लक्षात घ्या की स्केल लॉगरिदमिक आहे:

PostgreSQL मध्ये JSONB सह EAV बदलत आहे

आपण वर नमूद केलेल्या कारणास्तव, जर तुम्ही अनुक्रमणिका वापरत नसाल तर JSONB हे EAV पेक्षा कितीतरी जास्त (> 50000-x) वेगवान असल्याचे आम्हाला दिसते. जेव्हा आम्ही प्राथमिक की सह स्तंभ अनुक्रमित करतो, तेव्हा फरक जवळजवळ नाहीसा होतो, परंतु JSONB अजूनही EAV पेक्षा 1,3 पट वेगवान आहे. लक्षात ठेवा की JSONB स्तंभावरील निर्देशांकाचा येथे कोणताही प्रभाव नाही कारण आम्ही मूल्यमापन निकषांमध्ये गुणधर्म स्तंभ वापरत नाही.

मालमत्तेच्या मूल्यावर आधारित डेटा निवडण्यासाठी, आम्हाला खालील परिणाम मिळतात (सामान्य प्रमाण):

PostgreSQL मध्ये JSONB सह EAV बदलत आहे

तुमच्या लक्षात येईल की JSONB पुन्हा निर्देशांकांशिवाय EAV पेक्षा जलद काम करते, परंतु जेव्हा EAV निर्देशांकांसह, तरीही ते JSONB पेक्षा जलद कार्य करते. पण नंतर मी पाहिले की JSONB प्रश्नांची वेळ सारखीच होती, यामुळे मला GIN निर्देशांक कार्य करत नाहीत या वस्तुस्थितीकडे प्रवृत्त केले. वरवर पाहता जेव्हा तुम्ही पॉप्युलेट केलेल्या गुणधर्म असलेल्या कॉलमवर जीआयएन इंडेक्स वापरता, तेव्हा ते समाविष्ट ऑपरेटर @> वापरतानाच प्रभावी होते. मी हे एका नवीन चाचणीमध्ये वापरले आणि त्याचा वेळेवर मोठा प्रभाव पडला: फक्त 0,153ms! हे EAV पेक्षा 15000 पट जास्त आणि -->> ऑपरेटर पेक्षा 25000 पट जास्त वेगवान आहे.

मला वाटते की ते पुरेसे जलद होते!

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

दोन्ही पद्धतींसाठी सारणीच्या आकारांची तुलना करूया. psql मध्ये आपण कमांड वापरून सर्व टेबल्स आणि इंडेक्सचा आकार दाखवू शकतो dti+

PostgreSQL मध्ये JSONB सह EAV बदलत आहे

EAV पद्धतीसाठी, टेबलचा आकार सुमारे 3068 MB आहे आणि एकूण 3427 GB साठी 6,43 MB पर्यंत अनुक्रमणिका आहे. JSONB दृष्टिकोन टेबलसाठी 1817 MB आणि निर्देशांकांसाठी 318 MB वापरतो, जे 2,08 GB आहे. हे 3 वेळा कमी बाहेर वळते! या वस्तुस्थितीमुळे मला थोडे आश्चर्य वाटले कारण आम्ही प्रत्येक JSONB ऑब्जेक्टमध्ये मालमत्तेची नावे संग्रहित करतो.

परंतु तरीही, संख्या स्वतःसाठी बोलतात: EAV मध्ये आम्ही प्रति विशेषता मूल्य 2 पूर्णांक विदेशी की संग्रहित करतो, परिणामी 8 बाइट्स अतिरिक्त डेटा मिळतो. याव्यतिरिक्त, EAV सर्व मालमत्ता मूल्ये मजकूर म्हणून संग्रहित करते, तर JSONB अंकीय आणि बुलियन मूल्ये जेथे शक्य असेल तेथे अंतर्गत वापरेल, परिणामी एक लहान फूटप्रिंट होईल.

परिणाम

एकंदरीत, मला वाटते जेएसओएनबी फॉरमॅटमध्ये एंटिटी गुणधर्म जतन केल्याने तुमचा डेटाबेस डिझाइन करणे आणि देखरेख करणे खूप सोपे होऊ शकते. तुम्ही पुष्कळ क्वेरी चालवत असल्यास, सर्व काही घटक सारणीत ठेवल्यास ते अधिक कार्यक्षमतेने कार्य करेल. आणि हे डेटामधील परस्परसंवाद सुलभ करते हे तथ्य आधीपासूनच एक प्लस आहे, परंतु परिणामी डेटाबेस व्हॉल्यूममध्ये 3 पट लहान आहे.

तसेच, केलेल्या चाचण्यांच्या आधारे, आम्ही असा निष्कर्ष काढू शकतो की कामगिरीचे नुकसान फारच क्षुल्लक आहे. काही प्रकरणांमध्ये, JSONB EAV पेक्षा अधिक वेगवान आहे, ते आणखी चांगले बनवते. तथापि, हा बेंचमार्क अर्थातच सर्व पैलूंचा अंतर्भाव करत नाही (उदा. खूप मोठ्या संख्येने गुणधर्म असलेल्या संस्था, विद्यमान डेटाच्या गुणधर्मांच्या संख्येत लक्षणीय वाढ,...), त्यामुळे तुमच्याकडे त्या कशा सुधारायच्या याबद्दल काही सूचना असल्यास , कृपया टिप्पण्यांमध्ये मोकळ्या मनाने सोडा!

स्त्रोत: www.habr.com