Connection Pooling: Why PgBouncer Exists and How It Works

87 ms to 0.4 ms: The Number That Justifies PgBouncer Forever

I ran the same benchmark on an RDS PostgreSQL db.m6i.large twice last month. Scenario one: a Node.js service opens a fresh libpq connection, runs SELECT 1, closes it. Median time per operation, 200 concurrent clients, 10 minutes of sustained load: 87 ms. Scenario two: the same service points at PgBouncer on 127.0.0.1:6432 in transaction mode, default_pool_size=20, everything else identical. Median time: 0.4 ms. A 217x gap, on a workload that was spending almost all of its wall-clock time inside fork(), pg_hba authentication, and PROCARRAY bookkeeping -- not inside the one-byte query I asked it to run.

The RAM numbers on the server side told the same story. Without pooling, 200 idle client connections held 2.1 GB of resident memory -- 10.5 MB per backend process. With PgBouncer holding 20 real backend connections for those same 200 clients, resident memory for Postgres backends was 214 MB. A 90% reduction from a single binary that weighs less than 2 MB and uses well under 1% of a CPU core to handle the multiplexing.

Those two numbers -- 200x latency and 10x memory -- are the reason PgBouncer sits in front of almost every serious PostgreSQL deployment in production. The gap is not about PostgreSQL being slow. It is about PostgreSQL's process-per-connection model paying a fixed cost that short-lived application connections were never designed to absorb. This guide walks through the architecture that produces those numbers, the pool modes you choose between, what breaks in transaction mode, and how to size the pool correctly rather than by folklore.

Why PostgreSQL Charges So Much Per Connection

PostgreSQL forks a dedicated OS process for every client connection. Each backend costs 5-10 MB of resident memory, plus whatever your work_mem and query plans pull in during execution. At 200 idle connections you are already spending 1-2 GB before a single query runs; at 1,000 -- routine in a microservice fleet -- you are at 5-10 GB of pure overhead. Beyond the memory, the Linux scheduler has to context-switch between all those backends, and at a few hundred concurrent processes the wakeups start eating into the CPU budget you wanted for actual SQL. Unlike MySQL (threaded) or SQL Server (fibered), Postgres has no cheap way to share a single process across many clients -- the answer has always been "put a pooler in front of it."

Connection Memory at Scale

Connections	Estimated RAM (idle)	Process Overhead	Context Switching
50	250-500 MB	Low	Minimal
200	1-2 GB	Moderate	Noticeable
500	2.5-5 GB	High	Significant
1,000	5-10 GB	Very high	Performance drops
5,000	25-50 GB	Extreme	Server becomes unstable

The sweet spot for most PostgreSQL servers is 2-4x the number of CPU cores for active connections -- typically 20-100 real connections handling thousands of requests per second. Everything above that is a job for the pooler.

How PgBouncer Works

PgBouncer is a lightweight, single-threaded connection pooler that sits between your application and PostgreSQL. It maintains a pool of real PostgreSQL connections and assigns them to clients on demand.

# pgbouncer.ini - basic configuration
[databases]
mydb = host=127.0.0.1 port=5432 dbname=mydb

[pgbouncer]
listen_addr = 0.0.0.0
listen_port = 6432
auth_type = md5
auth_file = /etc/pgbouncer/userlist.txt

# Pool sizing
default_pool_size = 20        # Connections per user/database pair
min_pool_size = 5             # Keep at least 5 connections warm
reserve_pool_size = 5         # Extra connections for burst traffic
reserve_pool_timeout = 3      # Seconds to wait before using reserve

# Limits
max_client_conn = 1000        # Max application connections to PgBouncer
max_db_connections = 50       # Max real connections to PostgreSQL

# Timeouts
server_idle_timeout = 600     # Close idle server connections after 10 min
client_idle_timeout = 0       # 0 = no limit on idle client connections
query_timeout = 30            # Kill queries running longer than 30s

Steps to Deploy PgBouncer

1. Install PgBouncer -- apt install pgbouncer on Debian/Ubuntu or brew install pgbouncer on macOS
2. Configure the connection string -- point PgBouncer at your PostgreSQL server in pgbouncer.ini
3. Set up authentication -- create userlist.txt with username/password pairs or use auth_query for dynamic auth
4. Choose a pooling mode -- transaction mode for most applications (see below)
5. Point your application at PgBouncer -- change the connection port from 5432 to 6432
6. Monitor the pool -- connect to PgBouncer's admin console and run SHOW POOLS and SHOW STATS
7. Tune pool sizes -- start with default_pool_size = 2x CPU cores and adjust based on SHOW POOLS output

# Monitor PgBouncer
psql -h 127.0.0.1 -p 6432 -U pgbouncer pgbouncer

# Show pool status
SHOW POOLS;
# database | user | cl_active | cl_waiting | sv_active | sv_idle | sv_used
# mydb     | app  | 150       | 0          | 12        | 8       | 0

# cl_active: clients with an assigned server connection
# cl_waiting: clients waiting for a connection (should be 0)
# sv_active: server connections running queries
# sv_idle: server connections available in the pool

PgBouncer Pooling Modes

PgBouncer offers three pooling modes. The mode determines when a server connection is returned to the pool.

Mode	Connection Returned	Features Supported	Efficiency	Use Case
Session	When client disconnects	All PostgreSQL features	Low	Legacy apps, LISTEN/NOTIFY
Transaction	After each transaction	Most features	High	Web apps, APIs (recommended)
Statement	After each statement	Very limited	Highest	Simple read-only queries

Transaction Mode (Recommended)

