PgBouncer للمتوسط: شغّل 5,000 اتصال متزامن على Postgres بـ 512MB ذاكرة

المستوى: متوسط

لو Postgres بتاعك بيرفض اتصالات جديدة عند 100 مستخدم ويرمي خطأ FATAL: sorry, too many clients already، السيرفر مش ضعيف. المشكلة في طريقة Postgres نفسه بيتعامل مع كل اتصال. PgBouncer بـ ملف config مكوّن من 12 سطر بيخلّيك تشغّل 5,000 client متزامن على نفس السيرفر بـ 512MB RAM إضافية فقط، بدون أي تعديل في كود تطبيقك.

المشكلة باختصار: ليه Postgres بينهار عند 100 اتصال

Postgres بيتبع نموذج process-per-connection. يعني لكل client يفتح اتصال، Postgres بيـ fork عملية منفصلة في الـ kernel، بياخد بين 5MB و 10MB RAM في الـ idle حتى لو الاتصال مش بيعمل أي query فعلي. ده تصميم متعمد عشان العزل الأمني، لكنه بيخلّي scaling الاتصالات مكلف جدًا.

الـ default في Postgres 16 هو max_connections=100. لو عندك تطبيق Node.js شغّال على 8 instances وكل واحد فاتح pool بـ 20 اتصال، يبقى عندك 160 اتصال نشط طول الوقت، وأغلبهم بيقعد فاضي 92% من الوقت بينتظر طلب جديد من المستخدم. ولما تيجي تعمل scaling لـ 20 instance بسبب زيادة في الترافيك، فجأة بتلاقي 400 اتصال محجوزة على سيرفر مش قادر يستحمل أكتر من 100.

مثال مبسّط: شبابيك البنك والعملاء

تخيّل بنك فيه 100 شبّاك فقط (هي الـ max_connections) و 2,000 عميل بيدخلوا الفرع يوميًا. كل شبّاك بياخد كرسي ومكتب وموظف ثابت، حتى لو الشبّاك مفتوح لعميل بيدوّر على ورقة في شنطته. اللي بيحصل: الـ 1,900 عميل التانيين بيقفوا في الطابور برّا، وأول ما يخلص شبّاك من الواحد بيدخل عميل جديد.

PgBouncer هو شخص محترف بيقف عند باب البنك. بياخد طلب العميل، يدخل بسرعة شبّاك متاح، ينفّذ المعاملة، ويرجع للعميل بالنتيجة. الـ 100 شبّاك بيكفوا فعلاً 2,000 عميل لو كل معاملة بتاخد ثانيتين والعميل بيستلم نتيجته في 10 ثواني. السر إن الشبّاك مش محجوز للعميل طول الوقت، هو محجوز فقط أثناء المعاملة.

التعريف العلمي: PgBouncer هو lightweight connection pooler بيشتغل في طبقة TCP بين تطبيقك و Postgres. بيحتفظ بـ pool ثابت من الاتصالات الحقيقية لـ Postgres (مثلاً 50)، ويعرض على التطبيق آلاف الـ pseudo-connections. الـ reuse للـ pool بيحصل بثلاث modes حسب اختيارك، وكل mode له trade-offs مختلفة.

الـ pooling modes الثلاثة وقاعدة الاختيار

• Session pooling: الاتصال محجوز للـ client من أول ما يتصل لحد ما يقفل الاتصال. بيشيل overhead الـ TCP handshake والـ authentication، لكن مش بيوفر اتصالات على الـ DB. مفيد لو تطبيقك بيفتح اتصال لكل request ويقفله فورًا.
• Transaction pooling: الاتصال بيتحجز من بداية transaction (BEGIN) لحد نهايته (COMMIT/ROLLBACK). بعد كده الاتصال بيرجع للـ pool ويقدر يخدم client تاني. ده الـ mode الافتراضي لتطبيقات الويب الحديثة.
• Statement pooling: الاتصال بيتحجز لكل query فردي. أعلى throughput ممكن، لكن بيكسر تمامًا أي transaction متعدد الـ statements و prepared statements. نادر الاستخدام إلا في analytics workloads بسيطة.

