راهنمای تنظیم Connection Pooling برای PostgreSQL در VPS

چرا یک VPS کوچک پیش از رسیدن به max_connections با کمبود RAM مواجه می‌شود

استفاده از connection pooling برای PostgreSQL در یک VPS، صرفاً یک ترفند برای افزایش سرعت نیست. این کار عاملی است که یک سرور 4 گیگابایتی را سرپا نگه می‌دارد، زیرا هر اتصال PostgreSQL یک پردازش (process) مجزای سیستم‌عامل است که حافظه اختصاصی خود را اشغال می‌کند. یک pooler، تعداد کمی از پردازش‌های واقعی backend را پشت تعداد زیادی از اتصالات ارزان‌قیمت کلاینت قرار می‌دهد.

مقدار پیش‌فرض max_connections برابر با 100 است. این یک سقف محدودکننده است، نه یک بودجه مصرفی. PostgreSQL هرگز بررسی نمی‌کند که آیا ماشین شما واقعاً توانایی نگهداری 100 پردازش backend که در حال اجرای کوئری‌های واقعی هستند را دارد یا خیر؛ بنابراین ماشین زودتر از رسیدن به آن حد، از کار می‌افتد. مکانیزم OOM killer در هسته سیستم‌عامل، یک پردازش را انتخاب می‌کند و وقتی یک backend را هدف قرار می‌دهد، PostgreSQL کل کلاستر را ری‌استارت می‌کند تا امنیت حافظه اشتراکی (shared memory) دوباره تأمین شود. در لاگ‌ها ابتدا server process (PID 1234) was terminated by signal 9: Killed و سپس terminating any other active server processes دیده می‌شود. در این حالت، تمام اتصالات باز، حتی آن‌هایی که سالم بودند، قطع می‌شوند.

سرور به دلیل اینکه هر اتصال یک پردازش مجزا است و همچنین به این دلیل که work_mem به جای هر اتصال، به ازای هر عملیات sort یا hash تخصیص می‌یابد، با کمبود حافظه مواجه می‌شود. هر دوی این موارد باعث افزایش تصاعدی مصرف حافظه می‌شوند.

هر اتصال یک پردازش است و هر پردازش هزینه حافظه دارد

PostgreSQL برای هر اتصال از یک پردازش مجزا استفاده می‌کند. هنگامی که کلاینتی متصل می‌شود، postmaster یک backend ایجاد می‌کند (fork) و آن backend تا زمان قطع اتصال کلاینت فعال می‌ماند. این یک thread نیست. این پردازش جداول صفحه (page tables)، کش‌های کاتالوگ و برنامه‌های پرس‌وجوی کش‌شدهٔ مخصوص به خود را دارد. این کش‌ها با دسترسی اتصال به جداول بیشتر و اجرای پرس‌وجوهای متنوع‌تر رشد می‌کنند؛ بنابراین یک اتصال طولانی‌مدت در یک برنامه ORM پرمشغله، هزینه بیشتری نسبت به یک اتصال تازه دارد.

حافظه اشتراکی واقعاً به صورت اشتراکی است. shared_buffers یک تخصیص واحد برای کل کلاستر است که در فضای حافظه هر backend نگاشت می‌شود. حافظه خصوصی اشتراکی نیست و به همین دلیل است که top در اینجا شما را گمراه می‌کند: مقدار RSS (Resident Set Size) یک backend شامل صفحات اشتراکی است که آن backend لمس کرده است؛ بنابراین جمع زدن RSS برای 50 پردازش backend، مقدار shared_buffers را 50 بار محاسبه می‌کند.

به جای آن، بخش خصوصی را اندازه‌گیری کنید. PSS (Proportional Set Size) هر صفحه اشتراکی را بر تعداد پردازش‌هایی که آن را نگاشت کرده‌اند تقسیم می‌کند و USS (Unique Set Size) فقط صفحاتی را می‌شمارد که منحصراً متعلق به همان پردازش هستند.

sudo apt update
sudo apt install -y smem
sudo smem -k -P '^postgres'

ستون USS نشان‌دهنده مقدار حافظه‌ای است که در صورت خروج آن backend آزاد می‌شود. این هزینه واقعی هر اتصال برای شماست. ارقام منتشرشده معمولاً یک backend بیکار را در حد چند مگابایت و یک backend که پرس‌وجوهای سنگین ORM را اجرا کرده، چندین برابر آن نشان می‌دهند. با این اعداد به عنوان ارقام استاندارد برخورد کنید، نه اعداد مربوط به سیستم خودتان. تنها رقمی که ارزش برنامه‌ریزی بر اساس آن را دارد، عددی است که از سیستم خودتان تحت بار کاری واقعی به دست می‌آید.

یک تخصیص حافظه در هر نشست (session) وجود دارد که نادیده گرفتن آن آسان است. مقدار temp_buffers به‌طور پیش‌فرض 8MB است و اولین باری که نشست به یک جدول موقت (temporary table) دسترسی پیدا می‌کند، تخصیص می‌یابد. این حافظه تا پایان نشست بازگردانده نمی‌شود.

