Database Connection Pooling PgBouncer Explained

The Problem That Makes DBAs Lose Sleep

PostgreSQL is a fantastic database. But it has one architectural quirk that will bite you the moment your application starts to scale: it spawns a new OS process for every single client connection. Not a thread — a full process. Each one consumes somewhere between 5 and 10 MB of shared memory just to exist, before you've run a single query.

Now imagine a web application with 200 app server processes, each maintaining a pool of 10 database connections. That's 2,000 Postgres backend processes sitting there, most of them idle, draining memory and eating into your

max_connections

ceiling. I've seen this pattern cripple a perfectly healthy database cluster that had nothing wrong with it except too many connections from too many application nodes. The database itself was fine. The connection count killed it.

PgBouncer is the answer to this problem. It's a lightweight TCP proxy that sits between your application and PostgreSQL, multiplexing many application connections onto a much smaller number of real database connections. It doesn't do query routing, caching, or anything clever. It just manages connections — and it does that one job extremely well.

What PgBouncer Actually Is

PgBouncer is a single-process, event-driven connection pooler written in C. It speaks the PostgreSQL wire protocol on both ends — it looks like a Postgres server to your application, and it looks like a Postgres client to your actual database. Your app connects to PgBouncer on port 6432 (by convention), and PgBouncer maintains a pool of real connections to the backend on port 5432.

The binary is tiny. The memory footprint at idle is typically under 5 MB. It handles thousands of client connections with ease. In my experience, a single PgBouncer instance can comfortably manage 5,000 or more client connections while holding just 50 to 100 real backend connections open — a 100:1 reduction in backend load without the application noticing anything except things got faster.

It was originally written by Marko Kreen and is now maintained as an open-source project under the PgBouncer GitHub organization. It's been production-hardened for well over a decade. If you're running PostgreSQL at any meaningful scale, there's a very good chance PgBouncer is already somewhere in your stack — you just might not know it.

How Connection Pooling Works

Before going into PgBouncer's specific modes, it's worth understanding what connection pooling actually buys you. Establishing a PostgreSQL connection isn't free. There's a TCP handshake, TLS negotiation if you're doing it right, authentication, process fork on the Postgres side, and initialization of the backend process state. That can take anywhere from 5 to 50 milliseconds depending on your setup. For a high-throughput application doing thousands of requests per second, that overhead becomes a serious bottleneck all on its own.

Connection pools solve this by keeping connections alive and reusing them. Instead of your app opening and closing a connection for every request, it borrows a connection from the pool, uses it, and returns it. PgBouncer takes this further: it maintains a relatively small pool of connections to Postgres and multiplexes a much larger number of client connections across them based on availability and pooling mode.

PgBouncer's Three Pooling Modes

This is where PgBouncer gets interesting — and where most of the operational complexity lives. PgBouncer supports three distinct pooling modes, and choosing the wrong one for your workload will either leave performance on the table or introduce subtle application bugs that are genuinely painful to debug.

Session Pooling

In session pooling mode, a client is assigned a server connection for the duration of its session. The connection is only returned to the pool when the client disconnects. This is the safest mode — it preserves all PostgreSQL session-level semantics including temporary tables, advisory locks, SET parameters, and prepared statements. But it's also the least efficient: the ratio of client connections to server connections is still essentially 1:1, so you're not getting meaningful multiplexing. The main win here is avoiding the connection establishment overhead on every request.

Session pooling is a good starting point if you're migrating an existing application that heavily uses session-level features and you don't want to audit all that code right now. Think of it as the conservative first step.

Transaction Pooling

Transaction pooling is where PgBouncer really earns its keep. In this mode, a server connection is held only for the duration of a transaction. As soon as the client sends COMMIT or ROLLBACK, the server connection goes back to the pool and can be immediately reused by a different client. A client can maintain a persistent logical connection to PgBouncer while waiting for work, without holding a real backend connection open at all.

This is the mode that lets you go from 2,000 backend connections down to 50. I've seen services go from regularly hitting

max_connections

under load to cruising at 15% utilization on the backend, just by switching to transaction mode. The multiplexing ratio depends heavily on your query latency and transaction duration, but for short-lived OLTP workloads — which is most web application traffic — the gains are dramatic.

The catch: transaction pooling breaks anything that relies on session-level state persisting across transactions. That means no

SET

commands you expect to stick, no advisory locks held between transactions, no temporary tables across transaction boundaries, and no traditional prepared statements without additional configuration. If your ORM or application framework uses any of these features, you'll need to audit carefully before switching to transaction mode.

Statement Pooling