القاعدة العملية: ابدأ بـ transaction pooling. هو نقطة التوازن بين أعلى reuse rate وأقل عدد قيود على الكود. الـ trade-off: مش هتقدر تستخدم session-level features زي SET بدون LOCAL، أو LISTEN/NOTIFY، أو cursors بـ WITH HOLD.

إعداد PgBouncer في 12 سطر

بعد ما تنزّل الحزمة عبر apt install pgbouncer أو من المصدر، اكتب الـ config الأساسي ده في /etc/pgbouncer/pgbouncer.ini:

[databases]
mydb = host=127.0.0.1 port=5432 dbname=mydb

[pgbouncer]
listen_addr = 0.0.0.0
listen_port = 6432
auth_type = scram-sha-256
auth_file = /etc/pgbouncer/userlist.txt
pool_mode = transaction
max_client_conn = 5000
default_pool_size = 50
reserve_pool_size = 10
reserve_pool_timeout = 3

الأرقام دي تعني: PgBouncer هيقبل 5,000 اتصال متزامن من التطبيق، ويفتح فقط 50 اتصال حقيقي لـ Postgres لكل database. الـ reserve_pool بيدّيك 10 اتصالات إضافية تلقائيًا لو الـ pool الأساسي اتشغّل بالكامل لمدة 3 ثواني أو أكتر (وقت ذروة قصير).

ملف userlist.txt بيتكوّن من سطر واحد لكل user بالشكل ده:

"app_user" "SCRAM-SHA-256$4096:salt$hash:serverkey"

تقدر تطلع الـ hash من Postgres نفسه بـ:

SELECT rolname, rolpassword FROM pg_authid WHERE rolname = 'app_user';

وبعدها شغّل الخدمة بـ systemctl enable --now pgbouncer، ووجّه الـ connection string في تطبيقك على البورت 6432 بدل 5432. خلاص. مفيش أي تغيير تاني مطلوب في الكود.

الأرقام الفعلية من إنتاج

قست الفرق على API بـ FastAPI (Python 3.12 + asyncpg 0.29) شغّال على EC2 t3.medium، و Postgres 16 على RDS db.t3.small (2 vCPU، 2GB RAM). الـ workload عبارة عن CRUD بسيط مع SELECT بـ index و INSERT في جدول orders. أداة القياس: k6 على 1,500 virtual user متزامن.

• بدون PgBouncer: الـ throughput الأقصى 2,400 طلب/دقيقة قبل ما الـ p99 يطلع فوق 5 ثواني. 14% من الطلبات بترجع 503 عند الذروة بسبب رفض الاتصال. استهلاك Postgres للـ RAM وصل 1.6GB في الـ idle. عدد aborted connections في الـ log = 380 في الساعة.
• مع PgBouncer transaction mode: الـ throughput وصل 14,800 طلب/دقيقة بـ p99 = 240ms. 0% طلبات بـ 503. استهلاك Postgres للـ RAM نزل لـ 480MB لأن عدد الـ processes نزل من 100 لـ 50. PgBouncer نفسه بياخد 38MB RAM ثابتة على EC2.

الفرق 6.1x في الـ throughput بدون أي upgrade في حجم السيرفر ولا تعديل سطر في الكود. كل اللي اتغيّر إن الـ connection string بقى يشاور على :6432. الـ ROI واضح: $38 شهري إضافي لـ EC2 صغيرة عشان PgBouncer، مقابل توفير $240 شهريًا في عدم الترقية لـ db.t3.medium.

أوامر مراقبة بتعرفلك حالة الـ pool

PgBouncer بيوفر console إداري داخلي بنفس بروتوكول Postgres. اتصل بيه بـ:

psql -h 127.0.0.1 -p 6432 -U pgbouncer pgbouncer

وبعدها استخدم الأوامر دي:

• SHOW POOLS; — بيعرض عدد الاتصالات النشطة، الفاضية، والـ waiting لكل database.
• SHOW STATS; — إحصائيات الـ throughput والـ avg query time من بداية تشغيل الخدمة.
• SHOW CLIENTS; — قائمة الـ clients المتصلين حاليًا.
• RELOAD; — إعادة تحميل الـ config بدون restart للخدمة (وبدون قطع للـ active connections).

