PgBouncer: Connection Pooling Before You Think You Need It

There is a specific way Postgres falls over that catches homelabbers by surprise, because it strikes when nothing is obviously wrong. The database is not slow, the queries are fine, the disk is idle, and yet the app starts throwing FATAL: sorry, too many clients already and refusing connections. You raise max_connections, it works for a week, then it happens again, worse, and eventually the whole box grinds because Postgres is drowning in connections that are each doing almost nothing. This is a problem a connection pooler solves completely, and PgBouncer is the small, boring, twenty-year-old tool that does it. The reason to learn it before you are firefighting is that the failure looks like a scaling problem and is actually a connection-management problem, and the two have opposite fixes.

The counterintuitive part is in the title. Most advice says reach for a pooler when you have thousands of connections. In a homelab you will benefit from one at far lower numbers than that, because the cost of a Postgres connection has less to do with how many you have than with the model Postgres uses to serve them, and that model gets expensive surprisingly early.

Why a Postgres connection is expensive

To understand the fix you have to understand what a connection actually costs, and Postgres is unusual here in a way that shapes everything. When a client connects, Postgres forks a whole new operating-system process to serve it — a full backend, with its own memory, its own file descriptors, and its own slice of the scheduler. There is no thread pool or lightweight handle underneath; the unit of a connection is an entire OS process. That process lives for the entire duration of the connection, whether it is running a query or sitting idle waiting for the app to send the next one.

This has two consequences that bite a homelab. First, each backend consumes real memory — a few megabytes of baseline plus whatever work_mem a query grabs — so a hundred connections is a hundred processes and potentially a great deal of RAM committed to backends that are mostly idle. Second, establishing a connection is not free: forking a process, setting up the session, negotiating TLS, running whatever the app does on connect all take milliseconds, and an app that opens a fresh connection for every single web request pays that tax on every request. The default max_connections of 100 is a ceiling chosen to protect the machine, and the instinct to raise it when you hit the wall makes things worse, because more idle backends means more memory pressure and more context-switching overhead while useful throughput stays flat.

The insight that PgBouncer is built on is that most of those connections are idle almost all the time. An app with fifty worker threads might hold fifty connections open, but at any given instant maybe three of them are actually running a query; the other forty-seven are waiting for the app to do something. Committing forty-seven real Postgres backends to do nothing is the waste, and a pooler exists to eliminate it.

What a pooler actually does

PgBouncer sits between your applications and Postgres, pretending to be Postgres to the apps and pretending to be a single well-behaved app to Postgres. Your apps open as many connections to PgBouncer as they like — connections to PgBouncer are cheap, because PgBouncer is a single lightweight process that handles them with an event loop rather than forking. PgBouncer then maintains a small pool of real connections to Postgres and hands them out to client connections only when they have actual work to do, multiplexing your fifty mostly-idle app connections onto, say, twenty busy Postgres backends.

The effect is that Postgres sees a small, steady, fully-utilised set of connections instead of a large, spiky, mostly-idle one. Memory use drops because there are fewer backends. Connection-storm errors vanish because your apps can no longer open a hundred real backends by accident. And the per-request connection tax disappears, because the expensive real connection is already established and pooled; your app is connecting to PgBouncer, which is a microsecond affair.

The concept that matters most: pooling mode

If you learn one thing about PgBouncer, learn its three pooling modes, because choosing wrong will either waste the whole benefit or break your application in confusing ways.

Session pooling assigns a real Postgres connection to a client for the entire duration of the client’s connection, and only returns it to the pool when the client disconnects. This is the safest mode because it behaves exactly like talking to Postgres directly — every session-level feature works — but it gives you the least benefit, because an idle client still ties up a real backend. It is the right choice when your app holds long-lived connections and uses session-level state heavily.

Transaction pooling is where the real leverage is, and it is the mode most people should run. Here a real connection is assigned to a client only for the duration of a single transaction, then returned to the pool the moment the transaction commits or rolls back. Between transactions the client holds nothing. This is what lets twenty real backends serve a hundred clients, because a client only occupies a backend during the milliseconds it is actually mid-transaction. The catch, and it is a real one, is that anything relying on state between transactions on the same connection can break, because the next transaction might land on a different backend entirely. Session-level SET commands, LISTEN/NOTIFY, session-level advisory locks, and WITH HOLD cursors are the usual casualties. Prepared statements were historically a problem too, though recent PgBouncer versions handle them in transaction mode when configured to.

Statement pooling returns the connection after every individual statement, which forbids multi-statement transactions entirely. It is a niche mode for very specific autocommit workloads and you can safely ignore it for now.

The practical rule: run transaction pooling, and make sure your app tolerates it — most modern frameworks and ORMs do, or can be told to. If something depends on session state, either move it to a session-pooled port or reconsider the design.

Setting it up

PgBouncer’s configuration is a single, readable INI file, which is a large part of its charm. A minimal transaction-pooling config for one database looks like this:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15

