PostgreSQL 9 में विभाजन को समझना

PostgreSQL 10 को लगभग एक साल पहले अक्टूबर 2017 की शुरुआत में वापस जारी किया गया था।

सबसे दिलचस्प नई "सुविधाओं" में से एक बिना शर्त घोषित विभाजन है। लेकिन क्या होगा अगर आप 10k पर अपग्रेड करने की जल्दी में नहीं हैं? उदाहरण के लिए, अमेज़ॅन कोई जल्दी में नहीं है, और फरवरी 2018 के अंतिम दिनों में ही पोस्टग्रेक्यूएल 10 समर्थन पेश किया ।

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

चूंकि हमने 2015 में अपनी वित्तीय प्रणाली को फिर से लिखना शुरू किया, जब मैं बस कंपनी में शामिल हो गया, तो किसी भी घोषणात्मक विभाजन की कोई बात नहीं हुई। इसलिए आज तक, नीचे वर्णित तकनीक का सफलतापूर्वक उपयोग किया गया है।

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

हमारे मामले में, हम दो कॉलमों में तालिकाओं को विभाजित करते हैं, जिनमें से एक में यात्रा की तारीखें होती हैं। यह ऐसा मामला है जिस पर हम विचार करेंगे।

आइए शुरू करें कि हमारी तालिका कैसी दिखती है:

create table rides ( id bigserial not null primary key, tenant_id varchar(20) not null, ride_id varchar(36) not null, created_at timestamp with time zone not null, metadata jsonb -- Probably more columns and indexes coming here ); 

प्रत्येक किरायेदार के लिए, तालिका में प्रति माह लाखों पंक्तियाँ होती हैं। सौभाग्य से, किरायेदारों के बीच का डेटा कभी भी अंतर नहीं होता है, और सबसे कठिन अनुरोध एक या दो महीने के कटौती पर किए जाते हैं।

उन लोगों के लिए जिन्होंने पोस्टग्रेसीक्यू विभाजन में काम नहीं किया है (ओरेकल, हैलो! से भाग्यशाली!), मैं संक्षेप में इस प्रक्रिया का वर्णन करूंगा।

PostgreSQL इसके लिए अपने दो "फीचर्स" पर निर्भर करता है: टेबल, टेबल इनहेरिटेंस, और चेक की गई स्थितियों को इनहेरिट करने की क्षमता।

चलो विरासत के साथ शुरू करते हैं। INHERITS कीवर्ड का उपयोग करते हुए, हम इंगित करते हैं कि हम जिस तालिका का निर्माण करते हैं, वह विरासत में मिली तालिका के सभी क्षेत्रों को प्राप्त करती है। यह दो तालिकाओं के बीच एक संबंध भी बनाता है: माता-पिता से एक क्वेरी बनाते हुए, हम बच्चों से सभी डेटा भी प्राप्त करते हैं।

जाँच की गई स्थिति यह सुनिश्चित करती है कि डेटा प्रतिच्छेद नहीं करता है। इस प्रकार, PostgreSQL अनुकूलक क्वेरी से डेटा पर भरोसा करके बच्चे की तालिकाओं के हिस्से को काट सकता है।

इस दृष्टिकोण का पहला नुकसान काफी स्पष्ट प्रतीत होता है: किसी भी अनुरोध में किरायेदार_िद होना चाहिए। और फिर भी, यदि आप लगातार खुद को इस बात की याद नहीं दिलाते हैं, तो जल्दी या बाद में आप खुद कस्टम SQL लिखेंगे जिसमें आप इस tenant_id को निर्दिष्ट करना भूल जाते हैं। नतीजतन, सभी विभाजनों का स्कैन और एक गैर-कार्यशील डेटाबेस।

लेकिन वापस जो हम हासिल करना चाहते हैं। आवेदन स्तर पर, मैं पारदर्शिता चाहूंगा - हम हमेशा एक ही तालिका में लिखते हैं, और पहले से ही डेटाबेस चुनता है कि वास्तव में यह डेटा कहां रखा जाए।

