Running PgBouncer and RDS Proxy

The last lesson ended with a neat fix: two PgBouncer instances in transaction mode, 1,600 cheap client connections in, 80 real ones out. That's the design. This lesson is about the following six months: the config file, the numbers you watch, the bugs that transaction mode lets in through the back door, and what changes if you let AWS run the pooler for you with RDS Proxy.

Everything shown from PgBouncer and Postgres here is real output, from PgBouncer 1.26 and Postgres 18 running locally.

The config file

PgBouncer is configured with one small ini file. Here's the core of Tiffin's:

[databases]
tiffin = host=tiffin-db.internal port=5432 dbname=tiffin

[pgbouncer]
listen_port = 6432
pool_mode = transaction

max_client_conn = 2000      ; client connections accepted (default 100)
default_pool_size = 40      ; real connections per user and database (default 20)
max_db_connections = 40     ; hard cap per database, across all users
reserve_pool_size = 5       ; extras when clients have waited too long
reserve_pool_timeout = 3    ; ...this many seconds
query_wait_timeout = 5      ; give up on a client after 5 s in the queue (default 120)
server_idle_timeout = 600   ; close real connections idle this long

Three of those defaults are worth knowing by heart, because each one has caused somebody's outage.

max_client_conn defaults to 100. Tiffin has 20 app servers with 80 client connections each. With the default, the 101st connection is refused:

FATAL:  no more connections allowed (max_client_conn)

It's the "too many clients" error from the last lesson again, just moved to the pooler. Client connections to PgBouncer are cheap, so set this well above what autoscaling can ever reach.

default_pool_size is per user and database pair. If the app connects as tiffin_app and the analytics job as tiffin_reports, each gets its own pool of 40, so the database sees 80. max_db_connections caps the total, whoever asks.

query_wait_timeout defaults to 120 seconds. A client that can't get a real connection waits in PgBouncer's queue, by default for two minutes. No customer waits two minutes for a menu, so by then the request is long dead, but it still holds its place in line. With 5 seconds, a backed-up pooler fails fast:

FATAL:  query_wait_timeout

That's an error the app can catch, log and turn into a clear "try again", which is far better than a queue full of requests nobody is waiting for.

Two pools, one cap

Tiffin runs two PgBouncer instances for redundancy. Each one counts only its own connections, so each gets half the budget: 40 each, 80 in total. If one instance dies, the other is still capped at 40, and the app runs on half the database connections until it's back. That's usually fine, because Little's law said only about 15 were busy on average. But size the reserve pool so a single survivor can still cope with the dinner peak.

One more thing about PgBouncer itself: it's single-threaded. One process uses one CPU core, which is plenty for tens of thousands of small queries a second. If it ever isn't, run more processes on the same port with so_reuseport, rather than a bigger machine.

What to watch

PgBouncer has an admin console. Connect to the special pgbouncer database and run SHOW POOLS. Here it is during a test where 20 clients share a pool of just 2 real connections:

database              | tiffin
user                  | postgres
cl_active             | 2
cl_waiting            | 18
sv_active             | 2
sv_idle               | 0
maxwait               | 0
maxwait_us            | 434511
pool_mode             | transaction

How to read it:

In the test, 18 clients were queued and the oldest had waited 0.43 seconds. That's the signal to act on. The pool size is right when cl_waiting is usually zero and maxwait stays in milliseconds. If clients wait while the database is idle, the pool is too small. If clients wait while the database is at 90% CPU, a bigger pool will make things worse, not better; the database is the limit, and the fix is in the queries.

Most metrics systems have a PgBouncer exporter that turns these fields into time series. Alert on maxwait above one second for a few minutes. It fires before customers notice.

Transaction mode, the sharp edges

The last lesson listed what breaks in transaction mode: session SET, temporary tables, session advisory locks, LISTEN. It's easy to agree with that list and still ship the bug, because nothing errors. Here's what it looks like.

We pointed two clients at a pool with one real connection. The first client sets a short timeout for itself, then disconnects:

$ psql "...pgbouncer.../tiffin" -c "SET statement_timeout = '50ms'"
SET

