PostgreSQL Connection Pooling with PgBouncer: A Complete Guide

You launch your app. Traffic is light, everything works. A few weeks later you start seeing FATAL: remaining connection slots are reserved for non-replication superuser connections. Your PostgreSQL server is out of connections and your app is falling over.

This is one of the most common PostgreSQL scaling problems, and connection pooling is the fix. But the fix has its own complexity: PgBouncer has three modes with different tradeoffs, the configuration is full of footguns, and if you get it wrong you get subtle bugs that are much harder to debug than the original connection error.

This guide covers how PostgreSQL connections actually work, how to set up and configure PgBouncer correctly, and how to choose the right pool mode for your application.

Why PostgreSQL Connections Are Expensive

PostgreSQL handles each connection with a dedicated server process. When a client connects, Postgres forks a new OS process. That process:

• Allocates its own memory (typically 5-10 MB per connection including shared memory overhead)
• Maintains its own backend state, transaction state, and lock tables
• Requires the kernel to schedule it like any other process

At 50 connections, this is fine. At 500 connections, you have 500 OS processes and the scheduler starts showing up in your performance profiles. At 1,000 connections, you are likely hitting the max_connections limit (default 100 in stock PostgreSQL) and your app is returning errors.

The naive fix is to increase max_connections. Don't do that without thinking it through. Each connection costs memory. Set max_connections = 1000 on a server with 8 GB of RAM and you've allocated the entire heap to idle connections before a single query runs. The shared_buffers and work_mem math goes sideways fast.

The right fix is to reduce the number of actual connections to PostgreSQL. That's what connection poolers do.

What PgBouncer Does

PgBouncer sits between your application and PostgreSQL. Your app thinks it's talking to Postgres, but it's actually talking to PgBouncer. PgBouncer maintains a pool of real connections to Postgres and hands them out to client requests.

The numbers look like this in practice:

• Before PgBouncer: 300 app threads, 300 Postgres connections
• After PgBouncer (transaction mode): 300 app threads, 20 actual Postgres connections

Those 20 connections serve 300 clients because most clients are not actually executing SQL at any given moment. They're waiting for network I/O, processing results, or sitting idle. Transaction mode takes advantage of this by returning a connection to the pool the moment a transaction commits.

Installing PgBouncer

On Ubuntu/Debian:

sudo apt-get install pgbouncer

On macOS with Homebrew:

brew install pgbouncer

The main config file lives at /etc/pgbouncer/pgbouncer.ini on most Linux installs.

Configuring PgBouncer

A minimal working config looks like this:

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