ऐसा करने के लिए, हम निम्नलिखित संग्रहीत कार्यविधि का उपयोग करते हैं:

 CREATE OR REPLACE FUNCTION insert_row() RETURNS TRIGGER AS $BODY$ DECLARE partition_env TEXT; partition_date TIMESTAMP; partition_name TEXT; sql TEXT; BEGIN -- construct partition name partition_env := lower(NEW.tenant_id); partition_date := date_trunc('month', NEW.created_at AT TIME ZONE 'UTC'); partition_name := format('%s_%s_%s', TG_TABLE_NAME, partition_env, to_char(partition_date, 'YYYY_MM')); -- create partition, if necessary IF NOT EXISTS(SELECT relname FROM pg_class WHERE relname = partition_name) THEN PERFORM create_new_partition(TG_TABLE_NAME, NEW.tenant_id, partition_date, partition_name); END IF; select format('INSERT INTO %s values ($1.*)', partition_name) into sql; -- Propagate insert EXECUTE sql USING NEW; RETURN NEW; -- RETURN NULL; if no ORM END; $BODY$ LANGUAGE plpgsql; 

पहली बात जिस पर आपको ध्यान देना चाहिए वह है TG_TABLE_NAME का उपयोग। चूंकि यह एक ट्रिगर है, PostgreSQL हमारे लिए काफी कुछ वैरिएबल भरता है जिसे हम एक्सेस कर सकते हैं। पूरी सूची यहां देखी जा सकती है ।

हमारे मामले में, हम उस तालिका के माता-पिता का नाम प्राप्त करना चाहते हैं, जिस पर ट्रिगर ने काम किया है। हमारे मामले में, यह सवारी होगी। हम कई microservices में एक समान दृष्टिकोण का उपयोग करते हैं, और इस हिस्से को परिवर्तनों के बिना व्यावहारिक रूप से स्थानांतरित किया जा सकता है।

यदि हम एक फ़ंक्शन को कॉल करना चाहते हैं, तो PERFORM उपयोगी है। आमतौर पर, उदाहरणों में, वे सभी तर्कों को एक समारोह में रखने की कोशिश करते हैं, लेकिन हम सावधान रहने की कोशिश करते हैं।

USING NEW इंगित करता है कि इस क्वेरी में हम उन स्ट्रिंग से मानों का उपयोग करते हैं जिन्हें हमने जोड़ने की कोशिश की थी।

$1.* सभी न्यूलाइन मूल्यों का विस्तार करेगा। वास्तव में, इसका अनुवाद NEW.* में किया जा सकता है NEW.* । NEW.ID, NEW.TENANT_ID, …

निम्न प्रक्रिया, जिसे हम PERFORM कहते हैं, एक नया विभाजन बनाएगी, अगर यह पहले से मौजूद नहीं है। यह प्रत्येक किरायेदार के लिए प्रति अवधि एक बार होगा।

 CREATE OR REPLACE FUNCTION create_new_partition(parent_table_name text, env text, partition_date timestamp, partition_name text) RETURNS VOID AS $BODY$ DECLARE sql text; BEGIN -- Notifying RAISE NOTICE 'A new % partition will be created: %', parent_table_name, partition_name; select format('CREATE TABLE IF NOT EXISTS %s (CHECK ( tenant_id = ''%s'' AND created_at AT TIME ZONE ''UTC'' > ''%s'' AND created_at AT TIME ZONE ''UTC'' <= ''%s'')) INHERITS (%I)', partition_name, env, partition_date, partition_date + interval '1 month', parent_table_name) into sql; -- New table, inherited from a master one EXECUTE sql; PERFORM index_partition(partition_name); END; $BODY$ LANGUAGE plpgsql; 

जैसा कि पहले बताया गया है, हम माता-पिता के समान एक तालिका बनाने के लिए INHERITS का उपयोग करते हैं, और यह निर्धारित करने के लिए कि वहां क्या डेटा जाना चाहिए।

RAISE NOTICE सिर्फ एक स्ट्रिंग को कंसोल पर प्रिंट करता है। यदि हम अब PsSl से INSERT चलाते हैं, तो हम देख सकते हैं कि क्या विभाजन बनाया गया था।

हमें एक नई समस्या है। सूचकांक अनुक्रमणिकाओं को विरासत में नहीं INHERITS है। ऐसा करने के लिए, हमारे पास दो समाधान हैं:

