Connection pooling

What connection pooling is

Every connection to PostgreSQL is a process. When a client connects, the postmaster process forks a new backend process dedicated to that session. This backend allocates memory for work_mem, temp_buffers, catalog caches, and other per-session state. When the client disconnects, the process exits and all of that memory is freed.

For a web application handling hundreds or thousands of concurrent requests, creating and destroying a process per request is — and I say this with all due courtesy — a remarkable waste of resources. Most requests need a database connection for only a few milliseconds — the time it takes to run one or two queries. The connection setup and teardown often takes longer than the queries themselves.

A connection pool solves this by keeping a fixed number of connections open and reusing them. Application requests check out a connection, use it, and return it. The PostgreSQL backend never knows the difference — from its perspective, the connection has been open the whole time. Proper resource management at its finest.

Why it matters

PostgreSQL's process-per-connection model has a cost that scales poorly. The damage, if you'll permit a brief inventory.

Memory. Each backend process consumes roughly 5-10 MB of resident memory even when idle. At 100 connections, that is 0.5-1 GB dedicated to connection overhead. At 500 connections, you are spending 2.5-5 GB before a single query runs. This memory competes with shared_buffers and the OS page cache — the very resources that make your queries fast. It is the infrastructural equivalent of heating every room in the manor while the guests are all in the drawing room.

Connection latency. Establishing a new PostgreSQL connection involves a TCP handshake, SSL negotiation (if enabled), authentication, and process forking. On a local connection, this takes 2-5 ms. Over a network with SSL, it can reach 20-50 ms. For a web request that runs a 1 ms query, spending 20 ms on connection setup is not a rounding error.

max_connections. PostgreSQL defaults max_connections to 100. Raising it sounds simple — and it is the first thing everyone tries — but each additional connection adds memory overhead and increases contention on internal locks like ProcArrayLock. PostgreSQL was not designed for thousands of direct connections, and performance degrades well before you hit the configured limit.

-- Check current connection limit
SHOW max_connections;

-- See how many connections are currently open
SELECT count(*) FROM pg_stat_activity;

-- Breakdown by state
SELECT state, count(*)
FROM pg_stat_activity
GROUP BY state
ORDER BY count DESC;

-- Each PostgreSQL connection is a forked process
-- Check memory per backend (approximate)
SELECT
  pid,
  usename,
  application_name,
  state,
  backend_start,
  query_start
FROM pg_stat_activity
WHERE backend_type = 'client backend'
ORDER BY backend_start;

-- On Linux, each idle backend uses ~5-10 MB of RSS
-- 200 idle connections = 1-2 GB of memory doing nothing

Types of pooling

Connection pooling can happen at different layers, and the right choice depends on your architecture. If I may, a brief tour of the options.

Application-level pooling

Most database drivers and ORMs include a built-in connection pool. SQLAlchemy (Python), HikariCP (Java), node-postgres (Node.js), and ActiveRecord (Ruby) all maintain a pool of connections per application process. This is the simplest approach — no additional infrastructure — and handles the common case where a single application server needs to reuse connections efficiently.

The limitation is scope. Each application process maintains its own pool. If you have 50 containers each holding a pool of 20 connections, that is 1,000 connections to PostgreSQL — even if most sit idle. A well-run household does not employ fifty doormen when one suffices. Application-level pooling does not help with cross-process connection sharing.

# SQLAlchemy connection pool configuration
from sqlalchemy import create_engine

engine = create_engine(
    "postgresql://user:pass@localhost/mydb",
    pool_size=20,          # maintained connections
    max_overflow=10,       # temporary connections above pool_size
    pool_timeout=30,       # seconds to wait for a connection
    pool_recycle=1800,     # recycle connections after 30 minutes
    pool_pre_ping=True,    # verify connections before checkout
)

# HikariCP (Java) — similar concept
# maximumPoolSize=20
# minimumIdle=5
# connectionTimeout=30000
# idleTimeout=600000

External pooling (PgBouncer, Pgpool-II)

An external pooler sits between your application and PostgreSQL as a lightweight proxy. PgBouncer is the most widely used — it is a single-threaded, event-driven process that can handle thousands of client connections and multiplex them onto a much smaller number of PostgreSQL connections.

Pgpool-II offers connection pooling alongside load balancing, replication management, and query caching. It is more feature-rich but heavier. For pure connection pooling, PgBouncer is the standard choice due to its simplicity and low overhead.

Cloud-native pooling