پارامتر work_mem به ازای هر عملیات تخصیص می‌یابد، نه هر اتصال

در اینجا محاسبات ریاضی برای بسیاری از افراد پیچیده می‌شود. مقدار پیش‌فرض work_mem برابر با 4MB است و مستندات PostgreSQL به‌صراحت بیان می‌کند که این یعنی: «یک کوئری پیچیده ممکن است چندین عملیات مرتب‌سازی (sort) و هش (hash) را به‌طور هم‌زمان انجام دهد، و به هر عملیات معمولاً اجازه داده می‌شود تا پیش از شروع به نوشتن داده‌ها در فایل‌های موقت، از حافظه‌ای به اندازه این مقدار استفاده کند.» یک پلن کوئری با سه گره مرتب‌سازی می‌تواند در یک لحظه و در یک backend، سه برابر مقدار work_mem حافظه مصرف کند.

عملیات هش حافظه بیشتری دریافت می‌کنند. مقدار پیش‌فرض hash_mem_multiplier برابر با 2.0 است، بنابراین یک hash join یا یک hash aggregate ممکن است از دو برابر مقدار work_mem استفاده کند که در تنظیمات پیش‌فرض برابر با 8MB است. کوئری‌های موازی (Parallel query) این مقدار را باز هم افزایش می‌دهند، زیرا هر worker موازی یک پردازش مجزا با سهمیه حافظه خاص خود است.

محاسبات را برای یک VPS با 4 GB رم در نظر بگیرید. اگر shared_buffers را روی 1 GB تنظیم کنید، work_mem را روی 4MB باقی بگذارید و اجازه دهید 100 اتصال هم‌زمان هر کدام یک کوئری با دو گره هش اجرا کنند، نتیجه این می‌شود: 100 ضرب‌در 16MB، یعنی 1.6 GB حافظه اختصاصی علاوه بر 1 GB حافظه shared buffers؛ این مقدار پیش از محاسبه page cache و سایر پردازش‌های سیستم است. حالا اگر work_mem را به 64MB افزایش دهید (چون سرور رم اضافه دارد)، همان 100 اتصال به معنای 100 ضرب‌در 256MB خواهد بود. هیچ هشدار سیستمی وجود ندارد. شما زمانی متوجه مشکل می‌شوید که OOM killer وارد عمل شود.

به‌جای حدس زدن، می‌توانید بررسی کنید که آیا work_mem بیش از حد کوچک است یا خیر. مقدار log_temp_files = 0 را در postgresql.conf تنظیم کرده و سرویس را reload کنید. پس از آن، هر بار که داده‌ها به دیسک منتقل (spill) شوند، یک خط در لاگ نوشته می‌شود که نام فایل و اندازه آن را مشخص می‌کند، مانند temporary file: path "base/pgsql_tmp/pgsql_tmp1234.0", size 20971520. انتقال‌های مکرر به دیسک به این معناست که افزایش work_mem می‌تواند مفید باشد. اگر هیچ انتقال به دیسکی رخ نمی‌دهد، افزایش این مقدار هیچ سودی ندارد و تنها حافظه‌ای را اشغال می‌کند که در اختیار ندارید.

محاسبات مربوط به pool که واقعاً دردسرساز می‌شوند

هیچ‌کس با هدف باز کردن 240 اتصال کار را شروع نمی‌کند. آن‌ها یک pool با ظرفیت 20 اتصال تنظیم می‌کنند و سپس برنامه را در بیش از یک مکان اجرا می‌کنند.

[
  {
    "config": "1 worker",
    "backends": 20
  },
  {
    "config": "4 web workers",
    "backends": 80
  },
  {
    "config": "4 web + 2 background",
    "backends": 120
  },
  {
    "config": "2 hosts x 4 workers",
    "backends": 160
  },
  {
    "config": "3 hosts x 4 workers",
    "backends": 240
  }
]

چهار worker در Gunicorn، که هر کدام یک pool با ظرفیت 20 اتصال دارند، در مجموع 80 اتصال از backend درخواست می‌کنند. با افزودن دو worker برای پردازش‌های پس‌زمینه، این تعداد به 120 می‌رسد. با افزایش تنظیمات به 3 hosts x 4 workers، برنامه در مجموع 240 اتصال از backend درخواست می‌کند، در حالی که max_connections برابر با 100 است. هیچ‌کدام از این 5 پیکربندی‌ها در هیچ بخشی به‌تنهایی اشتباه تنظیم نشده‌اند. pool به ازای هر پردازش (process) است و هیچ بخشی از برنامه نمی‌تواند مجموع کل اتصالات را مشاهده کند.