# pgbouncer.ini
[databases]
appdb = host=127.0.0.1 port=5432 dbname=appdb

[pgbouncer]
listen_addr = 127.0.0.1
listen_port = 6432
auth_type = scram-sha-256
auth_file = /etc/pgbouncer/userlist.txt

pool_mode = transaction
max_client_conn = 200          ; how many clients may connect to PgBouncer
default_pool_size = 20         ; real Postgres connections per database/user
reserve_pool_size = 5          ; emergency extras when the pool is saturated
server_idle_timeout = 300      ; close idle server connections after 5 min

The auth_file holds the usernames and password hashes PgBouncer will accept, and with modern Postgres you want SCRAM rather than plain-text or MD5. Your applications then point at PgBouncer’s port (6432 here) instead of Postgres’s 5432, and nothing else in them changes — PgBouncer speaks the Postgres wire protocol, so from the app’s point of view it is Postgres. That drop-in transparency is why it is so easy to adopt: you change a port number in a connection string and the pooling is simply there.

Sizing the pool

The number that trips people up is default_pool_size, and the instinct is to set it high, which throws away the benefit. The pool size is the number of real Postgres backends you are willing to keep busy, and the point of pooling is for it to be small. A pool that is too large recreates the problem you came to solve; a pool that is too small makes clients queue for a backend and adds latency.

A workable starting formula for a CPU-bound workload is a pool size of roughly the number of CPU cores available to Postgres, plus a little headroom — often somewhere between the core count and two or three times it, depending on how much time queries spend waiting on I/O rather than the CPU. On a four-core homelab box, a default_pool_size of 10 to 20 is a sane place to begin. The crucial check is that default_pool_size stays comfortably below Postgres’s own max_connections, because the whole arrangement collapses if PgBouncer is allowed to open more real connections than Postgres will accept — leave Postgres headroom for your own psql sessions and any maintenance jobs on top of the pool. Watch SHOW POOLS; from the PgBouncer admin console under real load and adjust: if clients are frequently waiting (cl_waiting climbing) the pool is too small; if backends sit idle the pool is larger than you need.

Where pooling fits among the other fixes

Connection pooling addresses a specific bottleneck — too many connections — and it is worth being clear it does not fix the others, because reaching for it when the real problem is elsewhere wastes your time. If individual queries are slow, a pooler does nothing for that; the answer is tuning Postgres and fixing missing indexes, and no amount of pooling rescues a sequential scan over a million rows. If the same expensive read repeats constantly, the tool is a cache like Redis that eliminates the read entirely rather than a pooler that merely rations connections. And whatever else you add in front of Postgres, none of it substitutes for a database backup strategy you have actually tested, because a pooler protects availability and does nothing for durability. PgBouncer is the answer when the symptom is connection exhaustion and idle-backend memory bloat, and knowing that keeps you from bolting it on to a problem it cannot touch.

Troubleshooting

Apps break under transaction pooling with errors about prepared statements or SET. The app relies on session state that transaction mode does not preserve. Either configure your driver to disable server-side prepared statements (many, like some Python and Java drivers, have a flag for exactly this), enable PgBouncer’s prepared-statement support if your version has it, or move that app to a session-pooled connection.

SHOW POOLS shows clients waiting and latency has gone up. default_pool_size is too small for the concurrency, so clients are queuing for a backend. Raise the pool size in steps, checking each time that Postgres has the max_connections headroom to accommodate it.

PgBouncer authenticates fine but Postgres rejects the connection. The credentials in userlist.txt must match how Postgres expects to authenticate, and with SCRAM the hash format matters. Generate the entry correctly, and remember PgBouncer needs to reach Postgres with its own working credentials in addition to accepting the app’s.

LISTEN/NOTIFY stopped working after adding PgBouncer. That feature is session-scoped and incompatible with transaction pooling by design. Point the component that needs it at a session-pooled port or straight at Postgres.

Memory on the Postgres box barely changed. Check you actually pointed the apps at PgBouncer’s port and not Postgres’s — a surprising number of “PgBouncer isn’t helping” reports are apps still connecting straight to 5432. Confirm with SHOW CLIENTS; that PgBouncer is seeing the traffic at all.

Is it worth it?

For a homelab running a single app against Postgres with a handful of connections, honestly, no — you will not notice the difference, and adding PgBouncer is a moving part with no payoff. The moment worth acting on arrives when you have several apps sharing a Postgres instance, an app that opens a connection per request, or a background worker fleet, and you start seeing idle backends pile up or too many clients errors appear. At that point PgBouncer is a genuinely small intervention with a large effect: one lightweight process, a readable config file, a port change in your connection strings, and the connection-storm class of failure simply stops happening while Postgres reclaims the memory those idle backends were squatting on.

Learn it before you need it, because the failure it prevents arrives suddenly and looks like something else. Run transaction pooling, size the pool small and deliberately, keep it under Postgres’s own connection ceiling, and check your app tolerates the mode. Do that and PgBouncer joins the short list of infrastructure you set up once, understand completely, and never think about again.