Connection Pooling at the Database Level

Every database connection in PostgreSQL spawns a backend process. Each process consumes ~10MB of memory, holds shared buffers, and consumes a slot in the shared process array. With hundreds or thousands of application instances, you can exhaust the database's connection capacity before it runs out of CPU or memory. Connection pooling solves this by multiplexing many client connections onto fewer database connections.

Why Connection Pooling Matters

PostgreSQL's process-per-connection model has hard limits:

sql

-- Maximum connections
SHOW max_connections;  -- Default 100
 
-- Memory per connection (approximately)
-- work_mem: 4MB × max_parallel_workers_per_gather (default 2)
-- maintenance_work_mem: 64MB (only for VACUUM, CREATE INDEX)
-- temp_buffers: 32MB
-- Per-connection overhead: ~10MB
 
-- With 500 connections:
-- 500 × 10MB = 5GB just for connection overhead
-- Plus work_mem allocations for each query
-- Plus shared memory overhead

The real killer isn't memory — it's context switching. Each connection runs in its own process. With 500 connections, PostgreSQL's scheduler context-switches between processes hundreds of times per second, each switch costing ~1-10μs of CPU time.

PgBouncer: The Standard

PgBouncer is a lightweight connection pooler that sits between your application and PostgreSQL. It maintains a pool of database connections and assigns them to clients on demand.

ini

; pgbouncer.ini
[databases]
mydb = host=127.0.0.1 port=5432 dbname=mydb
 
[pgbouncer]
listen_port = 6432
listen_addr = 0.0.0.0
auth_type = md5
auth_file = /etc/pgbouncer/userlist.txt
 
pool_mode = transaction  ; session | transaction | statement
max_client_conn = 1000   ; Maximum client connections
default_pool_size = 20    ; Connections per user/database pair
min_pool_size = 5         ; Keep at least this many connections
reserve_pool_size = 5     ; Extra connections for bursts
reserve_pool_timeout = 3  ; Seconds before using reserve pool
 
server_idle_timeout = 600  ; Close idle server connections
client_idle_timeout = 0     ; No client timeout (0 = disabled)

The three pooling modes:

ini

# Session mode: Client gets a connection for the entire session
# Pro: Full SQL support (PREPARE, LISTEN/NOTIFY, SET)
# Con: Connections held even when idle
pool_mode = session
 
# Transaction mode: Connection returned after each transaction
# Pro: Most efficient, connections reused quickly
# Con: PREPARE, LISTEN/NOTIFY, SET only work within transactions
pool_mode = transaction
 
# Statement mode: Connection returned after each statement
# Pro: Maximum multiplexing
# Con: Multi-statement transactions not allowed
pool_mode = statement

tradeoff / Feature Support vs Multiplexing

Transaction pooling is the standard choice. Most applications don't need session-level features like LISTEN/NOTIFY or prepared statements across transactions.

Session pooling is safest but least efficient. Transaction pooling is the sweet spot for most applications. Statement pooling maximizes multiplexing but breaks multi-statement transactions.

Transaction Pooling Gotchas

Transaction pooling breaks certain PostgreSQL features:

sql

-- PREPARE/EXECUTE won't work across transactions
PREPARE foo AS SELECT * FROM accounts WHERE id = $1;
-- Connection returned to pool
EXECUTE foo(1);  -- ERROR: prepared statement "foo" does not exist
 
-- LISTEN/NOTIFY requires persistent connection
LISTEN my_channel;
-- Connection returned to pool
NOTIFY my_channel;  -- Notification may go to a different connection
 
-- SET commands are lost
SET work_mem = '256MB';
-- Connection returned to pool
-- Next query uses default work_mem

Solutions exist for most of these:

sql

-- Use extended query protocol instead of PREPARE
-- Most PostgreSQL drivers (libpq, Npgsql, psycopg2) handle this automatically
 
-- For LISTEN/NOTIFY, use a dedicated session-pooled connection
-- Or use pg_notify with a transaction-level listener
 
-- For SET, use per-transaction settings
BEGIN;
SET LOCAL work_mem = '256MB';
-- Query uses 256MB work_mem
COMMIT;  -- SET LOCAL is reset

Application-Level Pooling

Most applications also maintain their own connection pool. This creates a two-tier pooling architecture:

python

# Python SQLAlchemy example
from sqlalchemy import create_engine
 
engine = create_engine(
    "postgresql://user:pass@pgbouncer:6432/mydb",
    pool_size=20,           # App-level pool size
    max_overflow=10,        # Additional connections under load
    pool_timeout=30,        # Seconds to wait for a connection
    pool_recycle=1800,      # Recycle connections after 30 minutes
    pool_pre_ping=True,     # Test connections before use
)

The total connections to PostgreSQL:

plaintext

Total DB connections = (App instances) × (App pool size)
                     = 10 × 20 = 200 connections
 
Without PgBouncer:
Each app instance opens 20 direct connections
PostgreSQL sees 200 connections
Each consumes ~10MB = 2GB overhead
 
With PgBouncer (transaction mode, pool_size=20):
Each app instance opens 20 connections to PgBouncer
PgBouncer opens 20 connections to PostgreSQL
Total DB connections = 20
Memory savings: 180 × 10MB = 1.8GB

Connection Limits and Resource Management

PostgreSQL has several resource limits per connection:

sql

-- Per-connection limits
SHOW max_connections;          -- Total connection limit
SHOW superuser_reserved_connections;  -- Reserved for admins
 
-- Per-query limits
SHOW work_mem;                 -- Memory for sorts, hashes
SHOW maintenance_work_mem;     -- Memory for VACUUM, CREATE INDEX
SHOW temp_buffers;             -- Memory for temporary tables
 
-- Connection state
SELECT
    state,
    count(*)
FROM pg_stat_activity
GROUP BY state;
 
-- States:
-- active: Currently executing a query
-- idle: Waiting for a new query
-- idle in transaction: Transaction open but not executing
-- fastpath function: Calling a C function

sql

-- Kill long-running idle transactions
SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE state = 'idle in transaction'
AND state_change < now() - interval '10 minutes';
 
-- Set statement timeout to prevent runaway queries
SET statement_timeout = '30s';

Pgpool-II vs PgBouncer

Pgpool-II is a more feature-rich alternative to PgBouncer:

Feature	PgBouncer	Pgpool-II
Connection pooling	Yes	Yes
Load balancing	No	Yes (read replicas)
Query caching	No	Yes
Replication	No	Yes (deprecated)
Memory usage	~2MB	~20MB
Complexity	Low	High

tradeoff / Simplicity vs Features

Use PgBouncer unless you need Pgpool-II's specific features. PgBouncer's simplicity makes it easier to debug and operate.

PgBouncer is simple, fast, and reliable for connection pooling. Pgpool-II adds load balancing and caching but is more complex and uses more resources.

Synthesis

Connection pooling is essential for PostgreSQL deployments beyond a single application instance. PgBouncer's transaction mode provides the best balance of efficiency and feature support. The key is understanding the two-tier pooling architecture: application pools multiplex onto PgBouncer, which multiplexes onto PostgreSQL. Getting the pool sizes right — application pool, PgBouncer pool, and PostgreSQL max_connections — prevents both resource exhaustion and connection starvation.

Connection Pooling at the Database Level

Why Connection Pooling Matters

PgBouncer: The Standard

Transaction Pooling Gotchas

Application-Level Pooling

Connection Limits and Resource Management

Pgpool-II vs PgBouncer

Synthesis

Related reading

Why Your Query Is Slow (And It's Not the Index)

The Query Planner Cost Model

B-Tree Indexing: Why It Wins

One dispatch a week