فراتر از Npgsql: راهنمای کامل PgBouncer، Pgpool-II، Odyssey و پروکسی‌های ابری Postgres برای اپلیکیشن‌های C#

یه اعتراف کوچیک قبل از شروع

یه چیزی رو اعتراف کنم: چند وقت پیش وسط یه بحران واقعی تو Dr.Link گیر افتاده بودم که تا الان هم وقتی یادش می‌افتم قلبم تندتر می‌زنه!!

قضیه از این قرار بود: فیچر تراکنش‌های بین‌المللی رو پریروزش آن کرده بودیم. صبح روز بعد، درست همون لحظه‌ای که ترافیک واقعی شروع شد، سیستم زیر بار سنگین شروع کرد به کند شدن. تا ظهر همون روز، فاجعه کامل شده بود؛ صف تراکنش‌ها می‌رفت بالا، تایم‌اوت‌ها پشت سر هم می‌اومدن، و از یه طرف کارفرما پشت خط بود و از یه طرف تیم ساپورت داشت از هر طرف بمباران می‌شد. اون فشاری که اون لحظه رو آدم می‌ذاره رو خیلی‌هاتون احتمالا تجربه کردین؛ یه جایی که دیگه فکر تئوری نمی‌کنی، فقط می‌خوای هرجور شده آتیش رو خاموش کنی.

اولین واکنش من (که فکر کنم واکنش خیلی‌های دیگه‌م همینه) این بود: پول رو بزرگ‌تر کنیم! رفتیم MaxPoolSize رو تو Npgsql بردیم بالا، تعداد Instance‌های اپلیکیشن رو زیاد کردیم، حتی سایز سرور دیتابیس رو هم موقتا بزرگ‌تر کردیم. نتیجه؟ رو داشبورد Grafana عدد throughput خیلی خوشگل شده بود، ولی کاربر همچنان داشت رو دایره‌ی لودینگ زل می‌زد!! دقیقا همون دردی که قرار بود حل بشه، حل نشده بود.

همون شب، خسته و کلافه، نشستم پای گوگل و شروع کردم عین دیوونه‌ها سرچ زدن؛ «postgres connection pool exhausted high load»، «npgsql max_connections timeout»... همینجوری از یه لینک به لینک بعدی می‌رفتم تا رسیدم به چندتا thread قدیمی تو StackOverflow و یه‌سری ریپوی GitHub که دقیقا همین درد رو داشتن. همون‌جا بود که اسم کتاب Latency نوشته‌ی Pekka Enberg رو یه‌جا دیدم که یکی تو کامنت‌هاش رفرنس داده بود. خریدمش و همون شب فصل اولشو خوندم؛ و اونجا مثل چراغ‌قوه افتاد وسط تاریکی. اونجا خیلی مستقیم و بی‌تعارف می‌گه: latency و bandwidth دو تا حیوون کاملا متفاوتن. bandwidth یعنی هم‌زمان چقدر کار می‌تونی انجام بدی، latency یعنی از لحظه‌ای که درخواست می‌ره تا لحظه‌ای که جواب برمی‌گرده چقدر طول می‌کشه. من داشتم یه مشکل تاخیر رو با راه‌حل ظرفیت جواب می‌دادم، که خب... جواب نمی‌داد چون اصلا سوال درست همین نبود!

فرداش با همین دید تازه رفتم سراغ Postgres خودش، نه اپلیکیشن. اونجا بود که فهمیدم هر Instance اپلیکیشن داشت pool خودشو باز می‌کرد، و وقتی اینا رو جمع می‌زدی، از max_connections سرور رد شده بودیم. یعنی هر request جدید داشت منتظر یه process کامل جدید تو سیستم‌عامل می‌موند تا باز بشه؛ چیزی که هیچ‌ربطی به «ظرفیت پردازش» نداشت، دقیقا سر جای اتصال بین اپلیکیشن و دیتابیس گیر کرده بود. همون شکاف بین bandwidth و latency که همیشه باهم قاطی می‌شن.

نتیجه‌ای که برام موند: بعضی فاجعه‌ها نیاز به سرور بیشتر ندارن، نیاز به یه لایه‌ی درست‌تر بین اپلیکیشن و دیتابیس دارن. همون چیزی که این پست دقیقا درباره‌شه.

---

چرا فقط سمت اپلیکیشن کافی نیست؟

