# PgBouncer Setup: Connection Pooling Modes and Config for PostgreSQL

> Install and configure PgBouncer for PostgreSQL, choose between session, transaction and statement pooling, size the pools, and monitor them from the admin console.

Source: https://www.epifive.com/blog/postgresql-connection-pooling-pgbouncer-setup · Published 2026-10-25 · EpiFive

## Key takeaways

- PgBouncer sits between applications and PostgreSQL, reuses a small number of server connections, and listens on port 6432 by default.
- The default pool_mode is session; transaction mode releases the server connection after each transaction and allows many more clients per server connection.
- Transaction pooling does not support SET/RESET, LISTEN, WITH HOLD cursors, SQL-level PREPARE and DEALLOCATE, or session-level advisory locks.
- default_pool_size (default 20) applies per user and database pair, so total server connections can exceed it; use max_db_connections to cap a database.
- Use SHOW POOLS in the admin console to watch cl_waiting and maxwait before and after you change pool sizes.

PgBouncer is a lightweight connection pooler for PostgreSQL. Applications connect to PgBouncer, usually on port 6432, and PgBouncer reuses a small set of real server connections to the database. The key choice is the pool mode: session pooling (the default) keeps a server connection for the life of the client, transaction pooling hands it back after every transaction, and statement pooling hands it back after every query. This guide installs PgBouncer, writes a working configuration, and shows how to choose and test the mode.

## Who this is for

This is for administrators and developers running PostgreSQL on Linux whose application opens many connections, who see errors such as "too many clients already", or who want to cap load on the database server. By the end you will have a running PgBouncer with authentication configured, a pool mode chosen on evidence, and a way to watch the pools.