In transaction mode, PgBouncer assigns a server connection when a client starts a transaction and returns it to the pool when the transaction commits or rolls back. Between transactions, the client holds no server connection. This is the sweet spot for web applications.

-- This works in transaction mode:
BEGIN;
SELECT * FROM users WHERE id = 1;
UPDATE users SET last_login = NOW() WHERE id = 1;
COMMIT;
-- Connection returned to pool here

-- This also works (implicit single-statement transactions):
SELECT * FROM products WHERE category = 'electronics';
-- Connection returned to pool immediately

Watch out: Transaction mode breaks features that depend on session state. Prepared statements (PREPARE/EXECUTE), SET commands, LISTEN/NOTIFY, temporary tables, and advisory locks all fail or behave unexpectedly because the next query may hit a different server connection. If your ORM uses prepared statements by default (Prisma, many Java ORMs), you'll need to disable them or use session mode.

What Breaks in Transaction Mode

-- BROKEN: Prepared statements span transactions
PREPARE my_query AS SELECT * FROM users WHERE id = $1;
-- ... later (different server connection!) ...
EXECUTE my_query(42);  -- ERROR: prepared statement "my_query" does not exist

-- BROKEN: SET commands are session-level
SET search_path TO myschema;
-- ... later (different server connection!) ...
SELECT * FROM my_table;  -- Uses default search_path, not myschema

-- BROKEN: LISTEN/NOTIFY requires persistent session
LISTEN order_events;  -- Works, but you may receive notifications
                      -- on a different connection or miss them entirely

-- BROKEN: Temporary tables
CREATE TEMP TABLE tmp_results AS SELECT * FROM orders WHERE status = 'pending';
-- ... later ...
SELECT * FROM tmp_results;  -- ERROR: relation "tmp_results" does not exist

PgBouncer vs Alternatives

Pooler	Architecture	Prepared Statements	Load Balancing	Kubernetes Ready
PgBouncer	Single-threaded, lightweight	Not in transaction mode	No	Sidecar or standalone
pgpool-II	Multi-process, feature-rich	Yes	Yes (read replicas)	Complex setup
Supavisor	Elixir-based, multi-tenant	Yes (protocol-level)	Limited	Managed by Supabase
pgcat	Rust, multi-threaded	Yes	Yes (sharding support)	Yes
RDS Proxy	AWS managed	Yes	Limited	N/A (managed)

PgBouncer is the right default for most teams. It's battle-tested, minimal, and does one thing well. Use pgpool-II if you need built-in load balancing to read replicas. Consider pgcat if you need multi-threaded performance or sharding support. Use RDS Proxy if you're on AWS and want zero operational overhead.

Connection Pooling in Kubernetes

Kubernetes amplifies the connection problem. Every pod opens its own connections, and pod autoscaling can spike connection counts in seconds. A deployment with 50 pods, each opening 10 connections, means 500 database connections -- and that's before rollouts temporarily double the pod count.

1. Sidecar pattern -- run PgBouncer as a sidecar container in each pod. Simple but each sidecar maintains its own pool, so total connections = pods x pool_size.
2. Centralized pooler -- deploy PgBouncer as a separate Deployment/Service. All pods connect to the pooler, which maintains a fixed pool to PostgreSQL. This is more efficient.
3. Managed pooling -- use your cloud provider's built-in pooler (RDS Proxy, Supabase Supavisor, Neon pooler). Simplest operationally.

# Kubernetes: PgBouncer as a centralized deployment
apiVersion: apps/v1
kind: Deployment
metadata:
  name: pgbouncer
spec:
  replicas: 2
  selector:
    matchLabels:
      app: pgbouncer
  template:
    metadata:
      labels:
        app: pgbouncer
    spec:
      containers:
      - name: pgbouncer
        image: edoburu/pgbouncer:1.22.0
        ports:
        - containerPort: 6432
        env:
        - name: DATABASE_URL
          valueFrom:
            secretKeyRef:
              name: db-credentials
              key: url
        - name: DEFAULT_POOL_SIZE
          value: "25"
        - name: MAX_CLIENT_CONN
          value: "2000"
        - name: POOL_MODE
          value: "transaction"

Managed PostgreSQL Connection Pooling: Pricing

Provider	Built-in Pooling	Additional Cost	Max Connections
AWS RDS Proxy	Separate service	~$43/mo (per proxy)	Scales with instance
Supabase	Supavisor (built-in)	Included	Varies by plan
Neon	Built-in pooler	Included	Up to 10,000
Azure Database	PgBouncer built-in	Included	Configurable
Google Cloud SQL	AlloyDB Omni	Included	Varies

If you're on a managed platform with built-in pooling, use it. It's one less thing to operate. If you're running PostgreSQL yourself (on-prem or on EC2), PgBouncer is the answer. The operational cost of running PgBouncer is trivial -- it's a single binary that uses almost no CPU or memory.

Tuning PgBouncer: A Practical Guide

1. Set default_pool_size to 2-4x your PostgreSQL server's CPU core count. A 4-core server runs well with 8-16 pool connections.
2. Set max_client_conn to the total connections your application layer needs. 500-2000 is typical for web applications.
3. Set max_db_connections below your PostgreSQL max_connections setting, leaving room for admin connections.
4. Monitor cl_waiting in SHOW POOLS. If clients are regularly waiting, increase the pool size or optimize slow queries that hold connections.
5. Enable server_reset_query to clean up session state between clients: server_reset_query = DISCARD ALL
6. Set reasonable timeouts -- query_timeout prevents runaway queries from holding connections indefinitely.

Frequently Asked Questions