How to configure connection pooling for PostgreSQL

Connection pooling improves PostgreSQL performance by keeping a large number of application connections from turning into an equally large number of database backend processes. It reduces connection setup overhead, limits memory growth from idle sessions, and smooths latency spikes in workloads that create frequent short-lived connections.

Each client connection to PostgreSQL typically maps to a dedicated backend process, with its own memory and session state. A pooler such as PgBouncer sits between the application and the database, accepts many client connections, and multiplexes them onto a controlled number of server connections that stay warm.

Pooling changes the semantics of session state depending on pooling mode, so application features that assume a stable session can break under aggressive modes. Pool sizing also needs to account for the fact that PgBouncer creates server connections per pool (database/user), which can exceed PostgreSQL limits if configured loosely. Exposing the pooler beyond a trusted network requires concrete controls (private bind address, firewall rules, TLS), since the pooler port effectively becomes the application’s front door to the database.

1. Choose a pool_mode that matches application session behavior.

   session keeps a server connection per client session, transaction assigns a server connection per transaction, and statement assigns a server connection per statement.

   transaction or statement pooling can break session-dependent features such as prepared statements, temporary tables, advisory locks held across transactions, per-session SET variables, and long-lived LISTEN/NOTIFY usage.

2. Edit /etc/pgbouncer/pgbouncer.ini to define database mapping, listen endpoint, authentication, and pool limits.

   [databases]
   appdb = host=127.0.0.1 port=5432 dbname=appdb
   
   [pgbouncer]
   listen_addr = 0.0.0.0
   listen_port = 6432
   
   auth_type = scram-sha-256
   auth_file = /etc/pgbouncer/userlist.txt
   
   pool_mode = transaction
   max_client_conn = 1000
   default_pool_size = 50
   
   admin_users = pgbouncer_admin
   stats_users = pgbouncer_admin

   Set auth_type to match the password hash format used for pooled roles (scram-sha-256 or md5).

   Binding to 0.0.0.0 exposes port 6432 on every interface, so use a private address plus firewall rules when the pooler is reachable from application hosts.

3. Generate /etc/pgbouncer/userlist.txt from PostgreSQL role password hashes used through the pool.

   $ sudo -u postgres psql -tAc "SELECT '\"' || rolname || '\" \"' || rolpassword || '\"' FROM pg_authid WHERE rolname IN ('appuser','pgbouncer_admin');" | sudo tee /etc/pgbouncer/userlist.txt >/dev/null
   $ sudo cat /etc/pgbouncer/userlist.txt
   "appuser" "SCRAM-SHA-256$4096:n8QrGf5CG22YOfFXb97NQg==$LrKiFFibmOvJVcBNcoYwv2UO70DsS1EfxNU3ijpYbR8=:arcoKV0Hq2h6My4l/Gxg7TS8gMYd7QOxaRSSKtNPVuM="
   "pgbouncer_admin" "SCRAM-SHA-256$4096:5Dkf0be3V2zoGbahVbBL2Q==$Xt5KzbZ6ZLcbJ+JoZXDd5J9SlOaXCC0jehU23q9RtFQ=:m4mYgMyvrOMQ0ZmQarcxUaPeUiO/TBOoo80XwDLThmQ="

   The /etc/pgbouncer/userlist.txt file contains password hashes, so treat it as a secret and prevent reads by non-privileged users.

4. Restrict /etc/pgbouncer/userlist.txt ownership and permissions to the pgbouncer service account.

   $ sudo chown pgbouncer:pgbouncer /etc/pgbouncer/userlist.txt
   $ sudo chmod 640 /etc/pgbouncer/userlist.txt

5. Restart the pgbouncer service to apply configuration changes.

   $ sudo systemctl restart pgbouncer

6. Confirm the pgbouncer service is active with an open listener on port 6432.

   $ sudo ss -lntp | grep ':6432'
   LISTEN 0      128          0.0.0.0:6432      0.0.0.0:*    users:(("pgbouncer",pid=1,fd=7))

7. Test connectivity to PostgreSQL through PgBouncer.

   $ psql "host=127.0.0.1 port=6432 dbname=appdb user=appuser" -c "SELECT 1;"
    ?column? 
   ----------
           1
   (1 row)

   Application connection strings should use the pooler host and port (6432 in this example) instead of the PostgreSQL port (5432).

8. Check pool utilization from the PgBouncer admin database.

   $ psql "host=127.0.0.1 port=6432 dbname=pgbouncer user=pgbouncer_admin" -c "SHOW POOLS;"
    database  |   user    | cl_active | cl_waiting | cl_active_cancel_req | cl_waiting_cancel_req | sv_active | sv_active_cancel | sv_being_canceled | sv_idle | sv_used | sv_tested | sv_login | maxwait | maxwait_us |  pool_mode  | load_balance_hosts 
   -----------+-----------+-----------+------------+----------------------+-----------------------+-----------+------------------+-------------------+---------+---------+-----------+----------+---------+------------+-------------+--------------------
    appdb     | appuser   |         0 |          0 |                    0 |                     0 |         0 |                0 |                 0 |       1 |       0 |         0 |        0 |       0 |          0 | transaction | 
    pgbouncer | pgbouncer |         1 |          0 |                    0 |                     0 |         0 |                0 |                 0 |       0 |       0 |         0 |        0 |       0 |          0 | statement   | 
   (2 rows)

9. Adjust pool sizes to stay within PostgreSQL connection limits.

   default_pool_size is the maximum number of server connections per pool, and each unique (database, user) pair creates a separate pool, so total server connections can grow beyond a single application’s expectation.

   Related: How to set connection limits in PostgreSQL
   Related: How to monitor PostgreSQL performance metrics