Connection pooling (pgbouncer)

Connection management is one of the silent killers of Postgres performance. Apps connect with the assumption that connections are like HTTP requests, cheap and disposable. They are not. Get pooling wrong and your database falls over at 200 concurrent users.

Why connections cost so much

Postgres uses a process-per-connection model (forked from the postmaster). Each connection:

• Is a full OS process
• Allocates around 10MB of private memory (varies with work_mem, prepared statements, caches)
• Maintains its own catalog cache, plan cache, and lock state
• Has a backend that sits idle most of the time

At 100 connections, you have used a gigabyte just on connection overhead. At 1000, around 10GB plus context switch storms.

Postgres has max_connections (default 100) as a hard ceiling. Going above 500 on a single instance is usually a mistake.

pgbouncer architecture

pgbouncer is a single-threaded, event-driven (epoll/kqueue) C program. It speaks the Postgres wire protocol on both sides. To clients it looks like Postgres; to Postgres it looks like a client.

Single-threaded means one CPU core. For higher throughput, run multiple pgbouncer instances behind a load balancer or use pgcat.

The three pooling modes

Session pooling

Client connection is bound to a server connection for the entire session. Released when client disconnects. Provides multiplexing of connection setup cost but not concurrency benefits.

Use when: client uses session features (LISTEN/NOTIFY, server-side cursors, temp tables across transactions).

Transaction pooling

Server connection released back to pool on COMMIT or ROLLBACK. The most common mode.

Restrictions (because session state does not persist):

• SET variables: must use SET LOCAL
• Server-side prepared statements (PREPARE/EXECUTE): break across transactions
• Temporary tables: destroyed at transaction end if outside transaction
• LISTEN/NOTIFY: not delivered if connection released
• Cursors: declared in transaction only (HOLD cursors break)
• Advisory locks: use pg_advisory_xact_lock, not session-scope

Modern PG drivers handle most of this if configured correctly.

Statement pooling

Server connection released after each statement. Breaks all transactions. Almost never used.

Use when: read-only workloads where you do not need transactions.

Pool sizing

The textbook formula (Brandur Leach, Hikari documentation):

pool_size = (cores * 2) + effective_spindle_count

For modern SSDs, effective_spindle_count is 1. For 8-core Postgres, pool of 17. For 32-core, pool of 65.

Why not bigger? Because Postgres is bottlenecked by:

• CPU for parsing, planning, executing
• Disk I/O queue depth
• WAL serialization
• Lock contention

More connections than the bottleneck can serve just means longer queues per connection plus more context switching.

pgbouncer configuration essentials

[databases]
mydb = host=postgres dbname=mydb
 
[pgbouncer]
listen_port = 6432
listen_addr = 0.0.0.0
auth_type = scram-sha-256
auth_file = /etc/pgbouncer/userlist.txt
pool_mode = transaction
max_client_conn = 2000
default_pool_size = 20
reserve_pool_size = 5
reserve_pool_timeout = 3
server_idle_timeout = 600
server_lifetime = 3600

Key settings:

• max_client_conn: total client connections pgbouncer accepts
• default_pool_size: server connections per (user, database) pair
• reserve_pool_size: extra connections if pool exhausted for reserve_pool_timeout
• server_lifetime: recycle server connections to release memory bloat

Multi-tenant pools

default_pool_size is per (user, database). If you have 100 tenants each with their own role, pgbouncer maintains 100 separate pools. Multiply pool_size by tenants to size Postgres max_connections.

For SaaS with many tenants, use a single shared role and SET ROLE inside transactions. Then one pool covers all tenants.

Prepared statements with transaction pooling

The classic pain point. Server-side prepared statements live on a specific backend. With transaction pooling, the next statement may land on a different backend that does not have the prepared statement.

Solutions:

• Disable server-side prepares in driver: works for everyone, gives up cached plans.
• Use pgcat: handles prepared statements across pooled connections, transparently re-prepares.
• Use PgBouncer 1.21+: added support for prepared statements with max_prepared_statements.

Monitoring pgbouncer

Connect to the special pgbouncer database:

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

SHOW POOLS shows per-database stats: active/waiting clients, used/idle server connections, max wait time.

Alert on cl_waiting > 0 consistently and maxwait > 1s ever.

When pgbouncer is not enough

• Need read/write splitting: use HAProxy or app-level routing on top of pgbouncer.
• Need failover: pgbouncer does not handle Postgres failover. Use Patroni plus a load balancer that swaps backends.
• Need prepared statements with transaction pool: switch to pgcat or PgBouncer 1.21+.
• Multi-core scaling: run multiple pgbouncer instances, each on its own port, behind HAProxy or systemd socket activation.

Alternatives

pgcat

Rust, multi-threaded, modern. Supports prepared statements, sharding via SQL comments, load balancing across replicas. Best for read/write splitting and prepared statements.

Odyssey

Yandex's pooler. Multi-threaded, complex config, good performance. Less popular outside Russia.

Supabase Supavisor

Elixir-based, designed for serverless with thousands of clients. HA out of the box.

AWS RDS Proxy

Managed pooler with IAM auth, fail-over awareness, encryption. Costs roughly 1.5x RDS instance price. Worth it if you do not want to operate pgbouncer.

The serverless connection problem

Lambda, Vercel functions, Cloudflare Workers all spin up new connections per request. Without pooling, you exhaust Postgres in seconds.

Solutions:

• External pooler (pgbouncer, RDS Proxy, Supavisor) in front of Postgres
• HTTP-based clients: Neon Serverless Driver, Supabase Edge Functions use HTTP instead of Postgres wire protocol, with pooling on the server side
• Connection reuse via execution context: hold connections across invocations within the same Lambda container

Common pitfalls

• Pool too large: more contention, lower TPS. Start at 2 * cores, only grow if you see waiting clients.
• Session pooling with web app: defeats the purpose. Use transaction.
• Server-side prepares with transaction pool: silent failures or "prepared statement does not exist." Disable in driver or upgrade pooler.
• No pool: 5000 web requests open 5000 connections. Postgres dies.
• Ignoring server_lifetime: connections accumulate bloat. Recycle hourly.

The senior take

Always use a pooler in production. PgBouncer in transaction mode is the default choice and handles 95 percent of workloads. Size your pool to roughly 2 * cores, not "as high as possible." Configure your driver to not use server-side prepares unless you are on PgBouncer 1.21+ or pgcat. For serverless, use a pooler in front; for thousands of clients, run multiple poolers. The goal is the smallest pool that keeps your Postgres CPU busy.