👋🏿 👩🏼‍⚕️ ⚔️ बहुत बड़ा डेटा नहीं 🤹🏾 🥫 👩🏾‍🏭

यह आलेख पोस्टग्रेएसक्यूएल के संस्करण 12 में अंतर्निहित या घोषणात्मक विभाजन द्वारा प्रदान की गई विशेषताओं पर चर्चा करेगा। हाईलेड ++ साइबेरिया 2019 सम्मेलन में एक ही नाम की रिपोर्ट के लिए प्रदर्शन तैयार किया गया था (एपीपी: रिपोर्ट के साथ एक वीडियो दिखाई दिया)।

सभी उदाहरण हाल ही में प्रदर्शित बीटा संस्करण पर निष्पादित किए गए हैं:

=> SELECT version();

  version ------------------------------------------------------------------------------------------------------------------ PostgreSQL 12beta1 on i686-pc-linux-gnu, compiled by gcc (Ubuntu 5.4.0-6ubuntu1~16.04.10) 5.4.0 20160609, 32-bit (1 row)

उदाहरण डेमो डेटाबेस की बुकिंग और टिकट तालिकाओं का उपयोग करते हैं। आरक्षण तालिका में जून से अगस्त 2017 तक तीन महीने के लिए प्रविष्टियाँ हैं और निम्नलिखित संरचना है:

 => \d bookings

 Table "bookings.bookings" Column | Type | Collation | Nullable | Default --------------+--------------------------+-----------+----------+--------- book_ref | character(6) | | not null | book_date | timestamp with time zone | | not null | total_amount | numeric(10,2) | | not null | Indexes: "bookings_pkey" PRIMARY KEY, btree (book_ref) Referenced by: TABLE "tickets" CONSTRAINT "tickets_book_ref_fkey" FOREIGN KEY (book_ref) REFERENCES bookings(book_ref)

एक आरक्षण में कई टिकट शामिल हो सकते हैं। टिकट के साथ तालिका की संरचना:

 => \d tickets

  Table "bookings.tickets" Column | Type | Collation | Nullable | Default ----------------+-----------------------+-----------+----------+--------- ticket_no | character(13) | | not null | book_ref | character(6) | | not null | passenger_id | character varying(20) | | not null | passenger_name | text | | not null | contact_data | jsonb | | | Indexes: "tickets_pkey" PRIMARY KEY, btree (ticket_no) Foreign-key constraints: "tickets_book_ref_fkey" FOREIGN KEY (book_ref) REFERENCES bookings(book_ref) Referenced by: TABLE "ticket_flights" CONSTRAINT "ticket_flights_ticket_no_fkey" FOREIGN KEY (ticket_no) REFERENCES tickets(ticket_no)

यह जानकारी उन उदाहरणों को समझने के लिए पर्याप्त होनी चाहिए जिसमें हम तालिकाओं को विभाजित करने का प्रयास करेंगे।

→ डेमो बेस के बारे में अधिक जानें यहां।

श्रेणी विभाजन

सबसे पहले, बुकिंग रेंज को डेट रेंज द्वारा विभाजित करने का प्रयास करें। इस स्थिति में, तालिका इस प्रकार बनाई जाएगी:

 => CREATE TABLE bookings_range ( book_ref character(6), book_date timestamptz, total_amount numeric(10,2) ) PARTITION BY RANGE(book_date);

प्रत्येक माह के लिए अलग अनुभाग:

 => CREATE TABLE bookings_range_201706 PARTITION OF bookings_range FOR VALUES FROM ('2017-06-01'::timestamptz) TO ('2017-07-01'::timestamptz); => CREATE TABLE bookings_range_201707 PARTITION OF bookings_range FOR VALUES FROM ('2017-07-01'::timestamptz) TO ('2017-08-01'::timestamptz);

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

 => CREATE TABLE bookings_range_201708 PARTITION OF bookings_range FOR VALUES FROM (to_timestamp('01.08.2017','DD.MM.YYYY')) TO (to_timestamp('01.09.2017','DD.MM.YYYY'));

तालिका का विवरण:

 => \d+ bookings_range

 Partitioned table "bookings.bookings_range" Column | Type | Collation | Nullable | Default | Storage | Stats target | Description --------------+--------------------------+-----------+----------+---------+----------+--------------+------------- book_ref | character(6) | | | | extended | | book_date | timestamp with time zone | | | | plain | | total_amount | numeric(10,2) | | | | main | | Partition key: RANGE (book_date) Partitions: bookings_range_201706 FOR VALUES FROM ('2017-06-01 00:00:00+03') TO ('2017-07-01 00:00:00+03'), bookings_range_201707 FOR VALUES FROM ('2017-07-01 00:00:00+03') TO ('2017-08-01 00:00:00+03'), bookings_range_201708 FOR VALUES FROM ('2017-08-01 00:00:00+03') TO ('2017-09-01 00:00:00+03')

यह काफी है। रिकॉर्ड सम्मिलित करने के लिए कोई ट्रिगर नहीं; कोई CHECK बाधाओं की आवश्यकता नहीं। CONSTRAINT_EXCLUSION पैरामीटर की भी आवश्यकता नहीं है, आप इसे बंद भी कर सकते हैं:

 => SET constraint_exclusion = OFF;

अनुभागों में स्वचालित लेआउट के साथ भरना:

 => INSERT INTO bookings_range SELECT * FROM bookings;

 INSERT 0 262788

