Connection Pooling and pgBouncer

Connections are not free

Each Postgres backend connection forks a process and consumes ~10MB of RAM. Production Postgres typically caps connections at 100-200 — going higher hurts more than it helps. Modern web stacks (serverless functions, Lambda, Vercel) can spawn thousands of concurrent processes, each wanting its own connection. Math doesn't work.

The connection pooler

A connection pooler sits between your application and Postgres. Application processes connect to the pooler (cheap); the pooler maintains a small pool of real Postgres connections and multiplexes traffic. pgBouncer is the canonical tool. Most managed providers ship a pooler.

Pool modes

Session pooling: connection is held for an entire client session. Limited multiplexing; safest semantics.
Transaction pooling: connection is held only for the duration of a transaction. Far better multiplexing; loses session-level features (SET, prepared statements, LISTEN). The default for serverless.
Statement pooling: connection released after each statement. Most aggressive; loses transactions. Rarely used.

Code

Pool size math·text

Goal: support 1000 concurrent app processes against Postgres max_connections=100.

Direct connections: 1000 / 100 = 10× over capacity → connection storm, errors.

With pgBouncer (transaction mode):
  - 1000 app processes connect to pgBouncer (cheap).
  - pgBouncer maintains 100 real Postgres connections.
  - Average transaction is 10ms → effective throughput 100*1000/10 = 10,000 tx/sec.

Application-side pool config·python

# psycopg pool — application-level, complements pgBouncer
from psycopg_pool import ConnectionPool

pool = ConnectionPool(
    "postgresql://app:****@pgbouncer:6432/mydb",
    min_size=4,
    max_size=20,
    timeout=10.0,
)

with pool.connection() as conn:
    conn.execute("SELECT 1")

Supabase pgBouncer URL·bash

# Direct connection (port 5432) — uses real Postgres connection
postgresql://postgres.PROJECT:****@aws-0-region.pooler.supabase.com:5432/postgres

# Transaction pooler (port 6543) — cheap to connect, transaction-mode
postgresql://postgres.PROJECT:****@aws-0-region.pooler.supabase.com:6543/postgres
# Use this for serverless / Lambda / Vercel.

Connection Pooling and pgBouncer

Connections are not free

The connection pooler

Pool modes

Code

External links

Exercise

Progress

Comments 0