# Postgres Connection Pooling: PgBouncer in Front of RDS

URL: https://www.devopsness.com/blog/postgres-connection-pooling-pgbouncer
Author: Kiril Urbonas
Published: 2026-04-30
Updated: 2026-10-31
Categories: Infrastructure
Tags: Postgres, Pgbouncer, Database, Performance, Infrastructure

Summary: Why Postgres connection limits bite at unexpected times, the pooling layer we put in front, and the pool-mode tradeoffs we learned the hard way.

---
# Postgres Connection Pooling: PgBouncer in Front of RDS

When RDS Postgres starts rejecting connections while CPU and memory look fine, put PgBouncer in front of it in `pool_mode = transaction`. Clients connect to PgBouncer on port 6432; it multiplexes them onto a small pool of real server connections, so 1,000 idle app connections cost the database a few dozen backends instead of 1,000.

> [!VERIFIED]
> PgBouncer 1.26.1 (released 2026-10-14) and PostgreSQL 18.6, against the pgbouncer.org configuration, features, usage and changelog pages, the PostgreSQL connection settings docs, and the Amazon RDS quotas and RDS Proxy pricing pages. Checked October 2026.

The symptom is a database with headroom on every metric that still refuses new sessions. That's the signal you've outgrown direct app-to-Postgres connections. This is the setup we run, the pool-mode tradeoffs, and the metrics that tell you when the pool itself is the bottleneck.

## Why Postgres runs out of connections

Postgres is process-per-connection: every client connection is a backend process with its own memory. `max_connections` caps them. Upstream PostgreSQL defaults it to "typically 100", and RDS for PostgreSQL derives it from instance memory with `LEAST({DBInstanceClassMemory/9531392}, 5000)`, so a small instance gets far fewer slots than people assume.

The math gets bad fast. A client-side pool of 50 per app instance, times 8 instances, times 3 services on the same database, is 1,200 potential connections. Most sit idle, but an idle connection holds a slot as long as it's open. When the slots run out, new connections fail with one of these:

```
FATAL:  sorry, too many clients already
FATAL:  remaining connection slots are reserved for roles with the SUPERUSER attribute
```