घोषणात्मक वाक्यविन्यास अभी भी विरासत में मिली तालिकाओं को छुपाता है, इसलिए आप क्वेरी द्वारा वर्गों में पंक्तियों का वितरण देख सकते हैं:

 => SELECT tableoid::regclass, count(*) FROM bookings_range GROUP BY tableoid;

  tableoid | count -----------------------+-------- bookings_range_201706 | 7303 bookings_range_201707 | 167062 bookings_range_201708 | 88423 (3 rows)

लेकिन मूल तालिका में कोई डेटा नहीं है:

 => SELECT * FROM ONLY bookings_range;

  book_ref | book_date | total_amount ----------+-----------+-------------- (0 rows)

क्वेरी योजना में अनुभागों के बहिष्करण की जाँच करें:

 => EXPLAIN (COSTS OFF) SELECT * FROM bookings_range WHERE book_date = '2017-07-01'::timestamptz;

  QUERY PLAN ---------------------------------------------------------------------------- Seq Scan on bookings_range_201707 Filter: (book_date = '2017-07-01 00:00:00+03'::timestamp with time zone) (2 rows)

अपेक्षा के अनुसार केवल एक अनुभाग को स्कैन करना।

निम्न उदाहरण एक स्थिरांक के बजाय स्थिर परिवर्तनशीलता श्रेणी के साथ to_timestamp फ़ंक्शन का उपयोग करता है:

 => EXPLAIN (COSTS OFF) SELECT * FROM bookings_range WHERE book_date = to_timestamp('01.07.2017','DD.MM.YYYY');

  QUERY PLAN ------------------------------------------------------------------------------------ Append Subplans Removed: 2 -> Seq Scan on bookings_range_201707 Filter: (book_date = to_timestamp('01.07.2017'::text, 'DD.MM.YYYY'::text)) (4 rows)

फ़ंक्शन के मूल्य की गणना तब की जाती है जब क्वेरी योजना को आरंभीकृत किया जाता है, और अनुभागों के भाग को देखने से बाहर रखा जाता है (सबप्लस रिमूवल लाइन)।

लेकिन यह केवल SELECT के लिए काम करता है। डेटा बदलते समय, STABLE फ़ंक्शन के मूल्यों के आधार पर अनुभाग बहिष्करण अभी तक लागू नहीं किया गया है:

 => EXPLAIN (COSTS OFF) DELETE FROM bookings_range WHERE book_date = to_timestamp('01.07.2017','DD.MM.YYYY');

  QUERY PLAN ------------------------------------------------------------------------------------ Delete on bookings_range Delete on bookings_range_201706 Delete on bookings_range_201707 Delete on bookings_range_201708 -> Seq Scan on bookings_range_201706 Filter: (book_date = to_timestamp('01.07.2017'::text, 'DD.MM.YYYY'::text)) -> Seq Scan on bookings_range_201707 Filter: (book_date = to_timestamp('01.07.2017'::text, 'DD.MM.YYYY'::text)) -> Seq Scan on bookings_range_201708 Filter: (book_date = to_timestamp('01.07.2017'::text, 'DD.MM.YYYY'::text)) (10 rows)

इसलिए, आपको स्थिरांक का उपयोग करना चाहिए:

 => EXPLAIN (COSTS OFF) DELETE FROM bookings_range WHERE book_date = '2017-07-01'::timestamptz;

  QUERY PLAN ---------------------------------------------------------------------------------- Delete on bookings_range Delete on bookings_range_201707 -> Seq Scan on bookings_range_201707 Filter: (book_date = '2017-07-01 00:00:00+03'::timestamp with time zone) (4 rows)

सूचकांक छँटाई

निम्नलिखित क्वेरी करने के लिए, विभिन्न वर्गों से प्राप्त परिणामों को क्रमबद्ध करना आवश्यक है। इसलिए, क्वेरी योजना में, हम SORT नोड और योजना की उच्च प्रारंभिक लागत देखते हैं:

 => EXPLAIN SELECT * FROM bookings_range ORDER BY book_date;

  QUERY PLAN ------------------------------------------------------------------------------------------ Sort (cost=24649.77..25077.15 rows=170952 width=52) Sort Key: bookings_range_201706.book_date -> Append (cost=0.00..4240.28 rows=170952 width=52) -> Seq Scan on bookings_range_201706 (cost=0.00..94.94 rows=4794 width=52) -> Seq Scan on bookings_range_201707 (cost=0.00..2151.30 rows=108630 width=52) -> Seq Scan on bookings_range_201708 (cost=0.00..1139.28 rows=57528 width=52) (6 rows)

Book_date पर एक इंडेक्स बनाएं। एकल वैश्विक सूचकांक के बजाय, प्रत्येक अनुभाग में सूचकांक बनाए जाते हैं:

 => CREATE INDEX book_date_idx ON bookings_range(book_date);

 => \di bookings_range*

  List of relations Schema | Name | Type | Owner | Table ----------+-------------------------------------+-------+---------+----------------------- bookings | bookings_range_201706_book_date_idx | index | student | bookings_range_201706 bookings | bookings_range_201707_book_date_idx | index | student | bookings_range_201707 bookings | bookings_range_201708_book_date_idx | index | student | bookings_range_201708 (3 rows)