वंशानुक्रम का उपयोग करके अनुक्रमित बनाएँ:
ALTER TABLE INHERITS CREATE TABLE LIKE उपयोग करें और फिर ALTER TABLE INHERITS

या अनुक्रमिक रूप से अनुक्रमित बनाएँ:

 CREATE OR REPLACE FUNCTION index_partition(partition_name text) RETURNS VOID AS $BODY$ BEGIN -- Ensure we have all the necessary indices in this partition; EXECUTE 'CREATE INDEX IF NOT EXISTS ' || partition_name || '_tenant_timezone_idx ON ' || partition_name || ' (tenant_id, timezone(''UTC''::text, created_at))'; -- More indexes here... END; $BODY$ LANGUAGE plpgsql; 

बाल तालिकाओं को अनुक्रमित करने के बारे में नहीं भूलना बहुत महत्वपूर्ण है, क्योंकि विभाजन के बाद भी, उनमें से प्रत्येक में लाखों पंक्तियाँ होंगी। माता-पिता पर अनुक्रमित करने की आवश्यकता हमारे मामले में नहीं है, क्योंकि माता-पिता हमेशा खाली रहेंगे।

अंत में, हम एक ट्रिगर बनाते हैं जिसे तब बुलाया जाएगा जब एक नई लाइन बनाई जाएगी:

 CREATE TRIGGER before_insert_row_trigger BEFORE INSERT ON rides FOR EACH ROW EXECUTE PROCEDURE insert_row(); 

एक और सूक्ष्मता है जिस पर शायद ही कभी ध्यान दिया जाता है। विभाजन उन स्तंभों में सबसे अच्छा है जहां डेटा कभी नहीं बदलता है। हमारे मामले में, यह काम करता है: यात्रा कभी भी किरायेदार_ नहीं बदलती और बनाई जाती है। समस्या यह है कि अगर ऐसा नहीं है तो PostreSQL हमें डेटा का हिस्सा वापस नहीं करेगा। हमने उसके बाद CHECK से वादा किया कि सभी डेटा वैध है।

कई समाधान हैं (स्पष्ट को छोड़कर - उस डेटा को म्यूट न करें जिसके लिए हम विभाजन कर रहे हैं):

UPDATE बजाय UPDATE हम हमेशा एप्लिकेशन स्तर पर DELETE+INSERT करते हैं
हम UPDATE पर एक और ट्रिगर जोड़ते हैं जो डेटा को सही विभाजन में स्थानांतरित करेगा

विचार करने लायक एक और चेतावनी है कि कैसे तारीखों को सही ढंग से अनुक्रमित किया जाए। यदि हम प्रश्नों में AT TIME ZONE का उपयोग करते हैं, तो हमें यह नहीं भूलना चाहिए कि यह वास्तव में एक फ़ंक्शन कॉल है। और इसका मतलब है कि हमारे सूचकांक को कार्य आधारित होना चाहिए। मैं भूल गया। नतीजतन, आधार लोड से फिर से मर चुका है।

पिछले पहलू पर विचार करने के लायक है कि कैसे विभाजन विभिन्न ओआरएम फ्रेमवर्क के साथ बातचीत करते हैं, चाहे वह रूबी में एक्टिवरॉर्ड हो या गो में जीओआरएम।

PostgreSQL में विभाजन इस तथ्य पर भरोसा करते हैं कि मूल तालिका हमेशा खाली रहेगी। यदि आप ORM का उपयोग नहीं करते हैं, तो आप सुरक्षित रूप से पहले संग्रहीत प्रक्रिया पर वापस आ सकते हैं, और RETURN NEW को बदल सकते हैं; RETURN NULL पर ;; तब मूल तालिका में पंक्ति बस नहीं जोड़ी जाएगी, जो कि वास्तव में हम चाहते हैं।

लेकिन तथ्य यह है कि ज्यादातर ORMs INSERT के साथ RETURNING क्लॉज का उपयोग करते हैं। यदि हम NULL को अपने ट्रिगर से वापस करते हैं, तो ORM घबरा जाएगा, यह मानते हुए कि पंक्ति को जोड़ा नहीं गया है। यह जोड़ा जाता है, लेकिन नहीं जहां ORM लग रही है।

इसके आसपास कई तरीके हैं:

