👵🏻 👨🏻‍⚕️ ⛳️ पोस्टग्रे (नहीं) एसक्यूएल या फिर एक लचीली संरचना के साथ डेटा संग्रहीत करने के बारे में 👩🏾‍🤝‍👨🏽 🔶 🤚🏻

जब डेटाबेस में लचीले (पहले अज्ञात, अक्सर संशोधित) डेटा संरचनाओं को संग्रहीत करने की बात आती है, तो डेवलपर्स आमतौर पर "महान और भयानक" ईएवी पैटर्न, या अब फैशनेबल एनओएसक्यूएल डेटाबेस की ओर मुड़ते हैं।
बहुत समय पहले ऐसा काम मेरे सामने नहीं हुआ था।
- ईएवी । यह मेरे लिए लगातार शत्रुता का कारण बनता है, और यह कहा गया था और इसके बारे में लिखा गया था कि सभी नकारात्मक (पतंग, फाउलर, कार्विन, गोरमन) बहुत सारे थे। मुख्य नुकसान यह है कि प्रश्न लिखते समय, आपको वास्तविक संस्थाओं ("कर्मचारी", "होम", "क्लाइंट", जो कि एसक्यूएल के लिए है) के साथ काम नहीं करना है, लेकिन निचले स्तर पर आयोजित वस्तुओं के साथ (भ्रम के लिए खेद है)। इसलिए, यह सबसे अवांछनीय विकल्प था।
- एनओएसक्यूएल । सबसे पहले, यह विकल्प बहुत रुचि रखता था (विशेष रूप से, MongoDB)। संबंधपरक प्रणालियों के लंबे समय तक उपयोग के बाद, सबसे पहले आप कुल स्वतंत्रता की भावना का अनुभव करना शुरू करते हैं, जो आपकी सांस को दूर ले जाता है। किसी भी संरचना के दस्तावेजों का भंडारण, नए संग्रह का त्वरित निर्माण, उनसे अनुरोध - सौंदर्य! लेकिन एक छोटे से उपयोग के बाद, उत्साह कम होना शुरू हो गया, और समस्याएं दिखाई गईं:
- खराब क्वेरी भाषा (IMHO) + जोड़ की कमी;
- योजनाओं का अभाव (एक अच्छा लेख हाल ही में इस विषय पर था (और केवल इस एक पर नहीं) habrahabr.ru/post/164361 );
- संदर्भात्मक अखंडता के लिए अंतर्निहित समर्थन का अभाव;
- संग्रहीत कार्यविधियों / कार्यों, ट्रिगर, विचार और बहुत कुछ के रूप में गैजेट्स का अभाव।
- मेरे आवेदन में, एक लचीली (चर) संरचना के साथ डेटा के अलावा, साधारण स्थैतिक डेटा को संग्रहीत करना भी आवश्यक है - उपयोगकर्ताओं, विज़िट, कर्मचारियों, आदि की एक तालिका। जिसके साथ काम करना (फिर से IMHO) एक सामान्य संबंधपरक डेटाबेस (अधिक समान संदर्भात्मक अखंडता, आदि) में अधिक सरल और (सबसे महत्वपूर्ण) अधिक विश्वसनीय है।

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

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

शुरुआत के लिए, दो सर्विस टेबल पर्याप्त हैं, मुझे लगता है कि टिप्पणियां बहुत कम हैं:

CREATE TABLE classes ( id integer NOT NULL, name text, is_closed boolean, obects_count integer, CONSTRAINT classes_pk PRIMARY KEY (id ) ); CREATE TABLE objects ( id integer NOT NULL, body xml, id_classes integer, CONSTRAINT objects_pk PRIMARY KEY (id ), CONSTRAINT classes_objects FOREIGN KEY (id_classes) REFERENCES classes (id) MATCH SIMPLE ON UPDATE NO ACTION ON DELETE NO ACTION ); CREATE INDEX fki_classes_objects ON objects USING btree (id_classes );

हम प्रयोग के लिए दो संस्थाएँ बनाते हैं:

 INSERT INTO classes( id, name, is_closed, obects_count) VALUES (1, 'customers', FALSE, 0); INSERT INTO classes( id, name, is_closed, obects_count) VALUES (2, 'orders', FALSE, 0);