[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 = 1000
default_pool_size = 25
min_pool_size = 5
reserve_pool_size = 5
server_idle_timeout = 600
client_idle_timeout = 0
log_connections = 0
log_disconnections = 0

The userlist.txt file holds the credentials PgBouncer uses to authenticate clients. Format is:

"myuser" "mypassword"

For production, use hashed passwords. Generate them with:

SELECT concat('"', rolname, '" "', rolpassword, '"')
FROM pg_authid
WHERE rolname = 'myuser';

Start the service:

sudo systemctl start pgbouncer
sudo systemctl enable pgbouncer

Your app now connects to port 6432 instead of 5432. Nothing else changes in the app code.

The Three Pool Modes

This is where most guides gloss over the important part. PgBouncer has three pool modes and they have meaningfully different semantics:

Session Mode

A server connection is assigned to a client when the client connects and held until the client disconnects. This is the safest mode because it behaves identically to a direct PostgreSQL connection. Prepared statements, SET LOCAL, advisory locks, and everything else works exactly as you'd expect.

Session mode does not help much with connection counts. If you have 300 clients connected, you have 300 server connections. The benefit is mostly that PgBouncer handles connection queueing for you, so clients don't get rejected when the pool is full. They wait in line instead.

Use session mode when you need full PostgreSQL compatibility and your connection count problem is primarily about peaks rather than steady-state load.

Transaction Mode

A server connection is assigned for the duration of a transaction and returned to the pool immediately after. This is the most commonly used mode and the one that gives you the dramatic reduction in server connections.

The tradeoff is that session-level state does not persist across transactions. This breaks a surprising number of things:

• PREPARE and prepared statement caching (per-session)
• SET commands that are not wrapped in a transaction (session-level by default)
• Advisory locks (session-scoped)
• LISTEN and NOTIFY (session-level subscriptions)
• Temp tables that are supposed to persist across transactions
• pg_cancel_backend() and pg_terminate_backend() become unreliable because the server PID you see may have been reassigned

Transaction mode works well for stateless web applications using connection pools like pg in Node.js, psycopg2/psycopg3 in Python, or JDBC in Java, as long as those frameworks don't use session-level features.

Statement Mode

A server connection is held only for the duration of a single SQL statement, then returned. This is the most restrictive mode and breaks multi-statement transactions entirely. It's rarely the right choice for web applications.

Statement mode can work for simple analytics or read replicas where every query is a standalone SELECT. But if your app uses BEGIN/COMMIT at all, statement mode will break things in confusing ways.

Choosing Your Mode

The practical guidance:

Your app	Recommended mode
Stateless API using ORM (Django, Rails, Prisma)	Transaction
Long-lived connections with prepared statements (custom JDBC apps)	Session
Connection count issues at peak only	Session
Connection count issues at steady state	Transaction
Serverless functions with very short-lived connections	Transaction
Application using LISTEN/NOTIFY	Session (or bypass the pooler for that connection)

When in doubt, start with transaction mode. If you see weird failures around session state, check whether your library or ORM is using session-level features.

Pool Sizing

Getting the default_pool_size right matters. Too small and clients queue up and add latency. Too large and you've defeated the purpose of pooling.

A reasonable starting formula:

default_pool_size = (number of PostgreSQL CPU cores) * 2 + number of disks

For a 4-core PostgreSQL server with fast SSDs, this gives you roughly 10-12. In practice, 20-30 works well for most web applications. If you're running heavy analytics queries that hold connections for seconds, go lower. If you're mostly doing fast OLTP queries (sub-millisecond each), you can go higher.

You can check how your pool is being used while the system is running:

SHOW POOLS;

This returns something like:

database | user    | cl_active | cl_waiting | sv_active | sv_idle | sv_used | maxwait
---------+---------+-----------+------------+-----------+---------+---------+--------
mydb     | myuser  | 18        | 2          | 18        | 7       | 0       | 0

cl_waiting > 0 for any sustained period means your pool is undersized. sv_idle is consistently high means it's oversized. You want sv_idle to be nonzero (headroom) but not enormous.

Monitoring PgBouncer

Connect to PgBouncer's admin console (by default, the virtual pgbouncer database on port 6432):

psql -h 127.0.0.1 -p 6432 -U pgbouncer pgbouncer

Useful commands:

-- Pool status
SHOW POOLS;

-- Active client connections
SHOW CLIENTS;

-- Active server connections
SHOW SERVERS;

-- Statistics (requests, bytes, query time)
SHOW STATS;

-- Reload config without restart
RELOAD;

SHOW STATS is especially useful for spotting problems. If avg_query_time is climbing or avg_wait_time is nonzero, something is backing up.

For long-running production systems, export these metrics to your observability stack. PgBouncer exposes them via its admin interface; you can scrape them with a Prometheus exporter like pgbouncer_exporter and alert on cl_waiting > 0 sustained for more than a few seconds.

Common Pitfalls

Prepared Statements in Transaction Mode

If your application uses server-side prepared statements (not client-side), transaction mode will break it. The PREPARE command creates a prepared statement for the current session, and in transaction mode, that session may not be the one executing the next request.

The fix is either:

1. Switch to session mode
2. Disable server-side prepared statements in your driver. In psycopg2, pass prepare_threshold=None. In pg (Node.js), use { prepare: false }. ORMs often have this as a config option.

Timeouts and Long-Running Queries

PgBouncer's server_idle_timeout controls how long an idle server connection is kept. Setting this too low means connections are constantly being created and torn down. Too high and you may have stale connections.

Set server_idle_timeout to match your application's expected idle time. For web APIs that get consistent traffic, 600 seconds (10 minutes) is a reasonable default.

For long-running analytics queries, watch out for query_timeout in PgBouncer config. If you set it too aggressively it will kill legitimate long queries. Set it in PostgreSQL directly with statement_timeout per role if you need different limits for OLTP vs analytics.

Transaction Mode and Autocommit

In Python with psycopg2, connections are not in autocommit by default. Every query implicitly starts a transaction. This is fine for PgBouncer transaction mode. But if you are using a framework that calls SET AUTOCOMMIT ON at the session level, that SET command will not persist across transactions in transaction mode. The fix is to use autocommit=True in the connection options.

Connection Storms at Startup

When your application restarts and all workers reconnect simultaneously, PgBouncer can briefly queue hundreds of connection requests. If reserve_pool_size is 0 and max_client_conn is hit, some requests will be rejected.

Set a small reserve_pool_size (5-10) as a buffer, and make sure max_client_conn is set to at least 2-3x your expected peak. PgBouncer can handle thousands of client connections comfortably; it's the server side that needs protecting.

PgBouncer with SSL

For production, you should use SSL for both client-to-PgBouncer and PgBouncer-to-PostgreSQL connections.

Client-to-PgBouncer:

[pgbouncer]
server_tls_sslmode = require
server_tls_ca_file = /etc/ssl/certs/ca-certificates.crt
client_tls_sslmode = require
client_tls_cert_file = /etc/pgbouncer/server.crt
client_tls_key_file = /etc/pgbouncer/server.key

PgBouncer-to-PostgreSQL:

[databases]
mydb = host=postgres.example.com port=5432 dbname=mydb sslmode=require

On a managed PostgreSQL service, SSL is usually enforced at the server level and the connection string will already include the right sslmode. Check your provider's docs for the CA certificate format.

When to Skip PgBouncer

PgBouncer adds operational complexity. It's a process to monitor, config to maintain, a potential failure point. If you don't need it, don't add it.

You probably don't need PgBouncer if:

• Your application has fewer than 50 concurrent connections at peak
• You're using a framework or ORM that does client-side connection pooling (most modern ORMs do this already, including Prisma, Django, and Rails' Active Record)
• You're running serverless workloads where each function instance uses at most 1-2 connections and you keep the pool small

