How to Configure Connection Pooling in PostgreSQL

The first time I saw a production PostgreSQL database fall over under load that wasn’t actually that heavy in terms of queries per second, the culprit wasn’t slow queries at all — it was connection exhaustion. Every incoming web request was opening its own new database connection, holding it briefly, and closing it again, and PostgreSQL’s per-connection overhead simply couldn’t keep up with the churn. Connection pooling fixed it almost immediately. In this article, I want to explain why connection pooling matters so much for PostgreSQL specifically, walk through setting up PgBouncer (the tool I reach for most often), and cover the practical gotchas that come with it.

Why PostgreSQL Connections Are Expensive

Unlike some databases that use lightweight thread-based connection handling, PostgreSQL forks a full OS process for every connection. That process has real memory overhead, and PostgreSQL’s default max_connections setting (often 100) exists specifically because there’s a real ceiling on how many of these processes a server can handle efficiently. Once you approach that ceiling, new connection attempts either queue up or get rejected outright, and existing connections start competing for CPU and memory in ways that degrade performance across the board.

Modern applications, especially anything running in a container-based or serverless environment, tend to create a lot of short-lived connections — every request handler, every background worker, every horizontally-scaled instance opening its own connections. Multiply that across dozens of application instances and you can hit max_connections embarrassingly fast, even if the actual database workload is modest.

What a Connection Pooler Actually Does

A connection pooler sits between your application and PostgreSQL. Your application connects to the pooler instead of directly to PostgreSQL, and the pooler maintains a smaller set of actual, persistent connections to the database, handing them out to client connections as needed and returning them to the pool when a client is done with them. The application sees what looks like a normal PostgreSQL connection; behind the scenes, the pooler is doing the work of multiplexing many client connections onto far fewer real database connections.

PgBouncer: The Standard Choice

PgBouncer is lightweight, battle-tested, and the tool I reach for by default. It’s a separate process you run alongside your PostgreSQL server (or on a dedicated host), and it speaks the PostgreSQL wire protocol on both sides, so most clients don’t even need to know they’re talking to a pooler instead of PostgreSQL directly.

Installing PgBouncer

On a Debian/Ubuntu system:

sudo apt-get install pgbouncer

On other platforms, it’s available through most standard package managers, or you can build it from source.

Basic Configuration

PgBouncer’s main config file is typically at /etc/pgbouncer/pgbouncer.ini. Here’s a minimal but realistic setup:

[databases]
myapp = host=127.0.0.1 port=5432 dbname=myapp

[pgbouncer]

listen_addr = 0.0.0.0 listen_port = 6432 auth_type = scram-sha-256 auth_file = /etc/pgbouncer/userlist.txt pool_mode = transaction max_client_conn = 1000 default_pool_size = 20

Applications connect to port 6432 instead of PostgreSQL’s default 5432, and PgBouncer forwards the actual work to the real PostgreSQL instance on 5432.

Setting Up Authentication

The userlist.txt file needs usernames and password hashes:

"myapp_user" "SCRAM-SHA-256$4096:..."

I generate this properly rather than hand-typing hashes — PgBouncer includes a helper, or you can query the hash directly from PostgreSQL’s pg_shadow/pg_authid (accessible to superusers) and copy it over. Since PostgreSQL 14+, scram-sha-256 is the recommended auth method over the older md5, and I always use it unless I have a specific legacy compatibility reason not to.

Understanding Pool Modes

This is the single most important configuration decision in PgBouncer, and getting it wrong causes subtle, confusing bugs. There are three pool modes:

Session Pooling

A client gets a dedicated real connection for the entire duration of its session, released only when the client disconnects. This behaves exactly like connecting directly to PostgreSQL — full compatibility with session-level features like SET variables, LISTEN/NOTIFY, and prepared statements — but it doesn’t actually improve connection reuse much, since a real connection is tied up for as long as the client is connected, even if the client is idle most of the time.

Transaction Pooling

A client gets a real connection only for the duration of a single transaction. As soon as the transaction commits or rolls back, the underlying connection goes back into the pool and can be handed to a different client. This is the mode that provides the real multiplexing benefit — a small pool of real connections can serve a much larger number of client connections, since most transactions are short.

This is the mode I use in almost every production setup, because it gives you the actual capacity benefit pooling is meant to provide. But it comes with real constraints: session-level state doesn’t persist reliably across transactions in this mode. Prepared statements, session-level SET commands, advisory locks held outside a transaction, and LISTEN/NOTIFY don’t behave the way they would on a direct connection, because the underlying physical connection might be a completely different one for the next transaction from the same client.

Statement Pooling