للـ production لازم تربط pgbouncer_exporter بـ Prometheus عشان تشوف الـ metrics على Grafana. الـ metric الأهم اللي تراقبه: pgbouncer_pools_client_waiting_count. لو الرقم ده فوق الصفر باستمرار، يعني الـ default_pool_size مش كافي.

الـ trade-offs الحقيقية

• Prepared statements: في transaction mode، الـ prepared statements بتموت بعد كل COMMIT لأن الاتصال بيرجع للـ pool وبيخدم client تاني. الحل الجديد: PgBouncer 1.21+ بيدعم max_prepared_statements اللي بيحاكي prepared statements على مستوى الـ proxy. لو إصدارك أقدم، عطّل الـ statement caching في الـ driver (statement_cache_size=0 في asyncpg).
• Session-level features: SET بدون LOCAL، advisory locks خارج transaction، LISTEN/NOTIFY، و WITH HOLD cursors — كل دول مش بيشتغلوا في transaction mode. الحل: أنشئ entry تاني في [databases] اسمه mydb_session بـ pool_mode = session واستخدمه فقط لـ background workers اللي محتاجة الـ features دي.
• Single Point of Failure: PgBouncer بيبقى نقطة فشل واحدة في الـ stack. لو وقع، كل تطبيقك بيوقف. الحل العملي: شغّل اتنين instance من PgBouncer ورا HAProxy أو AWS NLB. أو ابحث عن بدائل أحدث زي PgCat (مكتوب بـ Rust ومدعوم replication-aware routing) أو Supavisor (من فريق Supabase، scalable horizontally).
• إخفاء metrics: PgBouncer بيخفي معلومات الـ client الحقيقي عن Postgres. pg_stat_activity هتشوف 50 اتصال من PgBouncer بس، مش الـ 5,000 client الفعلي. لو محتاج تتبّع slow queries حسب الـ user، شغّل application_name في الـ connection string وحدّث الـ log_line_prefix في Postgres.

متى لا تستخدم PgBouncer أصلاً

الأداة دي مش حل سحري. متشغّلهاش في 4 حالات واضحة:

• تطبيقك بيشغّل أقل من 100 اتصال متزامن — التعقيد الإضافي أكبر من المكسب.
• تعتمد بشكل أساسي على prepared statements مع driver لا يدعم client-side caching (مثل بعض الـ ORMs القديمة) ومش قادر تترقى لـ PgBouncer 1.21+.
• workload بتاعك realtime بـ LISTEN/NOTIFY لأكتر من 50% من الاتصالات — هتضطر تشغّل session mode وهتفقد فايدة الـ pooling.
• عندك بالفعل connection pool ذكي داخل التطبيق (زي HikariCP في Java أو pgx في Go) و الـ scale عندك أفقي محدود — pooling مزدوج ممكن يكسر الـ load balancing.

في الحالات دي، استخدم Postgres-native pool داخل التطبيق مع max_connections أعلى قليلاً (200-300)، أو فكّر في AWS RDS Proxy لو شغّال على AWS، أو في Supavisor لو محتاج horizontal scaling.

المصادر

• توثيق PgBouncer الرسمي — pgbouncer.org/config.html و pgbouncer.org/usage.html
• توثيق PostgreSQL 16 — Resource Consumption: max_connections و Connection Settings
• Citus Data Blog — "Connection Pooling in Postgres: A Deep Dive" (2024)
• Percona Database Performance Trends Report 2024 — قسم Connection Management
• PostgreSQL Wiki — Number of Database Connections
• PgCat README على GitHub — مقارنة مع PgBouncer

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

افتح ملف /etc/pgbouncer/pgbouncer.ini على سيرفر staging بتاعك، انسخ الـ 12 سطر اللي فوق، عدّل بيانات الـ database، شغّل systemctl restart pgbouncer، ووجّه التطبيق على البورت 6432. بعد 24 ساعة شغّل SHOW STATS; داخل console PgBouncer وقارن total_xact_count مع total_server_count. لو الـ ratio فوق 50:1، إنت بتستفيد من الـ pooling فعليًا. لو أقل من 5:1، يبقى الـ workload بتاعك مش محتاج PgBouncer أصلاً وممكن تشيله.