Connection Pooling: Why PgBouncer Exists and How It Works

Why Every PostgreSQL Connection Costs You 10 MB of RAM

PostgreSQL forks a new operating system process for every client connection. Each process consumes 5-10 MB of RAM just sitting idle. With 200 concurrent connections, that's 1-2 GB of RAM doing nothing. With 1,000 connections -- common in microservice architectures -- you're burning 5-10 GB before a single query runs. Connection pooling with PgBouncer fixes this by multiplexing hundreds or thousands of application connections over a small number of actual PostgreSQL connections.

I've seen PgBouncer drop a PostgreSQL server's memory usage by 80% and its connection latency by an order of magnitude. It's one of the highest-impact, lowest-effort infrastructure changes you can make. Here's how it works, how to configure it, and the gotchas that trip people up.

What Is Connection Pooling?

Definition: Connection pooling is a technique where a proxy sits between your application and the database, maintaining a pool of pre-established database connections. When your application needs a connection, the pool hands over an idle one instead of opening a new one. When the application is done, the connection returns to the pool rather than being closed.

Without a pooler, every web request that touches the database opens a new PostgreSQL connection (fork a process, authenticate, allocate memory) and closes it when done. The overhead of connection setup is 50-100ms on a cold start. With a pooler, connections are already open and ready. The application gets a connection in under 1ms.

PostgreSQL's Process-Per-Connection Model

Understanding why pooling matters requires understanding PostgreSQL's architecture. Unlike MySQL (which uses threads), PostgreSQL forks a new process for each connection. This design is robust -- a crash in one backend doesn't affect others -- but it's expensive.

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

Beyond memory, each process needs CPU time for context switching. At 500+ connections, the Linux scheduler spends significant time just switching between PostgreSQL backends, even if most are idle. The sweet spot for most PostgreSQL servers is 2-4x the number of CPU cores for active connections -- typically 20-100 connections handling thousands of requests per second.

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

Why does PostgreSQL need connection pooling?

PostgreSQL forks a new operating system process for every connection, consuming 5-10 MB of RAM each. At hundreds of connections, memory consumption becomes significant, and OS context switching degrades performance. Connection pooling reuses a small number of database connections across many application clients, reducing overhead by 80-90%.

What is the difference between PgBouncer session and transaction mode?

In session mode, PgBouncer assigns a server connection for the entire client session -- equivalent to no pooling benefit if clients stay connected. In transaction mode, the server connection is returned to the pool after each transaction completes, allowing many more clients to share fewer database connections. Transaction mode is recommended for most web applications.

Do prepared statements work with PgBouncer?

Not in transaction mode, because the prepared statement exists on a specific server connection, but the next transaction may use a different one. PgBouncer 1.21+ added protocol-level prepared statement support that translates named prepared statements. Alternatively, use session mode, disable prepared statements in your ORM, or switch to pgcat which handles them natively.

How many database connections should I set for PgBouncer?

Set the pool size to 2-4x your PostgreSQL server's CPU core count. A 4-core server works well with 8-16 real connections. Your application can open hundreds or thousands of connections to PgBouncer, which multiplexes them onto this smaller pool. Monitor SHOW POOLS and increase if you see clients waiting.

Should I use PgBouncer or pgpool-II?

Use PgBouncer if you just need connection pooling -- it's simpler, lighter, and faster. Use pgpool-II if you also need built-in load balancing across read replicas or connection-level failover. For most teams, PgBouncer plus a separate load balancer (HAProxy or Patroni) is a cleaner architecture than pgpool-II's monolithic approach.

How does connection pooling work in Kubernetes?

In Kubernetes, deploy PgBouncer as a centralized Deployment with a Service, not as a sidecar per pod. Sidecars multiply the total pool size by the number of pods, which can overwhelm the database during autoscaling events. A centralized pooler maintains a fixed pool to PostgreSQL regardless of how many application pods are running.

What is the overhead of running PgBouncer?

PgBouncer is extremely lightweight. It's single-threaded and typically uses less than 50 MB of RAM and negligible CPU, even handling thousands of connections. The main cost is operational: another service to deploy, monitor, and maintain. On managed PostgreSQL platforms with built-in pooling, there's no additional overhead at all.