Statement pooling is the most aggressive mode: a server connection is released back to the pool after every single statement, even within a transaction. This effectively makes multi-statement transactions impossible, since each statement might run on a different backend connection. It's rarely used for general-purpose OLTP workloads. You'll mostly see it in read-heavy analytics scenarios where every query is a self-contained SELECT and you need maximum connection density at the cost of transactional guarantees.

Installing and Configuring PgBouncer

On a Debian or Ubuntu-based system, installation is straightforward:

apt-get install pgbouncer

The main configuration file lives at

/etc/pgbouncer/pgbouncer.ini

. Here's a realistic production-style configuration for a single application database using transaction pooling, running on sw-infrarunbook-01:

[databases]
appdb = host=192.168.10.20 port=5432 dbname=appdb

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

pool_mode = transaction
max_client_conn = 2000
default_pool_size = 50
min_pool_size = 10
reserve_pool_size = 10
reserve_pool_timeout = 5

server_idle_timeout = 600
client_idle_timeout = 0
server_connect_timeout = 15
server_login_retry = 15

log_connections = 0
log_disconnections = 0
log_pooler_errors = 1

stats_period = 60
admin_users = infrarunbook-admin
stats_users = infrarunbook-admin

The

userlist.txt

file stores credentials PgBouncer uses to authenticate clients. For scram-sha-256 auth, pull the hashed password directly from PostgreSQL:

SELECT rolname, rolpassword FROM pg_authid WHERE rolname = 'appuser';

Then populate

/etc/pgbouncer/userlist.txt

in the expected format:

"appuser" "SCRAM-SHA-256$4096:<salt>$<client-key>:<server-key>"
"infrarunbook-admin" "SCRAM-SHA-256$4096:<salt>$<client-key>:<server-key>"

The

[databases]

section maps incoming connection names to backend targets. You can alias database names here, which is useful when you want your application to connect using a generic name like

appdb

regardless of what the actual Postgres database is called on the backend. It also means you can redirect connections to a different host by updating a single line in this config rather than touching every application node.

Key Configuration Parameters Explained

default_pool_size controls how many server connections PgBouncer will open per database/user pair. This is the most important tuning knob. Setting it too low causes client connection queuing; setting it too high defeats the purpose of pooling and pushes you toward the Postgres

max_connections

limit. A reasonable starting formula is to take your available Postgres memory, divide by the per-connection overhead (roughly 8–10 MB per backend process plus your working set), and target about 80% of that ceiling across all pool sizes combined.

max_client_conn is the total number of client connections PgBouncer will accept. This should be set well above your application's expected connection count to avoid spurious rejections under burst traffic. Values between 2,000 and 10,000 are typical in production environments.

reserve_pool_size adds extra server connections that PgBouncer can use when the main pool is exhausted. If a client waits longer than

reserve_pool_timeout

seconds, PgBouncer dips into the reserve. This is your safety valve for sudden load spikes — size it at roughly 20% of your main pool size and you'll absorb most organic traffic bursts without clients seeing errors.

server_idle_timeout controls how long an idle server connection stays open before PgBouncer closes it. Keeping this at 600 seconds is usually fine for stable on-premises workloads. If you're connecting to a cloud-managed database that aggressively terminates idle connections, lower this to something like 300 seconds to avoid being caught with stale connections that the backend has already closed.

Using the Admin Console

PgBouncer exposes an admin interface you connect to exactly like a Postgres database, using the virtual database named

pgbouncer

:

psql -h 192.168.10.10 -p 6432 -U infrarunbook-admin pgbouncer

From here you can inspect pool state in real time. The two commands I reach for first when troubleshooting connection issues are:

SHOW POOLS;
SHOW STATS;

SHOW POOLS

gives you a per-database/user breakdown of client connections (

cl_active

,

cl_waiting

), server connections (

sv_active

,

sv_idle

,

sv_used

), and max wait time. If

cl_waiting

is non-zero and climbing, your pool is exhausted and clients are queuing — either increase

default_pool_size

or investigate what's holding backend connections open for unusually long. If

sv_idle

is much larger than

sv_active

, your pool is oversized relative to actual demand.

SHOW STATS

gives you query rate, transaction rate, bytes in and out, and average query time — useful for before-and-after comparisons when tuning. You can also issue

RELOAD

to apply configuration changes without dropping existing client connections, and

PAUSE appdb

followed by

RESUME appdb

for zero-downtime Postgres maintenance like a primary promotion.

Real-World Deployment Patterns

In practice, PgBouncer is almost never run on the database server itself. It makes far more sense to run it close to the application — either as a sidecar on each application host, or as a dedicated pooling tier. I've seen both work well, and the right choice depends on your traffic shape and operational preferences.