हम परीक्षण रैंडम डेटा (इंटरनेट पर लिया गया) उत्पन्न करने के लिए दो कार्य तैयार करेंगे:

 CREATE OR REPLACE FUNCTION random(numeric, numeric) RETURNS numeric AS $BODY$ SELECT ($1 + ($2 - $1) * random())::numeric; $BODY$ LANGUAGE sql VOLATILE COST 100; CREATE OR REPLACE FUNCTION random_string(length integer) RETURNS text AS $BODY$ declare chars text[] := '{0,1,2,3,4,5,6,7,8,9,A,B,C,D,E,F,G,H,I,J,K,L,M,N,O,P,Q,R,S,T,U,V,W,X,Y,Z,a,b,c,d,e,f,g,h,i,j,k,l,m,n,o,p,q,r,s,t,u,v,w,x,y,z}'; result text := ''; i integer := 0; begin if length < 0 then raise exception 'Given length cannot be less than 0'; end if; for i in 1..length loop result := result || chars[1+random()*(array_length(chars, 1)-1)]; end loop; return result; end; $BODY$ LANGUAGE plpgsql VOLATILE COST 100;

यादृच्छिक डेटा के साथ तालिका भरना, कक्षाओं की वस्तुएँ "क्लाइंट" और "ऑर्डर" (एक-से-कई संबंध, प्रत्येक ग्राहक ने अन्य ऑर्डर किए हैं):

 DO $$ DECLARE customer_pk integer; order_pk integer; BEGIN FOR i in 1..10000 LOOP customer_pk := nextval('objects_id_seq'); order_pk := nextval('objects_id_seq'); insert into objects (body, id_classes) values(( '<Customers> <Customer> <ID>' || customer_pk || '</ID> <Name>' || random_string('10') || '</Name> <Partners>' || random_string('10') || '</Partners> </Customer> </Customers>')::xml, 1); for j in 1..5 LOOP insert into objects (body, id_classes) values(( '<Orders> <Order> <ID>' || order_pk || '</ID> <Customer_id>' || customer_pk || '</Customer_id> <Cost>' || random(1, 1000) || '</Cost> </Order> </Orders>')::xml, 2); end loop; END LOOP; END$$;

पहला अनुरोध हम अधिकतम ऑर्डर मूल्य चुनते हैं:

 explain select max(((xpath('/Orders/Order/Cost/text()', O.body))[1])::text::float) as cost_of_order from Objects O where O.id_classes = 2;

 /* Aggregate (cost=2609.10..2609.11 rows=1 width=32) -> Seq Scan on objects o (cost=0.00..2104.50 rows=50460 width=32) Filter: (id_classes = 2) */

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

 create index obj_orders_cost_idx on objects using btree (((xpath('/Orders/Order/Cost/text()', body))[1]::text::float));

और अब अनुरोध बहुत तेजी से पूरा होता है और सूचकांक का उपयोग करता है:

 /* Result (cost=0.15..0.16 rows=1 width=0) InitPlan 1 (returns $0) -> Limit (cost=0.00..0.15 rows=1 width=32) -> Index Scan Backward using obj_orders_cost_idx on objects o (cost=0.00..7246.26 rows=50207 width=32) Index Cond: ((((xpath('/Orders/Order/Cost/text()'::text, body, '{}'::text[]))[1])::text)::double precision IS NOT NULL) Filter: (id_classes = 2) */

अब चलो कई विशिष्ट कर्मचारियों के आदेशों के बारे में जानकारी का चयन करने की कोशिश करते हैं, अर्थात्। दो तालिकाओं का एक गुच्छा:

 explain select (xpath('/Customers/Customer/Name/text()', C.body))[1] as customer , (xpath('/Orders/Order/Cost/text()', O.body))[1] as cost_of_order from objects C , objects O where C.id_classes = 1 and O.id_classes = 2 and (xpath('/Orders/Order/Customer_id/text()', O.body))[1]::text::int = (xpath('/Customers/Customer/ID/text()', C.body))[1]::text::int and ((xpath('/Customers/Customer/ID/text()' ,C.body))[1])::text::int between 1997585 and 1997595;

 /* Hash Join (cost=1873.57..6504.85 rows=12867 width=64) Hash Cond: ((((xpath('/Orders/Order/Customer_id/text()'::text, o.body, '{}'::text[]))[1])::text)::integer = (((xpath('/Customers/Customer/ID/text()'::text, c.body, '{}'::text[]))[1])::text)::integer) -> Seq Scan on objects o (cost=0.00..2104.50 rows=50460 width=32) Filter: (id_classes = 2) -> Hash (cost=1872.93..1872.93 rows=51 width=32) -> Bitmap Heap Scan on objects c (cost=196.38..1872.93 rows=51 width=32) Recheck Cond: (id_classes = 1) Filter: (((((xpath('/Customers/Customer/ID/text()'::text, body, '{}'::text[]))[1])::text)::integer >= 1997585) AND ((((xpath('/Customers/Customer/ID/text()'::text, body, '{}'::text[]))[1])::text)::integer <= 1997595)) -> Bitmap Index Scan on fki_classes_objects (cost=0.00..196.37 rows=10140 width=0) Index Cond: (id_classes = 1) */