सॉर्टिंग वाली पिछली क्वेरी अब विभाजन कुंजी पर सूचकांक का उपयोग कर सकती है और परिणाम को अलग-अलग वर्गों से तुरंत सॉर्ट किए गए फॉर्म में वापस कर सकती है। SORT नोड की आवश्यकता नहीं है और परिणाम की पहली पंक्ति का उत्पादन करने के लिए न्यूनतम लागत की आवश्यकता है:

 => EXPLAIN SELECT * FROM bookings_range ORDER BY book_date;

  QUERY PLAN -------------------------------------------------------------------------------------------------------------------------------- Append (cost=1.12..14880.88 rows=262788 width=52) -> Index Scan using bookings_range_201706_book_date_idx on bookings_range_201706 (cost=0.28..385.83 rows=7303 width=52) -> Index Scan using bookings_range_201707_book_date_idx on bookings_range_201707 (cost=0.42..8614.35 rows=167062 width=52) -> Index Scan using bookings_range_201708_book_date_idx on bookings_range_201708 (cost=0.42..4566.76 rows=88423 width=52) (4 rows)

इस तरह से बनाए गए विभाजन सूचकांक को केंद्र द्वारा समर्थित किया जाता है। एक नया खंड जोड़ते समय, एक सूचकांक स्वचालित रूप से उस पर बनाया जाएगा। और आप केवल एक खंड के सूचकांक को नहीं निकाल सकते:

 => DROP INDEX bookings_range_201706_book_date_idx;

 ERROR: cannot drop index bookings_range_201706_book_date_idx because index book_date_idx requires it HINT: You can drop index book_date_idx instead.

केवल पूरे में:

 => DROP INDEX book_date_idx;

 DROP INDEX

भारतीय बनाएँ ... निश्चित रूप से

विभाजन तालिका पर एक इंडेक्स बनाते समय, आप निश्चित रूप से निर्दिष्ट नहीं कर सकते।

लेकिन आप निम्न कार्य कर सकते हैं। सबसे पहले, हम केवल मुख्य तालिका पर सूचकांक बनाते हैं, यह अमान्य स्थिति प्राप्त करेगा:

 => CREATE INDEX book_date_idx ON ONLY bookings_range(book_date);

 => SELECT indisvalid FROM pg_index WHERE indexrelid::regclass::text = 'book_date_idx';

  indisvalid ------------ f (1 row)

फिर CONCURRENTLY विकल्प के साथ सभी वर्गों पर अनुक्रमित बनाएं:

 => CREATE INDEX CONCURRENTLY book_date_201706_idx ON bookings_range_201706 (book_date); => CREATE INDEX CONCURRENTLY book_date_201707_idx ON bookings_range_201707 (book_date); => CREATE INDEX CONCURRENTLY book_date_201708_idx ON bookings_range_201708 (book_date);

अब हम स्थानीय सूचकांक को वैश्विक से जोड़ते हैं:

 => ALTER INDEX book_date_idx ATTACH PARTITION book_date_201706_idx; => ALTER INDEX book_date_idx ATTACH PARTITION book_date_201707_idx; => ALTER INDEX book_date_idx ATTACH PARTITION book_date_201708_idx;

यह विभाजन तालिकाओं को जोड़ने के समान है, जिसे हम थोड़ा बाद में देखेंगे। जैसे ही सभी सूचकांक खंड जुड़े होंगे, मुख्य सूचकांक अपनी स्थिति बदल देगा:

 => SELECT indisvalid FROM pg_index WHERE indexrelid::regclass::text = 'book_date_idx';

  indisvalid ------------ t (1 row)

कनेक्ट और डिस्कनेक्ट करें अनुभाग

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

हम एक नया खंड बनाएंगे, जबकि अन्य लेनदेन तालिका के साथ काम कर रहे हैं, उसी समय हम तालों को देखेंगे:

 => BEGIN; => SELECT count(*) FROM bookings_range WHERE book_date = to_timestamp('01.07.2017','DD.MM.YYYY');

  count ------- 5 (1 row)

 => SELECT relation::regclass::text, mode FROM pg_locks WHERE pid = pg_backend_pid() AND relation::regclass::text LIKE 'bookings%';

  relation | mode -----------------------+----------------- bookings_range_201708 | AccessShareLock bookings_range_201707 | AccessShareLock bookings_range_201706 | AccessShareLock bookings_range | AccessShareLock (4 rows)

AccessShareLock लॉक मुख्य टेबल पर लगाया जाता है, बयान की शुरुआत में सभी वर्गों और अनुक्रमित। To_timestamp फ़ंक्शन की गणना और अनुभागों का बहिष्करण बाद में होता है। यदि किसी फ़ंक्शन के बजाय एक स्थिरांक का उपयोग किया गया था, तो केवल मुख्य तालिका और बुकिंग_ व्यवस्था_201707 अनुभाग लॉक हो जाएगा। इसलिए, यदि संभव हो, तो अनुरोध में स्थिरांक निर्दिष्ट करें - यह किया जाना चाहिए, अन्यथा pg_locks में पंक्तियों की संख्या वर्गों की संख्या के अनुपात में बढ़ जाएगी, जिससे अधिकतम_locks_per_transaction बढ़ाने की आवश्यकता हो सकती है।

पिछले लेनदेन को पूरा किए बिना, एक नए सत्र में सितंबर के लिए निम्न अनुभाग बनाएं:

  || => CREATE TABLE bookings_range_201709 (LIKE bookings_range); || => BEGIN; || => ALTER TABLE bookings_range ATTACH PARTITION bookings_range_201709 FOR VALUES FROM ('2017-09-01'::timestamptz) TO ('2017-10-01'::timestamptz); || => SELECT relation::regclass::text, mode FROM pg_locks WHERE pid = pg_backend_pid() AND relation::regclass::text LIKE 'bookings%';

  relation | mode -------------------------------------+-------------------------- bookings_range_201709_book_date_idx | AccessExclusiveLock bookings_range | ShareUpdateExclusiveLock bookings_range_201709 | ShareLock bookings_range_201709 | AccessExclusiveLock (4 rows)

