PgBouncer is a lightweight connection pooler that sits between your applications and PostgreSQL. Each PostgreSQL connection is a separate backend process that costs memory, so applications that open hundreds of short-lived connections waste resources; PgBouncer keeps a small set of server connections open and hands them out to thousands of clients. In this tutorial you will install PgBouncer on Ubuntu 24.04, configure transaction pooling with SCRAM authentication, enable protocol-level prepared statements, add TLS for remote clients, and monitor the pools from the admin console.

Prerequisites

To follow this guide you need:

  • A server running Ubuntu 24.04 LTS with PostgreSQL 16 installed from the Ubuntu repositories (sudo apt install postgresql), for example a CubePath VPS.
  • A non-root user with sudo privileges.
  • Basic familiarity with psql.

This guide runs PgBouncer on the same server as PostgreSQL, which is the most common layout. Ubuntu 24.04 ships PgBouncer 1.22, which includes every feature used here.

Step 1 - Installing PgBouncer

Install the package from the Ubuntu repositories:

sudo apt update
sudo apt install -y pgbouncer

The package runs PgBouncer as the postgres system user, reads its configuration from /etc/pgbouncer/pgbouncer.ini and listens on 127.0.0.1:6432 by default. Check the version and service:

pgbouncer --version
systemctl status pgbouncer --no-pager

PgBouncer 1.22.0
...
     Active: active (running) since Thu 2026-09-25 10:02:11 UTC; 8s ago

Keep a copy of the original configuration file, which documents every option in its comments:

sudo cp /etc/pgbouncer/pgbouncer.ini /etc/pgbouncer/pgbouncer.ini.orig

Step 2 - Creating a database and roles

Create an application role and database to pool connections for, plus a role for the PgBouncer admin console. Replace both passwords with strong values of your own:

sudo -u postgres psql

CREATE ROLE appuser LOGIN PASSWORD 'your_app_password';
CREATE DATABASE appdb OWNER appuser;
CREATE ROLE pgbouncer_admin LOGIN PASSWORD 'your_admin_password';
\q

PostgreSQL 16 stores these passwords as SCRAM-SHA-256 hashes (password_encryption = scram-sha-256 is the default), and the default pg_hba.conf on Ubuntu requires scram-sha-256 for TCP connections from 127.0.0.1.

Step 3 - Configuring SCRAM authentication

PgBouncer authenticates clients itself before handing them a server connection. The simplest secure method is to copy the SCRAM secrets from PostgreSQL into PgBouncer's auth_file. When a client logs in with SCRAM and PgBouncer holds the SCRAM secret, PgBouncer can also use it to log in to PostgreSQL, so no plain-text password is stored anywhere.

Export the secrets in the "user" "secret" format that userlist.txt expects:

sudo -u postgres psql -Atc \
  "SELECT format('\"%s\" \"%s\"', rolname, rolpassword) FROM pg_authid WHERE rolname IN ('appuser','pgbouncer_admin');" \
  | sudo tee /etc/pgbouncer/userlist.txt > /dev/null

Restrict the file to the postgres user and check its content:

sudo chown postgres:postgres /etc/pgbouncer/userlist.txt
sudo chmod 640 /etc/pgbouncer/userlist.txt
sudo cat /etc/pgbouncer/userlist.txt

"appuser" "SCRAM-SHA-256$4096:mRd...==$Lq...=:x9...="
"pgbouncer_admin" "SCRAM-SHA-256$4096:Yc2...==$Hk...=:Qp...="

Whenever you change a role's password in PostgreSQL, run the export again and reload PgBouncer.

Step 4 - Choosing a pool mode

The pool mode decides when a server connection returns to the pool:

ModeServer connection releasedUse it when
sessionWhen the client disconnectsThe application relies on session state (SET, LISTEN, temporary tables, session advisory locks)
transactionWhen each transaction endsWeb applications and APIs with many short transactions; the usual choice
statementAfter every statementOnly autocommit workloads; multi-statement transactions are rejected