تنظیمات پیش‌فرض کتابخانه‌ها نیز همین روند را تشدید می‌کنند. مقدار پیش‌فرض QueuePool در SQLAlchemy برابر با pool_size=5 به همراه max_overflow=10 است که منجر به 15 اتصال به ازای هر پردازش می‌شود. مقدار پیش‌فرض HikariCP برابر با 10 است. Django پیش از نسخه 5.1 هیچ pool داخلی نداشت و به ازای هر worker process تنها یک اتصال برقرار می‌کرد؛ به همین دلیل برنامه‌های Django دیرتر با این مشکل مواجه می‌شوند و زمانی که شخصی CONN_MAX_AGE را تنظیم می‌کند یا گزینه جدیدتر "pool": True را فعال می‌نماید، ناگهان با تمام این محدودیت‌ها روبرو می‌شوند. اگر یک برنامه Django را پشت Gunicorn و nginx اجرا می‌کنید، عددی که باید در آن ضرب کنید، تعداد workerهای Gunicorn است، نه تعداد سرورهای شما.

اتصال Postgres connection pooling در یک VPS واقعاً چه چیزی را تغییر می‌دهد

یک pooler فرآیندی است که از یک سو با پروتکل PostgreSQL با برنامه شما صحبت می‌کند و از سوی دیگر، مجموعه‌ای کوچک از اتصالات واقعی به سرور را نگه می‌دارد. این ابزار هیچ کوئری را سریع‌تر نمی‌کند. بلکه تغییر می‌دهد که چه کسی هزینه اتصال را می‌پردازد و چه تعداد backend واقعی وجود دارد.

دو مورد بهبود می‌یابد. اتصال دیگر نیازی به fork کردن و جستجو در کاتالوگ که حافظه پنهان (cache) یک backend خالی را پر می‌کند ندارد، زیرا pooler خود به درخواست اتصال کلاینت پاسخ می‌دهد. مهم‌تر از آن، تعداد backendهای واقعی دیگر با تعداد اتصالات برنامه همخوانی ندارد، بنابراین 500 کلاینت می‌توانند 20 backend را به اشتراک بگذارند.

انتظار کشیدن، همان ویژگی اصلی است و این بخشی است که افراد در برابر آن مقاومت می‌کنند. بدون یک pooler، تعداد 500 کوئری همزمان همگی یک backend دریافت می‌کنند و همگی به‌طور همزمان روی دو هسته CPU اجرا می‌شوند، بنابراین تک‌تک آن‌ها کند می‌شوند و تمام حافظه در همان لحظه مصرف می‌شود. با یک pooler، تعداد 20 کوئری اجرا می‌شوند و بقیه چند میلی‌ثانیه منتظر می‌مانند، بنابراین هر کوئری در حال اجرا سهم واقعی از CPU را دریافت کرده و زودتر تمام می‌شود. وجود یک صف در مقابل یک pool کوچک، بهتر از نبود صف در مقابل یک pool بزرگ است.

آنچه یک pooler انجام نمی‌دهد، محدود کردن سایر بخش‌های ماشین است. اگر Postgres سرور VPS را با یک سرور برنامه یا با یک پایگاه‌داده برداری روی همان VPS به اشتراک بگذارد، pooler فقط Postgres را در برابر برنامه شما محافظت می‌کند و نه بیشتر. برای همسایه‌ها نیز سقف سخت تعیین کنید: شما می‌توانید حافظه و CPU قابل استفاده توسط یک سرویس را با systemd محدود کنید تا یک فرآیند سرکش نتواند پایگاه‌داده را با خود از کار بیندازد. محل قرارگیری خود پایگاه‌داده، نحوه تنظیم این محدودیت‌ها را تغییر می‌دهد، که تفاوت عملی بین اجرای Postgres در Docker یا مستقیماً روی host است.

تفاوت بین session pooling و transaction pooling

یک تنظیم، تعیین‌کننده همه موارد دیگر است و آن pool_mode است.

در session pooling، یک اتصال سرور برای تمام طول عمر اتصال کلاینت به آن اختصاص داده می‌شود و تنها زمانی که کلاینت قطع شود، آزاد می‌گردد. همه چیز به درستی کار می‌کند، زیرا pooler در اینجا صرفاً یک proxy ساده است. شما در این حالت فقط هزینه برقراری اتصال را صرفه‌جویی می‌کنید و نه چیز دیگری. اگر برنامه 200 اتصال باز کند، شما همچنان به 200 backend نیاز دارید.

در transaction pooling، یک اتصال سرور تنها برای مدت زمان یک تراکنش به کلاینت اختصاص می‌یابد. در لحظه COMMIT یا ROLLBACK، اتصال به pool بازمی‌گردد و کلاینت منتظر بعدی آن را دریافت می‌کند. این همان مکانیزمی است که 500 کلاینت را به 20 backend تبدیل می‌کند. این روش همچنین باعث بروز مشکلاتی می‌شود که ذاتاً در طراحی آن نهفته است: دستور بعدی شما ممکن است روی یک backend متفاوت از دستور قبلی اجرا شود.

حالت پیش‌فرض در PgBouncer برابر با pool_mode = session است. اگر آن را نصب کنید و هیچ تغییری ندهید، بخش کم‌هزینه را دریافت می‌کنید بدون اینکه از مزایای اصلی آن بهره‌مند شوید. حالت سوم، یعنی statement، اتصال را پس از هر دستور تکی بازمی‌گرداند و تراکنش‌های چند-دستوری را رد می‌کند. تا زمانی که دقیقاً نمی‌دانید چرا به آن نیاز دارید، آن را تغییر ندهید.