एक नया अनुभाग बनाते समय, ShareUpdateExclusiveLock लॉक, AccessShareLock के साथ संगत, मुख्य टेबल पर लगाया गया है। इसलिए, विभाजन जोड़ने की कार्रवाई विभाजन तालिका के खिलाफ प्रश्नों के साथ संघर्ष नहीं करती है।

 => COMMIT;

  || => COMMIT;

विभाजन सारणी के साथ किया जाता है ... विवरण विभाजन आदेश। अनुभाग स्वयं हटाया नहीं गया है, लेकिन एक स्वतंत्र तालिका बन गई है। डेटा को इससे डाउनलोड किया जा सकता है, इसे हटाया जा सकता है, और यदि आवश्यक हो तो फिर से जोड़ा जा सकता है (ATTACH PARTITION)।

अक्षम करने का एक अन्य विकल्प DROP टेबल कमांड वाले सेक्शन को हटाना है।

दुर्भाग्य से, दोनों विकल्प, डीआरओपी टेबल और डीटैक पार्टिशन, मेन टेबल पर AccessExclusiveLock लॉक का उपयोग करते हैं।

डिफ़ॉल्ट अनुभाग

यदि आप एक रिकॉर्ड जोड़ने की कोशिश करते हैं जिसके लिए एक खंड अभी तक नहीं बनाया गया है, तो एक त्रुटि उत्पन्न होगी। यदि यह व्यवहार वांछित नहीं है, तो आप एक डिफ़ॉल्ट अनुभाग बना सकते हैं:

 => CREATE TABLE bookings_range_default PARTITION OF bookings_range DEFAULT;

मान लीजिए कि रिकॉर्ड जोड़ते समय, उन्होंने एक सहस्राब्दी निर्दिष्ट किए बिना तारीख को मिलाया:

 => INSERT INTO bookings_range VALUES('XX0000', '0017-09-01'::timestamptz, 0) RETURNING tableoid::regclass, *;

  tableoid | book_ref | book_date | total_amount ------------------------+----------+------------------------------+-------------- bookings_range_default | XX0000 | 0017-09-01 00:00:00+02:30:17 | 0.00 (1 row) INSERT 0 1

हम ध्यान दें कि रिटर्निंग वाक्यांश एक नई पंक्ति देता है, जो डिफ़ॉल्ट अनुभाग में आता है।

वर्तमान तिथि निर्धारित करने (विभाजन कुंजी को बदलने) के बाद, रिकॉर्ड स्वचालित रूप से वांछित अनुभाग में चला जाता है, ट्रिगर की आवश्यकता नहीं है:

 => UPDATE bookings_range SET book_date = '2017-09-01'::timestamptz WHERE book_ref = 'XX0000' RETURNING tableoid::regclass, *;

  tableoid | book_ref | book_date | total_amount -----------------------+----------+------------------------+-------------- bookings_range_201709 | XX0000 | 2017-09-01 00:00:00+03 | 0.00 (1 row) UPDATE 1

मूल्य सूची अनुभाग

डेमो डेटाबेस में, book_ref कॉलम बुकिंग टेबल की प्राथमिक कुंजी होनी चाहिए। हालाँकि, चयनित विभाजन योजना ऐसी कुंजी बनाने की अनुमति नहीं देती है:

 => ALTER TABLE bookings_range ADD PRIMARY KEY(book_ref);

 ERROR: insufficient columns in PRIMARY KEY constraint definition DETAIL: PRIMARY KEY constraint on table "bookings_range" lacks column "book_date" which is part of the partition key.

विभाजन कुंजी को प्राथमिक कुंजी में शामिल किया जाना चाहिए।

महीनों तक टूटने और अभी भी प्राथमिक कुंजी में book_ref शामिल करने के लिए, आइए मानों की सूची के अनुसार बुकिंग तालिका के विभाजन के लिए एक और योजना का प्रयास करें। ऐसा करने के लिए, विभाजन कुंजी के रूप में निरर्थक कॉलम book_month जोड़ें:

 => CREATE TABLE bookings_list ( book_ref character(6), book_month character(6), book_date timestamptz NOT NULL, total_amount numeric(10,2), PRIMARY KEY (book_ref, book_month) ) PARTITION BY LIST(book_month);

हम बुकिंग तालिका डेटा के आधार पर गतिशील रूप से अनुभाग बनाएंगे:

 => WITH dates AS ( SELECT date_trunc('month',min(book_date)) min_date, date_trunc('month',max(book_date)) max_date FROM bookings ), partition AS ( SELECT to_char(g.month, 'YYYYMM') AS book_month FROM dates, generate_series(dates.min_date, dates.max_date, '1 month'::interval) AS g(month) ) SELECT format('CREATE TABLE %I PARTITION OF bookings_list FOR VALUES IN (%L)', 'bookings_list_' || partition.book_month, partition.book_month) FROM partition\gexec

 CREATE TABLE CREATE TABLE CREATE TABLE