Managed PostgreSQL services increasingly ship built-in poolers. Supabase offers Supavisor (a multi-tenant pooler written in Elixir). Neon provides a serverless connection proxy that pools automatically. AWS RDS Proxy handles pooling for RDS and Aurora. These remove the operational burden of running PgBouncer yourself, though they vary considerably in configurability and pooling mode support. As with all managed services, convenience and control are in tension.

PgBouncer modes

PgBouncer supports three pooling modes, each with a different trade-off between connection reuse and session feature compatibility.

# pgbouncer.ini — minimal configuration

[databases]
mydb = host=127.0.0.1 port=5432 dbname=mydb

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

# Pool mode: session, transaction, or statement
pool_mode = transaction

# Pool sizing
default_pool_size = 20        # connections per user/database pair
min_pool_size = 5             # keep this many open even when idle
max_client_conn = 1000        # max application connections accepted
max_db_connections = 50       # max connections to PostgreSQL

Session mode

A client gets a dedicated server connection for the entire duration of its session. The connection is returned to the pool only when the client disconnects. This is the safest mode — every PostgreSQL feature works exactly as it would with a direct connection — but it provides the least multiplexing benefit. If your clients hold sessions open for long periods, session mode offers little advantage over connecting directly.

Transaction mode

A server connection is assigned when a client begins a transaction and returned when the transaction completes. Between transactions, the client has no dedicated server connection. This is the most commonly used mode in production because it provides excellent connection reuse while still supporting multi-statement transactions.

The trade-off: any feature that depends on session-level state does not carry across transactions. This includes SET commands, advisory locks, LISTEN/NOTIFY, and — historically — prepared statements. I should note that this last omission caused a great deal of consternation for years. PgBouncer 1.21+ added transparent prepared statement support in transaction mode, addressing the most common pain point. Better late than never, though one wishes the discovery had not required quite so many production incidents.

Statement mode

The server connection is returned after every individual statement. This provides maximum connection reuse but breaks multi-statement transactions entirely — each statement may execute on a different server connection. Only suitable for simple, autocommit workloads with no transaction boundaries.

Pool sizing

Pool sizing is about finding the balance between two failure modes, and most teams err in the same direction. Too few connections and requests queue up waiting for a free slot, adding latency. Too many and PostgreSQL spends resources managing backends that are mostly idle, while contention on internal locks increases.

-- A starting formula for pool sizing:
-- connections = (core_count * 2) + effective_spindle_count
--
-- For a 4-core server with SSDs:
-- connections = (4 * 2) + 1 = 9 to 10
--
-- This is a floor, not a ceiling. Adjust based on:
-- - Workload mix (read-heavy vs write-heavy)
-- - Average query duration
-- - Acceptable queuing latency

-- Monitor pool utilization with pg_stat_activity
SELECT
  count(*) FILTER (WHERE state = 'active') AS active,
  count(*) FILTER (WHERE state = 'idle') AS idle,
  count(*) FILTER (WHERE state = 'idle in transaction') AS idle_in_tx,
  count(*) FILTER (WHERE wait_event_type = 'Client') AS waiting_for_client
FROM pg_stat_activity
WHERE backend_type = 'client backend';

A common starting formula comes from the PostgreSQL wiki: connections = (core_count * 2) + effective_spindle_count. For a 4-core server with SSDs, this suggests roughly 9-10 connections. This sounds low — I have seen the look on people's faces when they hear it — but PostgreSQL is most efficient when active connections roughly match CPU cores.

The key distinction is between connections to PostgreSQL and connections from your application. With PgBouncer in front, your application can open 1,000 connections to the pooler while PostgreSQL sees only 20. The pooler absorbs the connection fan-out. Size the PostgreSQL-facing pool for throughput; size the application-facing limit for concurrency.

Signs your pool is too small: rising queue depth, connection timeout errors, and latency spikes uncorrelated with query complexity. Signs your pool is too large: many idle connections in pg_stat_activity, memory pressure on the database server, and no improvement in throughput when adding connections. The right number is, like most things in PostgreSQL, discovered through measurement rather than intuition.

How Gold Lapel relates

Gold Lapel is a PostgreSQL proxy, and connection multiplexing is built into its architecture. Your application connections terminate at Gold Lapel, which maintains its own carefully sized pool of connections to PostgreSQL. Many application connections share a smaller number of database connections — the same multiplexing benefit as PgBouncer, without a separate process to operate and monitor.

I would not suggest this replaces an understanding of pool sizing and configuration. The principles on this page apply regardless of which component manages the pool. But if you are already running Gold Lapel for query optimization, connection pooling comes as part of the same deployment. One fewer piece of infrastructure to maintain is, in my experience, one fewer thing to troubleshoot at 3 AM.