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.

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:

[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

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.

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. 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 so schema changes don't hold locks that drain the pool.

Sources

• PgBouncer: Configuration reference
• PgBouncer: Features and SQL support per pool mode
• PgBouncer: Usage and admin console (SHOW POOLS, RECONNECT)
• Amazon RDS: Quotas and maximum database connections