تقنيات تحسين PostgreSQL التي أستخدمها على قواعد بيانات الإنتاج. EXPLAIN ANALYZE، واستراتيجيات الفهرسة، وإعادة كتابة الاستعلامات، والتغييرات الدقيقة التي قلّصت أوقات الاستعلام من ثوانٍ إلى ميكروثوانٍ.
في الشهر الماضي وصلتني رسالة Slack في الساعة 2 فجراً: "لوحة المعلومات تنتهي مهلتها." دخلت عبر SSH إلى خادم الإنتاج، فتحت pg_stat_activity، ووجدت استعلاماً واحداً يفحص 14 مليون صف ليُعيد 12 نتيجة. الحل كان فهرساً جزئياً استغرق 30 ثانية لإنشائه. الاستعلام انتقل من 4.2 ثانية إلى 0.3 مللي ثانية.
هذا هو الشيء بخصوص أداء PostgreSQL. المشاكل ليست غريبة أبداً تقريباً. إنها فهارس فائتة، إحصائيات سيئة، استعلامات كانت منطقية عندما كان الجدول يحتوي 10,000 صف لكن الآن يحتوي 10 ملايين. الحلول عادة بسيطة — بمجرد أن تعرف أين تنظر.
هذا المقال هو كل ما تعلمته عن تحسين استعلامات PostgreSQL من تشغيل قواعد بيانات الإنتاج. لا نظرية بدون ممارسة. كل تقنية هنا وفّرت لي وقتاً حقيقياً على أنظمة حقيقية.
أكبر خطأ منفرد أراه يرتكبه المطورون مع الاستعلامات البطيئة هو التخمين. "ربما نحتاج فهرساً على هذا العمود." "ربما JOIN بطيء." "ربما يجب إضافة المزيد من RAM."
توقف عن التخمين. PostgreSQL يمتلك أحد أفضل محللات الاستعلامات في أي قاعدة بيانات. استخدمه.
EXPLAIN العادي يُظهر لك ما يخطط PostgreSQL للقيام به، بدون تنفيذ الاستعلام فعلياً:
AkousaEXPLAIN SELECT * FROM orders WHERE customer_id = 42; QUERY PLAN
---------------------------------------------------------------------------
Index Scan using idx_orders_customer_id on orders (cost=0.43..8.45 rows=1 width=128)
Index Cond: (customer_id = 42)
هذا يخبرك بالخطة — مسح فهرس — والتكلفة المقدّرة. لكنه تنبؤ. PostgreSQL قد يكون مخطئاً تماماً بشأن عدد الصفوف. رأيت تقديرات "صف واحد" عندما كانت النتيجة الفعلية 50,000 صف. هذا النوع من التقدير الخاطئ يتسلسل إلى خيارات خطة فظيعة.
استخدم EXPLAIN العادي عندما تريد نظرة سريعة على الخطة بدون تنفيذ الاستعلام فعلياً. هذا مهم عندما تحلل DELETE سيعدّل البيانات، أو استعلاماً يستغرق 30 دقيقة للتشغيل.
EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 42; QUERY PLAN
---------------------------------------------------------------------------
Index Scan using idx_orders_customer_id on orders (cost=0.43..8.45 rows=1 width=128)
(actual time=0.021..0.023 rows=1 loops=1)
Index Cond: (customer_id = 42)
Planning Time: 0.085 ms
Execution Time: 0.041 ms
الآن ترى وقت التنفيذ الفعلي وعدد الصفوف الفعلي. هنا يحدث التصحيح الحقيقي. قارن rows=1 (المقدّر) مع rows=1 (الفعلي) — يتطابقان، فالمخطط اتخذ قراراً جيداً. عندما لا يتطابقان، هذا أول دليلك.
تحذير: EXPLAIN ANALYZE ينفّذ الاستعلام فعلاً. إذا كنت تحلل UPDATE أو DELETE، غلّفه في معاملة:
BEGIN;
EXPLAIN ANALYZE DELETE FROM sessions WHERE expires_at < now();
ROLLBACK;هذا ما أستخدمه فعلاً 90% من الوقت:
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT o.id, o.total, c.name
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE o.created_at > '2026-01-01'
AND o.status = 'completed'; QUERY PLAN
---------------------------------------------------------------------------
Hash Join (cost=425.00..1893.25 rows=2150 width=48)
(actual time=2.341..15.892 rows=2347 loops=1)
Hash Cond: (o.customer_id = c.id)
Buffers: shared hit=312 read=45
-> Bitmap Heap Scan on orders o (cost=12.50..1450.75 rows=2150 width=32)
(actual time=0.512..12.105 rows=2347 loops=1)
Recheck Cond: (created_at > '2026-01-01'::date)
Filter: (status = 'completed')
Rows Removed by Filter: 893
Heap Blocks: exact=189
Buffers: shared hit=195 read=45
-> Bitmap Index Scan on idx_orders_created_at (cost=0.00..11.96 rows=3043 width=0)
(actual time=0.287..0.287 rows=3240 loops=1)
Index Cond: (created_at > '2026-01-01'::date)
Buffers: shared hit=12
-> Hash (cost=287.50..287.50 rows=10000 width=24)
(actual time=1.753..1.753 rows=10000 loops=1)
Buffers: shared hit=117
-> Seq Scan on customers c (cost=0.00..287.50 rows=10000 width=24)
(actual time=0.008..0.892 rows=10000 loops=1)
Buffers: shared hit=117
Planning Time: 0.215 ms
Execution Time: 16.147 ms
مخرجات BUFFERS حاسمة. shared hit=312 يعني أن 312 صفحة جاءت من ذاكرة التخزين المؤقت (RAM). read=45 يعني أن 45 صفحة كان لابد من قراءتها من القرص. إذا رأيت كثيراً من read نسبة إلى hit، فقد يكون shared_buffers صغيراً جداً، أو الجدول كبير جداً ليبقى في الذاكرة المؤقتة.
أستخدم أيضاً FORMAT JSON عندما أحتاج للصق الخطة في explain.dalibo.com للتصور. عرض الشجرة يجعل الخطط المعقدة أسهل بكثير للقراءة:
EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON) SELECT ...;إليك استعلام التشخيص الكامل الذي أشغّله أولاً لأي تحقيق في استعلام بطيء:
EXPLAIN (ANALYZE, BUFFERS, TIMING, VERBOSE)
SELECT ...;TIMING — توقيت لكل عقدة (مفعّل بشكل افتراضي مع ANALYZE، لكنني صريح)VERBOSE — يُظهر قوائم أعمدة المخرجات وأسماء الجداول مؤهلة بالمخططكل خطة استعلام هي شجرة. PostgreSQL يقرأها من الأسفل للأعلى: العقد الأعمق إزاحة تنفّذ أولاً، وتغذي النتائج للأعلى. فهم أنواع المسح هو أساس كل شيء آخر.
Seq Scan on orders (cost=0.00..25000.00 rows=1000000 width=128)
المسح التسلسلي يقرأ كل صف في الجدول. لجدول بمليون صف، هذا كل صفحة على القرص.
لكن إليك الدقة: Seq Scan ليس سيئاً دائماً. إذا كنت تختار 30% أو أكثر من الجدول، المسح التسلسلي أسرع فعلاً من مسح الفهرس لأن الإدخال/الإخراج التسلسلي أسرع بكثير من الإدخال/الإخراج العشوائي. PostgreSQL يعرف هذا. إذا كان يختار Seq Scan وأنت لديك فهرس، تحقق من مدى انتقائية شرط WHERE فعلاً.
متى تكون مشكلة: عندما تختار جزءاً ضئيلاً من الصفوف من جدول كبير ولا يوجد فهرس.
Index Scan using idx_orders_customer_id on orders (cost=0.43..8.45 rows=5 width=128)
Index Cond: (customer_id = 42)
يذهب إلى فهرس B-tree، يجد الإدخالات المطابقة، ثم يجلب الصفوف الفعلية من كومة الجدول. كل جلب صف هو عملية إدخال/إخراج عشوائي إلى الكومة. هذا ممتاز للاستعلامات عالية الانتقائية لكن كل جلب من الكومة له تكلفة.
Index Only Scan using idx_orders_customer_total on orders (cost=0.43..4.15 rows=5 width=12)
Index Cond: (customer_id = 42)
Heap Fetches: 0
هذا أسرع نوع مسح. PostgreSQL يحصل على كل ما يحتاجه من الفهرس وحده، بدون لمس كومة الجدول. ترى هذا عندما تكون جميع الأعمدة التي تختارها وتشترط عليها في الفهرس.
التحذير: Heap Fetches: 0 يعني أن خريطة الرؤية محدّثة. إذا كان جدولك يحتوي الكثير من المجموعات الميتة (لم يُنظّف مؤخراً بالمكنسة)، PostgreSQL لا يزال يحتاج لفحص الكومة للتحقق من رؤية الصفوف. هذا سبب واحد أن المكنسة التلقائية مهمة للأداء، وليس فقط لمساحة القرص.
Bitmap Heap Scan on orders (cost=45.00..1250.00 rows=5000 width=128)
Recheck Cond: (created_at > '2026-01-01')
-> Bitmap Index Scan on idx_orders_created_at (cost=0.00..43.75 rows=5000 width=0)
Index Cond: (created_at > '2026-01-01')
مسح الصورة النقطية هو إجابة PostgreSQL على مشكلة "صفوف كثيرة جداً لمسح الفهرس، صفوف قليلة جداً لمسح تسلسلي." يبني صورة نقطية لأي الصفحات تحتوي صفوفاً مطابقة، يرتّبها حسب الموقع الفيزيائي، ثم يجلبها بالترتيب. هذا يحوّل الإدخال/الإخراج العشوائي إلى إدخال/إخراج تسلسلي.
غالباً ترى مسح الصورة النقطية عندما يُدمج فهرسان أو أكثر:
Bitmap Heap Scan on orders
Recheck Cond: ((status = 'completed') AND (created_at > '2026-01-01'))
-> BitmapAnd
-> Bitmap Index Scan on idx_orders_status
Index Cond: (status = 'completed')
-> Bitmap Index Scan on idx_orders_created_at
Index Cond: (created_at > '2026-01-01')
هذا BitmapAnd يدمج فهرسين منفصلين. PostgreSQL يقول "ليس لدي فهرس مركّب، لكن يمكنني دمج هذين الفهرسين أحاديي العمود." يعمل، لكن فهرساً مركّباً مناسباً سيكون أسرع.
الشيء الأول الذي أبحث عنه في خطة الاستعلام هو التقديرات غير المتطابقة:
Nested Loop (cost=0.43..50.12 rows=1 width=128)
(actual time=0.025..4521.337 rows=48753 loops=1)
المخطط قدّر صفاً واحداً. النتيجة الفعلية كانت 48,753 صفاً. هذا خطأ تقدير بخمس مراتب من الحجم. المخطط اختار Nested Loop لأنه ظن أنه يربط ضد صف واحد. مع 48,753 صفاً، Hash Join كان سيكون أسرع بمراتب من الحجم.
الأسباب الشائعة للتقديرات السيئة:
ANALYZE على الجدولstatus = 'shipped' وcreated_at > '2026-01-01' مترابطين (معظم الطلبات الأخيرة مشحونة)، يقلّل المخطط من تقدير الانتقائية المجمّعةعندما ترى تقديرات سيئة، الحل عادة واحد من: شغّل ANALYZE، أنشئ إحصائيات موسّعة، أعد كتابة الاستعلام، أو استخدم CTE كسياج تحسين.
-- Create extended statistics for correlated columns
CREATE STATISTICS orders_status_date (dependencies)
ON status, created_at FROM orders;
ANALYZE orders;الفهارس ليست مجانية. كل فهرس يبطئ عمليات الكتابة، ويستهلك مساحة قرص، ويحتاج صيانة. الهدف ليس "فهرسة كل شيء" — بل "فهرسة بالضبط ما تحتاجه."
B-tree هو الافتراضي ويتعامل مع الغالبية العظمى من الحالات. يدعم عوامل المساواة والنطاق (=، <، >، <=، >=، BETWEEN، IN، IS NULL).
CREATE INDEX idx_orders_created_at ON orders (created_at);ترتيب أعمدة فهرس B-tree مهم بشكل كبير للفهارس المركّبة. العمود الأيسر هو الترتيب الأولي، ثم التالي، وهكذا. فهرس على (a, b, c) يمكنه الإجابة بكفاءة على:
WHERE a = 1WHERE a = 1 AND b = 2WHERE a = 1 AND b = 2 AND c = 3WHERE a = 1 AND b > 5WHERE a = 1 ORDER BY bلا يمكنه الإجابة بكفاءة على:
WHERE b = 2 (تخطي العمود الأول)WHERE c = 3 (تخطي أول عمودين)WHERE a = 1 AND c = 3 (فجوة في الوسط — شرط a مستخدم، شرط c يتطلب تصفية)فكّر فيه كدليل هاتف مرتّب حسب الاسم الأخير، ثم الاسم الأول. يمكنك بسرعة إيجاد كل إدخالات "Smith"، أو "Smith, John" تحديداً. لكن لا يمكنك بسرعة إيجاد كل إدخالات "John" بدون مسح الكتاب بالكامل.
القاعدة: ضع شروط المساواة أولاً، ثم شروط النطاق، ثم أعمدة الترتيب.
-- Query: WHERE status = 'active' AND created_at > '2026-01-01' ORDER BY total DESC
-- Good: equality first, then range, then sort
CREATE INDEX idx_orders_optimized ON orders (status, created_at, total);
-- Bad: range before equality
CREATE INDEX idx_orders_bad ON orders (created_at, status, total);PostgreSQL 11+ يتيح لك إضافة أعمدة غير مفتاحية لفهرس مع INCLUDE. هذه الأعمدة مخزّنة في صفحات الأوراق لكنها ليست جزءاً من بنية B-tree. تمكّن مسح الفهرس فقط بدون تضخيم الشجرة:
-- We frequently run:
-- SELECT id, total, status FROM orders WHERE customer_id = 42;
CREATE INDEX idx_orders_customer_covering
ON orders (customer_id) INCLUDE (total, status);الآن PostgreSQL يمكنه تلبية الاستعلام بالكامل من الفهرس وحده. بدون INCLUDE، سيحتاج لجلب total وstatus من الكومة. الفرق على ذاكرة مؤقتة باردة يمكن أن يكون دراماتيكياً — رأيت استعلامات تنتقل من 50 مللي ثانية إلى 0.2 مللي ثانية فقط بإضافة أعمدة INCLUDE.
هذه ميزة PostgreSQL المفضلة عندي والتي لا يعرفها معظم المطورين.
-- Only 2% of orders are 'pending', but we query them constantly
CREATE INDEX idx_orders_pending ON orders (created_at)
WHERE status = 'pending';هذا الفهرس صغير مقارنة بفهرس كامل على created_at. يحتوي فقط الصفوف حيث status = 'pending'. الاستعلامات التي تطابق شرط WHERE تستخدم هذا الفهرس الصغير السريع. الاستعلامات التي لا تطابق تتجاهله.
مثال حقيقي من الإنتاج: كان لدي جدول sessions بـ 50 مليون صف. فقط حوالي 200,000 كانت نشطة (غير منتهية الصلاحية). فهرس كامل على user_id كان 1.2 جيجابايت. فهرس جزئي WHERE expires_at > now() كان 8 ميجابايت. الاستعلام انتقل من 12 مللي ثانية إلى 0.1 مللي ثانية لأن الفهرس بأكمله اتسع في الذاكرة المؤقتة.
-- Another common pattern: soft deletes
CREATE INDEX idx_users_active_email ON users (email)
WHERE deleted_at IS NULL;
-- Unique constraint only on active records
CREATE UNIQUE INDEX idx_users_unique_email ON users (email)
WHERE deleted_at IS NULL;الأخير مفيد بشكل لا يصدق. يتيح لك قيد بريد إلكتروني فريد على المستخدمين النشطين مع السماح لنفس البريد الإلكتروني بالظهور في السجلات المحذوفة.
GIN (فهرس معكوس معمّم) هو الإجابة عندما تحتاج للبحث داخل القيم — المصفوفات، وثائق JSONB، أو متجهات البحث النصي الكامل.
-- JSONB containment queries
CREATE INDEX idx_products_metadata ON products USING GIN (metadata);
-- Now this is fast:
SELECT * FROM products WHERE metadata @> '{"color": "red"}';
-- Full-text search
CREATE INDEX idx_articles_search ON articles USING GIN (to_tsvector('english', title || ' ' || body));فهارس GIN كبيرة وبطيئة البناء، لكنها سريعة للغاية في الاستعلام. تستحق لأحمال العمل كثيفة القراءة مع استعلامات احتواء معقدة.
لـ JSONB، هناك أيضاً jsonb_path_ops وهو أصغر وأسرع لاستعلامات الاحتواء @>، لكنه لا يدعم عوامل الوجود (?، ?|، ?&):
CREATE INDEX idx_products_metadata_pathops
ON products USING GIN (metadata jsonb_path_ops);GiST (شجرة بحث معمّمة) يتعامل مع أنواع البيانات المتداخلة: الأشكال الهندسية، النطاقات، البحث النصي الكامل (بديل لـ GIN، أصغر لكن أبطأ).
-- IP range lookups
CREATE INDEX idx_ip_ranges ON ip_blocks USING GiST (ip_range);
SELECT * FROM ip_blocks WHERE ip_range @> '192.168.1.50'::inet;
-- Temporal ranges (scheduling conflicts)
CREATE INDEX idx_bookings_period ON bookings USING GiST (
tstzrange(start_time, end_time)
);
-- Find overlapping bookings:
SELECT * FROM bookings
WHERE tstzrange(start_time, end_time) && tstzrange('2026-03-01 10:00', '2026-03-01 12:00');فهارس Hash مفيدة فقط لمقارنات المساواة. منذ PostgreSQL 10 هي مسجّلة في WAL وآمنة من الأعطال. أصغر من B-tree للأعمدة العريضة وأسرع قليلاً للمساواة الخالصة:
-- Good for equality-only lookups on large text columns
CREATE INDEX idx_sessions_token ON sessions USING HASH (session_token);
-- This is fast:
SELECT * FROM sessions WHERE session_token = 'abc123def456...';
-- This CANNOT use the hash index:
SELECT * FROM sessions WHERE session_token LIKE 'abc%';عملياً، نادراً ما أستخدم فهارس hash. B-tree يتعامل مع المساواة بشكل جيد، وعدم القدرة على إجراء استعلامات نطاق أو ترتيب يجعل فهارس hash غير مرنة لمعظم حالات الاستخدام الحقيقية.
كل فهرس له تكلفة:
أراجع الفهارس بانتظام على جداول الإنتاج:
-- Find unused indexes
SELECT
schemaname, tablename, indexname,
idx_scan,
pg_size_pretty(pg_relation_size(indexrelid)) AS index_size
FROM pg_stat_user_indexes
WHERE idx_scan = 0
AND indexrelid NOT IN (
SELECT conindid FROM pg_constraint
WHERE contype IN ('p', 'u') -- exclude primary key and unique constraints
)
ORDER BY pg_relation_size(indexrelid) DESC;وجدت فهارس بحجم عدة جيجابايت أُنشئت أثناء ترحيل لمرة واحدة ولم تُستخدم مرة أخرى. حذفها سرّع عمليات الكتابة بشكل ملحوظ.
-- Find duplicate indexes (same columns, same order)
SELECT
a.indexrelid::regclass AS index1,
b.indexrelid::regclass AS index2,
a.indrelid::regclass AS table_name
FROM pg_index a
JOIN pg_index b ON a.indrelid = b.indrelid
AND a.indexrelid < b.indexrelid
AND a.indkey = b.indkey;كل مطور ORM يواجه هذا في النهاية. إنها مشكلة الأداء الأكثر شيوعاً التي أصحّحها.
# Python / SQLAlchemy — the classic trap
orders = session.query(Order).filter_by(status='pending').all()
for order in orders:
# This triggers a separate query for EACH order
print(f"Customer: {order.customer.name}")هذا يولّد:
-- Query 1: Get all pending orders
SELECT * FROM orders WHERE status = 'pending';
-- Query 2: Get customer for order 1
SELECT * FROM customers WHERE id = 101;
-- Query 3: Get customer for order 2
SELECT * FROM customers WHERE id = 102;
-- ...Query 502: Get customer for order 500
SELECT * FROM customers WHERE id = 600;501 استعلام بدلاً من 1 أو 2. كل استعلام سريع بمفرده، ربما 0.5 مللي ثانية. لكن 501 منها تتراكم إلى 250 مللي ثانية من وقت قاعدة البيانات فقط، بالإضافة إلى زمن رحلة الشبكة لكل واحد.
أسرع طريقة لالتقاط استعلامات N+1 هي تمكين تسجيل العبارات مؤقتاً:
-- Log all queries that take more than 0ms (i.e., all queries)
SET log_min_duration_statement = 0;
-- Better for production: log only slow queries
ALTER SYSTEM SET log_min_duration_statement = 50; -- 50ms threshold
SELECT pg_reload_conf();ثم انظر في السجلات. N+1 لا لبس فيه — سترى مئات الاستعلامات المتطابقة بقيم معاملات مختلفة في تتابع سريع.
نهج أكثر استهدافاً للتطوير:
-- Enable auto_explain for slow queries
LOAD 'auto_explain';
SET auto_explain.log_min_duration = 100; -- 100ms
SET auto_explain.log_nested_statements = ON;
SET auto_explain.log_analyze = ON;هذا يسجّل مخرجات EXPLAIN ANALYZE الكاملة لأي استعلام يتجاوز 100 مللي ثانية، بما في ذلك الاستعلامات داخل الدوال.
نهج ORM — أخبر ORM بتحميل البيانات المرتبطة مسبقاً:
# SQLAlchemy — joinedload fetches customers in the same query
from sqlalchemy.orm import joinedload
orders = (
session.query(Order)
.options(joinedload(Order.customer))
.filter_by(status='pending')
.all()
)نهج SQL الخام — استخدم JOIN فقط:
SELECT o.*, c.name AS customer_name
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE o.status = 'pending';استعلام واحد. انتهى.
هناك نقاش صحيح هنا. أحياناً استعلامان أفضل من JOIN:
-- Approach 1: Single JOIN (might produce duplicate data if one-to-many)
SELECT o.*, i.product_name, i.quantity
FROM orders o
JOIN order_items i ON i.order_id = o.id
WHERE o.status = 'pending';
-- Approach 2: Two queries (less data transfer if orders have many columns)
SELECT * FROM orders WHERE status = 'pending';
-- application collects order_ids = [1, 2, 3, ...]
SELECT * FROM order_items WHERE order_id = ANY($1);نهج JOIN يكرر بيانات الطلب لكل عنصر. إذا كان لكل طلب 20 عنصراً وصف الطلب عريض، هذا 20 ضعف نقل البيانات. نهج الاستعلامين يرسل كل طلب مرة واحدة بالضبط.
قاعدتي: استخدم JOINs للعلاقات واحد-لواحد، فكّر في استعلامات منفصلة لعلاقات واحد-لمتعدد عندما يكون جانب "الواحد" عريضاً. لكن دائماً اختبر الأداء — رحلة الشبكة لاستعلام ثانٍ غالباً تكلف أكثر من البيانات المكررة.
بعض الاستعلامات بطيئة ليس بسبب فهارس مفقودة بل بسبب طريقة كتابتها. مخطط PostgreSQL جيد، لكنه ليس سحراً.
هذه الأساليب الثلاثة يمكن أن تنتج خططاً مختلفة جداً:
-- Subquery in WHERE — often fine, sometimes terrible
SELECT * FROM orders
WHERE customer_id IN (
SELECT id FROM customers WHERE country = 'US'
);
-- JOIN — usually the best choice
SELECT o.* FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE c.country = 'US';
-- CTE — materialized in PG 11 and below, optimized in PG 12+
WITH us_customers AS (
SELECT id FROM customers WHERE country = 'US'
)
SELECT o.* FROM orders o
JOIN us_customers uc ON uc.id = o.customer_id;في PostgreSQL 12+، CTEs عادة تُضمّن (المخطط يعاملها كاستعلامات فرعية)، فالأداء متطابق. لكن في PostgreSQL 11 وأقل، CTEs هي أسوار تحسين — المخطط يُجسّدها ولا يستطيع دفع المسندات عبرها. إذا كنت لا تزال على PG 11، تجنب CTEs للاستعلامات الحرجة للأداء.
هذا يظهر باستمرار:
-- EXISTS — stops at first match, great for checking existence
SELECT * FROM orders o
WHERE EXISTS (
SELECT 1 FROM returns r WHERE r.order_id = o.id
);
-- IN with subquery — builds full result set first
SELECT * FROM orders
WHERE id IN (SELECT order_id FROM returns);
-- JOIN — can produce duplicates if returns has multiple rows per order
SELECT DISTINCT o.* FROM orders o
JOIN returns r ON r.order_id = o.id;للجداول الكبيرة، EXISTS غالباً يفوز لأنه يقطع مبكراً. إصدار IN يجب أن يبني قائمة كاملة بمعرّفات طلبات المرتجعات قبل التصفية. إصدار JOIN يمكن أن ينتج تكرارات، مما يتطلب DISTINCT الذي يضيف خطوة ترتيب أو تجزئة.
افتراضي: استخدم EXISTS عند التحقق من وجود صفوف مرتبطة. إنه الأكثر وضوحاً دلالياً وعادة الأسرع.
لكن هناك مثال مضاد. إذا كانت مجموعة نتائج الاستعلام الفرعي صغيرة وتحتاجها لشروط متعددة:
-- If the subquery returns few rows, IN is perfectly fine
SELECT * FROM orders
WHERE customer_id IN (1, 2, 3, 4, 5);هذا ليس مجرد تفضيل أسلوب كود. له تأثيرات أداء حقيقية:
-- Bad: fetches all 25 columns including a TEXT column with kilobytes of data
SELECT * FROM products WHERE category = 'electronics';
-- Good: fetches only what you need
SELECT id, name, price FROM products WHERE category = 'electronics';مع SELECT *:
دوال النافذة هي واحدة من أقوى ميزات PostgreSQL وتتفوق دائماً تقريباً على الاستعلامات الفرعية المرتبطة:
-- Slow: correlated subquery runs once per row
SELECT
o.*,
(SELECT COUNT(*) FROM order_items i WHERE i.order_id = o.id) AS item_count,
(SELECT SUM(amount) FROM payments p WHERE p.order_id = o.id) AS total_paid
FROM orders o
WHERE o.status = 'completed';
-- Fast: window functions compute in a single pass
SELECT
o.*,
COUNT(i.id) OVER (PARTITION BY o.id) AS item_count,
SUM(p.amount) OVER (PARTITION BY o.id) AS total_paid
FROM orders o
LEFT JOIN order_items i ON i.order_id = o.id
LEFT JOIN payments p ON p.order_id = o.id
WHERE o.status = 'completed';نمط شائع آخر — الحصول على آخر صف لكل مجموعة:
-- Slow: correlated subquery
SELECT * FROM notifications n
WHERE n.created_at = (
SELECT MAX(created_at) FROM notifications
WHERE user_id = n.user_id
);
-- Fast: DISTINCT ON (PostgreSQL-specific, extremely useful)
SELECT DISTINCT ON (user_id) *
FROM notifications
ORDER BY user_id, created_at DESC;
-- Also fast: ROW_NUMBER window function
SELECT * FROM (
SELECT *, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at DESC) AS rn
FROM notifications
) sub
WHERE rn = 1;DISTINCT ON هو خياري المفضل لهذا النمط. موجز، مقروء، وPostgreSQL يحسّنه جيداً مع الفهرس الصحيح:
CREATE INDEX idx_notifications_user_latest
ON notifications (user_id, created_at DESC);OFFSET فخ لمجموعات البيانات الكبيرة:
-- Page 1: fast
SELECT * FROM products ORDER BY id LIMIT 20 OFFSET 0;
-- Page 500: slow — PostgreSQL must scan and discard 9,980 rows
SELECT * FROM products ORDER BY id LIMIT 20 OFFSET 9980;
-- Page 5000: very slow — scans 99,980 rows to return 20
SELECT * FROM products ORDER BY id LIMIT 20 OFFSET 99980;الحل هو ترقيم صفحات مجموعة المفاتيح (يسمى أيضاً ترقيم صفحات قائم على المؤشر):
-- First page
SELECT * FROM products ORDER BY id LIMIT 20;
-- Next page: use the last id from the previous page
SELECT * FROM products WHERE id > 12345 ORDER BY id LIMIT 20;هذا دائماً سريع لأنه يستخدم الفهرس للقفز مباشرة للموقع الصحيح، بغض النظر عن أي "صفحة" أنت فيها. المقايضة أنك لا تستطيع القفز لرقم صفحة عشوائي، لكن للتمرير اللانهائي أو واجهات "الصفحة التالية"، ترقيم صفحات مجموعة المفاتيح متفوق تماماً.
لترتيبات الفرز المعقدة:
-- Keyset pagination with multiple sort columns
SELECT * FROM products
WHERE (price, id) > (29.99, 12345)
ORDER BY price, id
LIMIT 20;هذا يستخدم مقارنة قيم الصفوف، والتي يتعامل معها PostgreSQL بكفاءة مع فهرس مركّب على (price, id).
مخطط استعلامات PostgreSQL يتخذ قرارات بناءً على إحصائيات حول بياناتك. إحصائيات سيئة تؤدي لخطط سيئة. بهذه البساطة.
-- Analyze a single table
ANALYZE orders;
-- Analyze the entire database
ANALYZE;
-- See current statistics for a column
SELECT
tablename, attname, n_distinct, most_common_vals, correlation
FROM pg_stats
WHERE tablename = 'orders' AND attname = 'status';قيمة correlation مثيرة للاهتمام بشكل خاص. تتراوح من -1 إلى 1 وتقيس مدى تطابق الترتيب الفيزيائي للصفوف مع الترتيب المنطقي للعمود. ارتباط قريب من 1 أو -1 يعني أن البيانات مرتّبة فيزيائياً على هذا العمود، مما يجعل مسح النطاق فعالاً جداً (إدخال/إخراج تسلسلي). ارتباط قريب من 0 يعني إدخال/إخراج عشوائي لاستعلامات النطاق.
-- Increase statistics target for columns with skewed distributions
ALTER TABLE orders ALTER COLUMN status SET STATISTICS 1000;
ANALYZE orders;هدف الإحصائيات الافتراضي هو 100، مما يعني PostgreSQL يأخذ عينة من 300 * 100 = 30,000 صف. للأعمدة ذات القيم المتميزة الكثيرة أو التوزيعات المنحرفة، زيادة هذا إلى 500 أو 1000 يعطي المخطط بيانات أفضل بتكلفة أوقات ANALYZE أطول قليلاً.
المكنسة التلقائية تفعل شيئين: تستعيد المساحة من المجموعات الميتة (صفوف محذوفة أو محدّثة) وتحدّث الإحصائيات. على الجداول المشغولة، إعدادات المكنسة التلقائية الافتراضية غالباً محافظة جداً.
-- Check autovacuum status
SELECT
relname,
n_live_tup,
n_dead_tup,
last_vacuum,
last_autovacuum,
last_analyze,
last_autoanalyze
FROM pg_stat_user_tables
WHERE n_dead_tup > 1000
ORDER BY n_dead_tup DESC;إذا رأيت جداول بملايين المجموعات الميتة وآخر مكنسة كانت قبل ساعات، فمكنستك التلقائية تتأخر.
للجداول عالية التدوير (مثل الجلسات، وقوائم انتظار المهام، أو المقاييس)، أضبط إعدادات المكنسة التلقائية لكل جدول:
-- More aggressive autovacuum for high-write tables
ALTER TABLE sessions SET (
autovacuum_vacuum_scale_factor = 0.01, -- vacuum at 1% dead tuples (default 20%)
autovacuum_analyze_scale_factor = 0.005, -- analyze at 0.5% changes (default 10%)
autovacuum_vacuum_cost_delay = 2 -- faster vacuum (default 2ms in newer PG)
);عندما يحدّث PostgreSQL صفاً، لا يعدّل الصف في مكانه — ينشئ إصداراً جديداً ويضع علامة على القديم كميت. المكنسة تستعيد الصفوف الميتة، لكن المساحة تُعاد استخدامها فقط لنفس الجدول. ملف الجدول على القرص لا يتقلص.
بمرور الوقت، جدول يُحدَّث بكثافة يمكن أن يكون لديه تضخم كبير — الجدول أكبر بكثير على القرص مما تتطلبه البيانات الحية. هذا يعني صفحات أكثر للمسح، إدخال/إخراج أكثر، ضغط ذاكرة مؤقتة أكبر.
-- Estimate table bloat (simplified version)
SELECT
current_database(),
schemaname,
tablename,
pg_size_pretty(pg_total_relation_size(schemaname || '.' || tablename)) AS total_size,
pg_size_pretty(
pg_total_relation_size(schemaname || '.' || tablename) -
pg_relation_size(schemaname || '.' || tablename)
) AS index_size
FROM pg_tables
WHERE schemaname = 'public'
ORDER BY pg_total_relation_size(schemaname || '.' || tablename) DESC
LIMIT 20;للجداول المتضخمة بشدة، الخيار النووي هو VACUUM FULL — يعيد كتابة الجدول بالكامل. لكنه يأخذ قفلاً حصرياً، فلا يمكنك فعله على جدول إنتاج حي بدون توقف. النهج الأفضل هو pg_repack، الذي يفعل نفس الشيء بدون قفل:
pg_repack --table orders --no-kill-backend -d mydbإليك شيء يفاجئ الكثير من المطورين: اتصالات PostgreSQL مكلفة. كل اتصال يُنشئ عملية جديدة (وليس خيطاً)، يستهلك حوالي 5-10 ميجابايت من الذاكرة، وله عبء fork غير تافه.
الافتراضي لـ max_connections هو 100. إذا كان لديك خادم تطبيق بـ 20 عاملاً، كل واحد يفتح 5 اتصالات، أنت بالفعل عند حدك. أضف معالج مهام خلفية، وأداة مراقبة، وترحيل يعمل في مكان ما، وأنت في مشكلة.
بدون مجمّع، إذا كان تطبيقك يحتاج التعامل مع 500 طلب متزامن، تحتاج 500 اتصال PostgreSQL. هذا 5 جيجابايت من الذاكرة فقط لعبء الاتصال، وأداء PostgreSQL يتدهور بشكل كبير بعد بضع مئات من الاتصالات بسبب عبء إدارة العمليات.
مع PgBouncer أمام PostgreSQL، تلك 500 اتصال تطبيق تُعيَّن لربما 20 اتصال PostgreSQL فعلي. المجمّع يضع الطلبات في قائمة انتظار عندما تكون جميع اتصالات قاعدة البيانات مشغولة.
[databases]
mydb = host=127.0.0.1 port=5432 dbname=mydb
[pgbouncer]
listen_addr = 127.0.0.1
listen_port = 6432
auth_type = md5
auth_file = /etc/pgbouncer/userlist.txt
; Pool mode
pool_mode = transaction
; Pool sizing
default_pool_size = 20
max_client_conn = 500
min_pool_size = 5
reserve_pool_size = 5
reserve_pool_timeout = 3
; Timeouts
server_idle_timeout = 300
client_idle_timeout = 0
query_timeout = 30pool_mode = transaction): تُعاد الاتصالات للمجمّع بعد كل معاملة. هذا ما تريده 95% من الوقت. أقصى إعادة استخدام للاتصال.pool_mode = session): تُحتفظ بالاتصالات طوال جلسة العميل. استخدم هذا إذا كنت تحتاج عبارات معدّة، أوامر SET، LISTEN/NOTIFY، أو ميزات أخرى على مستوى الجلسة.pool_mode = statement): تُعاد الاتصالات بعد كل عبارة. مقيّد جداً لمعظم التطبيقات — لا يمكنك حتى استخدام معاملات صريحة.المأزق مع وضع المعاملة: لا يمكنك استخدام عبارات معدّة (إنها حالة على مستوى الجلسة)، لا يمكنك استخدام SET لمتغيرات الجلسة، وLISTEN/NOTIFY لن يعمل كما هو متوقع. معظم أدوات ORM لديها طريقة لتعطيل العبارات المعدّة.
لـ Node.js مع مشغّل pg:
const pool = new Pool({
host: "127.0.0.1",
port: 6432, // PgBouncer port, not PostgreSQL port
database: "mydb",
max: 20, // matches PgBouncer default_pool_size
statement_timeout: 30000,
// Disable prepared statements for PgBouncer transaction mode
prepare: false,
});هناك صيغة شائعة لحجم مجمّع اتصالات PostgreSQL الأمثل:
optimal_connections = (core_count * 2) + effective_spindle_count
لخادم حديث بـ 4 أنوية وSSD (عمود دوران فعّال واحد):
optimal = (4 * 2) + 1 = 9
هذا يبدو منخفضاً بشكل غير بديهي. لكن PostgreSQL مقيّد بالمعالج على معظم الأجهزة الحديثة (أقراص SSD سريعة بما يكفي بحيث نادراً ما يكون الإدخال/الإخراج عنق الزجاجة). اتصالات أكثر من هذا تؤدي لعبء تبديل السياق الذي يبطئ الأمور فعلاً.
عملياً، أضبط default_pool_size عادة إلى 2-3 أضعاف هذا الرقم للتعامل مع حركة الذروة، مع فهم أنه عند ذروة الحمل، بعض الاستعلامات ستنتظر في قائمة انتظار PgBouncer بدلاً من أن تضرب جميعها PostgreSQL في وقت واحد.
إليك عمليتي الفعلية عندما أحصل على تقرير "هذا الاستعلام بطيء." أتبع هذه الخطوات بهذا الترتيب، في كل مرة.
ليس "نقطة النهاية بطيئة" — SQL الفعلي. إذا كنت تستخدم ORM، فعّل تسجيل الاستعلامات:
-- Temporarily log all queries over 100ms
ALTER SYSTEM SET log_min_duration_statement = 100;
SELECT pg_reload_conf();أو تحقق من pg_stat_statements لأعلى الاستعلامات حسب إجمالي الوقت:
-- Top 10 queries by total execution time
SELECT
queryid,
calls,
round(total_exec_time::numeric, 2) AS total_time_ms,
round(mean_exec_time::numeric, 2) AS avg_time_ms,
round((100 * total_exec_time / sum(total_exec_time) OVER ())::numeric, 2) AS pct,
query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;pg_stat_statements هو الإضافة الأكثر قيمة لأداء PostgreSQL. إذا لم تكن تشغّلها، ثبّتها الآن:
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT) <the slow query>;ابحث عن:
work_mem أو أضف فهرساً للترتيب-- When were stats last updated?
SELECT
relname,
last_analyze,
last_autoanalyze,
n_live_tup,
n_dead_tup
FROM pg_stat_user_tables
WHERE relname = 'orders';إذا كان last_analyze قديماً أو n_dead_tup عالياً نسبة إلى n_live_tup، شغّل:
ANALYZE orders;ثم أعد تشغيل EXPLAIN ANALYZE. إذا تغيّرت الخطة، الإحصائيات القديمة كانت المشكلة.
-- What indexes exist on this table?
SELECT
indexname,
indexdef,
idx_scan,
idx_tup_read,
pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_user_indexes
JOIN pg_indexes ON pg_stat_user_indexes.indexrelname = pg_indexes.indexname
WHERE tablename = 'orders'
ORDER BY idx_scan DESC;ربما الفهرس موجود لكن لا يُستخدم. ربما ترتيب الأعمدة خاطئ لاستعلامك.
بناءً على خطة الاستعلام، أنشئ الفهرس المناسب. اختبر مع EXPLAIN ANALYZE قبل وبعد.
-- Create the index concurrently (doesn't lock the table)
CREATE INDEX CONCURRENTLY idx_orders_customer_status
ON orders (customer_id, status)
WHERE status IN ('pending', 'processing');
-- Verify the index is used
EXPLAIN (ANALYZE, BUFFERS) <the slow query>;استخدم CONCURRENTLY دائماً على جداول الإنتاج. CREATE INDEX العادي يأخذ قفل جدول كامل يحجب جميع عمليات الكتابة.
إذا كان الفهرس موجوداً والإحصائيات حديثة لكن الاستعلام لا يزال بطيئاً، انظر في الاستعلام نفسه:
OFFSET يسبب مشاكل؟ انتقل لترقيم صفحات مجموعة المفاتيحلأنماط استعلام محددة، إعدادات الخادم مهمة:
-- work_mem: memory for sorts and hash joins (per operation, not per connection)
-- Default is 4MB, which is too low for complex queries
SET work_mem = '64MB'; -- try this and re-run EXPLAIN ANALYZE
-- effective_cache_size: tells planner how much disk cache to expect
-- Set to ~75% of total RAM
SHOW effective_cache_size;
-- random_page_cost: ratio of random I/O to sequential I/O
-- Default is 4.0, set to 1.1-1.5 for SSDs
SHOW random_page_cost;إذا كان تغيير work_mem يحل مشكلة الترتيب للقرص، فكّر في زيادته عالمياً. لكن كن حذراً — إنه لكل عملية، وليس لكل اتصال. استعلام معقد بـ 10 عمليات ترتيب وwork_mem = 256MB يمكن أن يستخدم 2.5 جيجابايت لاستعلام واحد.
لا تصلح وتنسَ. تحقق أن الإصلاح يصمد:
-- Reset pg_stat_statements to see fresh data after your changes
SELECT pg_stat_statements_reset();
-- Check back in an hour/day
SELECT
queryid,
calls,
round(mean_exec_time::numeric, 2) AS avg_time_ms,
round(max_exec_time::numeric, 2) AS max_time_ms,
query
FROM pg_stat_statements
WHERE query LIKE '%orders%'
ORDER BY mean_exec_time DESC;هذه إعدادات وممارسات أطبّقها على كل قاعدة بيانات PostgreSQL أُعدّها، قبل أي مشاكل أداء:
-- 1. Install pg_stat_statements
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
-- 2. Enable slow query logging (50ms is my threshold)
ALTER SYSTEM SET log_min_duration_statement = 50;
-- 3. SSD-appropriate settings
ALTER SYSTEM SET random_page_cost = 1.1;
ALTER SYSTEM SET effective_io_concurrency = 200;
-- 4. Increase work_mem from the anemic default
ALTER SYSTEM SET work_mem = '32MB';
-- 5. Set effective_cache_size to 75% of RAM
-- For a 16GB server:
ALTER SYSTEM SET effective_cache_size = '12GB';
-- 6. Set shared_buffers to 25% of RAM
-- For a 16GB server:
ALTER SYSTEM SET shared_buffers = '4GB';
-- 7. Improve autovacuum responsiveness
ALTER SYSTEM SET autovacuum_naptime = '10s';
-- Apply changes
SELECT pg_reload_conf();
-- Note: shared_buffers requires a restartواستعلام مراقبة أشغّله أسبوعياً:
-- Tables that need attention
SELECT
schemaname || '.' || relname AS table,
pg_size_pretty(pg_total_relation_size(relid)) AS total_size,
n_live_tup,
n_dead_tup,
CASE WHEN n_live_tup > 0
THEN round(100.0 * n_dead_tup / n_live_tup, 1)
ELSE 0
END AS dead_pct,
last_autovacuum,
last_autoanalyze
FROM pg_stat_user_tables
WHERE n_dead_tup > 10000
ORDER BY n_dead_tup DESC;تحسين استعلامات PostgreSQL ليس فنّاً غامضاً. إنه عملية منهجية:
الفرق بين استعلام يستغرق 4 ثوانٍ وآخر يستغرق 0.3 مللي ثانية نادراً ما يكون الأجهزة. إنه دائماً تقريباً المعرفة — معرفة أين تنظر وما تغيّر. والآن تعرف أين تنظر.