Transaction mode gives the best connection reuse, but consecutive transactions from one client can run on different server connections. Anything that must survive between transactions breaks: session-level SET (use SET LOCAL inside the transaction), LISTEN/NOTIFY listeners, WITH HOLD cursors, session advisory locks and temporary tables used across transactions. If part of your application needs these, point it at a separate PgBouncer database entry with pool_mode=session.

Step 5 - Writing the PgBouncer configuration

Open the configuration file:

sudo nano /etc/pgbouncer/pgbouncer.ini

Replace its content with the following:

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

[pgbouncer]
listen_addr = 127.0.0.1
listen_port = 6432
unix_socket_dir = /var/run/postgresql
logfile = /var/log/postgresql/pgbouncer.log
pidfile = /var/run/postgresql/pgbouncer.pid

; Authentication
auth_type = scram-sha-256
auth_file = /etc/pgbouncer/userlist.txt
admin_users = pgbouncer_admin
stats_users = pgbouncer_admin

; Pooling
pool_mode = transaction
max_client_conn = 500
default_pool_size = 20
min_pool_size = 5
reserve_pool_size = 5
reserve_pool_timeout = 3
max_db_connections = 50

; Timeouts (seconds)
server_idle_timeout = 300
server_lifetime = 3600
query_wait_timeout = 120

; Prepared statements in transaction mode
max_prepared_statements = 200

; Parameters some drivers send that PgBouncer cannot track
ignore_startup_parameters = extra_float_digits

The key settings are:

  • default_pool_size: server connections per database and user pair. Start around two to four times the number of CPU cores of the database server.
  • max_db_connections: a hard cap on server connections to one database across all users. Keep the sum of your pools below PostgreSQL's max_connections (100 by default), leaving room for superuser and maintenance sessions.
  • max_client_conn: how many clients PgBouncer accepts. Clients above the pool size wait in a queue instead of failing.
  • reserve_pool_size and reserve_pool_timeout: extra connections added when a client has waited more than 3 seconds, to absorb short bursts.
  • query_wait_timeout: a waiting client gets an error after 120 seconds rather than hanging forever.
  • max_prepared_statements: see the next step.

Restart PgBouncer to apply the new configuration:

sudo systemctl restart pgbouncer
sudo systemctl status pgbouncer --no-pager

Connect through PgBouncer on port 6432 and enter the application password:

psql -h 127.0.0.1 -p 6432 -U appuser appdb -c "SELECT current_user, inet_server_port();"

 current_user | inet_server_port
--------------+------------------
 appuser      |             5432
(1 row)

The query reports port 5432 because it runs on the PostgreSQL backend that PgBouncer assigned. If the connection fails, check sudo tail -n 20 /var/log/postgresql/pgbouncer.log.

Step 6 - Enabling prepared statements in transaction mode

Drivers such as JDBC, asyncpg, pgx and psycopg 3 use protocol-level prepared statements. Before PgBouncer 1.21, those broke in transaction mode because a statement prepared on one server connection was missing on the next one. With max_prepared_statements above zero, PgBouncer tracks the prepared statements of each client and prepares them transparently on whichever server connection it uses. The value is the number of statements cached per server connection.

Test it with pgbench in prepared mode. First create the test tables directly on PostgreSQL:

pgbench -h 127.0.0.1 -p 5432 -U appuser -i -s 10 appdb

Then run a 30-second benchmark with 50 clients through PgBouncer, using prepared statements:

pgbench -h 127.0.0.1 -p 6432 -U appuser -c 50 -j 2 -T 30 -M prepared appdb

...
number of failed transactions: 0 (0.000%)
latency average = 12.874 ms
tps = 3882.310448 (without initial connection time)

Fifty clients ran with no errors while PgBouncer used at most 20 server connections. With max_prepared_statements = 0 the same run fails with prepared statement "P_1" does not exist.

Step 7 - Adding TLS for remote clients

If application servers connect over the network, encrypt client traffic with TLS. The example uses Ubuntu's self-signed "snakeoil" certificate, which the postgres user can already read through the ssl-cert group. For production, use a certificate from your internal CA or a public CA and point the same options at it.

Edit the configuration:

sudo nano /etc/pgbouncer/pgbouncer.ini

Change listen_addr and add the TLS options in the [pgbouncer] section:

listen_addr = 0.0.0.0

client_tls_sslmode = require
client_tls_cert_file = /etc/ssl/certs/ssl-cert-snakeoil.pem
client_tls_key_file = /etc/ssl/private/ssl-cert-snakeoil.key

With client_tls_sslmode = require, PgBouncer refuses unencrypted client connections. Restart the service and open the port only to your application servers, replacing app_server_ip:

sudo systemctl restart pgbouncer
sudo ufw allow from app_server_ip to any port 6432 proto tcp

From the application server, connect with TLS and check the connection details:

psql "host=your_server_ip port=6432 dbname=appdb user=appuser sslmode=require" -c "\conninfo"

You are connected to database "appdb" as user "appuser" on host "your_server_ip" at port "6432".
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, compression: off)

The connection between PgBouncer and PostgreSQL stays on 127.0.0.1, so it does not leave the server. PostgreSQL itself does not need to listen on a public interface.

Step 8 - Monitoring pools from the admin console

PgBouncer exposes a virtual database named pgbouncer for monitoring and control. Connect as the admin user:

psql -h 127.0.0.1 -p 6432 -U pgbouncer_admin pgbouncer

SHOW POOLS is the most useful command. Run it while the pgbench test from Step 6 is running:

SHOW POOLS;

 database |      user       | cl_active | cl_waiting | sv_active | sv_idle | sv_used | maxwait | pool_mode
----------+-----------------+-----------+------------+-----------+---------+---------+---------+-------------
 appdb    | appuser         |        50 |          0 |        20 |       0 |       0 |       0 | transaction
 pgbouncer| pgbouncer       |         1 |          0 |         0 |       0 |       0 |       0 | statement

(Some columns are omitted here.) Read it this way:

  • cl_active: clients linked to a server connection or idle without waiting.
  • cl_waiting: clients queued for a server connection. Occasional values are normal; a sustained number means the pool is too small or queries are too slow.
  • sv_active and sv_idle: server connections in use and ready in the pool.
  • maxwait: how long, in seconds, the oldest waiting client has waited.

Other useful commands:

SHOW STATS;
SHOW CLIENTS;
SHOW SERVERS;
SHOW CONFIG;

SHOW STATS reports totals and averages per database, such as transactions per second and average query time. The console also controls the running process: RELOAD; rereads the configuration and userlist.txt without dropping connections, PAUSE appdb; waits for current transactions to finish and holds new ones (useful during a PostgreSQL restart), and RESUME appdb; releases them.

From the shell, sudo systemctl reload pgbouncer has the same effect as RELOAD.

Troubleshooting

password authentication failed through PgBouncer but not directly: the secret in userlist.txt is outdated or the file has a formatting error. Export it again as in Step 3 and reload PgBouncer.

no such database: appdb: the client asked for a database that is not listed in [databases]. Add it, or add a fallback line * = host=127.0.0.1 port=5432 to route any database name.

unsupported startup parameter: ...: a driver sends a startup parameter PgBouncer does not track. Add its name to ignore_startup_parameters.

Clients wait and time out with query_wait_timeout: the pool is saturated. Look for long-running transactions with SELECT pid, state, now() - xact_start AS age, query FROM pg_stat_activity ORDER BY age DESC NULLS LAST; on PostgreSQL before raising default_pool_size; idle-in-transaction sessions keep server connections busy in transaction mode.

sorry, too many clients already from PostgreSQL: the sum of pools exceeds max_connections. Lower default_pool_size or max_db_connections.

Conclusion

PgBouncer now pools connections to PostgreSQL in transaction mode, authenticates clients with SCRAM without storing plain-text passwords, supports prepared statements from modern drivers, encrypts remote client traffic and reports its state through the admin console. As next steps, feed the SHOW POOLS and SHOW STATS numbers into your monitoring system, size the pools from real load tests, and consider running PgBouncer on each application server or behind a load balancer if a single pooler becomes a point of failure.