यहाँ क्या हुआ:

 => \d+ bookings_list

  Partitioned table "bookings.bookings_list" Column | Type | Collation | Nullable | Default | Storage | Stats target | Description --------------+--------------------------+-----------+----------+---------+----------+--------------+------------- book_ref | character(6) | | not null | | extended | | book_month | character(6) | | not null | | extended | | book_date | timestamp with time zone | | not null | | plain | | total_amount | numeric(10,2) | | | | main | | Partition key: LIST (book_month) Indexes: "bookings_list_pkey" PRIMARY KEY, btree (book_ref, book_month) Partitions: bookings_list_201706 FOR VALUES IN ('201706'), bookings_list_201707 FOR VALUES IN ('201707'), bookings_list_201708 FOR VALUES IN ('201708')

अनुभागों में लेआउट के साथ भरना:

 => INSERT INTO bookings_list(book_ref,book_month,book_date,total_amount) SELECT book_ref,to_char(book_date, 'YYYYMM'),book_date,total_amount FROM bookings;

 INSERT 0 262788

एक वापसी के रूप में। Book_month को स्वचालित रूप से भरने के लिए, यह संस्करण 12 की नई कार्यक्षमता का उपयोग करने के लिए लुभावना है - सामान्य रूप से निर्मित कॉलम। लेकिन, दुर्भाग्य से, उन्हें विभाजन कुंजी के रूप में उपयोग नहीं किया जा सकता है। इसलिए, महीने को भरने का कार्य अन्य तरीकों से हल किया जाना चाहिए।

विभाजन तालिका पर CHECK और NOT NULL जैसी अखंडता बाधाएं खड़ी की जा सकती हैं। विरासत के साथ, INHERIT / NOINHERIT को निर्दिष्ट करना इंगित करता है कि प्रतिबंध सभी विभाजन तालिकाओं में विरासत में मिला होना चाहिए या नहीं। डिफ़ॉल्ट प्रवेश:

 => ALTER TABLE bookings_range ALTER COLUMN book_date SET NOT NULL;

 => \d bookings_range

  Partitioned table "bookings.bookings_range" Column | Type | Collation | Nullable | Default --------------+--------------------------+-----------+----------+--------- book_ref | character(6) | | | book_date | timestamp with time zone | | not null | total_amount | numeric(10,2) | | | Partition key: RANGE (book_date) Indexes: "book_date_idx" btree (book_date) Number of partitions: 5 (Use \d+ to list them.)

 => \d bookings_range_201706

  Table "bookings.bookings_range_201706" Column | Type | Collation | Nullable | Default --------------+--------------------------+-----------+----------+--------- book_ref | character(6) | | | book_date | timestamp with time zone | | not null | total_amount | numeric(10,2) | | | Partition of: bookings_range FOR VALUES FROM ('2017-06-01 00:00:00+03') TO ('2017-07-01 00:00:00+03') Indexes: "book_date_201706_idx" btree (book_date)

एक EXCLUDE बाधा केवल विभाजन पर स्थानीय रूप से बनाई जा सकती है।

Book_ref पर एक खोज सभी वर्गों में दिखाई देगी, लेकिन सूचकांक द्वारा, इस तथ्य के लिए धन्यवाद कि book_ref पहले सूचीबद्ध है:

 => EXPLAIN (COSTS OFF) SELECT * FROM bookings_list WHERE book_ref = '00000F';

  QUERY PLAN -------------------------------------------------------------------------- Append -> Index Scan using bookings_list_201706_pkey on bookings_list_201706 Index Cond: (book_ref = '00000F'::bpchar) -> Index Scan using bookings_list_201707_pkey on bookings_list_201707 Index Cond: (book_ref = '00000F'::bpchar) -> Index Scan using bookings_list_201708_pkey on bookings_list_201708 Index Cond: (book_ref = '00000F'::bpchar) (7 rows)

Book_ref पर एक खोज और कई वर्गों को केवल निर्दिष्ट सीमा में देखना चाहिए:

 => EXPLAIN (COSTS OFF) SELECT * FROM bookings_list WHERE book_ref = '00000F' AND book_month = '201707';

  QUERY PLAN ----------------------------------------------------------------------------------- Index Scan using bookings_list_201707_pkey on bookings_list_201707 Index Cond: ((book_ref = '00000F'::bpchar) AND (book_month = '201707'::bpchar)) (2 rows)

INSERT ... ON CONFLICT कमांड सही रूप से वांछित अनुभाग पाता है और अद्यतन करता है:

 => INSERT INTO bookings_list VALUES ('XX0001','201708','2017-08-01',0) RETURNING tableoid::regclass, *;

  tableoid | book_ref | book_month | book_date | total_amount ----------------------+----------+------------+------------------------+-------------- bookings_list_201708 | XX0001 | 201708 | 2017-08-01 00:00:00+03 | 0.00 (1 row) INSERT 0 1

 => INSERT INTO bookings_list VALUES ('XX0001','201708','2017-08-01',100) ON CONFLICT(book_ref,book_month) DO UPDATE SET total_amount = 100 RETURNING tableoid::regclass, *;

  tableoid | book_ref | book_month | book_date | total_amount ----------------------+----------+------------+------------------------+-------------- bookings_list_201708 | XX0001 | 201708 | 2017-08-01 00:00:00+03 | 100.00 (1 row) INSERT 0 1

विदेशी कुंजी

डेमो डेटाबेस में, टिकट टेबल बुकिंग को संदर्भित करता है।