The most aggressive mode — a connection is only held for a single statement, not even a full transaction. This breaks multi-statement transactions entirely and I’ve genuinely never had a use case for it. I mention it mostly so you know it exists and know not to reach for it by accident.

pool_mode = transaction

The Practical Impact of Transaction Pooling

Because transaction pooling is what most people actually want (and what gives you the real benefit), it’s worth being explicit about what breaks:

• Prepared statements created with PREPARE may not survive between transactions, since the next transaction might land on a different backend connection. Some drivers handle this transparently by re-preparing as needed (and PgBouncer 1.21+ added support for protocol-level prepared statement handling that helps significantly here); others don’t, and you’ll see prepared statement does not exist errors.
• SET at the session level (like SET search_path) won’t reliably persist. Use SET LOCAL inside a transaction instead, which is scoped correctly to the transaction and works fine under transaction pooling.
• Advisory locks taken outside a transaction (pg_advisory_lock, not pg_advisory_xact_lock) are dangerous under transaction pooling, since the lock is tied to a specific backend connection that might get handed to a different client afterward. Use the transaction-scoped variants (pg_advisory_xact_lock) instead.
• LISTEN/NOTIFY doesn’t work reliably under transaction pooling, since a listening connection needs to persist. If you need this, either use session pooling for that specific connection or connect directly to PostgreSQL for that use case.
• Temporary tables can behave unexpectedly since they’re tied to a session, not a transaction.

Whenever I set up transaction pooling, I make a point of auditing the application for any of these patterns before going live — it’s a much better experience to catch them ahead of time than to debug a “sometimes my prepared statement disappears” bug in production.

Sizing the Pool

default_pool_size controls how many real connections PgBouncer maintains per database/user pair. I don’t just guess at this number — I think about it in terms of what PostgreSQL can actually handle:

default_pool_size = 20
max_client_conn = 1000

A reasonable starting point is to keep the total real connections PgBouncer opens well under PostgreSQL’s max_connections setting, leaving headroom for direct admin connections, monitoring tools, and other poolers or services that might also be connecting. If you have multiple application instances each running their own PgBouncer, or multiple databases behind one PgBouncer, you need to account for the sum across all of them, not just one pool in isolation.

Monitoring PgBouncer

PgBouncer exposes an administrative console you connect to like a regular database:

psql -h 127.0.0.1 -p 6432 -U pgbouncer_admin pgbouncer

From there, useful commands include:

SHOW POOLS;
SHOW STATS;
SHOW CLIENTS;
SHOW SERVERS;

SHOW POOLS is what I check first when something feels off — it shows how many client connections are waiting (cl_waiting), how many server connections are active versus idle, and whether the pool is actually saturated.

Application-Side Pooling vs. PgBouncer

Many application frameworks and ORMs also include their own connection pooling on the client side. It’s worth understanding that this solves a related but different problem: application-side pooling reduces the overhead of establishing a new TCP/auth handshake for every query within a single application instance, while PgBouncer (or a similar external pooler) solves the problem of many application instances collectively overwhelming PostgreSQL’s connection limit. In most real deployments, I use both together — application-side pooling with a modest pool size per instance, feeding into PgBouncer, which then multiplexes across all instances down to a manageable number of real PostgreSQL connections.

Common Use Cases

• Serverless or auto-scaling applications where the number of application instances (and therefore potential connections) can spike unpredictably.
• Microservice architectures where dozens of small services each maintain their own connection pool to the same database.
• High-concurrency web applications with many short-lived request-scoped connections.
• Managed database services that impose strict connection limits, where pooling is often close to mandatory at any real scale.

Troubleshooting Tips

“too many connections” errors even with PgBouncer in place. Check whether your application is actually configured to connect through PgBouncer’s port, not PostgreSQL’s port directly. This is an embarrassingly common misconfiguration — I’ve made it myself.

Prepared statement errors under load. Almost always a symptom of transaction pooling combined with a driver or ORM that aggressively prepares statements. Check your driver’s documentation for how it handles this under pgbouncer, or consider disabling statement caching at the driver level if it’s not handled transparently.

SET search_path or similar settings not sticking. Classic transaction-pooling symptom. Switch to SET LOCAL inside explicit transactions, or configure the setting per-user/per-database directly in PostgreSQL instead of relying on a session-level SET.

Clients queuing / high cl_waiting in SHOW POOLS. The pool is saturated — either increase default_pool_size (if PostgreSQL has headroom) or investigate why transactions are taking longer than expected to complete, tying up real connections longer than necessary.

LISTEN/NOTIFY messages never arrive. This won’t work reliably under transaction pooling — use a direct connection or session pooling for that specific use case.

