أقفال في بوستجرس: 7 نصائح للعمل مع أقفال

مرحبا مرة أخرى! يوم الثلاثاء المقبل ، يبدأ تدفق جديد في الدورة التدريبية "Relational DBMS" ، لذلك نواصل نشر مواد مفيدة حول هذا الموضوع. دعنا نذهب.



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

1. أبدا إضافة عمود مع القيمة الافتراضية

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

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

لا تفعل هذا:

--     ,       (?) ALTER TABLE items ADD COLUMN last_update timestamptz DEFAULT now(); 

هل لأنها أفضل مثل هذا:

 -- select, update, insert  delete ,      () ALTER TABLE items ADD COLUMN last_update timestamptz; -- select  insert ,  update  delete ,    UPDATE items SET last_update = now(); 

أو الأفضل من ذلك ، تجنب تحديث delete الأقفال لفترة طويلة ، والتحديث على دفعات صغيرة ، على سبيل المثال:

 do { numRowsUpdated = executeUpdate( "UPDATE items SET last_update = ? " + "WHERE ctid IN (SELECT ctid FROM items WHERE last_update IS NULL LIMIT 5000)", now); } while (numRowsUpdate > 0); 

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

2. حذار من قوائم الانتظار قفل ، واستخدام المهلات

كل قفل في PostgreSQL له أولوية. إذا حاولت المعاملة B تعليق قفل تمسك به بالفعل المعاملة A بمستوى تأمين متعارض ، فستنتظر المعاملة B في قائمة انتظار التأمين. الآن يحدث شيء مثير للاهتمام: إذا وصلت معاملة أخرى C ، فسيتعين عليها التحقق ليس فقط من التعارض مع A ، ولكن أيضًا مع المعاملة B وأي معاملة أخرى في قائمة انتظار القفل.

هذا يعني أنه حتى إذا كان أمر DDL الخاص بك قادرًا على التنفيذ بسرعة كبيرة ، فقد يكون في قائمة الانتظار لفترة طويلة ، في انتظار استكمال الطلبات ، وسيتم حظر الطلبات التي تعمل بعد ذلك .

إذا واجهت استعلامات SELECT طويلة مقابل جدول ، فلا تفعل هذا:

 ALTER TABLE items ADD COLUMN last_update timestamptz; 

من الأفضل القيام بذلك:

 SET lock_timeout TO '2s' ALTER TABLE items ADD COLUMN last_update timestamptz; 

إذا lock_timeout تعيين lock_timeout فلن يتم تنفيذ أمر DDL إذا كان ينتظر تأمينًا ، وبالتالي ، يقوم بحظر الطلبات لأكثر من ثانيتين. الجانب السلبي هو أنه قد لا يتم تنفيذ ALTER TABLE الخاص بك ، ولكن يمكنك إعادة المحاولة لاحقًا. يمكنك الاستعلام عن pg_stat_activity لمعرفة ما إذا كان لديك استعلامات طويلة قبل تشغيل أمر DDL.

3. استخدام إنشاء فهرس غير مانع

قاعدة ذهبية أخرى في PostgreSQL: استخدم دائمًا إنشاء فهرس غير محظور.
يمكن أن يستغرق إنشاء فهرس لمجموعة بيانات كبيرة ساعات أو حتى أيامًا ، ويقوم الأمر CREATE INDEX العادي بتأمين جميع السجلات طوال مدة الأمر. على الرغم من حقيقة أنه لا يحظر SELECTs ، إلا أنه لا يزال سيئًا للغاية ، وهناك طريقة أفضل: CREATE INDEX CONCURRENTLY .

لا تفعل هذا:

 --    CREATE INDEX items_value_idx ON items USING GIN (value jsonb_path_ops); 

بدلاً من ذلك ، افعل هذا:

 --    DDL CREATE INDEX CONCURRENTLY items_value_idx ON items USING GIN (value jsonb_path_ops); 

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

 DROP INDEX CONCURRENTLY items_value_idx 

وحاول إنشائه مرة أخرى.

4. استخدام الأقفال العدوانية في أقرب وقت ممكن

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

على سبيل المثال ، إذا كنت تريد استبدال محتويات الجدول تمامًا. لا تفعل هذا:

 BEGIN; --     : TRUNCATE items; -  : \COPY items FROM 'newdata.csv' WITH CSV COMMIT; 

بدلاً من ذلك ، قم بتحميل البيانات في جدول جديد ، ثم استبدل القديم:

 BEGIN; CREATE TABLE items_new (LIKE items INCLUDING ALL); --  : \COPY items_new FROM 'newdata.csv' WITH CSV --     : DROP TABLE items; ALTER TABLE items_new RENAME TO items; COMMIT; 

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

 BEGIN; LOCK items IN EXCLUSIVE MODE; ... 

