PgBouncer Configuration for PostgreSQL: Setup, Authentication and Pool Tuning

Resource management is one of the key thing in all RDBMS. DB Connections is one of them. In PostgreSQL, Allocating more connections without proper analysis of resources causes an overhead in Memory usage and the creation of an OS process for each connection will lead to a scheduling overhead in the time-sharing operating system. These things will cause a great impact on application performance and an increase in latency.

To overcome these situations, we can adapt connection pooling. Connection Pool is a cache of database connections so that connections can be reused when future requests to the database are required. The creation of a backend process in Postgres requires opening a connection, user authentication and so on. Connection pooling will reduce these overheads. Also, if we reach the maximum connection limit, pooling will help us to avoid the end-user getting errors like max connections reached, instead it will keep on waiting until the connection is freed up in the database.

In this blog post, we will look at the most famous connection pooling tool for Postgres PGBouncer. At first, we will cover the configuration of PGBouncer followed by the performance improvements with and without PGBouncer.

Environment & Version Compatibility - accurate as of August 2026

PostgreSQL: 14 / 15 / 16 / 17 / 18 - the supported majors. PostgreSQL 18 is current. PostgreSQL 14 reaches its final release on 12 November 2026, so treat this list as dated.
PgBouncer: 1.24.x / 1.25.x - verified against 1.25.2 (8 May 2026)
Platforms: RHEL / Rocky / AlmaLinux 9 & 10 · Ubuntu 22.04 & 24.04 LTS · Debian 12 · self-hosted · AWS RDS & Aurora · GCP Cloud SQL · Azure Flexible Server (built-in PgBouncer) · Kubernetes (CloudNativePG Pooler)

Deprecation Notice

CentOS 7 reached end of life on 30 June 2024 and is no longer listed on the PostgreSQL RPM download page. PostgreSQL 12 (EOL 21 Nov 2024) and 13 (EOL 13 Nov 2025) are out of support - the benchmark in this post ran on 12.1. MD5 password authentication is deprecated in PostgreSQL and documented for removal in a future release; use scram-sha-256.

PGBouncer Configuration:

The original walkthrough used PostgreSQL 12.1 on CentOS 7 with 2 CPU cores, 2 GB RAM and a 100 GB SSD — retained here as the environment that produced the benchmark below. Both are now out of support, so the commands below target PostgreSQL 18 on Rocky Linux / AlmaLinux / RHEL 9. See the official download page.

postgres=# select version();
                     version
---------------------------------------------------------------------
 PostgreSQL 12.1 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-39), 64-bit
(1 row)

2. Now PgBouncer can be installed with the below command

# Rocky Linux / AlmaLinux / RHEL 9  (yum and CentOS 7 are both end-of-life)
sudo dnf install -y pgbouncer

# Debian / Ubuntu
sudo apt-get update && sudo apt-get install -y pgbouncer

# Distro packages lag upstream — check what you actually got
pgbouncer --version

3. After the successful installation, we can start working on the configuration.

[root@postgres ~]# pgbouncer
Need config file.  See pgbouncer -h for usage.
[root@postgres ~]#

4. Edit the configuration file called pgbouncer.ini in the path /etc/pgbouncer/pgbouncer.ini. By default, it contains a lot. Just make sure you have modified the below details in it.

[databases]
; NOTE: in the original this header was commented out, which stops the
; catch-all below from being read as a database definition.
* = host=127.0.0.1 port=5432

[pgbouncer]
listen_port = 6432
; Was listen_addr = * — that binds every interface. Scope it, and let the
; firewall or security group be the second line of defence, not the first.
listen_addr = 127.0.0.1

; Was auth_type = md5 — deprecated in PostgreSQL and documented for removal.
auth_type   = scram-sha-256
auth_file   = /etc/pgbouncer/userlist.txt

; Was admin_users = postgres — do not pool or administer as the superuser.
admin_users = pgbouncer_admin

logfile = /var/log/pgbouncer/pgbouncer.log
pidfile = /run/pgbouncer/pgbouncer.pid

pool_mode         = session
; Documented defaults are 20 and 100. The 80 below was specific to this
; 2 GB test box — size it to your own core count and workload.
default_pool_size = 20
max_client_conn   = 100

• * = host=localhost –> For which database you want to enable pooling. Here I have added the * for this to access all.

• Listen port and Listen Address –> Which IP address and port PGBouncer process listen to.

• auth_type and admin_users — authentication type to be used. Use scram-sha-256; md5 still functions but is deprecated in PostgreSQL and documented for removal. auth_file holds the username and verifier; admin_users lists roles allowed on the admin console.

• Auth file, log and PID files –> Auth file is required for md5 authentication type which contains the username and its respective encrypted passwords. Keep the auth, log, PID files in the secure location

