طريقي إلى التقسيم في بوستجرس

عندما نتوقف عن التحكم في حجم الجدول ، تصبح المحافظة على البيانات وإتاحتها مهمة غير تافهة. لقد واجهت بالفعل مشكلة في الإنتاج ، فهناك المزيد من البيانات كل يوم ، والجدول لا يتلاءم مع الذاكرة ، والخوادم تستجيب لفترة طويلة ، ولكن تم العثور على حل.

مرحبا يا هبر! اسمي Diamond وأريد الآن مشاركة طريقة ساعدتني في تطبيق التقسيم.

التقسيم في PostgreSql

التقسيم (أو ، كما يطلق عليه ، التقسيم) هو عملية تقسيم جدول منطقي كبير إلى عدة أقسام مادية أصغر. هذا هو ما يساعدنا على إدارة بياناتنا.

مثال: لدينا جدول "مبيعات" ، يتم تقسيمه بفاصل زمني قدره شهر واحد ، ويمكن تقسيم هذه الأقسام إلى أقسام فرعية أصغر حسب المنطقة.


جدول "مخطط المبيعات" المقسم

سلبيات هذا النهج:

- هيكل قاعدة البيانات المعقدة. كل قسم في تعريفات قاعدة البيانات هو جدول ، رغم أنه جزء من كيان منطقي واحد.
- لا يمكنك تحويل جدول موجود إلى جدول مقسم والعكس صحيح.
- لا يوجد دعم كامل في Postgres الإصدار 11.

الايجابيات:

+ الأداء. في بعض الحالات ، يمكننا العمل مع مجموعة محدودة من الأقسام دون المرور عبر الجدول بأكمله ، وحتى البحث عن فهرس الجداول الكبيرة سيكون أبطأ. يزيد من توافر البيانات.
+ تحميل بالجملة وحذف البيانات مع أوامر ATTACH / DETACH. هذا ينقذنا من النفقات العامة في شكل فراغ. مما يتيح لك الحفاظ على قاعدة البيانات بشكل أكثر كفاءة.
+ القدرة على تحديد TABLESPACE للقسم. هذا يتيح لنا الفرصة لنقل البيانات إلى أقسام أخرى ، ولكن لا نزال نعمل في نفس الحالة وسوف تحتوي البيانات الوصفية للدليل الرئيسي على معلومات حول الأقسام (يجب عدم الخلط بينها وبين المشاركة)

طريقتان لتطبيق التقسيم في PostgreSql:

1. وراثة الجداول (الوراثة)
عند إنشاء جدول ، نقول "ترث من جدول آخر (أصل)". في الوقت نفسه ، نضيف قيودًا لإدارة البيانات في الجدول. بهذا ، نؤيد منطق تقسيم البيانات ، لكن هذه الجداول مختلفة منطقيا.

تجدر الإشارة هنا إلى الامتداد الذي تم تطويره بواسطة Postgres Professional pg_pathman ، والذي ينفذ التقسيم ، أيضًا من خلال ميراث الجدول.

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

2. النهج التعريفي (الجزء)

يتم تعريف الجدول على أنه مقسم بالتعريف. ظهر هذا الحل في الإصدار 10 من PostgreSql.

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

لقد اخترت نهج التصريح. هذا يعطي ميزة كبيرة - المهد ، يتم دعم المزيد من الميزات من قبل النواة. النظر في تطوير بوستجرس في هذا الاتجاه:


مصدر

لكن 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); 

يعتمد وقت تشغيل البرنامج النصي على العديد من العوامل ، لكن العوامل الرئيسية هي حجم الجداول المستهدفة وعدد العلاقات والفهارس وخصائص الخادم. في حالتي ، تم تقسيم جدول 300 جيجابايت في أقل من ساعة.

يؤدي

ماذا حصلنا عليه؟ لنلقِ نظرة على خطة الاستعلام:

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

لقد حصلنا على النتيجة من الجدول المقسم بشكل أسرع واستخدمنا موارد أقل من خادمنا بالمقارنة مع الاستعلام إلى جدول منتظم.

في هذا المثال ، تكون الجداول العادية والمقسمة على نفس القاعدة ولديها حوالي 200 مليون سجل. هذه نتيجة جيدة ، بالنظر إلى أننا ، دون إعادة كتابة رمز التطبيق ، حصلنا على تسارع. تعمل استعلامات الفهارس الأخرى أيضًا بشكل جيد ، ولكن تذكر: كلما تمكنا من تحديد قسم ، ستكون النتيجة أسرع عدة مرات ، لأن يمكن لـ PostgreSql تجاهل الأقسام الإضافية في مرحلة التخطيط للطلب ( تعيين enable_partition_pruning إلى ).

يؤدي

تمكنت من تطبيق التقسيم على الجداول التي لها العديد من العلاقات وضمان تكامل قاعدة البيانات. البرنامج النصي مستقل عن هياكل البيانات المحددة ويمكن إعادة استخدامه.

PostgreSQL هي قاعدة البيانات العلائقية مفتوحة المصدر الأكثر تقدماً في العالم!

شكرا لكم جميعا!

رابط للمصدر