PostgreSQL में विभाजन का मेरा तरीका

जब हम तालिका के आकार को नियंत्रित करना बंद कर देते हैं, तो डेटा को बनाए रखना और उपलब्ध कराना गैर-तुच्छ कार्य बन जाता है। मुझे पहले से ही उत्पादन में इस तरह की समस्या का सामना करना पड़ा, हर दिन अधिक डेटा होता है, तालिका स्मृति में फिट नहीं होती है, सर्वर लंबे समय तक प्रतिक्रिया करते हैं, लेकिन एक समाधान मिला।

नमस्कार, हेब्र! मेरा नाम डायमंड है और अब मैं एक विधि साझा करना चाहता हूं जिससे मुझे विभाजन को लागू करने में मदद मिली।

PostgreSql में विभाजन

विभाजन (या, जैसा कि वे इसे कहते हैं, विभाजन) एक बड़ी तार्किक तालिका को कई छोटे भौतिक वर्गों में विभाजित करने की प्रक्रिया है। यही वह है जो हमें अपने डेटा को प्रबंधित करने में मदद करता है।

उदाहरण: हमारे पास एक "बिक्री" तालिका है, जिसे एक महीने के अंतराल से विभाजित किया जाता है, और इन खंडों को क्षेत्र द्वारा और भी छोटे उप-वर्गों में विभाजित किया जा सकता है।


विभाजन तालिका "बिक्री" स्कीमा

इस दृष्टिकोण के विपक्ष:

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

पेशेवरों:

+ प्रदर्शन। कुछ मामलों में, हम पूरी तालिका के माध्यम से जाने के बिना एक सीमित अनुभाग के साथ काम कर सकते हैं, यहां तक ​​कि बड़ी तालिकाओं के लिए अनुक्रमणिका खोज भी धीमी होगी। डेटा उपलब्धता बढ़ाता है।
+ ATTACH / DETACH आदेशों के साथ डेटा अपलोड करें और हटाएं। यह हमें VACUUM के रूप में ओवरहेड से बचाता है। जो आपको डेटाबेस को अधिक कुशलता से बनाए रखने की अनुमति देता है।
+ अनुभाग के लिए टैब निर्दिष्ट करने की क्षमता। यह हमें अन्य अनुभागों में डेटा स्थानांतरित करने का अवसर देता है, लेकिन फिर भी हम एक ही उदाहरण के भीतर काम करते हैं और मुख्य निर्देशिका के मेटाडेटा में अनुभागों के बारे में जानकारी होगी (शार्पिंग के साथ भ्रमित होने की नहीं)

PostgreSql में विभाजन को लागू करने के 2 तरीके:

1. तालिकाओं का प्रवेश (INITSITS)
एक तालिका बनाते समय, हम कहते हैं कि "दूसरे (मूल) तालिका से विरासत"। उसी समय, हम तालिका में डेटा प्रबंधन के लिए प्रतिबंध जोड़ते हैं। इसके द्वारा हम डेटा विभाजन के तर्क का समर्थन करते हैं, लेकिन ये तार्किक रूप से भिन्न सारणी हैं।

यहां यह पोस्टग्रेज प्रोफेशनल pg_pathman द्वारा विकसित विस्तार को ध्यान में रखा जाना चाहिए, जो विभाजन को लागू करता है, तालिका विरासत के माध्यम से भी।

