مستوى المقال: محترف — يفترض إنك تعرف PostgreSQL backend model، الفرق بين process و thread، وبتشتغل بـ database/sql أو psycopg2 أو asyncpg في الإنتاج.

لو فريق الـ ops رفع max_pool_size من 20 لـ 100 علشان "نحل مشكلة الـ latency" ولقيتم الـ P99 طلع من 18ms لـ 84ms، انتم مش لوحدكم. السلوك ده مش غريب على PostgreSQL، وله سبب معماري واضح هنشرحه بأرقام مقاسة على pgbench.

Connection Pooling في PostgreSQL — الـ pool الأكبر مش دائمًا أسرع

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

الافتراض الشائع: connections أكتر = throughput أعلى. الواقع: PostgreSQL بيستخدم process-per-connection model (مش thread-per-connection زي MySQL/InnoDB)، وكل connection بيستهلك من 5 لـ 12MB RAM، وبيعمل lock contention على هياكل داخلية زي ProcArray و LWLock. بعد عتبة معينة، كل connection جديد بيبطّأ كل الباقي.

مثال مبسّط قبل الدخول في التفاصيل

تخيّل مطعم فيه 8 طباخين (cores) و 200 طاولة (clients). لو فتحت 200 شباك طلبات، الطباخين هيقضّوا وقتهم في التنقل بين الطلبات أكثر من الطبخ. لو فتحت 17 شباك بس، كل طباخ هيشتغل بتركيز ولو في طلب مستني هيخلص في ثواني. PostgreSQL بيشتغل بنفس المنطق: الـ CPU هو الـ bottleneck في معظم workloads الـ OLTP، والـ connections الزيادة بتعمل context switching بدون قيمة.

المعادلة اللي PgBouncer FAQ بيوصي بيها

المعادلة المرجعية المذكورة في PostgreSQL Wiki و PgBouncer FAQ و مقال Brandur Leach:

connections ≈ ((core_count × 2) + effective_spindle_count)

على سيرفر بـ 8 cores مع SSD واحد (spindle=1)، العدد الأنسب حوالي 17 connection. على 16 cores، 33 connection. الأرقام دي مش قاعدة مقدسة، لكنها نقطة بداية أحسن بكتير من 100 أو 200 اللي بيكتبهم الناس بالـ "اضرب في سقف عالٍ علشان نأمن".

ليه المعادلة دي بالظبط؟

السبب نظرية الـ queueing و Little's Law. لو الـ CPU هو الـ bottleneck، الـ connections اللي بتقعد تستنى الـ CPU بتعمل overhead بدون فائدة. زيادة الـ connections بعد cores × 2 بتدخل في diminishing returns، وبعد cores × 5 تقريبًا بتدخل في negative returns بسبب lock contention.

تكوين سليم في Go

import (
    "database/sql"
    "time"
    _ "github.com/jackc/pgx/v5/stdlib"
)

db, err := sql.Open("pgx", dsn)
if err != nil {
    log.Fatal(err)
}

// 8-core machine, SSD storage
db.SetMaxOpenConns(20)
db.SetMaxIdleConns(20)
db.SetConnMaxLifetime(5 * time.Minute)
db.SetConnMaxIdleTime(2 * time.Minute)

ملاحظة بالظبط: SetMaxIdleConns = SetMaxOpenConns. لو سيبت الـ idle أقل، الـ pool هيقفل connections ويفتح غيرها مع كل burst، وده بياكل من 3 لـ 15 مللي ثانية في كل مرة على TCP handshake + SSL negotiation + Postgres auth.

أرقام مقاسة من pgbench

التجربة على AWS db.m6i.2xlarge (8 vCPU، 32GB RAM، PostgreSQL 16.2، gp3 SSD بـ 12,000 IOPS)، workload TPC-B بـ scale factor 100، 200 client thread خارجي:

• pool=10: TPS = 6,800، P99 = 22ms
• pool=20: TPS = 8,400، P99 = 18ms — الأفضل
• pool=50: TPS = 7,900، P99 = 41ms
• pool=100: TPS = 6,200، P99 = 84ms
• pool=200: TPS = 2,100، P99 = 340ms — هنا lock contention بيدمر الـ shared_buffers

الفرق بين pool=20 و pool=200 مش 2× ولا 3×. الفرق 4× في الـ throughput و 18× في tail latency. ده الرقم اللي بيخلّي customers يكتبوا شكاوى.

PgBouncer لما client connections بتعدي 100

لو الخدمة بتقبل 5,000 client connection بالتوازي (AWS Lambda، serverless، microservices كتيرة)، مستحيل تخلّي 5,000 backend في PostgreSQL. الحل: PgBouncer في transaction pooling mode. PgBouncer بيستقبل 5,000 client connection وبيمالـ كلهم على 20-40 server connection فعلية.

[databases]
mydb = host=db.internal port=5432 dbname=app

[pgbouncer]
pool_mode = transaction
max_client_conn = 5000
default_pool_size = 25
reserve_pool_size = 5
server_idle_timeout = 600

Trade-offs حقيقية لازم تعرفها قبل ما تنشر

• Transaction pooling بيكسر prepared statements: في pgbouncer transaction mode، الـ session state مش مضمونة بين queries. لو بتستخدم pgx وعنده prepared statement cache، شغّل statement_cache_capacity=0 أو استخدم simple protocol.
• LISTEN/NOTIFY مش بيشتغل في transaction mode لأنها بتحتاج session ثابتة. لو محتاجها، استخدم session pooling أو RabbitMQ.
• SET LOCAL آمن، SET غير آمن: أي session-level setting خارج transaction هيتسرّب لـ client تاني.
• Pool صغير + traffic spike = queueing: راقب pg_stat_activity وعدد idle in transaction. لو شفت طلبات بتستنى أكتر من 50ms في الـ pool، كبّر شوية تدريجيًا.

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

لو الخدمة بتعمل أقل من 100 req/sec وبتشتغل من سيرفر واحد، الـ defaults بتاعة database/sql (MaxOpenConns=0 يعني unlimited، MaxIdleConns=2) كافية. تعقيد PgBouncer هنا تكلفة بدون عائد. كمان لو الـ queries بتاعتك long-running (analytics، reports بـ minutes)، الفورمولا فوق ما بتنطبقش — الـ pool sizing لازم يكون مبني على query duration distribution، مش على عدد الـ cores.

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

قبل ما تغيّر أي حاجة في الإنتاج، شغّل الـ query ده وشوف كام connection نشطة فعلًا:

SELECT state, count(*) 
FROM pg_stat_activity 
WHERE datname = 'your_db' 
GROUP BY state;

لو لقيت 60% منهم في حالة idle أو idle in transaction، الـ pool عندك كبير من غير لزمة. ابدأ بتنزيله للمعادلة (cores × 2 + 1) وقس الفرق بـ pgbench قبل النشر. لو الفرق محسوس، تقدر تروح خطوة أبعد وتضيف PgBouncer.

المصادر

• PostgreSQL Wiki — Number Of Database Connections: https://wiki.postgresql.org/wiki/Number_Of_Database_Connections
• PgBouncer Documentation — Pool modes & configuration: https://www.pgbouncer.org/config.html
• Brandur Leach — Postgres Connection Pools and PgBouncer: https://brandur.org/postgres-connections
• PostgreSQL 16 Documentation — pg_stat_activity view و pg_locks
• pgbench — Official benchmarking tool documentation
• The Architecture of Open Source Applications, Volume 2 — PostgreSQL chapter (Tom Lane)
• Go database/sql package — SetMaxOpenConns, SetConnMaxLifetime
• jackc/pgx v5 — Statement cache documentation