ضبط أداء PostgreSQL: تقنيات متقدمة للتطبيقات ذات الحركة العالية

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

تحليل الاستعلامات باستخدام EXPLAIN ANALYZE

الخطوة الأولى في التحسين هي فهم كيفية تنفيذ الاستعلامات:

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT u.name, COUNT(o.id) as order_count
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
WHERE u.created_at > '2024-01-01'
GROUP BY u.id
ORDER BY order_count DESC
LIMIT 100;

أهم المقاييس التي ينبغي مراقبتها

• Seq Scan: مسح الجداول — غالباً يشير إلى فهارس مفقودة
• Nested Loop: قد يكون مكلفاً لمجموعات البيانات الكبيرة
• Sort: الفرز في الذاكرة مقابل القرص
• Buffers: كمّ البيانات المقروءة من القرص مقابل الذاكرة المؤقتة

استراتيجيات الفهرسة

فهارس B-tree (الافتراضية)

-- Simple index for equality and range queries
CREATE INDEX idx_users_email ON users(email);

-- Composite index for multi-column queries
CREATE INDEX idx_orders_user_status ON orders(user_id, status);

-- Partial index for filtered queries
CREATE INDEX idx_active_users ON users(email)
WHERE is_active = true;

أنواع الفهارس المتخصّصة

-- GIN index for JSONB and full-text search
CREATE INDEX idx_products_attrs ON products USING GIN(attributes);

-- GiST index for geometric data and ranges
CREATE INDEX idx_events_duration ON events USING GIST(duration);

-- BRIN index for large, naturally ordered tables
CREATE INDEX idx_logs_created ON logs USING BRIN(created_at);

ضبط الإعدادات

إعدادات الذاكرة

# postgresql.conf
shared_buffers = 4GB           # 25% of RAM
effective_cache_size = 12GB    # 75% of RAM
work_mem = 256MB               # Per-operation memory
maintenance_work_mem = 1GB     # For VACUUM, CREATE INDEX

إعدادات الاتصال

max_connections = 200          # Depends on your needs
superuser_reserved_connections = 3

تجميع الاتصالات باستخدام PgBouncer

الاتصالات المباشرة بقاعدة البيانات مكلفة. استخدم PgBouncer لتجميع الاتصالات:

# pgbouncer.ini
[databases]
mydb = host=localhost port=5432 dbname=mydb

[pgbouncer]
pool_mode = transaction
max_client_conn = 1000
default_pool_size = 20
min_pool_size = 5

التنظيف (Vacuum) والصيانة

الصيانة المنتظمة حاسمة لأداء PostgreSQL:

-- Check for bloated tables
SELECT
    schemaname || '.' || relname as table,
    n_dead_tup,
    n_live_tup,
    round(n_dead_tup * 100.0 / nullif(n_live_tup, 0), 2) as dead_pct
FROM pg_stat_user_tables
WHERE n_dead_tup > 1000
ORDER BY n_dead_tup DESC;

-- Manual vacuum for specific tables
VACUUM (VERBOSE, ANALYZE) orders;

ضبط Autovacuum

# postgresql.conf
autovacuum_vacuum_scale_factor = 0.1
autovacuum_analyze_scale_factor = 0.05
autovacuum_vacuum_cost_limit = 1000

مراقبة الاستعلامات

-- Find slow queries
SELECT
    query,
    calls,
    total_exec_time / 1000 as total_seconds,
    mean_exec_time as avg_ms,
    rows
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;

تحسين PostgreSQL عملية مستمرة. راقب وقِس وكرّر للحفاظ على تشغيل قاعدة بياناتك بسلاسة.