ليست بيانات كبيرة جدا

تناقش هذه المقالة الميزات التي يوفرها التقسيم المدمج أو التعريفي في الإصدار 12 من PostgreSQL. تم إعداد العرض التوضيحي للتقرير الذي يحمل نفس الاسم في مؤتمر HighLoad ++ Siberia 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 مع فئة التباين STABLE بدلاً من الثابت:

 => 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. عند تغيير البيانات ، لا يتم تطبيق استثناء القسم استنادًا إلى قيم الدالات الثابتة:

 => 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 

إنشاء مؤشر ... في الوقت الحالي

عند إنشاء فهرس في جدول مقسم ، لا يمكنك تحديد CONCURRENTLY.

ولكن يمكنك القيام بما يلي. أولاً ، نقوم بإنشاء الفهرس فقط على الجدول الرئيسي ، وسيحصل على حالة غير صالحة:

 => 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 واستبعاد المقاطع لاحقًا. إذا تم استخدام ثابت بدلاً من دالة ، فسيتم قفل الجدول الرئيسي وقسم bookings_range_201707 فقط. لذلك ، إذا كان ذلك ممكنًا ، حدد الثوابت في الطلب - يجب أن يتم ذلك ، وإلا فإن عدد الخطوط في pg_locks سيزداد بما يتناسب مع عدد الأقسام ، مما قد يؤدي إلى الحاجة إلى زيادة max_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; 

يتم التقسيم باستخدام الأمر ALTER TABLE ... DETACH PARTITION. لا يتم حذف القسم نفسه ، لكنه يصبح جدولًا مستقلاً. يمكن تنزيل البيانات منه ، ويمكن حذفه ، وإعادة الاتصال إذا لزم الأمر (قسم الملحق).

خيار آخر لتعطيل هو حذف القسم مع الأمر DROP TABLE.

لسوء الحظ ، استخدم كلا الخيارين ، DROP TABLE و DETACH PARTITION ، قفل 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 

نلاحظ أن عبارة RETURNING تُرجع سطرًا جديدًا ، والذي يقع في القسم الافتراضي.

بعد ضبط التاريخ الحالي (تغيير مفتاح التقسيم) ، ينتقل السجل تلقائيًا إلى القسم المرغوب فيه ، وليس هناك حاجة إلى مشغلات:

 => 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 ، وفي الوقت نفسه قسمه إلى أقسام حسب الشهر ، مثل bookings_list.

 => 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); 

تقييد FOREIGN KEY يستحق نظرة فاحصة. من ناحية ، هذا هو المفتاح الخارجي من الجدول المقسم (قائمة التذاكر) ، ومن ناحية أخرى ، هذا هو مفتاح الجدول المقسم (bookings_list). وبالتالي ، يتم دعم المفاتيح الخارجية للجداول المقسمة في كلا الاتجاهين.

إنشاء أقسام:

 => 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) 

دعونا نلقي نظرة على خطة تنفيذ الاستعلام مع ربط جدول bookings_hash والاستعلام الفرعي السابق:

 => 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) 

بدلاً من استعلام فرعي ، يمكنك استخدام الدالة التي تُرجع مجموعة مع فئة التباين STABLE:

 => 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 قد تلقى مجموعة غنية من الميزات ويمكن التوصية بأمان باستبدال التقسيم من خلال الميراث.