फुलस्कैन अपेक्षित, अब सूचकांक थोड़ा:

 create index obj_customers_id_idx on objects using btree (((xpath('/Customers/Customer/ID/text()', body))[1]::text::int)); create index obj_orders_id_idx on objects using btree (((xpath('/Orders/Order/ID/text()', body))[1]::text::int)); create index obj_orders_customerid_idx on objects using btree (((xpath('/Orders/Order/Customer_id/text()', body))[1]::text::int));

अब यह और मजेदार है:

 /* Hash Join (cost=380.52..5011.80 rows=12867 width=64) Hash Cond: ((((xpath('/Orders/Order/Customer_id/text()'::text, o.body, '{}'::text[]))[1])::text)::integer = (((xpath('/Customers/Customer/ID/text()'::text, c.body, '{}'::text[]))[1])::text)::integer) -> Seq Scan on objects o (cost=0.00..2104.50 rows=50460 width=32) Filter: (id_classes = 2) -> Hash (cost=379.88..379.88 rows=51 width=32) -> Bitmap Heap Scan on objects c (cost=204.00..379.88 rows=51 width=32) Recheck Cond: (((((xpath('/Customers/Customer/ID/text()'::text, body, '{}'::text[]))[1])::text)::integer >= 1997585) AND ((((xpath('/Customers/Customer/ID/text()'::text, body, '{}'::text[]))[1])::text)::integer <= 1997595) AND (id_classes = 1)) -> BitmapAnd (cost=204.00..204.00 rows=51 width=0) -> Bitmap Index Scan on obj_customers_id_idx (cost=0.00..7.35 rows=303 width=0) Index Cond: (((((xpath('/Customers/Customer/ID/text()'::text, body, '{}'::text[]))[1])::text)::integer >= 1997585) AND ((((xpath('/Customers/Customer/ID/text()'::text, body, '{}'::text[]))[1])::text)::integer <= 1997595)) -> Bitmap Index Scan on fki_classes_objects (cost=0.00..196.37 rows=10140 width=0) Index Cond: (id_classes = 1) */

इस अनुरोध ने भी अपनी स्पष्टता नहीं खोई, हालांकि इसे और भी कंघी किया जा सकता है: प्रकार के रूपांतरण से निपटना, एक्सएमएल संरचना को अनुकूलित करना, आदि। अभी भी बहुत काम है, यह केवल एक छोटा सा उदाहरण है।

और क्या किया जा सकता है:

1. किसी भी वर्ग की वस्तुओं की विशेषताओं द्वारा लचीली खोज;
2. वस्तुओं की मेज को विभाजित किया जा सकता है (कम से कम आंशिक रूप से), उदाहरण के लिए, बड़ी कक्षाओं की वस्तुओं को भौतिक रूप से अलग-अलग संग्रहीत करने के लिए।

डेटाबेस में xml प्रारूप में डेटा संग्रहीत करना स्वाभाविक रूप से एक नवीनता नहीं है, हालांकि, जब मेरे प्रश्न का हल खोजते हैं, तो इस बारे में बहुत कम जानकारी थी, विशिष्ट उदाहरणों का उल्लेख नहीं करना। मुझे आशा है कि कोई व्यक्ति काम में आता है और / या टिप्पणियों में उन लोगों की समीक्षाओं और विचारों को सुनता है जिन्होंने एक समान योजना के साथ काम किया है।

पोस्टग्रे (नहीं) एसक्यूएल या फिर एक लचीली संरचना के साथ डेटा संग्रहीत करने के बारे में

More articles: