تحسين استعلامات PostgreSQL بدون تخمين

مستوى القارئ: متوسط

هتخرج من المقال بطريقة عملية تمسك query بطيء في PostgreSQL، تقيسه، وتعرف هل محتاج index فعلًا ولا المشكلة في مكان تاني.

المشكلة باختصار

الطريقة الشائعة الغلط إنك تشوف endpoint بطيء، فتضيف index على أول عمود ظاهر في شرط WHERE. الطريقة دي بتفشل لأن البطء ممكن يكون من sort، أو join، أو قراءة عدد صفوف أكبر من المتوقع، أو statistics قديمة.

الافتراض إن عندك تطبيق SaaS متوسط، جدول orders فيه 3 مليون صف، وendpoint يعرض آخر طلبات المستخدم. تحت ضغط 50K زائر يوميًا، الاستعلام بدأ يوصل 920ms في P95. الهدف الواقعي إننا ننزله تحت 200ms بدون ما نكسر الكتابة أو نزود indexes بلا داعي.

ابدأ من الاستعلامات الأعلى تكلفة

ركز: مش كل query بطيء يستحق أولويتك. الأفضل تبدأ بالاستعلامات التي تستهلك وقتًا إجماليًا أعلى، لأن query بياخد 80ms ويتكرر 200 ألف مرة أخطر من query بياخد ثانيتين ويتكرر 10 مرات.

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

-- postgresql.conf
shared_preload_libraries = 'pg_stat_statements'
compute_query_id = on

-- داخل قاعدة البيانات
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

SELECT
  query,
  calls,
  round(total_exec_time::numeric, 2) AS total_ms,
  round(mean_exec_time::numeric, 2) AS mean_ms,
  rows
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

الـ trade-off هنا واضح. بتكسب visibility ممتازة، لكن بتحتاج restart عند تفعيل shared_preload_libraries، وبعض الإعدادات قد تضيف overhead بسيط. لو عندك production حساس، فعّلها في نافذة صيانة قصيرة.

اقرأ EXPLAIN قبل ما تضيف index

بعد ما تحدد الاستعلام، استخدم EXPLAIN (ANALYZE, BUFFERS). كلمة ANALYZE مهمة لأنها تنفذ الاستعلام وتعرض الزمن الفعلي، وليس تقدير planner فقط. هذا مذكور في وثائق PostgreSQL الخاصة بأمر EXPLAIN.

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, total, created_at
FROM orders
WHERE user_id = 91231
ORDER BY created_at DESC
LIMIT 20;

لو الخطة بتعرض Seq Scan on orders ومعها قراءة مئات الآلاف من الصفوف، فالمشكلة غالبًا إن قاعدة البيانات لا تملك index يخدم شرط المستخدم والترتيب معًا. المثال البسيط: أنت بتطلب آخر 20 طلبًا لمستخدم واحد، لكن PostgreSQL يقرأ جزءًا ضخمًا من الجدول ثم يرتب النتيجة. ده زي إنك تدور على آخر فاتورة لشخص في أرشيف الشركة كله بدل ما تفتح ملف الشخص مباشرة.

بشكل أدق: index مفيد هنا لأنه يسمح بالوصول للصفوف المطابقة لـ user_id بترتيب created_at المطلوب. لكن لو الاستعلام بيرجع 60% من الجدول، index غالبًا مش هيكسبك كتير.

أضف index يخدم الشرط والترتيب

في حالتنا، الاستعلام فيه شرط مساواة على user_id وترتيب تنازلي على created_at. أفضل طريقة هي index مركب بنفس نمط الوصول.

CREATE INDEX CONCURRENTLY idx_orders_user_created_desc
ON orders (user_id, created_at DESC);

ANALYZE orders;

استخدمت CONCURRENTLY لأننا في production. حسب وثائق CREATE INDEX، هذا الخيار يسمح ببناء index بدون منع عمليات الكتابة المعتادة على الجدول، لكنه يأخذ وقتًا أطول ويستهلك CPU وI/O أكثر من البناء العادي.

بعد الإضافة، شغّل نفس EXPLAIN. في سيناريو مشابه، النتيجة المتوقعة ممكن تنزل من 920ms إلى 180ms تقريبًا، يعني تحسن حوالي 80%. الرقم تقديري، لكنه مبني على نفس فكرة تقليل الصفوف المقروءة وتجنب sort كبير.

ما الذي يجب مراقبته بعد التحسين

أي index جديد له ثمن. بتكسب قراءة أسرع، لكن بتخسر مساحة تخزين، وبتزود تكلفة INSERT وUPDATE لأن PostgreSQL لازم يحدّث index مع كل تغيير مناسب. لو الجدول بيستقبل 2,000 write في الثانية، اختبر الأثر قبل الاعتماد.

راقب 3 أرقام بعد النشر: P95 latency للـ endpoint، حجم index، ومعدل الكتابة. لو القراءة تحسنت 80% لكن الكتابة تباطأت 25% في مسار critical، يبقى الحل محتاج إعادة تقييم.

متى لا تستخدم هذه الطريقة

لا تبدأ بـ index لو الجدول صغير جدًا، مثل 5 آلاف صف فقط. لا تستخدمها لو الاستعلام نادر ولا يؤثر على المستخدم. ولا تضف index مركب لكل query يظهر في اللوج، لأنك هتحول قاعدة البيانات لمجموعة فهارس مكلفة. كمان لو المشكلة من N+1 queries في التطبيق، فالـ index هيخفي جزءًا من العرض ولن يعالج السبب.

مصادر اعتمد عليها المقال

• وثائق PostgreSQL عن pg_stat_statements.
• وثائق PostgreSQL عن EXPLAIN وANALYZE.
• وثائق PostgreSQL عن CREATE INDEX وCONCURRENTLY.
• وثائق PostgreSQL عن الفهارس وأنواعها.

الخطوة التالية

افتح أبطأ endpoint عندك، استخرج الاستعلام من pg_stat_statements، وشغّل له EXPLAIN (ANALYZE, BUFFERS). لا تضف index قبل ما ترى عدد الصفوف المقروءة والزمن الفعلي.