چه حالت‌های تراکنشی از کار می‌افتند و چرا

همه موارد زیر به یک دلیل شکست می‌خورند. این موارد وضعیت‌هایی هستند که درون یک backend واحد باقی می‌مانند، در حالی که transaction pooling تضمین نمی‌کند که شما دو بار به یک backend یکسان متصل شوید.

• SET و RESET در سطح session. دستورات SET search_path، SET statement_timeout، SET TIME ZONE و SET ROLE روی هر backend که آن دستور را اجرا کرده است اعمال می‌شوند و با شروع تراکنش بعدی شما، از بین می‌روند. از SET LOCAL درون یک تراکنش صریح (explicit transaction) استفاده کنید که محدود به همان تراکنش است و بنابراین ایمن می‌باشد.
• LISTEN. تحویل اعلان‌ها (Notification) متعلق به backendای است که LISTEN را اجرا کرده است، و آن backend به محض پایان تراکنش به کلاینت دیگری واگذار می‌شود. NOTIFY همچنان در حالت transaction mode کار می‌کند که باعث ایجاد یک خطای گیج‌کننده می‌شود: ارسال موفقیت‌آمیز است، اما دریافت هرگز اتفاق نمی‌افتد. اگر به LISTEN نیاز دارید، یک اتصال اضافی مستقیم به پورت 5432 باز کنید که از pooler عبور نکند.
• قفل‌های مشورتی (Advisory locks) در سطح session. قفل pg_advisory_lock() توسط session نگه داشته می‌شود و با پایان session آزاد می‌گردد. در حالت transaction pooling، دستور باز کردن قفل (unlock) شما روی یک backend متفاوت اجرا می‌شود، بنابراین قفل تا زمانی که PgBouncer آن اتصال سرور را بازنشسته نکند، باقی می‌ماند؛ این اتفاق به‌صورت پیش‌فرض پس از server_lifetime، یعنی یک ساعت، رخ می‌دهد. از pg_advisory_xact_lock() استفاده کنید که در پایان تراکنش توسط همان backend که قفل را گرفته بود، آزاد می‌شود.
• PREPARE و DEALLOCATE، به عنوان دستورات SQL. این دستورات هرگز در حالت transaction mode در دسترس نیستند.
• کرسرهای WITH HOLD و هر کرسر سمت سرور که انتظار می‌رود بیش از عمر تراکنش خود باقی بماند.
• جداول موقت (Temporary tables) که قرار است پس از commit باقی بمانند. دستور CREATE TEMP TABLE ... ON COMMIT PRESERVE ROWS جدول را در schema موقت یک backend قرار می‌دهد و تراکنش بعدی شما ممکن است در آن backend نباشد.
• LOAD.

دستورات آماده (Prepared statements) در سطح پروتکل، تنها موردی هستند که تغییر وضعیت داده‌اند. نسخه 1.21.0 از PgBouncer پشتیبانی از آن‌ها را در حالت transaction mode اضافه کرد و نسخه 1.24.0 با تنظیم max_prepared_statements روی 200، آن را به‌صورت پیش‌فرض فعال نمود. نسخه‌های قدیمی‌تر این مقدار را روی 0 باقی می‌گذارند که به معنای غیرفعال بودن است. توزیع Ubuntu 24.04 شامل PgBouncer 1.22.0 است، بنابراین این قابلیت وجود دارد اما باید max_prepared_statements را خودتان تنظیم کنید. اگر از عملکرد نسخه خود مطمئن نیستید، تنظیم ایمن در سمت کلاینت است: کتابخانه psycopg 3 با تنظیم prepare_threshold روی None، استفاده از prepared statements سمت سرور را متوقف می‌کند.

Django نسخه خاص خود را از این موضوع دارد. مستندات بیان می‌کنند که «استفاده از یک connection pooler در حالت transaction pooling (مانند PgBouncer) مستلزم غیرفعال کردن کرسرهای سمت سرور برای آن اتصال است»، زیرا «کرسرهای سمت سرور فقط در اتصالی که در آن ایجاد شده‌اند قابل دسترسی هستند». مقدار DISABLE_SERVER_SIDE_CURSORS را در تنظیمات آن دیتابیس روی True قرار دهید، در غیر این صورت هر فراخوانی .iterator() به یک خطای متناوب تبدیل می‌شود که فقط تحت فشار (load) ظاهر می‌گردد.

استفاده از transaction mode ارزشمند است و یک قرارداد محسوب می‌شود. لیست را بخوانید، ORM و کتابخانه پردازش‌های پس‌زمینه (background job) خود را با آن تطبیق دهید و سپس سوئیچ کنید.

نصب PgBouncer و هدایت برنامه به سمت آن

پیکربندی زیر برای اجرا روی سرور شخصی شماست: Ubuntu 24.04، در حالی که PostgreSQL از قبل روی 127.0.0.1 و پورت 5432 در حال گوش دادن است.

