Scrolls
5 min read
On this page

Connection Pooling

Why Every Connection Costs You

PostgreSQL creates a separate operating system process for every client connection. Each process allocates memory for its own working area, query parsing structures, catalog caches, and sort buffers. A single idle connection consumes 5-10 MB of RAM. An active connection running complex queries can consume hundreds of megabytes.

At 50 connections, this is manageable. At 500 connections, you have consumed 2.5-5 GB just for connection overhead before any queries run. At 1000 connections, Postgres performance degrades significantly due to lock contention on shared data structures, context switching between hundreds of processes, and snapshot management overhead.

The solution is not to increase max_connections. The solution is connection pooling.

The Math That Justifies Pooling

A typical web request holds a database connection for 5-50 ms. If your average request takes 20 ms of database time, a single database connection can handle 50 requests per second. A pool of 20 connections handles 1000 requests per second.

Most applications open far more connections than they actually use concurrently. A web server with 100 worker threads might have 100 database connections open, but only 10-20 are executing queries at any given moment. The rest are idle, consuming resources for nothing.

Without pooling:
  100 app workers * 4 app instances = 400 database connections
  400 connections * 10 MB = 4 GB of overhead

With pooling:
  PgBouncer pool of 30 connections
  30 connections * 10 MB = 300 MB of overhead
  Handles the same 400 concurrent app workers

PgBouncer: The Standard

PgBouncer is a lightweight connection pooler that sits between your application and PostgreSQL. It multiplexes many client connections onto a smaller number of server connections.

# /etc/pgbouncer/pgbouncer.ini
[databases]
myapp = host=127.0.0.1 port=5432 dbname=myapp

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

# Pool settings
pool_mode = transaction
default_pool_size = 25
max_client_conn = 1000
min_pool_size = 5

# Timeouts
server_idle_timeout = 600
client_idle_timeout = 0
query_timeout = 30

# /etc/pgbouncer/userlist.txt
"app_user" "md5hash_of_password"

Applications connect to PgBouncer on port 6432 instead of connecting to PostgreSQL on port 5432. PgBouncer transparently manages the actual database connections.

Transaction Mode vs Session Mode

Transaction mode (recommended): A server connection is assigned to a client only for the duration of a transaction. Between transactions, the connection returns to the pool. This provides maximum multiplexing.

pool_mode = transaction

In transaction mode, session-level features do not work across transactions:

-- These break in transaction mode (state is lost between transactions)
SET search_path TO myschema;        -- Lost after transaction ends
PREPARE my_plan AS SELECT ...;      -- Lost after transaction ends
DECLARE my_cursor CURSOR FOR ...;   -- Lost after transaction ends
CREATE TEMP TABLE ...;              -- Lost after transaction ends
LISTEN channel;                     -- Does not work

Session mode: A server connection is assigned to a client for the entire session. This supports all PostgreSQL features but provides less multiplexing.

pool_mode = session

Use session mode only when your application requires session-level state (prepared statements, temp tables, LISTEN/NOTIFY). For most web applications, transaction mode is correct.

Monitoring PgBouncer

# Connect to PgBouncer admin console
psql -p 6432 -U pgbouncer pgbouncer

# Show pool statistics
SHOW POOLS;

 database | user     | cl_active | cl_waiting | sv_active | sv_idle | sv_used | pool_mode
----------+----------+-----------+------------+-----------+---------+---------+------------
 myapp    | app_user |        45 |          0 |        12 |      13 |       0 | transaction

Key metrics:

  • cl_waiting > 0: Clients are waiting for a connection. Increase pool size.
  • sv_active: Server connections currently executing queries.
  • sv_idle: Server connections available in the pool.
# Show per-connection statistics
SHOW STATS;
SHOW SERVERS;
SHOW CLIENTS;

Connection Limits: Less Is More

With connection pooling, you need far fewer actual database connections than you think.

Rule of thumb:
  Pool size = 2-3x the number of CPU cores on the database server

  8-core server:  pool size of 16-24
  16-core server: pool size of 32-48
  32-core server: pool size of 64-96

More connections does not mean more throughput. Beyond a certain point, adding connections increases contention and decreases total throughput. The optimal pool size is often surprisingly small.

-- On the Postgres side, keep max_connections reasonable
-- max_connections = pool_size * number_of_pools + admin_connections + replication
ALTER SYSTEM SET max_connections = 100;

Alternatives: Supavisor & pgcat

Supavisor is a Postgres connection pooler built in Elixir, developed by Supabase. It supports multi-tenancy and is designed for cloud-native deployments.

