فهم الأقسام في PostgreSQL 9

تم إصدار PostgreSQL 10 في أوائل أكتوبر 2017 ، قبل عام تقريبًا.

واحدة من "الميزات" الجديدة الأكثر إثارة للاهتمام هي التقسيم الإعلاني دون قيد أو شرط. ولكن ماذا لو لم تكن على عجل للترقية إلى 10k؟ الأمازون ، على سبيل المثال ، ليست في عجلة من أمرها ، وقدمت دعم PostgreSQL 10 فقط في الأيام الأخيرة من فبراير 2018.

ثم يأتي التقسيم القديم الجيد من خلال الميراث لإنقاذ. أنا مهندس البرمجيات في قسم الشؤون المالية في شركة سيارات أجرة ، لذا فإن جميع الأمثلة تتعلق بالسفر بطريقة أو بأخرى (سنترك مشاكل المال لفترة أخرى).

منذ أن بدأنا في إعادة كتابة نظامنا المالي في عام 2015 ، عندما انضممت للتو إلى الشركة ، لم يكن هناك أي حديث عن التقسيم التعريفي. حتى يومنا هذا ، تم استخدام التقنية الموضحة أدناه بنجاح.

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

في حالتنا ، نقوم بتقسيم الجداول في عمودين ، يحتوي أحدهما على تواريخ سفر. هذه هي الحالة التي سننظر فيها.

لنبدأ بما تبدو عليه طاولتنا:

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 (محظوظ من Oracle ، مرحبًا!) ، سأصف العملية بإيجاز.

تعتمد PostgreSQL على اثنين من "ميزاتها" لهذا الغرض: القدرة على وراثة الجداول ، ووراثة الجدول ، والظروف المحددة.

لنبدأ بالميراث. باستخدام الكلمة الأساسية INHERITS ، فإننا نشير إلى أن الجدول الذي نقوم بإنشائه يرث جميع حقول الجدول الموروث. يؤدي هذا أيضًا إلى إنشاء علاقة بين الجدولين: من خلال إجراء استعلام من أحد الوالدين ، نحصل أيضًا على جميع البيانات من الأطفال.

تكمل الشروط التي تم التحقق منها الصورة عن طريق التأكد من عدم تقاطع البيانات. وبالتالي ، يمكن لمحسن PostgreSQL قطع جزء من الجداول الفرعية بالاعتماد على البيانات من الاستعلام.

قد يبدو المأزق الأول لهذا النهج واضحًا تمامًا: يجب أن يحتوي أي طلب على tenant_id. ومع ذلك ، إذا لم تذكر نفسك باستمرار بهذا الأمر ، فستكتب بنفسك عاجلاً أم آجلاً 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 يملأ بعض المتغيرات التي يمكننا الوصول إليها. يمكن العثور على القائمة الكاملة هنا .

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

PERFORM مفيد إذا أردنا استدعاء دالة لا تُرجع شيئًا. عادة ، في الأمثلة ، يحاولون وضع كل المنطق في وظيفة واحدة ، لكننا نحاول توخي الحذر.

يشير USING NEW إلى أننا في هذا الاستعلام نستخدم القيم من السلسلة التي حاولنا إضافتها.

سوف يقوم $1.* بتوسيع جميع قيم السطر الجديد. في الواقع ، يمكن ترجمة ذلك إلى 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 لإنشاء جدول مشابه للجدول الرئيسي ، INHERITS لتحديد البيانات التي يجب أن تذهب هناك.

RAISE NOTICE يطبع مجرد سلسلة إلى وحدة التحكم. إذا قمنا الآن بتشغيل INSERT من psql ، فيمكننا معرفة ما إذا تم إنشاء القسم.

لدينا مشكلة جديدة. لا يرث 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(); 

هناك دقة أخرى نادرا ما تولي اهتماما. التقسيم هو الأفضل في الأعمدة حيث لا تتغير البيانات. في حالتنا ، يعمل هذا: الرحلة لا تتغير أبدًا tenant_id و create_at. المشكلة التي تنشأ إذا لم يكن الأمر كذلك هي أن PostreSQL لن تعيد جزءًا من البيانات إلينا. ثم وعدناه بالتحقق من صحة جميع البيانات.

هناك العديد من الحلول (باستثناء ما هو واضح - لا تقم بتحويل البيانات التي نقوم بتقسيمها):

بدلاً من UPDATE نقوم دائمًا DELETE+INSERT على مستوى التطبيق
نضيف مشغلًا آخر في UPDATE والذي سينقل البيانات إلى القسم الصحيح

تحذير آخر يستحق النظر فيه هو كيفية فهرسة الأعمدة التي تحتوي على التواريخ بشكل صحيح. إذا استخدمنا AT TIME ZONE في الاستعلامات ، فيجب ألا ننسى أن هذا هو في الواقع استدعاء دالة. وهذا يعني أن مؤشرنا يجب أن يستند إلى الوظيفة. لقد نسيت. ونتيجة لذلك ، ماتت القاعدة مرة أخرى من الحمل.

الجانب الأخير الذي يستحق النظر فيه هو كيفية تفاعل الأقسام مع أطر ORM المختلفة ، سواء أكانت ActiveRecord في Ruby أو GORM في Go.

تعتمد الأقسام في PostgreSQL على حقيقة أن الجدول الأصلي سيكون فارغًا دائمًا. إذا كنت لا تستخدم ORM ، يمكنك العودة بأمان إلى أول إجراء مخزن ، وتغيير RETURN NEW ؛ على RETURN NULL ؛. ثم ببساطة لن يتم إضافة الصف في الجدول الأصلي ، وهو بالضبط ما نريده.

ولكن الحقيقة هي أن معظم ORMs يستخدمون جملة RETURNING مع INSERT. إذا عدنا NULL من الزناد الخاص بنا ، فسوف يصاب ORM بالذعر ، معتقدًا أنه لم تتم إضافة الصف. يتم إضافته ، ولكن ليس حيث يبحث ORM.

هناك عدة طرق حول هذا:

• لا تستخدم ORM للإدراج
• Patch 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) 

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

آمل أن يكون هذا المقال مثيرًا للاهتمام لأولئك الذين لم يكونوا على دراية بعد بما هو التقسيم وكيف يتم تنفيذه في PostgreSQL. لكن أولئك الذين لم يعد هذا الموضوع جديدًا بالنسبة لهم ، فقد تعلموا مع ذلك بعض الحيل المثيرة للاهتمام.

تحديث:
كما لوحظ bigtrot بشكل صحيح ، لن يعمل كل سحر الشارع هذا إذا تم إيقاف إعداد CONSTRAINT_EXCLUSION .

يمكنك التحقق من ذلك باستخدام الأمر

 show CONSTRAINT_EXCLUSION 

يحتوي الإعداد على ثلاث قيم: تشغيل وإيقاف وتقسيم

يكون تكوين القسم أكثر مثالية إذا كنت ترغب فجأة في استخدام CHECK CONSTRAINTS ليس فقط للأقسام ، ولكن أيضًا لتطبيع البيانات.