sudo apt update
sudo apt install -y pgbouncer
pgbouncer --version

سیستم‌عامل Ubuntu 24.04 بسته PgBouncer نسخه 1.22.0 را ارائه می‌دهد. نسخه اصلی (Upstream) تا اوت 2026 به 1.25.2 رسیده است. بررسی کنید کدام نسخه را در اختیار دارید، زیرا رفتار prepared statement که در بالا ذکر شد به آن بستگی دارد.

یک نقش (role) ایجاد کنید که تنها وظیفه‌اش ورود به کنسول مدیریتی PgBouncer باشد، سپس فایل رمز عبور را بسازید. PgBouncer برای کار به secrets نوع SCRAM (مکانیسم احراز هویت چالش-پاسخ نمک‌دار) از جدول pg_authid نیاز دارد و تنها یک superuser می‌تواند آن جدول را بخواند.

sudo -u postgres psql -c "CREATE ROLE pgb_admin LOGIN PASSWORD 'change-this'"
sudo -u postgres psql -At -c \
  'SELECT format($$"%s" "%s"$$, rolname, rolpassword) FROM pg_authid WHERE rolpassword IS NOT NULL' \
  > /tmp/userlist.txt
sudo install -o postgres -g postgres -m 640 /tmp/userlist.txt /etc/pgbouncer/userlist.txt
rm /tmp/userlist.txt

کپی کردن secrets به جای تایپ مجدد رمزهای عبور، دلیل کارکرد این روش است. PgBouncer تنها زمانی می‌تواند یک secret نوع SCRAM را برای ورود به PostgreSQL بازاستفاده کند که کلاینت نیز با SCRAM احراز هویت کرده باشد، secret موجود در فایل دقیقاً بایت به بایت با آنچه در pg_authid است مطابقت داشته باشد (همان salt و تعداد تکرار، نه صرفاً همان رمز عبور)، و خط [databases] یک user= را محدود نکرده باشد. اگر user=appuser را به آن خط اضافه کنید، PgBouncer به یک رمز عبور متنی ساده (plaintext) نیاز خواهد داشت. با استفاده از systemctl show pgbouncer -p User تأیید کنید که مالک فایل با حسابی که سرویس تحت آن اجرا می‌شود، مطابقت دارد. تغییر رمز عبور در PostgreSQL به معنای تولید مجدد این فایل است، در غیر این صورت اتصال بعدی با خطای password authentication failed مواجه خواهد شد.

اکنون فایل /etc/pgbouncer/pgbouncer.ini را بنویسید.

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

[pgbouncer]
listen_addr = 127.0.0.1
listen_port = 6432
auth_type = scram-sha-256
auth_file = /etc/pgbouncer/userlist.txt
admin_users = pgb_admin
pool_mode = transaction
max_client_conn = 500
default_pool_size = 20
min_pool_size = 5
max_db_connections = 80
max_prepared_statements = 200
ignore_startup_parameters = extra_float_digits

listen_addr = 127.0.0.1 باعث می‌شود pooler از دسترس اینترنت عمومی خارج بماند؛ این موضوع اهمیت دارد زیرا pooler که از بیرون قابل دسترسی باشد، یک نقطه احراز هویت است که احتمالاً قصد انتشار آن را نداشته‌اید. max_client_conn تعداد اتصالاتی است که PgBouncer از برنامه می‌پذیرد و چون هزینه کمی دارد، می‌تواند عدد بزرگی باشد. default_pool_size تعداد backendهای واقعی است که یک جفت دیتابیس و کاربر می‌توانند نگه دارند و این عدد پرهزینه‌ای است. max_db_connections کل دیتابیس را روی 80 محدود می‌کند و فضایی را زیر max_connections برای psql، پشتیبان‌گیری و مانیتورینگ باقی می‌گذارد. ignore_startup_parameters = extra_float_digits از رد شدن درایورها توسط PgBouncer جلوگیری می‌کند (از جمله درایور JDBC) که این پارامتر را در زمان اتصال ارسال می‌کنند.

sudo systemctl restart pgbouncer
sudo systemctl status pgbouncer --no-pager
sudo journalctl -u pgbouncer -n 20 --no-pager

یک شروع موفق، خطی را در لاگ ثبت می‌کند که نشان می‌دهد PgBouncer روی 127.0.0.1:6432 در حال گوش دادن است. شروعی که به دلیل فایل رمز عبور با شکست مواجه شود، مسیری را که نتوانسته بخواند در لاگ ثبت می‌کند که تقریباً همیشه مشکل از مجوزها (mode) یا مالکیت فایل است، نه خطای نحوی (syntax). سپس رشته اتصال برنامه را از پورت 5432 به پورت 6432 تغییر دهید و آن را restart کنید. هیچ چیز دیگری در برنامه تغییر نمی‌کند.

نحوه بررسی عملکرد استخر اتصالات

نرم‌افزار PgBouncer دارای یک کنسول مدیریتی است که از طریق یک دیتابیس مجازی به نام pgbouncer در دسترس قرار می‌گیرد.

