Connection Pooling Explained
Every PostgreSQL connection spawns a process, allocates memory, and holds shared resources. A web application that opens a connection per request is not slow because of network latency — it is slow because it is paying the cost of process creation on every HTTP request. Connection pooling solves this, but the mode you choose changes what SQL you can run.
Situation
PostgreSQL uses a process-per-connection model. Each client connection forks a backend process that consumes 5–10MB of memory for its own stack, buffers, and per-session state. On a server with 8GB of RAM dedicated to PostgreSQL, this limits you to roughly 800 concurrent connections before memory pressure begins — and most production systems become resource-constrained well before that.
Web applications under load open and close connections constantly. At 500 requests per second, establishing a new PostgreSQL connection for each request adds 1–10ms of connection setup time per request — a latency floor that cannot be optimized away without pooling.
The Problem
A production database receiving connection errors under load is often not at its query processing limit — it is at its connection count limit. The fix is not always “increase max_connections” because that consumes more memory and can destabilize the database. The correct fix is a connection pool between the application and the database.
What does a connection pool actually do, and why does the pooling mode matter?
What a Pool Does
A connection pool maintains a set of long-lived PostgreSQL connections and lends them to application requests. The application connects to the pool (which is fast — TCP to a local process), and the pool forwards queries over an existing backend connection. When the application is done, the connection returns to the pool rather than being closed.
PgBouncer is the standard choice for PostgreSQL. It operates in three modes that differ in when the connection is returned to the pool:
Session mode: the backend connection is held for the entire application session. Equivalent to a direct connection — no query-level multiplexing. Useful for applications that rely on session-level state (SET, LISTEN, prepared statements that persist across transactions).
Transaction mode: the backend connection is returned to the pool after each transaction. One backend connection can serve multiple application sessions sequentially. Most OLTP applications work in this mode.
Statement mode: the backend connection is returned after each individual statement. Incompatible with multi-statement transactions. Rarely used.
# PgBouncer config (pgbouncer.ini)
[databases]
mydb = host=127.0.0.1 port=5432 dbname=mydb
[pgbouncer]
pool_mode = transaction
max_client_conn = 1000
default_pool_size = 25
min_pool_size = 5
server_idle_timeout = 600
With this config: 1,000 application connections share 25 backend connections, in transaction mode.
In Practice
PgBouncer’s documented transaction mode limitation is that per-session PostgreSQL features are broken: prepared statements created with PREPARE, advisory locks, SET LOCAL (which only persists for a transaction), and LISTEN/NOTIFY. Applications that use SET search_path outside a transaction will find their setting lost when the backend connection is returned to the pool. These are documented constraints, not bugs — transaction-mode pooling fundamentally cannot preserve session state between pool handoffs.
The common production pattern for applications using an ORM: switch from session mode to transaction mode, then fix the resulting errors one by one. The errors typically involve prepared statement handling (some ORMs cache prepared statements per connection) and search path assumptions.
Where It Breaks
| Failure | Cause | Fix |
|---|---|---|
ERROR: prepared statement does not exist | Prepared statement created in a previous transaction on a now-different backend | Disable prepared statements in the ORM; or use session mode |
| Advisory lock released unexpectedly | Advisory lock tied to session, returned to pool | Use transaction-scoped advisory locks or session mode |
SET variables lost between queries | Session state not preserved across pool handoffs | Move SET into transaction blocks; or use session mode for that use case |
| Pool exhausted under load | default_pool_size too small | Increase; but also check for long-running transactions blocking pool return |
What to Do Next
- Problem: Applications that open a PostgreSQL connection per request pay process-creation cost on every request and hit
max_connectionsunder load. - Solution: Put PgBouncer in front of PostgreSQL in transaction mode; set
default_pool_sizeto 20–50 depending on core count and query duration. - Proof: After adding PgBouncer,
SELECT count(*) FROM pg_stat_activityshould show a stable, small number of backend connections even under peak load. - Action: Run
SELECT count(*), state FROM pg_stat_activity GROUP BY state;today — ifidleconnections exceed 20% ofmax_connections, you are holding connections open unnecessarily and a pool would immediately free that capacity.