If you are currently seeing connection exhaustion, read [the too many clients error fix](https://www.epifive.com/blog/postgresql-too-many-clients-error-fix) first, because a pooler is one of several remedies.

## Why pool connections

Each PostgreSQL connection is a server process with its own memory, so thousands of mostly idle application connections are wasteful. A pooler accepts many client connections and multiplexes them onto fewer server connections. Applications that open and close a connection per request also benefit, because connecting to PgBouncer is cheaper than connecting to PostgreSQL each time.

## The three pool modes

PgBouncer's configuration reference defines them as follows.

| Mode | The server connection is released | Typical fit |
|---|---|---|
| session | After the client disconnects (the default) | Applications that use session features; low risk |
| transaction | After the transaction finishes | Web applications with short transactions and many clients |
| statement | After the query finishes; multi-statement transactions are disallowed | Special cases with autocommit-only workloads |

### What breaks in transaction pooling

Because consecutive transactions from one client may run on different server connections, anything that stores state in the session is unreliable. PgBouncer's feature table marks these as never supported in transaction pooling:

- `SET` and `RESET`
- `LISTEN`
- `WITH HOLD` cursors
- SQL-level `PREPARE` and `DEALLOCATE`
- Temp tables with `PRESERVE` or `DELETE ROWS`
- The `LOAD` statement
- Session-level advisory locks

Two related points. Protocol-level prepared statements work in transaction pooling only when `max_prepared_statements` is set to a non-zero value; the default is 200, and 0 disables the support. And common startup parameters such as `client_encoding`, `DateStyle`, `TimeZone` and `application_name` stay consistent for the client because PgBouncer tracks their changes.

Review your application and driver for these features before you switch. Frameworks that run `SET search_path` or take session advisory locks need session mode or a code change. If you need `LISTEN` for notifications, connect those clients directly to PostgreSQL.

## Prerequisites

- A PostgreSQL server you can reach, and a role with permission to create roles and functions if you use `auth_query`.
- A Linux host for PgBouncer. It can share the database host or run on its own host or on the application servers.
- Root or sudo access.
- A decision on where it listens, and firewall rules that allow only your application hosts to reach port 6432.

## Step 1: Install PgBouncer

Install the `pgbouncer` package from your distribution or from the PostgreSQL project's package repositories, if available for your system. To build from source, PgBouncer's installation page lists these requirements: Libevent 2.0 or later, pkg-config, OpenSSL 1.0.1 or later for TLS support and a C11-capable compiler, plus GNU Make 3.81 or later for Autoconf builds. Autoconf is the recommended build for production builds and packaging.

```bash
./configure --prefix=/usr/local
make
make install
```

If you build from Git, run `./autogen.sh` first, which needs autoconf, automake, libtool and pandoc. The documentation notes that with Autoconf, optional features such as systemd support must be enabled with flags like `--with-systemd`, and that example unit files exist in the source tree as `etc/pgbouncer.service` and `etc/pgbouncer.socket`.

## Step 2: Create the configuration

The configuration file, commonly `pgbouncer.ini`, has a `[databases]` section and a `[pgbouncer]` section.

```ini
[databases]
appdb = host=10.0.0.10 port=5432 dbname=appdb

[pgbouncer]
listen_addr = 10.0.0.20
listen_port = 6432
auth_type = scram-sha-256
auth_file = /etc/pgbouncer/userlist.txt
pool_mode = transaction
max_client_conn = 500
default_pool_size = 20
admin_users = pgbouncer_admin
logfile = /var/log/pgbouncer/pgbouncer.log
pidfile = /var/run/pgbouncer/pgbouncer.pid
```

What each line does, with the documented defaults:

| Setting | Meaning | Default |
|---|---|---|
| `[databases]` entries | `name = connection string`; a `*` entry is a fallback for names not listed | none |
| `listen_addr` | Comma-separated addresses to listen on for TCP; unset means Unix sockets only | not set |
| `listen_port` | Port for TCP and Unix sockets | 6432 |
| `auth_type` | How clients authenticate | md5 |
| `auth_file` | File of user names and passwords | not set |
| `pool_mode` | session, transaction or statement | session |
| `max_client_conn` | Maximum client connections accepted | 100 |
| `default_pool_size` | Server connections per user and database pair | 20 |
| `admin_users` | Users allowed to run all console commands | empty |

The addresses, database name and user names above are placeholders. Replace them with yours.

## Step 3: Set up authentication

PgBouncer supports several `auth_type` values, including `md5` (the default), `scram-sha-256`, `cert`, `hba`, `ldap` and `pam`. With `scram-sha-256`, the `auth_file` must contain SCRAM secrets or plain-text passwords.

### The auth file

Each line of `auth_file` starts with two double-quoted fields: the user name and then the password, which can be plain text, an MD5 hash or a SCRAM secret. PgBouncer ignores the rest of the line.

```text
"appuser" "SCRAM-SHA-256$4096:<salt>$<StoredKey>:<ServerKey>"
"pgbouncer_admin" "SCRAM-SHA-256$4096:<salt>$<StoredKey>:<ServerKey>"
```

You can copy the stored secret for a role from `pg_authid` as a superuser. Keep the file readable only by the account PgBouncer runs as, because it holds credentials.

### Look users up with auth_query

Maintaining a file by hand does not scale. With `auth_user`, PgBouncer looks up any user not found in the file by running `auth_query` as that user. The documented default query selects `rolname` and `rolpassword` from `pg_authid`, which needs elevated rights. The documentation therefore recommends a `SECURITY DEFINER` function instead of direct access to `pg_authid`. Design it so it exposes only the one user's password and is executable only by the lookup role. The `auth_user`'s own password is read from `auth_file`, unless that role needs no password.

## Step 4: Run it

Run PgBouncer as a service. If you use the packaged systemd unit, enable and start it. To run it by hand, the synopsis is:

```bash
pgbouncer -d /etc/pgbouncer/pgbouncer.ini
```

The `-d` option runs in the background. The documentation says that in daemon mode you must set `pidfile` as well as `logfile` or `syslog`. The `-u` option switches to a given user at start, and `-v` increases verbosity.

Connect your application by changing only the host and port in its connection string, for example host `10.0.0.20` and port `6432`, with the same database name and credentials.

```bash
psql "host=10.0.0.20 port=6432 dbname=appdb user=appuser"
```

## Step 5: Size the pools

Three numbers interact.

- `max_client_conn` is how many clients PgBouncer accepts. It can be much larger than the pool.
- `default_pool_size` is the server connections per user and database pair. If two roles use one database, up to twice this number of server connections can exist.
- `max_db_connections` caps server connections per database regardless of user; the default is 0, meaning unlimited.

Make sure the sum across all pools, plus any direct connections from other tools, stays below PostgreSQL's `max_connections` with headroom for administrators. Other settings that matter:

| Setting | Default | Effect |
|---|---|---|
| `min_pool_size` | 0 | Keeps a minimum of server connections warm |
| `reserve_pool_size` | 0 | Extra connections allowed when clients wait, governed by `reserve_pool_timeout` |
| `server_idle_timeout` | 600 seconds | Closes server connections idle longer than this |
| `query_wait_timeout` | 120 seconds | Disconnects a client whose query is not assigned a server in time |

Start with a modest `default_pool_size`, load test, and watch `cl_waiting` rather than guessing. A larger pool is not always faster, because the database has finite CPU and disk.

## Step 6: Monitor with the admin console

Connect to the virtual database named `pgbouncer`:

```bash
psql -h 10.0.0.20 -p 6432 -U pgbouncer_admin pgbouncer
```

Only users in `admin_users` or `stats_users` can log in. The console supports only the simple query protocol, so some drivers that use the extended protocol will not work.

```sql
SHOW POOLS;
SHOW STATS;
SHOW CLIENTS;
SHOW CONFIG;
```

In `SHOW POOLS`, one row represents a database and user pair. Read these columns:

- `cl_active`: clients linked to server connections, or idle with no queued queries.
- `cl_waiting`: clients that have sent queries but have not yet got a server connection.
- `sv_active`: server connections linked to a client.
- `sv_idle`: server connections that are unused and immediately usable.
- `maxwait`: seconds the oldest queued client has waited. A steadily rising value suggests the pool cannot keep up.

`RELOAD` reloads the configuration and auth files and updates changeable settings, without dropping clients. `PAUSE` disconnects server connections once they are released and does not return until all have disconnected, and `RESUME` continues. `SHOW CONFIG` lists each setting's value and whether it is changeable at runtime.

## Troubleshooting

### Clients wait or time out

`cl_waiting` above zero and a growing `maxwait` mean demand exceeds the pool. Check for slow or idle-in-transaction sessions holding server connections, raise `default_pool_size` within PostgreSQL's limits, or fix the slow queries. Clients whose query is not assigned a server within `query_wait_timeout` are disconnected.

### Unsupported startup parameter

By default PgBouncer rejects startup parameters it cannot track. If a driver sends one, either stop sending it or list it in `ignore_startup_parameters`, which accepts and ignores the parameter. Only ignore parameters whose loss is harmless.

### Application errors only in transaction mode

Look for session state: `SET` statements, advisory locks, `LISTEN`, SQL-level `PREPARE`. Use session mode for those clients, or change the code. For driver-level prepared statements, check that `max_prepared_statements` is non-zero.

### The console refuses a connection

Confirm the user appears in `admin_users` or `stats_users`, and that you connect to the database named `pgbouncer` on the PgBouncer port, not on 5432.

### Cannot connect from another host

If `listen_addr` is unset, PgBouncer accepts only Unix socket connections. Set it to the address to listen on and open the firewall for port 6432 from the application hosts only.

## Checklist

- Application reviewed for session-level features before choosing transaction mode.
- `listen_addr`, `listen_port` and firewall rules set deliberately.
- `auth_type` set to `scram-sha-256` where your roles use SCRAM, with a restricted auth file or a `SECURITY DEFINER` lookup.
- Pool sizes chosen so the total stays under PostgreSQL's connection limit.
- Admin users limited to administrators.
- `SHOW POOLS` reviewed under load, with a baseline recorded.
- Logging and pid file configured, and the service enabled at boot.

## References

- PgBouncer configuration: https://www.pgbouncer.org/config.html
- PgBouncer features (SQL feature map for pooling modes): https://www.pgbouncer.org/features.html
- PgBouncer usage, admin console and command-line options: https://www.pgbouncer.org/usage.html
- PgBouncer installation: https://www.pgbouncer.org/install.html

## Frequently asked questions

### Which PgBouncer pool mode should I use?

Session pooling is the default and the safest, because a client keeps one server connection until it disconnects. Transaction pooling releases the server connection when each transaction ends and supports far more clients, but it does not support session features such as SET, LISTEN, SQL-level PREPARE or session-level advisory locks.

### What port does PgBouncer listen on?

The default listen_port is 6432. By default listen_addr is not set, so PgBouncer accepts only Unix socket connections until you set listen_addr to the addresses it should listen on.

### Does PgBouncer support prepared statements in transaction mode?

Protocol-level prepared statements work in transaction and statement pooling when max_prepared_statements is set to a non-zero value. The default is 200. SQL-level PREPARE and DEALLOCATE statements are not supported in transaction pooling.

### How do I see whether clients are waiting for a connection?

Connect to the pgbouncer admin database and run SHOW POOLS. A non-zero cl_waiting means clients have sent queries but have no server connection yet, and a steadily rising maxwait suggests the pool cannot keep up.