CREATE TABLE orders_y2010 ( CHECK (log_date >= DATE '2010-01-01) ) INHERITS (orders); 

2. घोषणात्मक दृष्टिकोण (विभाजन)

एक तालिका को घोषित रूप से विभाजित के रूप में परिभाषित किया गया है। यह समाधान PostgreSql के संस्करण 10 में दिखाई दिया।

 CREATE TABLE orders (log_date date not null, …) PARTITION BY RANGE(log_date); 

मैंने एक घोषणात्मक दृष्टिकोण चुना है। यह एक बड़ा लाभ देता है - स्वभाव, कर्नेल द्वारा अधिक सुविधाओं का समर्थन किया जाता है। इस दिशा में PostgreSQL के विकास पर विचार करें:


स्रोत

लेकिन PostgreSql विकसित होना जारी है, और संस्करण 12 में एक विभाजन तालिका से जोड़ने के लिए समर्थन है। यह एक बड़ी सफलता है।

मेरा रास्ता

उपरोक्त को देखते हुए, PL / pgSQL में एक स्क्रिप्ट लिखी गई थी, जो मौजूदा एक के आधार पर एक विभाजन तालिका बनाता है और नई तालिका के सभी लिंक "फेंकता" है। इस प्रकार, हम मौजूदा एक के आधार पर एक विभाजन तालिका प्राप्त करते हैं और एक नियमित तालिका के साथ इसके साथ काम करना जारी रखते हैं।
स्क्रिप्ट को अतिरिक्त निर्भरता की आवश्यकता नहीं है और एक अलग सर्किट में चलता है जो इसे स्वयं बनाता है। इसके अलावा फिर से और पूर्ववत कार्रवाई लॉग करता है। यह स्क्रिप्ट दो मुख्य कार्यों को हल करती है: एक विभाजन तालिका बनाता है और ट्रिगर ट्रिगर के माध्यम से बाहरी लिंक को लागू करता है।

स्क्रिप्ट की आवश्यकता: PostgreSql v.:11 और उच्चतर।

अब स्क्रिप्ट के बारे में अधिक विस्तार से जानते हैं। इंटरफ़ेस बहुत सरल है:
दो प्रक्रियाएं हैं जो सभी काम करती हैं।

1. मुख्य चुनौती - इस स्तर पर हम मुख्य तालिका को नहीं बदलते हैं, लेकिन सेक्शनिंग के लिए आवश्यक सब कुछ एक अलग योजना में बनाया जाएगा:

  call partition_run(); 

2. मुख्य कार्य के दौरान योजनाबद्ध कार्यों को कॉल करें:

  call partition_run_jobs(); 

कार्य को कई थ्रेड में लॉन्च किया जा सकता है। धागे की इष्टतम संख्या विभाजन तालिका की संख्या के करीब है।

स्क्रिप्ट के लिए इनपुट पैरामीटर (_pt रिकॉर्ड)



स्क्रिप्ट अंदर से, मुख्य क्रियाएँ:

- एक विभाजन तालिका बनाएँ

  perform _partition_create_parent_table(_pt); 

- अनुभाग बनाएं

  perform _partition_create_child_tables(_pt); 

- सेक्शन में डेटा कॉपी करें

  perform _partition_copy_data(_pt); 

- प्रतिबंध (नौकरी) जोड़ें

  perform _partition_add_constraints(_pt); 

- बाहरी तालिकाओं के लिंक को पुनर्स्थापित करें

  perform _partition_restore_referrences(_pt); 

- ट्रिगर को पुनर्स्थापित करें

  perform _partition_restore_triggers(_pt); 

- एक घटना ट्रिगर बनाएँ

  perform _partition_def_tr_on_delete(_pt); 

- अनुक्रमणिका बनाएँ (नौकरी)

  perform _partition_create_index(_pt); 

- विचार, अनुभाग लिंक (नौकरी) बदलें

  perform _partition_replace_view(_pt); 

स्क्रिप्ट का रनिंग समय कई कारकों पर निर्भर करता है, लेकिन मुख्य लक्ष्य तालिका के आकार, संबंधों की संख्या, अनुक्रमित और सर्वर की विशेषताएं हैं। मेरे मामले में, एक 300Gb तालिका को एक घंटे से भी कम समय में विभाजित किया गया था।

परिणाम

हमें क्या मिला? आइए प्रश्न योजना को देखें:

  EXPLAIN ANALYZE select * from “sales” where dt BETWEEN '01.01.2019'::date and '14.01.2019'::date 

हमें विभाजन तालिका से परिणाम तेजी से मिला और एक नियमित तालिका की तुलना में हमारे सर्वर के कम संसाधनों का उपयोग किया।

इस उदाहरण में, नियमित और विभाजित टेबल एक ही आधार पर हैं और लगभग 200M रिकॉर्ड हैं। यह एक अच्छा परिणाम है, यह देखते हुए कि हमने, आवेदन कोड को फिर से लिखे बिना, त्वरण प्राप्त किया। अन्य अनुक्रमित पर क्वेरी भी अच्छी तरह से काम करती हैं, लेकिन याद रखें: जब भी हम किसी अनुभाग को निर्धारित कर सकते हैं, तो परिणाम कई गुना तेजी से होगा, क्योंकि PostgreSql अनुरोध नियोजन चरण में अतिरिक्त अनुभागों को छोड़ सकता है ( पर enable_partition_pruning सेट करें )।

परिणाम

मैं उन तालिकाओं पर विभाजन को लागू करने में कामयाब रहा जिनके कई संबंध हैं और डेटाबेस अखंडता सुनिश्चित करते हैं। स्क्रिप्ट विशिष्ट डेटा संरचनाओं से स्वतंत्र है और इसका पुन: उपयोग किया जा सकता है।

PostgreSQL दुनिया का सबसे उन्नत ओपन सोर्स रिलेशनल डेटाबेस है!

आप सभी को धन्यवाद!

स्रोत से लिंक करें