विदेशी कुंजी को संभव बनाने के लिए, कॉलम book_month जोड़ें, और उसी समय इसे महीने के अनुसार अनुभागों में तोड़ दें, जैसे बुकिंग_सूची।

 => CREATE TABLE tickets_list ( ticket_no character(13), book_month character(6), book_ref character(6) NOT NULL, passenger_id varchar(20) NOT NULL, passenger_name text NOT NULL, contact_data jsonb, PRIMARY KEY (ticket_no, book_month), FOREIGN KEY (book_ref, book_month) REFERENCES bookings_list (book_ref, book_month) ) PARTITION BY LIST (book_month);

पूर्व प्रमुख प्रतिबंध एक करीब देखो लायक है। एक ओर, यह विभाजन तालिका (टिकट_सूची) से विदेशी कुंजी है , और दूसरी ओर, यह विभाजन तालिका (बुकिंग_सूची) की कुंजी है । इस प्रकार, विभाजित तालिकाओं के लिए विदेशी कुंजी दोनों दिशाओं में समर्थित हैं।

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

 => WITH dates AS ( SELECT date_trunc('month',min(book_date)) min_date, date_trunc('month',max(book_date)) max_date FROM bookings ), partition AS ( SELECT to_char(g.month, 'YYYYMM') AS book_month FROM dates, generate_series(dates.min_date, dates.max_date, '1 month'::interval) AS g(month) ) SELECT format('CREATE TABLE %I PARTITION OF tickets_list FOR VALUES IN (%L)', 'tickets_list_' || partition.book_month, partition.book_month) FROM partition\gexec

 CREATE TABLE CREATE TABLE CREATE TABLE

 => \d+ tickets_list

  Partitioned table "bookings.tickets_list" Column | Type | Collation | Nullable | Default | Storage | Stats target | Description ----------------+-----------------------+-----------+----------+---------+----------+--------------+------------- ticket_no | character(13) | | not null | | extended | | book_month | character(6) | | not null | | extended | | book_ref | character(6) | | not null | | extended | | passenger_id | character varying(20) | | not null | | extended | | passenger_name | text | | not null | | extended | | contact_data | jsonb | | | | extended | | Partition key: LIST (book_month) Indexes: "tickets_list_pkey" PRIMARY KEY, btree (ticket_no, book_month) Foreign-key constraints: "tickets_list_book_ref_book_month_fkey" FOREIGN KEY (book_ref, book_month) REFERENCES bookings_list(book_ref, book_month) Partitions: tickets_list_201706 FOR VALUES IN ('201706'), tickets_list_201707 FOR VALUES IN ('201707'), tickets_list_201708 FOR VALUES IN ('201708')

हम इसमें भरते हैं:

 => INSERT INTO tickets_list (ticket_no,book_month,book_ref,passenger_id,passenger_name,contact_data) SELECT t.ticket_no,b.book_month,t.book_ref, t.passenger_id,t.passenger_name,t.contact_data FROM bookings_list b JOIN tickets t ON (b.book_ref = t.book_ref);

 INSERT 0 366733

 => VACUUM ANALYZE tickets_list;

वर्गों में लाइनों का वितरण:

 => SELECT tableoid::regclass, count(*) FROM tickets_list GROUP BY tableoid;

  tableoid | count ---------------------+-------- tickets_list_201706 | 10160 tickets_list_201707 | 232755 tickets_list_201708 | 123818 (3 rows)

कनेक्शन और एकत्रीकरण अनुरोध

उसी तरह विभाजित दो तालिकाओं में शामिल हों:

 => EXPLAIN (COSTS OFF) SELECT b.* FROM bookings_list b JOIN tickets_list t ON (b.book_ref = t.book_ref and b.book_month = t.book_month);

  QUERY PLAN ---------------------------------------------------------------------------- Hash Join Hash Cond: ((t.book_ref = b.book_ref) AND (t.book_month = b.book_month)) -> Append -> Seq Scan on tickets_list_201706 t -> Seq Scan on tickets_list_201707 t_1 -> Seq Scan on tickets_list_201708 t_2 -> Hash -> Append -> Seq Scan on bookings_list_201706 b -> Seq Scan on bookings_list_201707 b_1 -> Seq Scan on bookings_list_201708 b_2 (11 rows)

एक कनेक्शन शुरू करने से पहले, प्रत्येक तालिका पहले उन खंडों को जोड़ती है जो क्वेरी स्थिति में आते हैं।

लेकिन आप पहले दोनों तालिकाओं के संबंधित मासिक वर्गों को जोड़ सकते हैं, और फिर परिणाम को जोड़ सकते हैं। इसे enable_partitionwise_join पैरामीटर को सक्षम करके प्राप्त किया जा सकता है:

 => SET enable_partitionwise_join = ON; => EXPLAIN (COSTS OFF) SELECT b.* FROM bookings_list b JOIN tickets_list t ON (b.book_ref = t.book_ref and b.book_month = t.book_month);

  QUERY PLAN ------------------------------------------------------------------------------------------ Append -> Hash Join Hash Cond: ((t.book_ref = b.book_ref) AND (t.book_month = b.book_month)) -> Seq Scan on tickets_list_201706 t -> Hash -> Seq Scan on bookings_list_201706 b -> Hash Join Hash Cond: ((t_1.book_ref = b_1.book_ref) AND (t_1.book_month = b_1.book_month)) -> Seq Scan on tickets_list_201707 t_1 -> Hash -> Seq Scan on bookings_list_201707 b_1 -> Hash Join Hash Cond: ((t_2.book_ref = b_2.book_ref) AND (t_2.book_month = b_2.book_month)) -> Seq Scan on tickets_list_201708 t_2 -> Hash -> Seq Scan on bookings_list_201708 b_2 (16 rows)