• Pool mode and default Pool size –> Based on the pool mode, the connection will be returned to the pool. It has three values: Session – Connection back to the pool when client closes the session. Transaction – Connection back to the pool when the transaction completes. Statement – Connection back to the pool when the statement completes.

Here I leave it as the value default – session, and raised the pool size to 80 on this test instance. Note the documented defaults are default_pool_size = 20 and max_client_conn = 100; 80 was specific to this hardware, not a general recommendation. Now we need to make an entry in userlist.txt, because PgBouncer is a separate process with no access to the credentials stored in the database.

Login the Postgres server as you usually do

[root@postgres ~]# sudo -u postgres psql
psql (12.1) Type "help" for help.
postgres=#

Execute this query in the psql prompt

-- Use the application role, not the superuser.
postgres=# select rolname, rolpassword from pg_authid where rolname = 'appuser';
 rolname |                            rolpassword
---------+--------------------------------------------------------------------
 appuser | SCRAM-SHA-256$4096:<salt>$<storedkey>:<serverkey>
(1 row)

Add the entry in the username and password order of userlist.txt file.

[root@postgres ~]# cat /etc/pgbouncer/userlist.txt
"appuser"         "SCRAM-SHA-256$4096:<salt>$<storedkey>:<serverkey>"
"pgbouncer_admin" "SCRAM-SHA-256$4096:<salt>$<storedkey>:<serverkey>"

# Restrict the file — it holds password verifiers
[root@postgres ~]# chown pgbouncer:pgbouncer /etc/pgbouncer/userlist.txt
[root@postgres ~]# chmod 640 /etc/pgbouncer/userlist.txt

Now, restart the PGBouncer to take effect  and check the status too

# systemctl directly — the service wrapper just redirects to it
[root@postgres ~]# systemctl restart pgbouncer
[root@postgres ~]# systemctl status pgbouncer --no-pager
pgbouncer.service - A lightweight connection pooler for PostgreSQL
   Active: active (running)
 Main PID: 2976 (pgbouncer)

# ss replaces netstat, which is deprecated on current distributions
[root@postgres ~]# ss -lntp | grep 6432
LISTEN 0  128  127.0.0.1:6432  0.0.0.0:*  users:(("pgbouncer",pid=2976,fd=8))

That’s it. Now we can access the postgres server through PGbouncer just by changing the port to 6432 and also we can connect to the PGBouncer database and execute some useful commands for the pool (show stats,  show pools and so on)

bash-4.2$ psql -Upostgres -h127.0.0.1 --port 6432
Password for user postgres:
psql (12.1) Type "help" for help.
postgres=#

bash-4.2$ psql -p 6432 -h localhost -d pgbouncer
Password for user postgres:
psql (12.1, server 1.12.0/bouncer) Type "help" for help.
pgbouncer=# SHOW STATS;

In applications, we just need to change the port of the connection pooling.

Performance Benchmarking Without and with PGBouncer:

Note on this benchmark

The figures below are from the original test run: PostgreSQL 12.1 on CentOS 7, 2 CPU cores, 2 GB RAM, 100 GB SSD, using sysbench. Both the OS and the PostgreSQL major version are now out of support, so treat the shape of the result as the finding and re-run on your own hardware before quoting absolute numbers. The relative pattern — pooling helps most as concurrency rises past what the server can schedule efficiently — holds across versions.

Test 1:

To benchmark the postgresql server, I am using a utility called sysbench. We can install this utility by executing the following command

# EPEL — Rocky Linux / AlmaLinux 9
sudo dnf install -y epel-release
sudo dnf install -y sysbench
sysbench --version

Created a test database in postgres server and populating the data in it

postgres=# create database benchmark;
CREATE DATABASE

# Password redacted — never publish a live credential in a command example.
[root@postgres log]# sysbench --db-driver=pgsql --oltp-table-size=10000 \
  --oltp-tables-count=24 --threads=1 \
  --pgsql-host=127.0.0.1 --pgsql-port=5432 \
  --pgsql-user=postgres --pgsql-password='<password>' --pgsql-db=benchmark \
  /usr/share/sysbench/tests/include/oltp_legacy/parallel_prepare.lua run
sysbench 1.0.17 (using system LuaJIT 2.0.4)

Data loading is done, now we will generate some loads without PGBouncer

