{"id":29755,"date":"2025-10-07T12:39:35","date_gmt":"2025-10-07T07:09:35","guid":{"rendered":"https:\/\/opstree.com\/blog\/?p=29755"},"modified":"2025-10-07T12:39:35","modified_gmt":"2025-10-07T07:09:35","slug":"postgresql-performance-with-pgbouncer","status":"publish","type":"post","link":"https:\/\/opstree.com\/blog\/2025\/10\/07\/postgresql-performance-with-pgbouncer\/","title":{"rendered":"Complete Guide to Fixing PostgreSQL Performance with PgBouncer Connection Pooling"},"content":{"rendered":"<p><span dir=\"ltr\" role=\"presentation\">Several factors affect database performance, and one of the most critical is <\/span><span dir=\"ltr\" role=\"presentation\">how efficiently your application manages database connections. When <\/span><span dir=\"ltr\" role=\"presentation\">multiple clients connect to PostgreSQL simultaneously, creating a new <\/span><br role=\"presentation\" \/><span dir=\"ltr\" role=\"presentation\">connection for each request can be resource-intensive and slow. <\/span><span dir=\"ltr\" role=\"presentation\">This is where <\/span><span dir=\"ltr\" role=\"presentation\">connection pooling<\/span><span dir=\"ltr\" role=\"presentation\"> comes into play. Connection pooling <\/span><span dir=\"ltr\" role=\"presentation\">allows connections to be reused instead of creating a new one every time, <\/span><span dir=\"ltr\" role=\"presentation\">reducing overhead and improving performance. In this blog, we\u2019ll explore <\/span><span dir=\"ltr\" role=\"presentation\">PgBouncer<\/span><span dir=\"ltr\" role=\"presentation\">, a lightweight PostgreSQL connection pooler, and how to set it <\/span><span dir=\"ltr\" role=\"presentation\">up for your environment.<\/span><!--more--><\/p>\n<h2>What is PgBouncer and Why Use It?<\/h2>\n<p><span dir=\"ltr\" role=\"presentation\">PgBouncer<\/span><span dir=\"ltr\" role=\"presentation\"> is an open-source, single-binary connection pooler for <\/span><span dir=\"ltr\" role=\"presentation\"><a href=\"https:\/\/opstree.com\/blog\/2024\/12\/17\/stream-postgresql-data-to-s3-via-kafka-using-jdbc-and-s3-sink-connectors-part-1\/\" target=\"_blank\" rel=\"noopener\">PostgreSQL<\/a>. It acts as an intermediary between clients and the database, <\/span><span dir=\"ltr\" role=\"presentation\">efficiently reusing backend connections to improve performance<\/span><\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone wp-image-29762 size-full\" src=\"https:\/\/opstree.com\/blog\/wp-content\/uploads\/2025\/10\/pgBadger-in-PostgreSQL.png\" alt=\"pgBadger in PostgreSQL?\" width=\"800\" height=\"512\" srcset=\"https:\/\/opstree.com\/blog\/wp-content\/uploads\/2025\/10\/pgBadger-in-PostgreSQL.png 800w, https:\/\/opstree.com\/blog\/wp-content\/uploads\/2025\/10\/pgBadger-in-PostgreSQL-300x192.png 300w, https:\/\/opstree.com\/blog\/wp-content\/uploads\/2025\/10\/pgBadger-in-PostgreSQL-768x492.png 768w\" sizes=\"(max-width: 709px) 85vw, (max-width: 909px) 67vw, (max-width: 984px) 61vw, (max-width: 1362px) 45vw, 600px\" \/><\/p>\n<h3>Key Benefits<\/h3>\n<ol>\n<li><span dir=\"ltr\" role=\"presentation\">Reduces PostgreSQL resource consumption (memory, forks, backends)<\/span><\/li>\n<li><span dir=\"ltr\" role=\"presentation\">Improves response time by reusing existing connections<\/span><\/li>\n<li><span dir=\"ltr\" role=\"presentation\">Supports all PostgreSQL authentication methods<\/span><\/li>\n<li><span dir=\"ltr\" role=\"presentation\">Simple, lightweight, and easy to configure<\/span><br role=\"presentation\" \/><span dir=\"ltr\" role=\"presentation\">In short, <\/span><span dir=\"ltr\" role=\"presentation\">PgBouncer<\/span><span dir=\"ltr\" role=\"presentation\"> is essential for high-concurrency PostgreSQL <\/span><br role=\"presentation\" \/><span dir=\"ltr\" role=\"presentation\">environments<\/span><\/li>\n<\/ol>\n<div style=\"border: 1px solid #ddd; padding: 10px; border-radius: 4px; background-color: #f9f9f9; text-align: center;\"><strong>Are you looking for expert PostgreSQL performance optimization and <a href=\"https:\/\/opstree.com\/services\/middleware-database-and-data-engineering\/\" target=\"_blank\" rel=\"noopener\">database management services<\/a>?<\/strong><\/div>\n<h2>How Does PgBouncer Work?<\/h2>\n<p><span dir=\"ltr\" role=\"presentation\">Instead of every client opening a direct connection to PostgreSQL, PgBouncer <\/span><span dir=\"ltr\" role=\"presentation\">maintains a <\/span><span dir=\"ltr\" role=\"presentation\">pool of persistent connections<\/span><span dir=\"ltr\" role=\"presentation\">:<\/span><\/p>\n<ul>\n<li>A client requests a connection.<\/li>\n<li><span dir=\"ltr\" role=\"presentation\">PgBouncer checks for an existing cached connection with the same <\/span><span dir=\"ltr\" role=\"presentation\">username and database.<\/span><\/li>\n<li><span dir=\"ltr\" role=\"presentation\">If found, it returns the cached connection; if not, it creates a new backend <\/span><br role=\"presentation\" \/><span dir=\"ltr\" role=\"presentation\">connection.<\/span><\/li>\n<li><span dir=\"ltr\" role=\"presentation\">This reduces the overhead of repeatedly opening and closing connections, <\/span><span dir=\"ltr\" role=\"presentation\">lowering latency and improving throughput.<\/span><\/li>\n<\/ul>\n<h2>When Should You Use PgBouncer?<\/h2>\n<p>PgBouncer is useful when:<\/p>\n<ul>\n<li><span dir=\"ltr\" role=\"presentation\">Your PostgreSQL server has <\/span><span dir=\"ltr\" role=\"presentation\">many simultaneous client connections<\/span><\/li>\n<li><span dir=\"ltr\" role=\"presentation\">You want to <\/span><span dir=\"ltr\" role=\"presentation\">reduce memory usage<\/span><span dir=\"ltr\" role=\"presentation\"> and the number of backend <\/span><br role=\"presentation\" \/><span dir=\"ltr\" role=\"presentation\">processes<\/span><\/li>\n<li><span dir=\"ltr\" role=\"presentation\">You need <\/span><span dir=\"ltr\" role=\"presentation\">faster response times<\/span><span dir=\"ltr\" role=\"presentation\"> for applications with short-lived <\/span><br role=\"presentation\" \/><span dir=\"ltr\" role=\"presentation\">queries<\/span><\/li>\n<\/ul>\n<p><span dir=\"ltr\" role=\"presentation\">Essentially, PgBouncer is ideal for <\/span><span dir=\"ltr\" role=\"presentation\">high-concurrency scenarios<\/span><span dir=\"ltr\" role=\"presentation\"> where <\/span><br role=\"presentation\" \/><span dir=\"ltr\" role=\"presentation\">performance matters<\/span><\/p>\n<div style=\"border: 1px solid #ddd; padding: 10px; border-radius: 4px; background-color: #f9f9f9; text-align: center;\"><strong>Also Read: <a href=\"https:\/\/opstree.com\/blog\/2021\/11\/09\/nginx-monitoring-using-telegraf-prometheus-grafana\/\" target=\"_blank\" rel=\"noopener\">Complete Guide to Nginx Monitoring with Telegraf, Prometheus, and Grafana<\/a><\/strong><\/div>\n<h2>Dataflow Diagram<\/h2>\n<p><span dir=\"ltr\" role=\"presentation\">This diagram shows how PgBouncer manages client connections and <\/span><br role=\"presentation\" \/><span dir=\"ltr\" role=\"presentation\">backend pooling.<\/span><\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone wp-image-29766 size-full\" src=\"https:\/\/opstree.com\/blog\/wp-content\/uploads\/2025\/10\/AI-APPLICATION-6.png\" alt=\"Dataflow Diagram\" width=\"800\" height=\"512\" srcset=\"https:\/\/opstree.com\/blog\/wp-content\/uploads\/2025\/10\/AI-APPLICATION-6.png 800w, https:\/\/opstree.com\/blog\/wp-content\/uploads\/2025\/10\/AI-APPLICATION-6-300x192.png 300w, https:\/\/opstree.com\/blog\/wp-content\/uploads\/2025\/10\/AI-APPLICATION-6-768x492.png 768w\" sizes=\"(max-width: 709px) 85vw, (max-width: 909px) 67vw, (max-width: 984px) 61vw, (max-width: 1362px) 45vw, 600px\" \/><\/p>\n<h2>Step-by-Step Installation &amp; Configuration of PgBouncer<\/h2>\n<p>Before starting, ensure you have:<\/p>\n<ul>\n<li><span dir=\"ltr\" role=\"presentation\">A <a href=\"https:\/\/opstree.com\/blog\/2025\/02\/10\/cloud-security-posture-management-how-to-stay-compliant\/\" target=\"_blank\" rel=\"noopener\">cloud account<\/a> (AWS, Azure, Scaleway, etc.)<\/span><\/li>\n<li><span dir=\"ltr\" role=\"presentation\">SSH key and instance running Ubuntu\/Debian<\/span><\/li>\n<li><span dir=\"ltr\" role=\"presentation\">Sudo privileges or root access<\/span><\/li>\n<\/ul>\n<h3>Step 1: Install PgBouncer<\/h3>\n<pre><code>sudo apt update\r\nsudo apt install pgbouncer\r\npgbouncer --version<\/code><\/pre>\n<h3>Step 2: Configure PgBouncer<\/h3>\n<p><strong>Configuration file path: <code>\/etc\/pgbouncer\/pgbouncer.ini<\/code><\/strong><code><br \/>\n<\/code><\/p>\n<h4>Define Databases<\/h4>\n<p>Add the following under the <code>[databases]<\/code> section:<\/p>\n<pre><code>[databases]\r\nemployee = host=127.0.0.1 port=5432 dbname=employee<\/code><\/pre>\n<h4><img loading=\"lazy\" decoding=\"async\" class=\"alignnone wp-image-29763 size-full\" src=\"https:\/\/opstree.com\/blog\/wp-content\/uploads\/2025\/10\/AI-APPLICATION-4.png\" alt=\"Configure PgBouncer\" width=\"800\" height=\"512\" srcset=\"https:\/\/opstree.com\/blog\/wp-content\/uploads\/2025\/10\/AI-APPLICATION-4.png 800w, https:\/\/opstree.com\/blog\/wp-content\/uploads\/2025\/10\/AI-APPLICATION-4-300x192.png 300w, https:\/\/opstree.com\/blog\/wp-content\/uploads\/2025\/10\/AI-APPLICATION-4-768x492.png 768w\" sizes=\"(max-width: 709px) 85vw, (max-width: 909px) 67vw, (max-width: 984px) 61vw, (max-width: 1362px) 45vw, 600px\" \/><\/h4>\n<h4>Set Pooling Mode and Client Limit<\/h4>\n<pre><code>[pgbouncer]\r\npool_mode = transaction\r\nmax_client_conn = 1000\r\n<\/code><\/pre>\n<p><strong>Note:<\/strong><br \/>\n<span dir=\"ltr\" role=\"presentation\">Using transaction mode releases backend connections after each <\/span><br role=\"presentation\" \/><span dir=\"ltr\" role=\"presentation\">transaction, improving resource efficiency.<\/span><\/p>\n<h3>Step 3: Configure Authentication<\/h3>\n<p>Open the PgBouncer configuration file and set the authentication type and file location:<\/p>\n<pre><code>auth_type = md5\r\nauth_file = \/etc\/pgbouncer\/userlist.txt\r\n<\/code><\/pre>\n<h3>Define Admin Users<\/h3>\n<pre><code>admin_users = postgres\r\n<\/code><\/pre>\n<h3>Step 4: Add Users<\/h3>\n<p>Edit the <code>\/etc\/pgbouncer\/userlist.txt<\/code> file and add your users in the following format:<\/p>\n<pre><code>\"shubhanshi\" \"SCRAM-SHA-256$4096:xd55Qkz5WNarPsBWG5wTIw==$ZsgbdrzalbuawI2RJmWEITyNQvBnoGRz\/IRgd+7Ktdc=:ru+QjXpBUYf0WAaAR\/b\/q2g7P8eYSE1sBG0RYps28u4=\"\r\n<\/code><\/pre>\n<p><strong>To check password hashes in PostgreSQL:<\/strong><\/p>\n<pre><code>SELECT usename, passwd FROM pg_shadow;\r\n<\/code><\/pre>\n<hr \/>\n<h3>Step 5: Test Direct PostgreSQL Connections<\/h3>\n<p>Initialize the <strong>pgbench<\/strong> benchmarking tool:<\/p>\n<pre><code>pgbench -i &lt;my_db&gt;\r\n<\/code><\/pre>\n<p>Run a benchmark test without PgBouncer:<\/p>\n<pre><code>pgbench -c &lt;connections&gt; -T &lt;time&gt; &lt;my_db&gt; -h &lt;ip&gt; -p &lt;port&gt; -U &lt;user&gt;\r\n<\/code><\/pre>\n<h3>Step 6: Update <code>pg_hba.conf<\/code><\/h3>\n<p>Add the following line under <strong>IPv4 local connections<\/strong> to allow MD5 authentication:<\/p>\n<pre><code>host    all     all     127.0.0.1\/32     md5\r\n<\/code><\/pre>\n<hr \/>\n<h3>Step 7: Restart Services<\/h3>\n<p>Restart PostgreSQL and PgBouncer to apply changes:<\/p>\n<pre><code>sudo systemctl restart postgresql\r\nsudo systemctl restart pgbouncer\r\n<\/code><\/pre>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone wp-image-29764 size-full\" src=\"https:\/\/opstree.com\/blog\/wp-content\/uploads\/2025\/10\/AI-APPLICATION-5.png\" alt=\"Restart Services\" width=\"800\" height=\"512\" srcset=\"https:\/\/opstree.com\/blog\/wp-content\/uploads\/2025\/10\/AI-APPLICATION-5.png 800w, https:\/\/opstree.com\/blog\/wp-content\/uploads\/2025\/10\/AI-APPLICATION-5-300x192.png 300w, https:\/\/opstree.com\/blog\/wp-content\/uploads\/2025\/10\/AI-APPLICATION-5-768x492.png 768w\" sizes=\"(max-width: 709px) 85vw, (max-width: 909px) 67vw, (max-width: 984px) 61vw, (max-width: 1362px) 45vw, 600px\" \/><\/p>\n<h3>Step 8: Run Benchmark Using PgBouncer<\/h3>\n<p>The default PgBouncer port is <strong>6432<\/strong>. Use the following command to run the benchmark:<\/p>\n<pre><code>pgbench -c &lt;connections&gt; -T &lt;time&gt; &lt;my_db&gt; -h &lt;ip&gt; -p 6432 -U &lt;user&gt; -f &lt;file_name&gt;\r\n<\/code><\/pre>\n<p><strong>Tip:<\/strong> Comparing the benchmark results with and without PgBouncer helps you measure connection pooling efficiency and overall <a href=\"https:\/\/opstree.com\/blog\/2023\/06\/20\/database-migration-service-in-aws\/\" target=\"_blank\" rel=\"noopener\">database performance<\/a> improvement.<\/p>\n<h2>Conclusion<\/h2>\n<p><span dir=\"ltr\" role=\"presentation\">Using <a href=\"https:\/\/www.pgbouncer.org\/\" target=\"_blank\" rel=\"noopener\">PgBouncer <\/a><\/span><span dir=\"ltr\" role=\"presentation\">significantly improves PostgreSQL performance<\/span><span dir=\"ltr\" role=\"presentation\"> in <\/span><br role=\"presentation\" \/><span dir=\"ltr\" role=\"presentation\">high-concurrency scenarios by:\u00a0<\/span><\/p>\n<ul>\n<li><span dir=\"ltr\" role=\"presentation\">Reducing latency<\/span><\/li>\n<li><span dir=\"ltr\" role=\"presentation\">Increasing transaction throughput<\/span><\/li>\n<li><span dir=\"ltr\" role=\"presentation\">Optimizing connection managemen<\/span><\/li>\n<\/ul>\n<p><span dir=\"ltr\" role=\"presentation\">It is a lightweight, effective solution for any PostgreSQL setup with multiple <\/span><br role=\"presentation\" \/><span dir=\"ltr\" role=\"presentation\">client connections<\/span><\/p>\n<h2>FAQs<\/h2>\n<h5><b data-stringify-type=\"bold\">Q 1. How can you check if PgBouncer is running on your system?<\/b><\/h5>\n<p>Ans: Run:\u00a0<code class=\"c-mrkdwn__code\" data-stringify-type=\"code\">sudo systemctl status pgbouncer<\/code>\u00a0or check with\u00a0<code class=\"c-mrkdwn__code\" data-stringify-type=\"code\">ps aux | grep pgbouncer<\/code><\/p>\n<h5><b data-stringify-type=\"bold\">Q2. What is the default port used by PgBouncer?<\/b><\/h5>\n<p>Ans:\u00a0 PgBouncer listens on port<b data-stringify-type=\"bold\">\u00a06432<\/b>\u00a0by default.<\/p>\n<h5><b data-stringify-type=\"bold\">Q3. How can you test PostgreSQL performance with and without PgBouncer?<\/b><\/h5>\n<div class=\"p-rich_text_section\">Ans: Use the\u00a0<code class=\"c-mrkdwn__code\" data-stringify-type=\"code\">pgbench<\/code>\u00a0tool:<\/div>\n<ul class=\"p-rich_text_list p-rich_text_list__bullet p-rich_text_list--nested\" data-stringify-type=\"unordered-list\" data-list-tree=\"true\" data-indent=\"0\" data-border=\"0\">\n<li data-stringify-indent=\"0\" data-stringify-border=\"0\">Without PgBouncer:\u00a0<code class=\"c-mrkdwn__code\" data-stringify-type=\"code\">pgbench -c &lt;connections&gt; -T &lt;time&gt; -h &lt;db_host&gt; -U &lt;user&gt;<\/code><\/li>\n<li data-stringify-indent=\"0\" data-stringify-border=\"0\">With PgBouncer: use the same command but with\u00a0<code class=\"c-mrkdwn__code\" data-stringify-type=\"code\">-p 6432\\<\/code><\/li>\n<\/ul>\n<h5><b data-stringify-type=\"bold\">Q4. Which command is used to restart PgBouncer after configuration changes?<\/b><\/h5>\n<p>Ans:\u00a0\u00a0<code class=\"c-mrkdwn__code\" data-stringify-type=\"code\">sudo systemctl restart pgbouncer<\/code><\/p>\n<h5><b data-stringify-type=\"bold\">Q5. Where is the main PgBouncer configuration file located?<\/b><\/h5>\n<p>Ans:\u00a0\u00a0<code class=\"c-mrkdwn__code\" data-stringify-type=\"code\">\/etc\/pgbouncer\/pgbouncer.ini<\/code><\/p>\n","protected":false},"excerpt":{"rendered":"<p>Several factors affect database performance, and one of the most critical is how efficiently your application manages database connections. When multiple clients connect to PostgreSQL simultaneously, creating a new connection for each request can be resource-intensive and slow. This is where connection pooling comes into play. Connection pooling allows connections to be reused instead of &hellip; <a href=\"https:\/\/opstree.com\/blog\/2025\/10\/07\/postgresql-performance-with-pgbouncer\/\" class=\"more-link\">Continue reading<span class=\"screen-reader-text\"> &#8220;Complete Guide to Fixing PostgreSQL Performance with PgBouncer Connection Pooling&#8221;<\/span><\/a><\/p>\n","protected":false},"author":244582706,"featured_media":29760,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_coblocks_attr":"","_coblocks_dimensions":"","_coblocks_responsive_height":"","_coblocks_accordion_ie_support":"","jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":true,"jetpack_social_options":{"image_generator_settings":{"template":"highway","enabled":false},"version":2}},"categories":[768739361],"tags":[768739342,1279075,768739581,15989,343865],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"https:\/\/opstree.com\/blog\/wp-content\/uploads\/2025\/10\/PostgreSQL.jpg","jetpack_likes_enabled":false,"jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/pfDBOm-7JV","jetpack-related-posts":[],"_links":{"self":[{"href":"https:\/\/opstree.com\/blog\/wp-json\/wp\/v2\/posts\/29755"}],"collection":[{"href":"https:\/\/opstree.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/opstree.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/opstree.com\/blog\/wp-json\/wp\/v2\/users\/244582706"}],"replies":[{"embeddable":true,"href":"https:\/\/opstree.com\/blog\/wp-json\/wp\/v2\/comments?post=29755"}],"version-history":[{"count":5,"href":"https:\/\/opstree.com\/blog\/wp-json\/wp\/v2\/posts\/29755\/revisions"}],"predecessor-version":[{"id":29767,"href":"https:\/\/opstree.com\/blog\/wp-json\/wp\/v2\/posts\/29755\/revisions\/29767"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/opstree.com\/blog\/wp-json\/wp\/v2\/media\/29760"}],"wp:attachment":[{"href":"https:\/\/opstree.com\/blog\/wp-json\/wp\/v2\/media?parent=29755"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/opstree.com\/blog\/wp-json\/wp\/v2\/categories?post=29755"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/opstree.com\/blog\/wp-json\/wp\/v2\/tags?post=29755"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}