The sidecar pattern means running PgBouncer on sw-infrarunbook-01 alongside the application process. This minimizes network hops and means each application node manages its own pool independently. The tradeoff is that your backend sees N times pool_size connections, where N is the number of app nodes. With 20 app nodes and a pool size of 25, that's 500 backend connections — still manageable, but something to track carefully as you scale out.

The centralized pooler pattern puts one or two PgBouncer instances on dedicated hosts that all app nodes connect to. This gives you much tighter control over total backend connection count and makes monitoring dramatically simpler — you have one place to look at pool saturation instead of 20. The tradeoff is an additional network hop and a potential single point of failure if you don't run at least two poolers behind a virtual IP. Using keepalived in front of two PgBouncer nodes on 192.168.10.10 and 192.168.10.11 is a common, solid HA pattern that covers planned maintenance and unexpected node failures cleanly.

With read replicas in the picture, you can define multiple database aliases in PgBouncer pointing to different backends:

[databases]
appdb    = host=192.168.10.20 port=5432 dbname=appdb
appdb_ro = host=192.168.10.21 port=5432 dbname=appdb

Your application connects to

appdb

for writes and

appdb_ro

for read-only queries. When you add more replicas, you update the alias to point to a load balancer fronting the replica pool — zero changes to the application, zero changes to PgBouncer's main config beyond that one line.

Common Misconceptions

The most persistent misconception I encounter is that PgBouncer caches queries or query results. It doesn't. It's a TCP proxy. It does not parse your SQL, has no concept of query plans, and knows nothing about your schema. It forwards bytes in one direction and bytes back in the other. If you want query-level caching, that's a completely separate layer.

Another one: that transaction pooling is always safe to enable. It isn't — not without auditing your application first. I've seen engineers flip the pooling mode to transaction and then spend days debugging errors that turned out to be SET LOCAL statements not persisting across a connection reuse, or an ORM pre-caching prepared statement handles that no longer mapped to valid backend state. Transaction pooling is powerful, but it requires your application to be stateless at the session level. That's not always true out of the box, and ORMs in particular tend to use session-level features quietly.

The prepared statement problem is worth its own paragraph. PostgreSQL protocol-level prepared statements (

PREPARE foo AS SELECT ...

) are session-scoped. In transaction pooling mode there's no guarantee the same backend handles your

PREPARE

and your subsequent

EXECUTE foo

, because they may occur in different transactions dispatched to different backend connections. PgBouncer 1.21 introduced protocol-level prepared statement tracking that partially addresses this, but many teams simply disable prepared statements at the driver level when running transaction pooling. Check your specific driver's documentation — most have a flag to disable them explicitly.

People also confuse PgBouncer with application-level connection pools built into their language driver — the kind that come bundled with SQLAlchemy, HikariCP, or ActiveRecord. These are not the same thing and they're not redundant. Driver-level pools manage connections within a single process. PgBouncer manages connections across your entire application fleet. They're complementary, but you do need to size your driver pool appropriately: if each application process tries to hold more connections than PgBouncer has available in the pool, your clients will queue at the pooler under any sustained load.

Finally: PgBouncer is not a high-availability solution. It doesn't do automatic failover. If your primary Postgres node goes down, PgBouncer's server connections to that node fail along with it. You still need a separate HA mechanism — Patroni, repmgr, or your cloud provider's managed failover. What PgBouncer gives you is the ability to reconnect cleanly after a failover, especially if you route backend connections through a CNAME or VIP that your HA solution manages. The application doesn't need to know anything changed.

Monitoring What Matters

The metrics I watch closely in production are

cl_waiting

(clients waiting for a connection — should normally be zero or near-zero), the

sv_idle

to

sv_active

ratio for pool sizing insight, and

maxwait

, which is the longest any client has been waiting in seconds. I alert on

cl_waiting

greater than zero for more than 30 seconds, and on

maxwait

exceeding 5 seconds. Those thresholds catch pool exhaustion early, before it cascades into application timeouts.

PgBouncer exposes all of this via the admin console, and the

pgbouncer_exporter

project makes it easy to scrape these metrics into Prometheus and build dashboards in Grafana. Once you have visibility into pool saturation over time, tuning becomes much more precise. You stop guessing at

default_pool_size

and start making data-driven decisions based on observed peak sv_active versus your headroom.

---

PgBouncer isn't exciting technology. It doesn't show up in conference keynotes about distributed systems or cloud-native architectures. But if you're running Postgres at any real scale without it, you're leaving a significant amount of performance and stability on the table. It's one of those infrastructure components where the best endorsement I can offer is this: in years of running PostgreSQL in production environments ranging from small startups to high-traffic platforms, I have never once seen adding PgBouncer make things worse. I have seen not having it cause serious, production-impacting incidents more times than I can count.