Best Practices

1. Default to transaction pool mode for the actual capacity benefit, but audit your application for incompatible patterns first.
2. Use SET LOCAL instead of SET inside transactions when working with transaction pooling.
3. Use transaction-scoped advisory locks (pg_advisory_xact_lock) rather than session-scoped ones under transaction pooling.
4. Size your pool relative to PostgreSQL’s actual max_connections, accounting for all poolers and direct connections combined.
5. Monitor SHOW POOLS regularly, not just when something breaks.
6. Use scram-sha-256 authentication rather than the older md5 method.
7. Combine application-side pooling with PgBouncer rather than treating them as redundant — they solve different parts of the same problem.
8. Route all application connections through the pooler, not just some of them — mixed direct and pooled connections make capacity planning much harder to reason about.

A Real-World Example: Diagnosing a Connection Storm

I want to walk through an actual incident, since it ties several of these concepts together. An application had started auto-scaling more aggressively after a marketing push drove a traffic spike, and within minutes PostgreSQL started rejecting new connections with FATAL: too many connections. Here’s the sequence I went through.

First, I checked how many connections were actually active versus what PostgreSQL allowed:

SELECT count(*) FROM pg_stat_activity;
SHOW max_connections;

The count was sitting right at the ceiling. Next, I checked whether those connections were doing real work or just sitting idle:

SELECT state, count(*) FROM pg_stat_activity GROUP BY state;

The vast majority were idle, not active — meaning the application wasn’t actually overwhelmed with query volume, it was simply opening far more connections than it needed and holding onto them. This pointed straight at connection pooling as the fix rather than, say, scaling the database itself.

The actual root cause turned out to be that each new auto-scaled application instance was configuring its ORM’s connection pool with a fixed size that assumed it was the only instance running — multiply that fixed pool size by the number of instances the autoscaler had just spun up, and it comfortably exceeded PostgreSQL’s max_connections even though the real query load hadn’t grown nearly as much.

The fix was twofold: reducing each instance’s own pool size to something sane relative to the expected maximum instance count, and — more importantly — routing everything through PgBouncer in transaction mode so the real PostgreSQL-side connection count stayed low and predictable regardless of how many application instances the autoscaler decided to run.

[pgbouncer]
pool_mode = transaction
default_pool_size = 25
max_client_conn = 2000

After the change, pg_stat_activity on the actual PostgreSQL instance stayed comfortably under 50 connections even at 20x the application instance count, because PgBouncer was multiplexing thousands of short client connections down onto that small, stable pool of real connections.

PgBouncer in Front of Multiple Databases

A detail worth calling out: a single PgBouncer instance can front multiple databases, which is convenient when several logical databases live on the same PostgreSQL server.

[databases]
app_primary = host=127.0.0.1 port=5432 dbname=app_primary
app_reporting = host=127.0.0.1 port=5432 dbname=app_reporting
* = host=127.0.0.1 port=5432

The wildcard entry (*) is a convenience that forwards any database name not explicitly listed, using the same connection details — useful in development, though I avoid relying on it in production in favor of explicit entries, since explicit configuration makes capacity planning per database much easier to reason about.

Frequently Asked Questions

Does PgBouncer add noticeable latency? In practice, no — it’s written to be extremely lightweight, and the added hop typically costs a fraction of a millisecond, which is negligible compared to typical query execution time.

Can I run PgBouncer on the same host as PostgreSQL? Yes, and it’s a common setup for smaller deployments. For larger ones, I’ve also seen it run as a sidecar alongside each application instance, or as a dedicated pooling tier — the right topology depends on your scale and failure-isolation preferences.

What happens if PgBouncer itself goes down? All connections through it are lost, so it’s a real point of failure worth planning for — running it in a highly-available pair behind a load balancer, or using a managed pooling service, is worth considering once it’s sitting in a critical path.

Is PgBouncer the only option? No — pgcat and Odyssey are newer alternatives with some additional features (like built-in load balancing across replicas), and some managed database providers offer their own built-in pooling layer. I still reach for PgBouncer by default because of its maturity and the sheer volume of production experience the community has with it, but it’s worth knowing alternatives exist.

Wrapping Up

Connection pooling isn’t an optional nice-to-have for any PostgreSQL deployment beyond a small, low-traffic application — it’s close to a requirement once you have more than a handful of application instances or any kind of bursty traffic pattern. PgBouncer in transaction pooling mode has been the right default for nearly every project I’ve worked on, as long as I take the time upfront to audit for the handful of session-level features that don’t play well with it. Get the pool mode right, size it sensibly, and monitor it, and connection exhaustion stops being the mysterious production incident it once was for me.