pgcat is a Postgres pooler and proxy written in Rust. It supports sharding, load balancing across replicas, and connection pooling. It is a newer alternative that combines pooling with query routing.

Both are drop-in replacements for PgBouncer with additional features. The choice depends on your specific needs: PgBouncer for simplicity, pgcat for sharding and load balancing, Supavisor for multi-tenant cloud deployments.

The Thundering Herd Problem

When a connection pooler restarts, all client connections are dropped. Every client immediately attempts to reconnect. If you have 500 application workers, they all try to establish connections simultaneously. The pooler and database get slammed with 500 connection requests at once.

Normal operation:
  App workers: [connected] [connected] [connected] ...
  PgBouncer:   [running, 25 server connections]

PgBouncer restart:
  App workers: [reconnecting] [reconnecting] [reconnecting] ... (all 500)
  PgBouncer:   [starting, 0 server connections, 500 clients queuing]
  PostgreSQL:  [25 new connections created simultaneously]

Mitigation strategies:

# PgBouncer: use graceful restart when possible
# This drains existing connections before stopping
kill -SIGHUP $(cat /var/run/pgbouncer/pgbouncer.pid)

# Application: implement exponential backoff on connection errors
# Don't retry immediately; wait 100ms, then 200ms, then 400ms...

# Application: use connection retry with jitter
# Add random delay so not all clients retry at the same instant

In your application code, always handle connection failures gracefully:

-- Application-level connection retry logic (pseudocode)
-- retry_delay = base_delay * (2 ^ attempt) + random_jitter
-- attempt 1: 100ms + random(0-50ms)
-- attempt 2: 200ms + random(0-50ms)
-- attempt 3: 400ms + random(0-50ms)

Application-Level Pooling

Most database drivers and ORMs include built-in connection pools. These manage a pool of connections from the application to the database (or to PgBouncer).

-- SQLx connection pool in Rust (conceptual)
-- PgPoolOptions::new()
--     .max_connections(5)     -- 5 connections per app instance
--     .min_connections(1)
--     .acquire_timeout(Duration::from_secs(3))
--     .idle_timeout(Duration::from_secs(600))
--     .connect("postgres://user:pass@pgbouncer:6432/myapp")

The typical architecture layers the pools:

App Instance 1 (5 connections) ──┐
App Instance 2 (5 connections) ──┤
App Instance 3 (5 connections) ──┼──→ PgBouncer (pool of 25) ──→ PostgreSQL
App Instance 4 (5 connections) ──┤
App Instance 5 (5 connections) ──┘

Each app instance has a small pool (3-5 connections). PgBouncer multiplexes all application pools onto a smaller number of actual database connections. This two-tier approach handles thousands of application workers with minimal database load.

Common Pitfalls

  • Increasing max_connections instead of adding a pooler. This is the single most common mistake. 500 direct connections to Postgres will perform worse than 25 pooled connections. Always pool.
  • Setting pool size too large. A pool of 200 connections is almost never necessary and defeats the purpose. Start with 2-3x your CPU core count and measure.
  • Using session mode when transaction mode works. Transaction mode provides much better multiplexing. Only use session mode if your application actually requires session-level state.
  • Not monitoring pool utilization. If cl_waiting in PgBouncer is consistently above zero, your pool is too small. If sv_idle is consistently high, your pool is too large.
  • Forgetting about PgBouncer in failover plans. When the database fails over to a standby, PgBouncer must be reconfigured to point to the new primary. Include this in your DR runbook.
  • Application pools competing with each other. If you have 20 app instances each with a pool of 20, that is 400 connections hitting PgBouncer. Size your app-level pools so the total does not overwhelm the pooler.
  • Not handling connection errors gracefully. When PgBouncer restarts or the database fails over, connections break. Application code must retry with backoff and jitter.

Key Takeaways

  • Every Postgres connection is a process that consumes memory and CPU. Connection pooling is essential for any production deployment.
  • PgBouncer in transaction mode is the standard solution. It multiplexes hundreds of client connections onto a small pool of database connections.
  • Pool size should be 2-3x the database server's CPU core count. More connections does not mean more throughput.
  • Transaction mode is correct for most web applications. Session mode is needed only for features like prepared statements, temp tables, or LISTEN/NOTIFY.
  • The thundering herd problem occurs when a pooler restarts. Mitigate with graceful restarts, exponential backoff, and jitter in application retry logic.
  • Two-tier pooling (application pool to PgBouncer to PostgreSQL) provides the best resource efficiency for multi-instance deployments.