• ORSERT के लिए ORM का उपयोग न करें
• पैच ORM (जिसे कभी-कभी ActiveRecord के मामले में सलाह दी जाती है)
• एक और ट्रिगर जोड़ें, जो पैरेंट से लाइन को हटा देगा।

अंतिम विकल्प अवांछनीय है, क्योंकि प्रत्येक ऑपरेशन के लिए हम तीन प्रदर्शन करेंगे। फिर भी, यह कभी-कभी अपरिहार्य है, क्योंकि हम इसे अलग से विचार करेंगे:

 CREATE OR REPLACE FUNCTION delete_parent_row() RETURNS TRIGGER AS $BODY$ DECLARE BEGIN delete from only rides where id = NEW.ID; RETURN null; END; $BODY$ LANGUAGE plpgsql; 

 CREATE TRIGGER after_insert_row_trigger AFTER INSERT ON rides FOR EACH ROW EXECUTE PROCEDURE delete_parent_row(); 

आखिरी चीज जो हमें करने की जरूरत है वह है हमारे समाधान का परीक्षण। ऐसा करने के लिए, हम एक निश्चित संख्या में लाइनें बनाते हैं:

 DO $script$ DECLARE year_start_epoch bigint := extract(epoch from '20170101'::timestamptz at time zone 'UTC'); delta bigint := extract(epoch from '20171231 23:59:59'::timestamptz at time zone 'UTC') - year_start_epoch; tenant varchar; tenants varchar[] := array['tenant_a', 'tenant_b', 'tenant_c', 'tenant_d']; BEGIN FOREACH tenant IN ARRAY tenants LOOP FOR i IN 1..100000 LOOP insert into rides (tenant_id, created_at, ride_id) values (tenant, to_timestamp(random() * delta + year_start_epoch) at time zone 'UTC', i); END LOOP; END LOOP; END $script$; 

और देखते हैं कि डेटाबेस कैसे व्यवहार करता है:

 explain select * from rides where tenant_id = 'tenant_a' and created_at AT TIME ZONE 'UTC' > '20171102' and created_at AT TIME ZONE 'UTC' <= '20171103'; 

यदि सब कुछ सही हुआ, तो हमें निम्नलिखित परिणाम देखना चाहिए:

  Append (cost=0.00..4803.76 rows=4 width=196) -> Seq Scan on rides (cost=0.00..4795.46 rows=3 width=196) Filter: (((created_at)::timestamp without time zone > '2017-11-02 00:00:00'::timestamp without time zone) AND ((created_at)::timestamp without time zone <= '2017-11-03 00:00:00'::timestamp without time zone) AND ((tenant_id)::text = 'tenant_a'::text)) -> Index Scan using rides_tenant_a_2017_11_tenant_timezone_idx on rides_tenant_a_2017_11 (cost=0.28..8.30 rows=1 width=196) Index Cond: (((tenant_id)::text = 'tenant_a'::text) AND ((created_at)::timestamp without time zone > '2017-11-02 00:00:00'::timestamp without time zone) AND ((created_at)::timestamp without time zone <= '2017-11-03 00:00:00'::timestamp without time zone)) (5 rows) 

इस तथ्य के बावजूद कि प्रत्येक किरायेदार के पास सैकड़ों हजारों पंक्तियां हैं, हम केवल वांछित डेटा स्लाइस से चयन करते हैं। सफलता!

मुझे उम्मीद है कि यह लेख उन लोगों के लिए दिलचस्प था जो अभी तक परिचित नहीं थे कि विभाजन क्या है और इसे पोस्टग्रेक्यूएल में कैसे लागू किया गया है। लेकिन जिन लोगों के लिए यह विषय अब नया नहीं है, फिर भी उन्होंने कुछ दिलचस्प ट्रिक्स सीखे।

युपीडी:
जैसा कि bigtrot सही ढंग से देखा गया है, अगर CONSTRAINT_EXCLUSION सेटिंग बंद हो जाती है तो यह सब सड़क का जादू नहीं चलेगा।

आप कमांड का उपयोग करके इसे सत्यापित कर सकते हैं

 show CONSTRAINT_EXCLUSION 

सेटिंग के तीन मूल्य हैं: ऑन, ऑफ और पार्टीशन

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