psql -h 127.0.0.1 -p 6432 -U pgb_admin pgbouncer

SHOW POOLS;
SHOW STATS;
SHOW CLIENTS;

مقدار SHOW POOLS پارامتری است که باید زیر نظر داشته باشید. cl_active نشان‌دهنده کلاینت‌هایی است که در حال حاضر به یک اتصال سرور متصل هستند، cl_waiting کلاینت‌هایی را نشان می‌دهد که در صف انتظار برای اتصال قرار دارند، sv_active و sv_idle به ترتیب تعداد بک‌اندهای فعال و آزاد را نمایش می‌دهند و maxwait مدت‌زمانی (به ثانیه) است که کلاینتِ ابتدای صف منتظر مانده است. در شرایط بار کاری عادی، وضعیت سالم به این معناست که cl_waiting برابر با 0 و maxwait نیز برابر با 0 باشد. اگر مقدار maxwait از یک یا دو ثانیه فراتر رود، به این معنی است که اندازه استخر (pool) بسیار کوچک است یا کوئری‌ها بیش از حد کند هستند؛ این دو مورد نیازمند راهکارهای متفاوتی برای رفع مشکل هستند.

پیش از افزایش default_pool_size، بررسی کنید که کدام مورد عامل اصلی است.

SELECT state, count(*) FROM pg_stat_activity
  WHERE backend_type = 'client backend' GROUP BY state;

اگر اکثر بک‌اِندها در وضعیت idle in transaction قرار دارند، مشکل از اندازه استخر نیست. اپلیکیشن در حال باز کردن یک تراکنش است و سپس عملیات کندی (مانند فراخوانی HTTP) را درون آن انجام می‌دهد؛ در نتیجه هر بک‌اِند بدون اجرای کوئری، اشغال باقی می‌ماند. پارامتر idle_in_transaction_session_timeout این اتصالات را قطع می‌کند، اما راهکار اصلی در کد اپلیکیشن نهفته است. اگر در مقابل، تمام بک‌اِندها در وضعیت active باشند، استخر واقعاً اشباع شده است و پیش از تخصیص اتصالات بیشتر، کوئری‌ها نیازمند EXPLAIN (ANALYZE, BUFFERS) هستند.

در مورد تعیین اندازه، رایج‌ترین نقطه شروع، الگوریتم اکتشافی HikariCP است که تقریباً دو برابر تعداد هسته‌ها به‌علاوه یک را پیشنهاد می‌دهد؛ بنابراین برای یک VPS با 2 هسته، این مقدار برابر با 5 است. این عدد را به عنوان یک نقطه شروع استاندارد در نظر بگیرید، default_pool_size را نزدیک به آن تنظیم کنید و سپس بر اساس maxwait آن را تغییر دهید. استخرهای کوچک ممکن است در ظاهر ناکافی به نظر برسند، اما معمولاً عملکرد بهتری دارند؛ زیرا بک‌اِندی که در صف انتظار است هزینه‌ای ندارد، در حالی که بک‌اِندی که در حال اجراست، منابع CPU، حافظه و درگیری قفل (lock contention) مصرف می‌کند.

انتخاب بین PgBouncer، PgDog و Pgpool-II

PgBouncer پاسخ مناسب برای موارد معمول است: یک سرور PostgreSQL، یک VPS و برنامه‌ای که اتصالات بیشتری نسبت به توان سخت‌افزار باز می‌کند. این ابزار تنها یک وظیفه دارد، پیکربندی آن در یک فایل ini واحد انجام می‌شود و در مخازن Debian و Ubuntu موجود است. مدیریت اتصالات در این ابزار به‌صورت تک‌رشته‌ای (single-threaded) انجام می‌شود که برای بارهای کاری در مقیاس VPS کاملاً کافی است و تنها در ماشین‌های بسیار بزرگ‌تر به محدودیت تبدیل می‌شود.

زمانی که تصمیم‌گیری برای مسیریابی باید در همان گام شبکه (network hop) مربوط به pooling انجام شود، PgDog گزینه قابل‌توجهی است. این ابزار که با زبان Rust نوشته شده، خود را به عنوان یک پروکسی برای مقیاس‌پذیری PostgreSQL معرفی می‌کند. PgDog علاوه بر pooling در سطح تراکنش و نشست، با تحلیل پرس‌وجوها (query parsing)، قابلیت تفکیک عملیات خواندن/نوشتن (read/write splitting)، شاردینگ با مسیریابی چند-شاردی و تعهد دو مرحله‌ای (two-phase commit) را فراهم می‌کند. زمانی از آن استفاده کنید که یک سرور اصلی (primary) و یک یا چند replica دارید و می‌خواهید عملیات خواندن به replicaها ارسال شود، بدون اینکه نیاز باشد به برنامه بفهمانید که replicaها وجود دارند. دو نکته احتیاطی: مجوز آن AGPLv3 است، بنابراین پیش از استفاده در محیط عملیاتی، بند مربوط به استفاده در شبکه (network-use clause) را با مسئول حقوقی شرکت خود بررسی کنید؛ موضع خود پروژه این است که استفاده داخلی و تغییرات خصوصی، تعهدی برای انتشار سورس‌کد ایجاد نمی‌کند. همچنین این پروژه نوپا است، به‌صورت هفتگی نسخه جدید ارائه می‌دهد و شماره نسخه‌های آن 0.x است؛ بنابراین به جای دنبال کردن main، یک تگ نسخه خاص را ثابت (pin) کنید.