در پست قبلی درباره‌ی NpgsqlDataSource pooling و AddDbContextPool صحبت کردیم — این‌ها سمت کلاینت (اپلیکیشن) اجرا می‌شوند. هر instance از اپلیکیشن شما (هر Pod در Kubernetes، هر instance در App Service) یک pool مستقل از اتصالات نگه می‌دارد.

مشکل وقتی شروع می‌شود که تعداد instance‌ها زیاد شود:

10 instance × MaxPoolSize=20  =  200 اتصال هم‌زمان به Postgres

Postgres به‌صورت پیش‌فرض max_connections=100 دارد و هر اتصال یک process جداگانه در سطح سیستم‌عامل ایجاد می‌کند (نه thread). این یعنی مصرف حافظه بالا (هر connection حدود ۵-۱۰ مگابایت overhead) و افت شدید کارایی بعد از عبور از چند صد اتصال هم‌زمان.

راه‌حل: یک لایه‌ی pooling سمت سرور بین اپلیکیشن‌ها و Postgres قرار می‌گیرد که صدها اتصال منطقی از اپلیکیشن‌ها را روی تعداد کمی اتصال فیزیکی به Postgres multiplex می‌کند.

graph LR
    subgraph "اپلیکیشن‌ها (چند Instance)"
        A1[App Instance 1<br/>Pool: 20]
        A2[App Instance 2<br/>Pool: 20]
        A3[App Instance N<br/>Pool: 20]
    end
    subgraph "لایه Server-Side Pooling"
        PB[PgBouncer / PgDoorman<br/>/ Pgpool-II]
    end
    subgraph "PostgreSQL"
        PG[(Postgres<br/>max_connections=100)]
    end
    A1 --> PB
    A2 --> PB
    A3 --> PB
    PB -->|فقط ۱۰-۲۰ اتصال فیزیکی| PG

---

مقایسه سریع گزینه‌ها

نکته‌ی کلیدی: PgBouncer هنوز پرکاربردترین گزینه‌است چون مستندات فراوان، پایداری اثبات‌شده و پشتیبانی وسیع کتابخانه‌ها (از جمله Npgsql) را دارد. PgDoorman و Odyssey برای مقیاس‌های بسیار بزرگ‌تر معنا پیدا می‌کنند.

---

بخش ۱: PgBouncer — عمیق‌تر

سه Pool Mode و تفاوت واقعی‌شان

sequenceDiagram
    participant App as "اپلیکیشن C#"
    participant PB as PgBouncer
    participant PG as Postgres

    Note over App,PG: "Transaction Mode - پرکاربردترین حالت"
    App->>PB: "شروع تراکنش و اجرای Query"
    PB->>PG: "اتصال فیزیکی را قرض می‌گیرد"
    PG-->>PB: "نتیجه"
    PB-->>App: "نتیجه"
    PB->>PG: "اتصال را بلافاصله به pool برمی‌گرداند"

• Session mode: یک اتصال فیزیکی از ابتدای اتصال کلاینت تا قطع آن اختصاص داده می‌شود. امن‌ترین حالت (Prepared statement، session-level SET و temp table درست کار می‌کنند) اما بازدهی pooling کم است.
• Transaction mode: اتصال فیزیکی فقط طول یک تراکنش قرض داده می‌شود. پرکاربردترین حالت در production، اما PREPARE، advisory locks بین چند تراکنش، و session-level state دیگر قابل‌اعتماد نیستند.
• Statement mode: حتی درون یک تراکنش هم اتصال بین statementها عوض می‌شود. تقریباً هیچ‌وقت استفاده نمی‌شود.