You need PgBouncer (or an equivalent) if:

• Your application has multiple processes or pods each maintaining their own connection pool, and the total exceeds PostgreSQL's max_connections
• You're running a high-traffic API where connection overhead is measurable
• You're on a resource-constrained PostgreSQL instance where each connection's memory cost matters

For more on sizing production PostgreSQL properly, the PostgreSQL high availability guide covers the operational side of running Postgres in production, including replication and failover.

PgBouncer vs Built-in Connection Pools

Some PostgreSQL managed services include connection pooling as a feature. Instead of running your own PgBouncer, you connect to a pooled endpoint and the service handles the rest. This is the approach Rivestack takes: every Rivestack instance comes with a built-in PgBouncer-compatible pooled endpoint running in transaction mode, sized for the instance's compute.

The practical difference: you don't manage another process, there's no config file to maintain, and the pool size is tuned automatically to your instance's resources. The semantics are identical to running PgBouncer yourself in transaction mode, including all the same caveats about session-level state.

Whether you're running PgBouncer yourself or using a managed service that includes it, the underlying principles are the same. Understanding how the pool works, what transaction mode does and doesn't support, and how to monitor pool utilization applies either way.

For more on pgvector specifically and how connection pooling interacts with vector search workloads, see the pgvector getting started guide and the pgvector vs Pinecone comparison.

Bottom Line

PostgreSQL connection pooling is not optional at scale. If you have more than ~50 concurrent active connections, you need a pooler.

PgBouncer in transaction mode is the right default for most web applications. Session mode is the right choice when you need full PostgreSQL session compatibility. Test your application with transaction mode first; most modern ORMs and frameworks work correctly with it.

The main things to get right:

1. Pool size: start at 20-30 for OLTP workloads, adjust based on SHOW POOLS data
2. Mode: transaction for stateless apps, session for anything using prepared statements or advisory locks
3. Monitoring: watch cl_waiting and avg_wait_time, alert if either is nonzero for more than a few seconds
4. SSL: always, for both client-to-PgBouncer and PgBouncer-to-PostgreSQL in production

Connection pooling is unglamorous infrastructure, but it's the difference between an app that falls over at traffic spikes and one that just handles them.

Try Rivestack

If you want PostgreSQL with connection pooling already configured, Rivestack includes a built-in PgBouncer-compatible pooler on every instance. No extra process to run, no config file to tune. Get started free and connect to the pooled endpoint with the same connection string format you use today.