git clone https://github.com/pgdogdev/pgdog
cd pgdog
cargo build --release
./target/release/pgdog --config pgdog.toml --users users.toml

ساخت از سورس‌کد نیازمند یک toolchain پایدار و به‌روز Rust، ابزار CMake و یک کامپایلر C/C++ است. همچنین باینری‌های از پیش ساخته‌شده برای لینوکس و بسته‌های Debian در صفحه releases و یک image کانتینر در ghcr.io/pgdogdev/pgdog موجود است. پیکربندی در دو فایل تقسیم شده است. فایل اول شامل تنظیمات عمومی و یک ورودی برای هر دیتابیس است که در اینجا به صورت آرایه‌ای از جداول درون‌خطی (inline tables) در فرمت TOML نوشته شده تا تشخیص دو فرم از یکدیگر آسان باشد.

databases = [
  { name = "appdb", host = "127.0.0.1" },
]

[general]
port = 6432
default_pool_size = 10

فایل دوم شامل یک ورودی برای هر کاربر، در همان فرمت آرایه‌ای است.

users = [
  { name = "appuser", database = "appdb", password = "change-this" },
]

PgDog به‌صورت پیش‌فرض روی پورت 6432 گوش می‌دهد که مشابه پورت PgBouncer است، بنابراین این دو نمی‌توانند هم‌زمان از پورت پیش‌فرض روی یک میزبان استفاده کنند.

Pgpool-II، در نسخه 4.7.2 تا ژوئن 2026، قابلیت pooling به همراه load balancing و یک watchdog برای failover خودکار را ارائه می‌دهد. ویژگی‌های اضافی، حالت‌های خرابی (failure modes) بیشتری نیز به همراه می‌آورند و مدل pooling آن چیزی است که پیش از انتخاب باید درک شود. Pgpool-II تعداد num_init_children پردازش فرزند (child process) را پیش‌تولید (pre-fork) می‌کند و هر فرزند تا max_pool اتصال به سرور را کش می‌کند، بنابراین سقف اتصالات به backend برابر است با num_init_children ضرب‌در max_pool. هر فرزند در هر لحظه تنها به یک کلاینت سرویس می‌دهد، بنابراین تعداد کلاینت‌هایی که می‌توانید بپذیرید برابر با num_init_children است که در زمان راه‌اندازی ثابت می‌شود و یک کلاینت بیکار همچنان یک فرزند را اشغال می‌کند. اگر num_init_children را روی 100 و max_pool را روی 4 تنظیم کنید، 400 اتصال backend مجاز خواهید داشت که دقیقاً همان مشکلی است که برای حل آن یک pooler نصب کرده‌اید. زمانی Pgpool-II را انتخاب کنید که به قابلیت failover و مسیریابی پرس‌وجوی آن نیاز دارید، و سپس آن ضرب را با دقت انجام دهید. اگر تنها هدف شما کاهش تعداد اتصالات backend است، این ابزار پیچیدگی بیش از حد نیاز دارد.

پرسش درباره پروکسی‌های مدیریت‌شده و معادل self-hosted آن‌ها

پلتفرم‌های مدیریت‌شده این قابلیت را به عنوان یک محصول جداگانه می‌فروشند. AWS سرویس RDS Proxy را در مقابل RDS قرار می‌دهد و Supabase نیز pooler اختصاصی خود یعنی Supavisor را در مقابل Supabase Postgres می‌گذارد. هر دو همان کاری را انجام می‌دهند که در اینجا توصیف شد: اتصالات کلاینت را با هزینه کم نگه می‌دارند و تعداد محدودی اتصال واقعی به backend اختصاص می‌دهند. Supavisor متن‌باز است و می‌تواند به صورت self-hosted اجرا شود، بنابراین انتخاب بین گزینه اختصاصی و رایگان نیست.

معادل self-hosted یک پروکسی مدیریت‌شده، ایده متفاوتی نیست. همان ایده است با این تفاوت که فایل پیکربندی در دستان شماست: PgBouncer در حالت transaction mode، روی همان VPS که دیتابیس قرار دارد و در حال گوش دادن روی 127.0.0.1. دو تفاوت واقعی وجود دارد. یک پروکسی مدیریت‌شده یک گام شبکه دورتر است، بنابراین تأخیر اضافه می‌کند و در حالی که دیتابیس زیر آن ری‌استارت می‌شود، اتصالات کلاینت را حفظ می‌کند. PgBouncer روی میزبان دیتابیس یک گام loopback اضافه می‌کند که تقریباً بدون هزینه است، اما وقتی آن میزبان از کار می‌افتد، آن هم از بین می‌رود. اگر رفتار «بقا پس از ری‌استارت» را می‌خواهید، به مکانیزم failover نیاز دارید؛ جایی که watchdog در Pgpool-II یا بررسی‌های سلامت در PgDog ارزش پیچیدگی خود را نشان می‌دهند.

