How PgBouncer Works

A from-the-ground-up explanation of PgBouncer: why Postgres needs a connection pooler, what each pooling mode actually does, what breaks under transaction pooling, and how to run the thing in production. About a 30-minute read. Versions reference PgBouncer 1.25.1, the current stable as of writing.

1. The thing PgBouncer fixes

If you have used Postgres for any length of time, you have probably run into some version of this story. Your app has been fine for months. Then you push a release that adds a few more workers, or traffic spikes during a launch, or your background job system starts running more parallel jobs than it used to. Suddenly the database starts refusing connections. Latency goes up. Memory usage on the database host climbs to ninety-something percent. The error logs are full of FATAL: sorry, too many clients already, and your max_connections setting, which seemed generous when you set it, is now the thing standing between you and a working application.

The textbook fix is “add a connection pooler.” The textbook recommendation is PgBouncer. And then, if you are like most people, you set pool_mode = transaction, point your app at port 6432 instead of 5432, and walk away thinking the problem is solved.

It usually is. But the thing you just deployed is doing something quite specific, and if you do not understand what, the next bug you hit will be confusing in a way that direct Postgres bugs are not. Why doesn’t SET search_path stick? Why did my long-running report time out at 5 seconds when I never set a 5-second timeout? Why does this Perl script work when I connect directly but fail through PgBouncer? PgBouncer presents itself as a transparent proxy, and in one mode it almost is, but in the mode you actually want to use, it isn’t, and the gap between what looks transparent and what actually is transparent is where the bugs live.

This piece is about that gap. We’ll start with why Postgres connections cost something in the first place, work through the three pooling modes and what each one buys and breaks, then look at how to actually run PgBouncer in production: how to size the pool, how to authenticate without storing every password twice, how to monitor it, and the long list of small things that go subtly wrong.

Act 1 — Why connections are expensive

The Postgres process model and why it does not love thousands of clients.

2. The fork that started it all

Postgres has been around since 1986. Its connection model dates from a time when the modern web did not exist and the typical workload was a handful of analysts running queries from terminals. The model is simple. There is one supervisor process called the postmaster. When a client connects, the postmaster calls fork() and produces a new child process called a backend. That backend is yours, exclusively, for the lifetime of the connection. When you disconnect, it exits. Every query you run, every transaction, every SET, every prepared statement, every temporary table, lives inside that one process.

This design has real virtues. Each connection is isolated, so a memory bug in one query cannot corrupt another client’s session. Cancellation is a SIGINT to a specific PID. The OS scheduler does the heavy lifting of fairness between connections. It is a very Unix design and it ages well in many ways.

It does not age well in one specific way. A process is not free. Forking it is not free. Initializing the backend’s catalog cache is not free. And keeping it around when it is doing nothing is not free either.

postmasterlistens on 5432backendpid 1812~5–15 MBbackendpid 1813idlebackendpid 1814running querybackendpid 1815idle in txbackendpid 1816idleclient Aclient Bclient Cclient Dclient Eone process per client. always. even when the client is doing nothing.Fig 1. The Postgres connection model. Every client gets a dedicated OS process. The processes are isolated, which is great for stability, and unshared, which is bad for scaling.<br>3. What an idle connection actually costs

Memory is the headline cost, and the number you usually hear is “10 MB per connection.” That number is roughly right but slightly misleading. The truth is that ps and top overcount, because Linux uses copy-on-write for forked processes and does not split out shared memory cleanly in those tools. When you measure more carefully — Andres Freund’s writeup uses a mix of /proc/[pid]/smaps_rollup (an approximation, not an exact figure) and direct PSS accounting — the incremental private memory of an idle Postgres backend is closer to 1.5 to 2 MB on a fresh connection, growing to 5 to 15 MB once the connection has done some work and accumulated catalog cache, prepared plans, and per-process state. The AWS database team’s RDS-side measurements landed at 1.5 to 14.5 MB depending on workload, which lines up.

So it is not “10 MB per connection” exactly. It is more like “a few MB per connection, more if it does anything interesting, multiplied by however many...