अब, पहले, दो तालिकाओं के संबंधित अनुभागों को जोड़ा जाता है, और फिर जोड़ के परिणाम संयुक्त होते हैं।

एकत्रीकरण के साथ एक समान स्थिति:

 => EXPLAIN (COSTS OFF) SELECT count(*) FROM bookings_list;

  QUERY PLAN ------------------------------------------------------------------- Finalize Aggregate -> Gather Workers Planned: 2 -> Partial Aggregate -> Parallel Append -> Parallel Seq Scan on bookings_list_201707 -> Parallel Seq Scan on bookings_list_201708 -> Parallel Seq Scan on bookings_list_201706 (8 rows)

ध्यान दें कि अनुभाग स्कैन समानांतर में किया जा सकता है। लेकिन पहले खंड एक साथ आते हैं, उसके बाद ही एकत्रीकरण शुरू होता है। वैकल्पिक रूप से, आप प्रत्येक अनुभाग में एकत्रीकरण कर सकते हैं, फिर परिणाम को जोड़ सकते हैं:

 => SET enable_partitionwise_aggregate = ON; => EXPLAIN (COSTS OFF) SELECT count(*) FROM bookings_list;

  QUERY PLAN ------------------------------------------------------------------- Finalize Aggregate -> Gather Workers Planned: 2 -> Parallel Append -> Partial Aggregate -> Parallel Seq Scan on bookings_list_201707 -> Partial Aggregate -> Parallel Seq Scan on bookings_list_201708 -> Partial Aggregate -> Parallel Seq Scan on bookings_list_201706 (10 rows)

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

हैश विभाजन

तालिका विभाजन के लिए तीसरा तरीका हैश विभाजन।

एक तालिका बनाना:

 => CREATE TABLE bookings_hash ( book_ref character(6) PRIMARY KEY, book_date timestamptz NOT NULL, total_amount numeric(10,2) ) PARTITION BY HASH(book_ref);

एक विभाजन कुंजी के रूप में book_ref के इस संस्करण में, आप तुरंत इसे प्राथमिक कुंजी के रूप में घोषित कर सकते हैं।

तीन खंडों में विभाजित करें:

 => CREATE TABLE bookings_hash_p0 PARTITION OF bookings_hash FOR VALUES WITH (MODULUS 3, REMAINDER 0); => CREATE TABLE bookings_hash_p1 PARTITION OF bookings_hash FOR VALUES WITH (MODULUS 3, REMAINDER 1); => CREATE TABLE bookings_hash_p2 PARTITION OF bookings_hash FOR VALUES WITH (MODULUS 3, REMAINDER 2);

अनुभागों में स्वचालित लेआउट के साथ भरना:

 => INSERT INTO bookings_hash SELECT * FROM bookings;

 INSERT 0 262788

वर्गों में लाइनों का वितरण समान रूप से होता है:

 => SELECT tableoid::regclass AS partition, count(*) FROM bookings_hash GROUP BY tableoid;

  partition | count ------------------+------- bookings_hash_p0 | 87649 bookings_hash_p1 | 87651 bookings_hash_p2 | 87488 (3 rows)

विभाजन की वस्तुओं को देखने के लिए नई कमांड:

 => \dP+

  List of partitioned relations Schema | Name | Owner | Type | Table | Total size | Description ----------+--------------------+---------+-------------------+----------------+------------+------------- bookings | bookings_hash | student | partitioned table | | 13 MB | bookings | bookings_list | student | partitioned table | | 15 MB | bookings | bookings_range | student | partitioned table | | 13 MB | bookings | tickets_list | student | partitioned table | | 50 MB | bookings | book_date_idx | student | partitioned index | bookings_range | 5872 kB | bookings | bookings_hash_pkey | student | partitioned index | bookings_hash | 5800 kB | bookings | bookings_list_pkey | student | partitioned index | bookings_list | 8120 kB | bookings | tickets_list_pkey | student | partitioned index | tickets_list | 19 MB | (8 rows)

 => VACUUM ANALYZE bookings_hash;

उपश्रेणी और नेस्टेड लूप जुड़ते हैं

नेस्टेड लूप कनेक्शन के साथ रनटाइम पर अनुभाग बहिष्करण संभव है।

वर्गों में पहले 10 बुकिंग का वितरण:

 => WITH top10 AS ( SELECT tableoid::regclass AS partition, * FROM bookings_hash ORDER BY book_ref LIMIT 10 ) SELECT partition, count(*) FROM top10 GROUP BY 1 ORDER BY 1;

  partition | count ------------------+------- bookings_hash_p0 | 3 bookings_hash_p1 | 3 bookings_hash_p2 | 4 (3 rows)

आइए बुकिंग निष्पादन योजना को बुकिंग_शश सारणी और पिछली उपसमुच्चय के साथ देखें:

 => EXPLAIN (ANALYZE,COSTS OFF,TIMING OFF) WITH top10 AS ( SELECT tableoid::regclass AS partition, * FROM bookings ORDER BY book_ref LIMIT 10 ) SELECT bh.* FROM bookings_hash bh JOIN top10 ON bh.book_ref = top10.book_ref;

  QUERY PLAN ----------------------------------------------------------------------------------------------------- Nested Loop (actual rows=10 loops=1) -> Limit (actual rows=10 loops=1) -> Index Only Scan using bookings_pkey on bookings (actual rows=10 loops=1) Heap Fetches: 0 -> Append (actual rows=1 loops=10) -> Index Scan using bookings_hash_p0_pkey on bookings_hash_p0 bh (actual rows=1 loops=3) Index Cond: (book_ref = bookings.book_ref) -> Index Scan using bookings_hash_p1_pkey on bookings_hash_p1 bh_1 (actual rows=1 loops=3) Index Cond: (book_ref = bookings.book_ref) -> Index Scan using bookings_hash_p2_pkey on bookings_hash_p2 bh_2 (actual rows=1 loops=4) Index Cond: (book_ref = bookings.book_ref) Planning Time: 0.632 ms Execution Time: 0.278 ms (13 rows)