یک گزینه دیگر نیز باید در این لیست قرار گیرد. اگر تعداد اتصالات اصلی‌ترین عاملی است که استقرار شما را پیچیده کرده، یک دیتابیس تعبیه‌شده (embedded) مدل اتصالی برای pool کردن ندارد، زیرا به جای یک سرور روی یک پورت، یک کتابخانه درون پردازش شماست. برای یک سرور اپلیکیشن واحد با حجم نوشتن متوسط، اجرای SQLite در محیط عملیاتی روی یک VPS به جای مدیریت این مشکل، آن را به طور کامل حذف می‌کند. زمانی که واقعاً به یک سرور نیاز دارید، پیش از تعیین اندازه ماشین، اندازه pool را تعیین کنید.

FAQ

آیا اگر اپلیکیشن من از قبل دارای connection pool است، همچنان به PgBouncer نیاز دارم؟

معمولاً بله، زیرا pool اپلیکیشن در سطح هر پردازش است و از پردازش‌های دیگر بی‌خبر است. چهار worker در Gunicorn که هر کدام یک pool با 20 اتصال 80 دارند، به همراه دو worker پس‌زمینه، مجموعاً 120 اتصال ایجاد می‌کنند. PgBouncer تنها مؤلفه‌ای است که مجموع اتصالات را می‌بیند و می‌تواند آن را محدود کند. چیدمان بهینه، استفاده از هر دو است: یک pool کوچک در هر worker تا درخواست‌ها هزینه برقراری اتصال TCP را نپردازند، و PgBouncer در حالت transaction mode برای محدود کردن تعداد اتصالات واقعی به دیتابیس.

دقیقاً چه چیزی با تغییر PgBouncer به حالت transaction mode از کار می‌افتد؟

هر چیزی که وضعیت (state) را در یک backend در طول تراکنش‌ها حفظ کند. دستورات SET و RESET در سطح session، کرسورهای LISTEN و WITH HOLD، دستورات SQL PREPARE و DEALLOCATE، قفل‌های مشورتی (advisory locks) در سطح session، جداول موقتی (temporary tables) که باید پس از commit باقی بمانند، و LOAD. دستور NOTIFY همچنان کار می‌کند، که باعث می‌شود خرابی‌های ناشی از LISTEN بیشتر شبیه به یک باگ در تحویل داده به نظر برسند تا یک مشکل در pooling. در Django، مقدار DISABLE_SERVER_SIDE_CURSORS را روی True تنظیم کنید. در psycopg 3، یا prepare_threshold را روی None تنظیم کنید یا از PgBouncer نسخه 1.22 یا جدیدتر با مقدار max_prepared_statements بزرگ‌تر از 0 استفاده کنید. عبارت pg_advisory_lock() را با pg_advisory_xact_lock() جایگزین کنید.

مقدار default_pool_size روی یک VPS با 2 هسته چقدر باید باشد؟

کمتر از آن چیزی که تصور می‌کنید. قاعده کلی HikariCP که به‌طور گسترده منتشر شده، حدود دو برابر تعداد هسته‌ها به علاوه یک است، یعنی تقریباً 5 برای دو هسته؛ این یک نقطه شروع است، نه یک پاسخ قطعی. آن را تنظیم کنید، سپس در حین بار کاری واقعی، مقادیر maxwait و cl_waiting را در SHOW POOLS بررسی کنید. صفر بودن هر دو به این معنی است که pool به اندازه کافی بزرگ است. افزایش maxwait به این معنی است که کلاینت‌ها در صف انتظار هستند؛ پیش از افزایش تعداد، pg_stat_activity را بررسی کنید: backendهایی که در وضعیت idle in transaction گیر کرده‌اند، یک باگ اپلیکیشنی هستند که افزایش تعداد اتصالات فقط آن را پنهان می‌کند.

PgBouncer یا PgDog؟

برای یک سرور PostgreSQL روی یک VPS، که شامل اکثر استقرارها می‌شود، از PgBouncer استفاده کنید. این ابزار در مخازن Ubuntu موجود است، رفتار آن به‌خوبی مستند شده و کل پیکربندی آن در یک فایل ini قرار دارد. زمانی از PgDog استفاده کنید که نیاز به تفکیک خواندن/نوشتن (read/write splitting) بین replicaها یا sharding دارید و می‌خواهید این عملیات در همان مرحله pooling انجام شود تا اپلیکیشن نیازی به دانستن توپولوژی نداشته باشد. پیش از استفاده از PgDog، مسئله مجوز AGPLv3 را با بخش حقوقی محل کار خود حل کنید و حتماً یک نسخه (release) خاص را pin کنید، زیرا این پروژه هنوز در نسخه‌های 0.x است و به‌صورت هفتگی نسخه جدید منتشر می‌کند.