(The second message's exact wording varies across Postgres versions.) You can raise `max_connections`, and RDS lets you, but every backend costs memory, and AWS's own quota page warns that setting it too high can push the instance into an `incompatible-parameters` state. A pooler fixes the shape of the problem instead of the ceiling.

## A working pgbouncer.ini

This is a minimal config for one RDS database, with every non-default setting explained:

```ini
# /etc/pgbouncer/pgbouncer.ini
[databases]
app = host=mydb.abc123.us-east-1.rds.amazonaws.com port=5432 dbname=app

[pgbouncer]
listen_addr = 0.0.0.0
listen_port = 6432
auth_type = scram-sha-256
auth_file = /etc/pgbouncer/userlist.txt
pool_mode = transaction          ; default is session, which pools nothing useful
default_pool_size = 8            ; default 20; start near 2x the DB's vCPUs
reserve_pool_size = 4            ; extra slots once a client waits reserve_pool_timeout
max_client_conn = 2000           ; default 100; raise the OS fd limit to match
max_prepared_statements = 200    ; default since 1.24; 0 disables
server_tls_sslmode = require
admin_users = pgbouncer_admin
```

Start it, then point one client at it and confirm queries flow:

```
$ pgbouncer -d /etc/pgbouncer/pgbouncer.ini
$ psql "host=127.0.0.1 port=6432 dbname=app user=app_user" -c "select 1"
 ?column?
----------
        1
```

## The pool-mode decision

PgBouncer has three modes. **Session** dedicates a server connection to a client until it disconnects: identical semantics, almost no savings. **Transaction** holds a server connection only for the duration of a transaction, then returns it, which is where real pooling happens. **Statement** returns it after every statement and disallows multi-statement transactions, which is too restrictive for most apps.

We use transaction mode for almost everything. The cost is that session state stops being yours. PgBouncer's feature matrix lists what never works in transaction pooling: `SET`/`RESET`, `LISTEN`, `WITH HOLD` cursors, SQL-level `PREPARE`/`DEALLOCATE`, temp tables with `PRESERVE` or `DELETE ROWS`, `LOAD`, and session-level advisory locks. What does work: `NOTIFY`, `ON COMMIT DROP` temp tables, and protocol-level prepared statements (the kind drivers use), which PgBouncer has supported since 1.21 and enables by default since 1.24.

The practical rules:

- Use `SET LOCAL` inside a transaction, not plain `SET`. A plain `SET` sticks to whichever server connection ran it and leaks into the next client.
- Set `application_name` as a connection-string parameter, not with `SET`. PgBouncer tracks it as a startup parameter by default (along with `client_encoding`, `DateStyle`, `IntervalStyle`, `TimeZone` and `standard_conforming_strings`).
- Move `LISTEN` consumers to a separate session-mode pool or a direct connection.
- Use `pg_advisory_xact_lock`, not `pg_advisory_lock`.

## Sizing the pool

`default_pool_size` is the number of server connections per database and user pair. Our starting point is twice the database's vCPU count: 8 for a 4 vCPU instance. It sounds tiny and usually works, because CPU-bound queries past roughly that concurrency just queue inside Postgres, and queueing at PgBouncer is cheaper and observable. I/O-heavy workloads with lots of waiting tolerate more; we run one 4 vCPU database at 20. Treat 2x as a starting heuristic and tune against `cl_waiting` and query latency.

Remember the multiplication: total server connections are pool size times database/user pairs times PgBouncer instances. Three PgBouncer replicas at pool size 20 against one user is 60 backends, which must fit under `max_connections`.

## Topology: where to run it

- **Sidecar per app pod.** Simplest, no network hop, but every pod has its own pool, so fan-in to the database barely improves.
- **Central PgBouncer deployment**, two or three replicas per AZ behind a service. Real pooling across all pods for one extra same-AZ hop. This is our default.
- **RDS Proxy.** Managed, AWS handles HA and patching. It bills per vCPU-hour of the underlying instance (per ACU-hour for Aurora Serverless), and its pinning behavior has its own session-state rules worth reading before you migrate.

## Metrics that matter

Connect to the admin console with `psql -p 6432 -U pgbouncer_admin pgbouncer` and run `SHOW POOLS;`. The columns to watch, per the PgBouncer docs:

- `cl_waiting`: clients that sent a query but have no server yet. The alert signal.
- `maxwait`: seconds the oldest waiting client has queued. A steadily rising value means the pool can't keep up.
- `sv_active`, `sv_idle`, `sv_used`: server connections in use, ready, and needing a check query before reuse.

The prometheus-community `pgbouncer_exporter` serves these on port 9127 as `pgbouncer_pools_client_waiting_connections` and `pgbouncer_pools_client_maxwait_seconds`. Our alerts: waiting clients above 10 for 30 seconds, max wait above 0.2 seconds for a minute, and PgBouncer restart loops. The usual cause when they fire is one slow report query holding several server slots; kill it and the pool drains.

## Errors you'll hit, and the fix

| Error or symptom | Cause | Fix |
|---|---|---|
| `prepared statement "S_1" already exists` / `does not exist` | Driver prepared statements on an old PgBouncer or `max_prepared_statements = 0` | Upgrade to 1.21+ and keep it non-zero, or disable driver-side statement caching |
| `no more connections allowed (max_client_conn)` | Client cap at the default 100 | Raise `max_client_conn` and the fd limit |
| Settings from `SET` appear in other sessions | Plain `SET` in transaction mode | `SET LOCAL`, or startup parameters |
| `LISTEN` never receives notifications | `LISTEN` is unsupported in transaction mode | Session-mode pool or direct connection |
| Clients hang right after an RDS failover | Server connections still point at the old primary | Run `RECONNECT` on the admin console; keep `dns_max_ttl` low (default 15s) |

The failover case is the one that bites in production. RDS Multi-AZ failover flips the DNS record to the standby, and existing server connections are dead. `RECONNECT` closes them as they're released so new ones resolve the new address. Practicing exactly this is covered in [Postgres replication lag and failover practice](/blog/postgres-replication-lag-failover-practice).

## Where this breaks

Transaction pooling is wrong for workloads built on session state: long-lived `LISTEN` consumers, apps that lean on session advisory locks, or ORMs that issue `SET` at connect time and can't be changed. PgBouncer also doesn't route reads to replicas by itself; for that, see [routing reads to Postgres replicas](/blog/postgres-read-replicas-routing-reads). And a pooler won't save a database that's genuinely out of CPU. If `cl_waiting` stays high with `sv_active` pinned at pool size, fix the slow queries before you add connections.

## Questions people ask

### What is the difference between session and transaction pooling in PgBouncer?

Session pooling assigns a server connection to a client until it disconnects, so every Postgres feature works but you save few connections. Transaction pooling returns the server connection after each transaction, letting one backend serve many clients. The cost is session state: `SET`, `LISTEN`, `WITH HOLD` cursors, SQL `PREPARE` and session advisory locks don't work in transaction mode.

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

Yes, protocol-level prepared statements, the kind database drivers send, are supported since PgBouncer 1.21 (October 2023). Since 1.24, `max_prepared_statements` defaults to 200, so support is on out of the box. SQL-level `PREPARE` and `DEALLOCATE` commands still don't work in transaction pooling. If you set `max_prepared_statements = 0`, driver statement caching will break.

### How big should the PgBouncer pool size be?

Start `default_pool_size` near twice the database's vCPU count, so about 8 for a 4 vCPU instance, then tune. Raise it when `cl_waiting` climbs while CPU stays low, which signals I/O-bound queries. Keep pool size times database/user pairs times PgBouncer replicas under `max_connections`, and set `max_client_conn` generously, since clients waiting at PgBouncer are cheap.

## The call we'd make

Run PgBouncer in transaction mode for every new service that shares a database, as two or three central replicas rather than sidecars. Start small on pool size, raise `max_client_conn`, scrape `cl_waiting` from day one, and teach the team `SET LOCAL`. Reach for RDS Proxy when you'd rather pay AWS than operate the pooler. Pair it with [expand-contract migrations](/blog/zero-downtime-postgres-migrations-expand-contract) so schema changes don't hold locks that drain the pool.

## Sources

- [PgBouncer: Configuration reference](https://www.pgbouncer.org/config.html)
- [PgBouncer: Features and SQL support per pool mode](https://www.pgbouncer.org/features.html)
- [PgBouncer: Usage and admin console (SHOW POOLS, RECONNECT)](https://www.pgbouncer.org/usage.html)
- [Amazon RDS: Quotas and maximum database connections](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/CHAP_Limits.html)