SSD Nodes Learn Hosting plans →
راهنماها Matt Connorتوسط Matt Connor · به‌روزرسانی شده 2026-10-30

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

هر اتصال Postgres یک پردازش مجزا با مصرف RAM بالا است. در این مطلب بررسی می‌کنیم چگونه با استفاده از Pooler از خطای OOM Killer و ری‌استارت شدن کلاستر در سرورهای کوچک جلوگیری کنید.

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

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

مقدار پیش‌فرض max_connections برابر با 100 است. این یک حد مجاز است، نه یک بودجه. PostgreSQL هرگز بررسی نمی‌کند که آیا ماشین شما واقعاً توانایی نگهداری 100 پردازش backend در حال اجرای کوئری‌های واقعی را دارد یا خیر، بنابراین ماشین زودتر از رسیدن به این حد دچار خطا می‌شود. مکانیزم OOM killer در هسته سیستم‌عامل یک پردازش را انتخاب می‌کند و وقتی یک backend را انتخاب می‌کند، PostgreSQL کل کلاستر را مجدداً راه‌اندازی می‌کند تا امنیت حافظه اشتراکی دوباره تأمین شود. لاگ‌ها ابتدا 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 اتصال تنظیم می‌کنند و سپس برنامه را در بیش از یک مکان اجرا می‌کنند.

نمودارBackends requested per app topology, pool size 20 per worker (arithmetic)
داده‌های پشت این نمودار
[
  {
    "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 واحد باقی می‌مانند، در حالی که pool کردن تراکنش‌ها تضمین نمی‌کند که شما دو بار به همان backend متصل شوید.

  • SET و RESET در سطح session. دستورات SET search_path، SET statement_timeout و SET ROLE روی هر backend که آن دستور را اجرا کرده است اعمال می‌شوند و تا تراکنش بعدی شما از بین می‌روند، یا زمانی که backend به کلاینت دیگری اختصاص می‌یابد، همچنان فعال باقی می‌مانند. از SET LOCAL درون یک تراکنش صریح استفاده کنید که محدود به همان تراکنش است و بنابراین ایمن است. PgBouncer فهرستی کوتاه از پارامترهایی که PostgreSQL به کلاینت گزارش می‌دهد، از جمله TimeZone، client_encoding و application_name را ردیابی می‌کند و آن‌ها را روی هر backend که دریافت کنید، بازیابی می‌نماید. از نسخه 1.26.0 به بعد PgBouncer، این فهرست شامل search_path نیز می‌شود، اما فقط زمانی که سرور PostgreSQL 18 یا جدیدتر باشد. اگر این ردیابی جدید در تنظیمات شما دچار مشکل شد، PgBouncer 1.26.1 به شما اجازه می‌دهد search_path را به ignore_startup_parameters اضافه کنید تا رفتار نسخه 1.25 مبنی بر نادیده گرفتن آن بازگردد، و لاگ تغییرات آن بیان می‌کند که سایر باگ‌های ردیابی search_path در نسخه‌های بعدی اصلاح خواهند شد.
  • LISTEN. تحویل اعلان متعلق به backendای است که LISTEN را اجرا کرده است، و آن backend به محض پایان تراکنش به کلاینت دیگری داده می‌شود. دستور NOTIFY همچنان در حالت تراکنش کار می‌کند، که این موضوع باعث یک شکست گیج‌کننده می‌شود: ارسال موفقیت‌آمیز است، اما دریافت هرگز اتفاق نمی‌افتد. اگر به LISTEN نیاز دارید، یک اتصال اضافی مستقیم به پورت 5432 باز کنید که از pooler عبور نکند.
  • قفل‌های مشورتی (advisory locks) در سطح session. قفل pg_advisory_lock() توسط session نگه داشته می‌شود و با پایان session آزاد می‌گردد. در حالت pool کردن تراکنش، دستور باز کردن قفل شما روی یک backend متفاوت اجرا می‌شود، بنابراین قفل تا زمانی که PgBouncer آن اتصال سرور را بازنشسته کند (که به‌صورت پیش‌فرض پس از server_lifetime یعنی یک ساعت است) باقی می‌ماند. از pg_advisory_xact_lock() استفاده کنید که در پایان تراکنش توسط همان backend که آن را گرفته است، آزاد می‌شود.
  • PREPARE و DEALLOCATE، دستورات SQL. هرگز در حالت تراکنش در دسترس نیستند.
  • کرسرهای WITH HOLD، و هر کرسر سمت سرور که انتظار می‌رود بیش از تراکنش خود عمر کند.
  • جداول موقت که قرار است پس از commit باقی بمانند. دستور CREATE TEMP TABLE ... ON COMMIT PRESERVE ROWS جدول را در schema موقت یک backend قرار می‌دهد، و تراکنش بعدی شما ممکن است در آن backend نباشد.
  • LOAD.

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

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

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

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

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

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

بسته‌های Ubuntu 24.04 شامل نسخه 1.22.0 از PgBouncer هستند. نسخه اصلی (Upstream) در تاریخ 14 اکتبر 2026 به نسخه 1.26.1 به‌روزرسانی شده است. بررسی کنید کدام نسخه را در اختیار دارید، زیرا رفتار 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 موجود در فایل دقیقاً (بایت به بایت) مشابه 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 از رد شدن درایورهایی مانند JDBC توسط PgBouncer که این پارامتر را در زمان اتصال ارسال می‌کنند، جلوگیری می‌کند.

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 کاملاً کافی است و تنها در ماشین‌های بسیار بزرگ‌تر به محدودیت تبدیل می‌شود.

PgDog زمانی ارزش بررسی دارد که تصمیم‌گیری برای مسیریابی باید در همان گام شبکه (network hop) انجام شود که pooling صورت می‌گیرد. این ابزار خود را به عنوان یک پروکسی برای مقیاس‌پذیری PostgreSQL معرفی می‌کند، با زبان Rust نوشته شده و علاوه بر pooling تراکنش و نشست (session)، با تجزیه کوئری‌ها، تفکیک خواندن/نوشتن (read/write splitting)، شاردینگ با مسیریابی چند-شاردی و تعهد دو مرحله‌ای (two-phase commit) را انجام می‌دهد. زمانی از آن استفاده کنید که یک سرور اصلی (primary) و یک یا چند کپی (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 و یک ایمیج کانتینر در 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.3 تا اکتبر 2026، قابلیت pooling به همراه load balancing و یک watchdog برای failover خودکار ارائه می‌دهد. آن نسخه که در 29 سپتامبر 2026 منتشر شد، یک نسخه امنیتی برای رفع نقص‌های مدیریت پیام watchdog و احراز هویت گواهی است، بنابراین پیش از تکیه بر watchdog، به این نسخه یا نسخه اصلاحی مشابه در شاخه‌های قدیمی‌تر مهاجرت کنید. ویژگی‌های اضافی، حالت‌های شکست (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 است و به‌صورت هفتگی نسخه جدید منتشر می‌کند.

آیا این مقاله مفید بود؟

#postgres#pgbouncer#pgdog#connections#performance