نصب و کانفیگ نمونه (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 = 1000
default_pool_size = 20
min_pool_size = 5
reserve_pool_size = 5
reserve_pool_timeout = 3

server_idle_timeout = 300
server_lifetime = 3600
query_wait_timeout = 30

; مهم: در transaction mode باید خاموش باشد چون هر بار سرور عوض می‌شود
server_reset_query = DISCARD ALL

# اجرا با Docker
docker run -d --name pgbouncer \
  -p 6432:6432 \
  -v $(pwd)/pgbouncer.ini:/etc/pgbouncer/pgbouncer.ini \
  -v $(pwd)/userlist.txt:/etc/pgbouncer/userlist.txt \
  edoburu/pgbouncer

کانفیگ سمت C# / Npgsql وقتی پشت PgBouncer هستید

نکته‌ی حیاتی: وقتی در transaction mode پشت PgBouncer هستید، Npgsql نباید prepared statement بسازد یا فرض کند اتصال فیزیکی ثابت است.

var dataSourceBuilder = new NpgsqlDataSourceBuilder(
    "Host=pgbouncer-host;Port=6432;Database=mydb;Username=app;Password=***;" +
    "Pooling=true;" +
    "Maximum Pool Size=15;" +       // pool سمت اپلیکیشن را کوچک نگه دار
    "Minimum Pool Size=2;" +
    "No Reset On Close=true;" +     // PgBouncer خودش reset می‌کند، دوباره لازم نیست
    "Multiplexing=false"            // با transaction pooling سازگار نیست، خاموش کن
);

// در transaction mode، prepared statement خودکار خطرناک است
dataSourceBuilder.ConnectionStringBuilder.MaxAutoPrepare = 0;

var dataSource = dataSourceBuilder.Build();
builder.Services.AddSingleton(dataSource);

builder.Services.AddDbContext<AppDbContext>((sp, options) =>
{
    var ds = sp.GetRequiredService<NpgsqlDataSource>();
    options.UseNpgsql(ds, npgsqlOptions =>
    {
        // اگر از EF Core Migration استفاده می‌کنی، آن را با یک
        // connection string جدا (مستقیم به Postgres، نه از پشت PgBouncer) اجرا کن
    });
});

قانون طلایی: اگر از transaction-mode PgBouncer استفاده می‌کنید، Maximum Pool Size سمت Npgsql را کوچک (۱۰-۲۰) نگه دارید؛ pooling واقعی کار PgBouncer است، نه Npgsql. اگر هر دو لایه را بزرگ بگیرید فقط overhead اضافه کرده‌اید بدون فایده.

---

بخش ۲: Pgpool-II — وقتی Load Balancing هم لازم است

Pgpool-II علاوه بر pooling، قابلیت مسیر‌دهی خودکار خواندن/نوشتن بین master و replica‌ها را دارد — چیزی که PgBouncer و PgDoorman به‌تنهایی ندارند.

graph TB
    App[اپلیکیشن C#] --> PP[Pgpool-II]
    PP -->|"نوشتن (INSERT/UPDATE)"| M[(Master)]
    PP -->|"خواندن (SELECT)"| R1[(Replica 1)]
    PP -->|"خواندن (SELECT)"| R2[(Replica 2)]
    M -.replication.-> R1
    M -.replication.-> R2

کانفیگ نمونه (بخش‌های کلیدی pgpool.conf)

listen_addresses = '*'
port = 9999

backend_hostname0 = 'master.db.internal'
backend_port0 = 5432
backend_weight0 = 0          # وزن صفر یعنی فقط برای نوشتن استفاده شود
backend_flag0 = 'ALWAYS_MASTER'

backend_hostname1 = 'replica1.db.internal'
backend_port1 = 5432
backend_weight1 = 1
backend_flag1 = 'DISALLOW_TO_FAILOVER'

load_balance_mode = on
master_slave_mode = on
master_slave_sub_mode = 'stream'

connection_cache = on
num_init_children = 100
max_pool = 4

سمت C#: تشخیص read-only query برای بهره از load balancing

Pgpool-II خودش SELECTهای خارج از تراکنش explicit را به replica می‌فرستد. اما اگر EF Core از تراکنش ضمنی استفاده کند، ممکن است این رفتار خنثی شود:

// برای بهره‌مندی کامل از load balancing، کوئری‌های فقط-خواندنی را
// بدون تراکنش صریح اجرا کن
public async Task<List<Order>> GetOrdersReadOnlyAsync()
{
    return await _dbContext.Orders
        .AsNoTracking()                 // تراکنش ضمنی نمی‌سازد
        .Where(o => o.Status == "Shipped")
        .ToListAsync();
}

استفاده از Pgpool-II را زمانی در نظر بگیرید که هم نیاز به pooling و هم نیاز به توزیع بار بین replica‌ها دارید؛ در غیر این صورت overhead معماری آن (چند فرآیند، تنظیمات پیچیده‌تر) نسبت به PgBouncer توجیه ندارد.

---

بخش ۳: Odyssey — برای مقیاس واقعاً بزرگ

Odyssey (ساخته‌شده در Yandex) یک pooler چندنخی (multi-threaded) است که هدفش حل محدودیت تک‌نخی‌بودن PgBouncer در بارهای بسیار سنگین (ده‌ها هزار اتصال هم‌زمان) است.

# odyssey.conf (خلاصه)
listen {
    host "0.0.0.0"
    port 6433
}

storage "postgres_server" {
    type "remote"
    host "127.0.0.1"
    port 5432
}

database "mydb" {
    user "app" {
        authentication "scram-sha-256"
        storage "postgres_server"
        pool "transaction"
        pool_size 20
        pool_routing "internal"
    }
}

workers 8   # پردازش موازی روی چند هسته — تفاوت اصلی با PgBouncer

سمت C# هیچ تفاوتی با PgBouncer ندارد؛ چون هر دو پروتکل استاندارد Postgres را پیاده‌سازی می‌کنند — کافی است فقط Port را در connection string به پورت Odyssey تغییر دهید.

چه زمانی سراغ Odyssey یا PgDoorman بروید نه PgBouncer؟
تنها زمانی که خودِ PgBouncer (تک‌نخی) به گلوگاه CPU تبدیل شده باشد — معمولاً در حدود چند ده‌هزار اتصال هم‌زمان یا throughput بسیار بالای query. برای اکثر پروژه‌ها این آستانه هرگز لمس نمی‌شود.

---

بخش ۴: گزینه‌های مدیریت‌شده (Cloud-Managed)

اگر روی AWS، Supabase یا Azure هستید، لازم نیست خودتان زیرساخت pooler را نگه دارید:

// نمونه: اتصال به RDS Proxy از C# — تفاوتی با اتصال معمولی ندارد،
// فقط Endpoint عوض می‌شود
var connStr =
    "Host=my-proxy.proxy-xxxx.us-east-1.rds.amazonaws.com;" +
    "Port=5432;Database=mydb;Username=app;Password=***;" +
    "SSL Mode=Require;Trust Server Certificate=true;" +
    "Maximum Pool Size=15";

مزیت اصلی این گزینه‌ها: صفر overhead عملیاتی (patching، monitoring، HA) به قیمت هزینه‌ی بیشتر و کنترل کمتر روی تنظیمات دقیق.

---

نمای کلی معماری پیشنهادی (Decision Tree)

flowchart TD
    Start[چند instance اپلیکیشن داری؟] -->|۱-۲ instance| A[فقط Npgsql + DbContext Pooling کافی است]
    Start -->|چند instance / میکروسرویس| B{به Read Replica هم نیاز داری؟}
    B -->|خیر| C{روی Cloud مدیریت‌شده هستی؟}
    B -->|بله| D[Pgpool-II]
    C -->|بله| E[RDS Proxy / Supavisor]
    C -->|خیر، Self-hosted| F{بار خیلی سنگین است؟<br/>ده‌ها هزار اتصال}
    F -->|خیر - اکثر پروژه‌ها| G[PgBouncer<br/>transaction mode]
    F -->|بله| H[Odyssey یا PgDoorman]

جمع‌بندی و توصیه عملی

1. همیشه از سمت اپلیکیشن شروع کن: NpgsqlDataSource singleton + AddDbContextPool با اندازه‌ی pool کوچک و منطقی.
2. وقتی چند instance/سرویس داری و به Postgres فشار می‌آید، PgBouncer در transaction mode پیش‌فرض درست است — ساده، پایدار، مستندسازی‌شده.
3. اگر Read Replica داری و می‌خواهی load balancing خودکار، برو سراغ Pgpool-II.
4. اگر روی Cloud هستی و نمی‌خواهی خودت زیرساخت نگه‌داری، از RDS Proxy / Supavisor استفاده کن.
5. Odyssey / PgDoorman را فقط وقتی در نظر بگیر که با داده و متریک واقعی ثابت کرده باشی PgBouncer تک‌نخی گلوگاه شده — نه از قبل به‌عنوان "بهینه‌سازی زودهنگام".
6. مهم‌ترین اشتباه رایج: بزرگ نگه‌داشتن Pool سمت C# و هم‌زمان سمت PgBouncer. یکی از این دو باید کوچک باشد؛ لایه‌ی سروری‌ست که pooling واقعی را انجام می‌دهد.