PostgreSQL Connection Pooling: PgBouncer, Supavisor & Built-In

Every PostgreSQL connection creates a new OS process. Each one allocates roughly 5-10 MB of memory. A server with max_connections = 200 might use 2 GB just for connection overhead -- before a single query runs. At 500 connections, you're at 5 GB of memory consumed by connection infrastructure alone.

If you've ever seen "too many connections" errors and responded by increasing max_connections, you've treated the symptom while creating a bigger problem.

Why More Connections Make Things Worse

Modern architectures multiply connections fast. 20 container replicas with 10 connections each = 200 persistent connections. During a rolling deployment, old and new replicas coexist briefly, doubling the count. Serverless functions are worse -- each invocation might open its own connection, creating hundreds of short-lived connections per second.

Each new connection takes 50-100ms to establish (with TLS). PostgreSQL must fork a process, authenticate, load shared catalog data, and negotiate encryption. That's not free.

There are two walls:

1. max_connections exhaustion: application gets "too many connections" errors
2. Memory exhaustion: so much RAM consumed by connection overhead that there's nothing left for shared_buffers, work_mem, and the OS page cache

Connection pooling solves both by maintaining a fixed pool of database connections and sharing them among application connections. A pool of 20 database connections can serve hundreds of application connections because most connections are idle at any moment.

Diagnosing Connection Problems

-- Connection count by state and application
SELECT
    state,
    usename AS username,
    application_name,
    count(*) AS connection_count,
    count(*) FILTER (WHERE state = 'idle') AS idle_connections,
    count(*) FILTER (WHERE state = 'idle in transaction') AS idle_in_transaction
FROM pg_stat_activity
WHERE backend_type = 'client backend'
GROUP BY state, usename, application_name
ORDER BY connection_count DESC;

-- How close are you to max_connections?
SELECT
    count(*) AS active_connections,
    current_setting('max_connections')::int AS max_connections,
    round(100.0 * count(*) / current_setting('max_connections')::int, 1)
        AS utilization_percentage
FROM pg_stat_activity
WHERE backend_type = 'client backend';

Above 70% utilization means you're at risk during traffic spikes. A high idle_connections count relative to total means connections are held open unnecessarily -- a pooler would reclaim these.

-- Find idle-in-transaction connections wasting pool capacity
SELECT
    pid,
    usename,
    application_name,
    state,
    now() - state_change AS idle_duration,
    left(query, 100) AS last_query
FROM pg_stat_activity
WHERE state = 'idle in transaction'
  AND now() - state_change > interval '5 minutes'
ORDER BY state_change;

These are the worst offenders. They hold locks, snapshots, and pool slots while doing absolutely nothing.

PgBouncer Setup (The Standard Choice)

PgBouncer is the most widely deployed PostgreSQL pooler -- lightweight, stable, battle-tested:

; /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 = scram-sha-256
auth_file = /etc/pgbouncer/userlist.txt

; Transaction mode: connection returned after each transaction
pool_mode = transaction

; Pool sizing
default_pool_size = 20
min_pool_size = 5
reserve_pool_size = 5
reserve_pool_timeout = 3

; Connection limits
max_client_conn = 1000
max_db_connections = 50

; Timeouts
server_idle_timeout = 600
query_timeout = 300

The critical setting is pool_mode. Transaction mode gives the best connection reuse but means you can't use session-level features (prepared statements, SET commands, LISTEN/NOTIFY, temporary tables) across transactions.

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

SHOW POOLS;
SHOW STATS;
SHOW SERVERS;

The Pool Sizing Formula

The optimal pool size depends on your hardware, not your application concurrency:

optimal_pool_size = (CPU_cores * 2) + number_of_disks

For 8 cores with SSD (count as 1 disk): ~17 connections. This seems small, but PostgreSQL can only execute as many queries in parallel as it has CPU cores. Beyond that, connections compete for CPU and context switching reduces throughput.

-- Check how many connections are truly active at any time
SELECT
    count(*) FILTER (WHERE state = 'active') AS truly_active,
    count(*) FILTER (WHERE state = 'idle') AS idle,
    count(*) FILTER (WHERE state = 'idle in transaction') AS idle_in_txn,
    count(*) AS total
FROM pg_stat_activity
WHERE backend_type = 'client backend';

If truly_active is consistently below 20, a pool of 20-25 is sufficient regardless of your application instance count.

Supavisor (Cloud-Native Alternative)

Supavisor is Supabase's open-source pooler built in Elixir, designed for multi-tenant cloud environments:

tenants:
  - id: "my-tenant"
    db_host: "localhost"
    db_port: 5432
    db_database: "myapp"
    pool_size: 20
    mode: "transaction"

Key advantages over PgBouncer:

• Multi-threaded (handles more client connections per instance)
• Supports named prepared statements in transaction mode
• Per-tenant pool isolation

Application-Level Pooling

Use it alongside (not instead of) an external pooler:

# Python: SQLAlchemy
engine = create_engine(
    "postgresql://user:password@pgbouncer-host:6432/myapp",
    pool_size=5,
    max_overflow=10,
    pool_timeout=30,
    pool_recycle=3600,
    pool_pre_ping=True,
)

// Node.js: pg
const pool = new Pool({
  host: 'pgbouncer-host',
  port: 6432,
  max: 5,
  idleTimeoutMillis: 30000,
  connectionTimeoutMillis: 5000,
});

Keep the application pool small (5-10 per instance) when connecting through PgBouncer. The application pool handles lifecycle; PgBouncer handles multiplexing.

Preventing Connection Waste

-- Terminate idle-in-transaction connections after 60 seconds
ALTER SYSTEM SET idle_in_transaction_session_timeout = '60s';
SELECT pg_reload_conf();

-- Kill runaway queries after 5 minutes
ALTER SYSTEM SET statement_timeout = '300s';
SELECT pg_reload_conf();

Quick Decision Guide

• PgBouncer: battle-tested, lightweight, works for most deployments
• Supavisor: multi-tenant, cloud-native, supports prepared statements in transaction mode
• pgcat: adds sharding and load balancing
• Application pool only: fine for a single instance with low concurrency, insufficient for anything larger

PostgreSQL does not have built-in connection pooling as of version 17. Every client connection creates a new OS process. Until that changes, an external pooler is essential for any production workload with more than a handful of concurrent users.