PgBouncer Transaction Pooling: ليه PostgreSQL بيرفض الاتصال رقم 101

[databases]
appdb = host=10.0.1.5 port=5432 dbname=appdb

[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 = 2000
default_pool_size = 25
reserve_pool_size = 5
reserve_pool_timeout = 3
server_idle_timeout = 600
query_wait_timeout = 120

التطبيق بيوصل على port 6432 بدل الـ 5432. الفرق المهم في الإعداد ده:

• max_client_conn = 2000: عدد الاتصالات اللي PgBouncer هيقبلها من التطبيقات.
• default_pool_size = 25: الاتصالات الحقيقية اللي PgBouncer هيفتحها مع Postgres لكل database/user.
• reserve_pool_size: pool احتياطي بيتفعّل لو الـ default اتلخبط تحت ضغط.

النسبة هنا 80:1 (2000 client مقابل 25 server connection). ده بيشتغل لأن متوسط الـ transaction أقل من 50ms في معظم workloads.

أرقام قياس فعلية — قبل وبعد

على workload فيه 800 req/s، وقاعدة بيانات بـ max_connections = 100، RAM 4GB:

• قبل PgBouncer: 12 instance × 10 conn = 120 اتصال مطلوب → ~20 connection refused/sec في الـ peak، p95 latency 340ms.
• بعد PgBouncer (transaction mode، pool_size=25): 0 connection refused، p95 latency 95ms، p99 nizel من 1.2s لـ 180ms.
• استهلاك RAM على الـ DB: من 1.2GB لـ 280MB بسبب تقليل عدد الـ Postgres processes.

لاحظ إن الـ throughput الحقيقي للـ Postgres ما زادش. اللي زاد هو قدرة التطبيق إنه يستفيد من الـ throughput ده بدل ما يفقد طلبات على connection limit.

الـ Trade-offs اللي لازم تعرفها قبل ما تطبّق

transaction pooling بيكسر مزايا session-level في Postgres، لأن الاتصال الحقيقي بيتغير بين كل transaction واللي بعده:

• SET command بره transaction: بيتنفّذ على connection ممكن متشوفهوش تاني.
• LISTEN/NOTIFY: بيقع تمامًا، لأن الـ subscription مرتبط بالـ session.
• Prepared statements بطريقتها التقليدية: بتفشل لو الـ ORM بيعمل cache على مستوى الـ session. SQLAlchemy لازم تعمله statement_cache_size=0، وnode-postgres لازم تستخدم pg-cursor بحذر.
• Advisory locks خارج transaction: pg_advisory_lock() بدون commit مش هيشتغل صح.
• Temp tables عبر transactions: مش هتشوفها بعد COMMIT.

الافتراض الأساسي هنا: تطبيقك بيستخدم short transactions (أقل من 200ms في معظم الأوقات). لو عندك transaction بتشتغل 30 ثانية، PgBouncer بيحجز الاتصال طول المدة دي، والـ pool بيتقفل على باقي الـ requests، وبتنتهي بمشكلة أسوأ من اللي بدأت بيها.

متى لا تستخدم Transaction Pooling

سيب transaction mode واستخدم session mode أو اتصال مباشر في الحالات دي:

• تطبيقك معتمد بشكل أساسي على LISTEN/NOTIFY كنظام pub/sub داخلي.
• عندك analytics queries طويلة (15 دقيقة+) — transaction mode هيخنق الـ pool عليها.
• الـ ORM بتاعك بيعتمد على prepared statement caching على مستوى الـ session ومش قادر تطفّيه.
• عدد الاتصالات الحقيقي أقل من 50 وما عندكش مشكلة scaling — مفيش داعي لطبقة زيادة.

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

افتح Postgres دلوقتي وشغّل الاستعلام ده: SELECT count(*) FROM pg_stat_activity WHERE state='idle';. لو الرقم أكبر من 50% من max_connections، انت بتدفع ذاكرة على connections فاضية. ركّب PgBouncer كـ sidecar في نفس الـ host بتاع تطبيقك (مش على الـ DB host) عشان متضيفش single point of failure على قاعدة البيانات نفسها، وابدأ بـ pool_size = max_connections / عدد التطبيقات + 5 كنقطة بداية.

المصادر

• التوثيق الرسمي لـ PgBouncer: pgbouncer.org/config.html
• PostgreSQL Wiki: Number Of Database Connections — wiki.postgresql.org/wiki/Number_Of_Database_Connections
• AWS Database Blog (2024): Working with PgBouncer on Amazon RDS for PostgreSQL
• Brandur Leach: Postgres Connection Pooling with pgbouncer
• Citus Data Engineering: When to use PgBouncer transaction pooling