कनेक्शन नेस्टेड लूप विधि का उपयोग करके किया जाता है। सामान्य टेबल एक्सप्रेशन के अनुसार बाहरी लूप को 10 बार निष्पादित किया जाता है। लेकिन टेबल-सेक्शन (लूप) पर कॉल की संख्या पर ध्यान दें। बाहरी लूप के प्रत्येक book_ref मान के लिए, केवल वह खंड स्कैन किया जाता है, जहां यह मान bookings_hash तालिका में संग्रहीत किया जाता है।

अक्षम अनुभाग बहिष्करण के साथ तुलना करें:

 => SET enable_partition_pruning TO OFF; => EXPLAIN (ANALYZE,COSTS OFF,TIMING OFF) WITH top10 AS ( SELECT tableoid::regclass AS partition, * FROM bookings ORDER BY book_ref LIMIT 10 ) SELECT bh.* FROM bookings_hash bh JOIN top10 ON bh.book_ref = top10.book_ref;

  QUERY PLAN ------------------------------------------------------------------------------------------------------ Nested Loop (actual rows=10 loops=1) -> Limit (actual rows=10 loops=1) -> Index Only Scan using bookings_pkey on bookings (actual rows=10 loops=1) Heap Fetches: 0 -> Append (actual rows=1 loops=10) -> Index Scan using bookings_hash_p0_pkey on bookings_hash_p0 bh (actual rows=0 loops=10) Index Cond: (book_ref = bookings.book_ref) -> Index Scan using bookings_hash_p1_pkey on bookings_hash_p1 bh_1 (actual rows=0 loops=10) Index Cond: (book_ref = bookings.book_ref) -> Index Scan using bookings_hash_p2_pkey on bookings_hash_p2 bh_2 (actual rows=0 loops=10) Index Cond: (book_ref = bookings.book_ref) Planning Time: 0.886 ms Execution Time: 0.771 ms (13 rows)

 => RESET enable_partition_pruning;

यदि आप एक आरक्षण में चयन को कम करते हैं, तो दो खंड बिल्कुल दिखाई नहीं देंगे:

 => EXPLAIN (ANALYZE,COSTS OFF,TIMING OFF) WITH top AS ( SELECT tableoid::regclass AS partition, * FROM bookings ORDER BY book_ref LIMIT 1 ) SELECT bh.* FROM bookings_hash bh JOIN top ON bh.book_ref = top.book_ref;

  QUERY PLAN --------------------------------------------------------------------------------------------------- Nested Loop (actual rows=1 loops=1) -> Limit (actual rows=1 loops=1) -> Index Only Scan using bookings_pkey on bookings (actual rows=1 loops=1) Heap Fetches: 0 -> Append (actual rows=1 loops=1) -> Index Scan using bookings_hash_p0_pkey on bookings_hash_p0 bh (actual rows=1 loops=1) Index Cond: (book_ref = bookings.book_ref) -> Index Scan using bookings_hash_p1_pkey on bookings_hash_p1 bh_1 (never executed) Index Cond: (book_ref = bookings.book_ref) -> Index Scan using bookings_hash_p2_pkey on bookings_hash_p2 bh_2 (never executed) Index Cond: (book_ref = bookings.book_ref) Planning Time: 0.250 ms Execution Time: 0.090 ms (13 rows)

एक उपश्रेणी के बजाय, आप परिवर्तन सेट की श्रेणी के साथ एक सेट लौटाने वाले फ़ंक्शन का उपयोग कर सकते हैं:

 => CREATE OR REPLACE FUNCTION get_book_ref(top int) RETURNS SETOF bookings AS $$ BEGIN RETURN QUERY EXECUTE 'SELECT * FROM bookings ORDER BY book_ref LIMIT $1' USING top; END;$$ LANGUAGE plpgsql STABLE;

 => EXPLAIN (ANALYZE,COSTS OFF,TIMING OFF) SELECT * FROM bookings_hash bh JOIN get_book_ref(10) f ON bh.book_ref = f.book_ref;

  QUERY PLAN ----------------------------------------------------------------------------------------------------- Nested Loop (actual rows=10 loops=1) -> Function Scan on get_book_ref f (actual rows=10 loops=1) -> Append (actual rows=1 loops=10) -> Index Scan using bookings_hash_p0_pkey on bookings_hash_p0 bh (actual rows=1 loops=3) Index Cond: (book_ref = f.book_ref) -> Index Scan using bookings_hash_p1_pkey on bookings_hash_p1 bh_1 (actual rows=1 loops=3) Index Cond: (book_ref = f.book_ref) -> Index Scan using bookings_hash_p2_pkey on bookings_hash_p2 bh_2 (actual rows=1 loops=4) Index Cond: (book_ref = f.book_ref) Planning Time: 0.175 ms Execution Time: 0.843 ms (11 rows)

परिणाम

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

बहुत बड़ा डेटा नहीं