A completely different client connects a moment later and runs a normal query:

$ psql "...pgbouncer.../tiffin" -c "SHOW statement_timeout" -c "SELECT ... pg_sleep(0.2)"
 statement_timeout
-------------------
 50ms
(1 row)

ERROR:  canceling statement due to statement timeout

The first client's setting stayed on the real connection, and PgBouncer lent that connection to someone else. In production this shows up as a handful of random timeouts, in random endpoints, that nobody can reproduce. The same goes for anything else set with SET.

PgBouncer does track a few settings that Postgres reports back to the client, like TimeZone and application_name, and restores the right values per client. Since version 1.26 that includes search_path on Postgres 18. But statement_timeout, lock_timeout and most others aren't tracked. The safe patterns:

• Per transaction: SET LOCAL statement_timeout = '2s' inside the transaction. It ends with the transaction, so it can't leak.
• For the whole app: set it on the role, so every real connection starts with it: ALTER ROLE tiffin_app SET statement_timeout = '5s'.
• Never a plain SET at the start of a request.

Prepared statements

A prepared statement is a query the database has parsed and planned once, then runs many times with different values. Drivers and ORMs use them for speed, and they live on a connection, so they used to be the most famous transaction-mode bug: "prepared statement does not exist".

PgBouncer fixed the common case in version 1.21: it tracks protocol-level prepared statements, the kind drivers create, and prepares them again on whichever real connection a client gets. Since version 1.24 this is on by default (max_prepared_statements = 200). So the old advice to switch prepared statements off in your driver is mostly out of date. What still breaks is SQL-level PREPARE and EXECUTE written by hand, which almost nobody needs.

What must bypass the pooler

Some jobs genuinely need a session:

• Migrations. They set lock_timeout, and several migration tools take a session advisory lock so two deploys can't migrate at once. Run them straight against the database.
• LISTEN. Anything waiting for Postgres notifications needs its own direct connection.
• Long reports with temp tables or cursors. Give them a direct connection, or a separate pool in session mode.

Keep a small allowance of direct connections for these, outside PgBouncer's 80.

Pausing for maintenance

PgBouncer can hold traffic while the database restarts. PAUSE waits for running transactions to finish and then queues every new query instead of sending it. RESUME releases the queue. For a planned database restart that takes 20 seconds, customers see a slow request instead of errors, as long as the app's own timeouts are longer than the pause.

RDS Proxy: the managed version

On AWS, RDS Proxy does the same job without a PgBouncer fleet to run, patch and monitor. In Mumbai it costs $0.017 per vCPU-hour of the database behind it (late 2026), so about $400 a month in front of Tiffin's 32-vCPU primary. It also handles failover better than a hand-run pooler: the app keeps its connections to the proxy while the proxy finds the new primary.

The ideas map across, with different names:

Set ConnectionBorrowTimeout down from 120 seconds for the same reason as query_wait_timeout. And AWS recommends keeping at least 30% headroom between your busiest real usage and the connection cap.

Pinning

RDS Proxy handles session state differently from PgBouncer. Instead of letting a SET leak, it pins the client: from then on, that client keeps its own real connection until it disconnects, and nobody else can use it. Safe, but if every client pins, you've lost the pooling and the database is back to one connection per client.

For PostgreSQL, AWS documents these as causing pinning, among others:

• any SET, including setting a parameter back to its default,
• SQL PREPARE, EXECUTE, DEALLOCATE or DISCARD, which includes the DISCARD ALL that some pool libraries run when a connection is returned,
• temporary tables, sequences or views, and cursors,
• LISTEN,
• explicit nextval() or setval() calls,
• session advisory locks (the _xact_ transaction-level ones don't pin),
• any statement longer than 16 KB.

Watch the DatabaseConnectionsCurrentlySessionPinned metric. It should sit near zero. If it climbs, the proxy's logs say which statement pinned. The usual fix for a SET that every connection needs is to move it into the proxy's initialization query, which runs once on every new real connection, and delete it from the app.

Which one?

That last-but-one row is why Tiffin ends up with both, as the replicas lab will show: RDS Proxy in front of the primary, PgBouncer in front of the read replicas.