في بعض الأحيان يكون من الأفضل أن تأخذ الحظر بين يديك.

5. إضافة مفتاح أساسي مع الحد الأدنى من الحجب

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

يسهل Postgres إنشاء مفتاح أساسي باستخدام ALTER TABLE ، لكن في الوقت الحالي يتم إنشاء فهرس للمفتاح الأساسي ، والذي قد يستغرق وقتًا طويلاً إذا كان الجدول كبيرًا ، سيتم حظر جميع الطلبات.

 ALTER TABLE items ADD PRIMARY KEY (id); --      

لحسن الحظ ، يمكنك القيام بكل العمل الشاق أولاً باستخدام CREATE UNIQUE INDEX CONCURRENTLY ، ثم استخدام الفهرس الفريد كمفتاح أساسي ، وهو عملية سريعة.

 CREATE UNIQUE INDEX CONCURRENTLY items_pk ON items (id); --   ,     ALTER TABLE items ADD CONSTRAINT items_pk PRIMARY KEY USING INDEX items_pk; --  ,   

تقسيم إنشاء المفتاح الأساسي إلى مرحلتين عمليًا لا يؤثر على المستخدم.

6. أبدا استخدام فراغ كامل

يمكن أن تكون تجربة استخدام postgres في بعض الأحيان مجرد رهيبة قليلاً. على الرغم من أن VACUUM FULL يبدو مثل ما تريد القيام به لتنظيف "غبار" قاعدة البيانات الخاصة بك ، فإن الأمر الأكثر ملاءمة هو:

 PLEASE FREEZE MY DATABASE FOR HOURS; 

VACUUM FULL بالكتابة فوق الجدول بأكمله على القرص ، والذي يمكن أن يستغرق ساعات أو أيام ، وفي الوقت نفسه يحظر جميع الطلبات. على الرغم من وجود العديد من VACUUM FULL الاستخدام الصالحة لـ VACUUM FULL ، مثل الجدول الذي كان كبيرًا ، إلا أنه الآن صغير ولا يزال يشغل مساحة كبيرة ، لكن هذا ربما لا يكون خيارك.
على الرغم من أنه يجب عليك السعي لتكوين خيارات التنظيف التلقائي واستخدام الفهارس لتسريع الاستعلامات ، في بعض الأحيان يمكنك تشغيل VACUUM ، ولكن ليس VACUUM FULL .

7. تجنب الجمود عن طريق ترتيب الأوامر

إذا كنت تستخدم PostgreSQL لبعض الوقت ، فمن المحتمل أنك رأيت أخطاء مثل:

 ERROR: deadlock detected DETAIL: Process 13661 waits for ShareLock on transaction 45942; blocked by process 13483. Process 13483 waits for ShareLock on transaction 45937; blocked by process 13661. 

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

 BEGIN; UPDATE items SET counter = counter + 1 WHERE key = 'hello'; --    hello UPDATE items SET counter = counter + 1 WHERE key = 'world'; --    world END; 

في الوقت نفسه ، قد تصدر معاملة أخرى نفس الأوامر ، ولكن بترتيب مختلف.

 BEGIN UPDATE items SET counter = counter + 1 WHERE key = 'world'; --    world UPDATE items SET counter = counter + 1 WHERE key = 'hello'; --    hello END; 

إذا تم تنفيذ مجموعات المعاملات هذه في نفس الوقت ، فمن المحتمل أن تكون عالقة في انتظار بعضها البعض ولن تنتهي أبدًا. يتعرف Postgres على هذا الموقف خلال ثانية تقريبًا وسيُلغي إحدى المعاملات لإكمال الآخر. عندما يحدث هذا ، يجب عليك إلقاء نظرة على طلبك لمعرفة ما إذا كان يمكنك التأكد من أن معاملاتك تنفذ دائمًا بنفس الترتيب. إذا غيرت كلتا المعاملتين hello أولاً ، ثم world ، فستقوم المعاملة الأولى بإغلاق الثانية hello قبل أن تتمكن من التقاط أي أقفال أخرى.
مشاركة نصائحك!

نأمل أن تجد هذه التوصيات مفيدة. إذا كانت لديك نصائح أخرى ، فلا تتردد في تغريداتcitusdata أو مجتمع Citus النشط على Slack .

نذكرك أنه في غضون ساعات قليلة ، سيكون هناك يوم مفتوح سنتحدث فيه بالتفصيل عن البرنامج للدورة القادمة.