[root@postgres log]# sysbench --db-driver=pgsql --report-interval=2 --oltp-table-size=10000 --oltp-tables-count=24 --threads=64 --time=60 --pgsql-host=127.0.0.1 --pgsql-port=5432 --pgsql-user=postgres --pgsql-password=Aakash@007 --pgsql-db=benchmark /usr/share/sysbench/tests/include/oltp_legacy/oltp.lua run
sysbench 1.0.17 (using system LuaJIT 2.0.4)
SQL statistics:
    queries performed:
        read:                            89208
        write:                           25352
        other:                           12798
        total:                           127358
    transactions:                        6332 (59.54 per sec.)
    queries:                             127358 (1197.51 per sec.)
    ignored errors:                      40 (0.38 per sec.)
    reconnects:                          0 (0.00 per sec.)

General statistics:
    total time:                          106.3507s
    total number of events:              6332

Latency (ms):
         min:                                    3.41
         avg:                                  717.55
         max:                                50688.52
         95th percentile:                      694.45
         sum:                              4543543.98

Threads fairness:
    events (avg/stddev):           98.9375/48.88
    execution time (avg/stddev):   70.9929/19.52

Doing the same test with PGBouncer

SQL statistics:

 queries performed:
        read:                            189784
        write:                           54193
        other:                           27125
        total:                           271102
    transactions:                        13547 (224.38 per sec.)
    queries:                             271102 (4490.21 per sec.)
    ignored errors:                      9 (0.15 per sec.)
    reconnects:                          0 (0.00 per sec.)

General statistics:
    total time:                          60.3742s
    total number of events:              13547

Latency (ms):
         min:                                   25.45
         avg:                                  284.49
         max:                                60369.53
         95th percentile:                      161.51
         sum:                              3854024.30

Threads fairness:
    events (avg/stddev):           211.6719/312.49
    execution time (avg/stddev):   60.2191/0.11

Reconciling the three sets of numbers in this post

From the above stats, we can observe that the TPS value increased from 59.54 to 224.38 per second — a 3.8× gain at 64 threads — with PgBouncer compared with the direct connection, and average latency fell from 717.55 ms to 284.49 ms.

The below are the test results for different concurrent connections value.

Test 2:

I reduced the max_connections to 50, performed the same test.

Without PGbouncer, I got the max connection reached error

Initializing worker threads...
Threads started!
FATAL: `thread_run' function failed: /usr/share/sysbench/tests/include/oltp_legacy/oltp.lua:43: Failed to connect to the database
FATAL: Connection to database failed: FATAL:  sorry, too many clients already

Executed the same command with PgBouncer. The load test starts without any errors — the requests that would have been rejected are queued instead:

Number of threads: 64
Report intermediate results every 2 second(s)
Initializing random number generator from current time
Initializing worker threads...
Threads started!
[ 2s ] thds: 64 tps: 240.59 qps: 4912.63 (r/w/o: 3451.62/968.35/492.66) lat (ms,95%): 130.13 err/s: 0.50 reconn/s: 0.00
[ 4s ] thds: 64 tps: 236.45 qps: 4740.01 (r/w/o: 3327.81/939.30/472.90) lat (ms,95%): 125.52 err/s: 0.00 reconn/s: 0.00

So far, we have seen the basic configuration and performance improvements with the usage of the PGBouncer. By tweaking even more config variables based on our needs in PGBouncer, we can get more performance improvements.

For any application that needs to scale, effective connection pooling is non-negotiable. PgBouncer is the proven solution to prevent database overload, dramatically improving transaction throughput (TPS) and reducing latency. Implementing it correctly is a critical step in building a resilient and high-performance PostgreSQL architecture.

Optimize Your PostgreSQL Performance

Properly configuring PgBouncer is key to avoiding common pitfalls and maximizing its benefits. The Mydbops team provides expert services to ensure your database is fast, secure, and ready for scale.

We can help you with:

• 24/7 Managed Services: Proactive monitoring and management for reliability.
• Expert Consulting & Support: On-demand access to PostgreSQL veterans.
• Performance & Security Audit: Review your PgBouncer setup, pool sizing and query patterns.

Ensure your database infrastructure is built right.

Featured image by Te Pania Noonan on Unsplash

FAQs

1. How to set up PgBouncer?

Install PgBouncer, configure pgbouncer.ini and userlist.txt, then start the service. Define database and user settings to enable pooling.

2. What port and authentication does PgBouncer use by default?

PgBouncer listens on port 6432 by default. Credentials come from the file named in auth_file, conventionally /etc/pgbouncer/userlist.txt. Use auth_type = scram-sha-256; md5 remains supported for legacy setups, and when md5 is configured against a role that holds a SCRAM secret, PgBouncer negotiates SCRAM automatically.

3. What are PgBouncer’s pooling modes?

Session (default): Holds connection per client. Transaction: Releases after each transaction. Statement: Releases after each query.

4. Does PgBouncer improve performance?

Yes. It reduces connection overhead, handles spikes better, and limits PostgreSQL load. Great for high-concurrency apps.