Configuring a PostgreSQL Connection Pool

A connection pool is a cache of open database connections that many clients reuse instead of opening a new connection per request. A connection pooler is the component that owns that cache, sitting between your application and the database and handing out existing connections on demand. You configure one to cut connection setup overhead and to keep the number of concurrent server-side connections under the database's max_connections ceiling. On PostgreSQL this matters because every connection is a separate operating-system process.

Why a Connection Pooler Helps

PostgreSQL uses a process-per-connection model: the postmaster forks a dedicated backend process for every client connection. Each backend carries its own memory (commonly 5-10 MB once catalog and query caches warm up), and the max_connections limit (default 100) is enforced at startup by pre-allocating shared memory for every possible slot. Opening a connection is not free either - it involves a TCP handshake, authentication, and a process fork - so a stateless app that connects per request pays that cost on every call.

A pooler removes both problems. It keeps a fixed set of real connections open and reuses them, so clients no longer pay per-request setup latency. In transaction-level modes it also multiplexes: many idle clients share a small number of backends, so 1,000 application connections can run on 20-50 real PostgreSQL processes if most are idle between transactions. The trade-off is one more component to operate and a potential point of failure, and aggressive multiplexing breaks any feature that outlives a single transaction.

External vs In-App Pooling

Pooling lives in one of two places: inside your application process, or as a standalone proxy. In-app pools (HikariCP for Java, pg-pool for Node.js, SQLAlchemy's QueuePool for Python, ActiveRecord's pool for Rails) remove per-request setup cost within one process. They do not lower the global connection count - 10 app servers each holding a 25-connection pool still present 250 backends to PostgreSQL, most of them idle.

External poolers (PgBouncer, pgpool-II, Odyssey, Supavisor) run as a separate process that all app instances connect to, giving one global ceiling on server connections regardless of how many app processes exist. The two layers compose well: keep a small in-app pool to avoid per-process churn, and point it at an external pooler that enforces the database-wide limit. Use an in-app pool alone when you run a single long-lived process; add an external pooler once you scale horizontally or run serverless functions that each open their own connections.

Pooling Modes

The pooling mode decides when a server connection returns to the pool, which in turn decides which database features keep working. This is the single most consequential setting on any pooler, PgBouncer included.

Session mode preserves every PostgreSQL feature but gives no reduction in backend count, only reuse. Transaction mode is the default for web workloads and the reason most teams deploy a pooler at all. In transaction mode, replace pg_advisory_lock() with the transaction-scoped pg_advisory_xact_lock(), use SET LOCAL instead of SET, and run schema migrations on a direct connection since many migration tools take a session advisory lock. Statement mode is niche - read-only, single-statement reporting only. ORM driver prepared statements (protocol-level Parse/Bind/Execute) work in transaction mode on PgBouncer 1.21.0+ via max_prepared_statements. See the PgBouncer guide for the full feature-compatibility matrix and per-parameter reference.

Built-In and Managed Poolers

Most managed PostgreSQL platforms ship a pooler so you do not run PgBouncer yourself. They default to transaction-level reuse, so the same session-feature caveats apply.

RDS Proxy is worth a note: instead of breaking on session-scoped state, it detects the incompatible statement and "pins" that client to one backend for the rest of the session. Pinning is safe but defeats multiplexing, so tuning RDS Proxy is largely about avoiding pins. PgBouncer has no equivalent - in transaction mode the burden of session-feature compatibility is on you.

If you run a managed pooler, monitoring pool saturation matters as much as configuring it. Pulse is an AI-native database optimization and maintenance platform that tracks connection counts, pool saturation, and max_connections exhaustion incidents, then runs automated root-cause analysis and proposes a fix a human approves before anything changes. That turns a 2 a.m. "remaining connection slots are reserved" page into a reviewed recommendation.

Case Study: Configuring and Benchmarking PgBouncer

A minimal transaction-mode PgBouncer config for a web app:

# /etc/pgbouncer/pgbouncer.ini
[databases]
myapp = host=127.0.0.1 port=5432 dbname=myapp

[pgbouncer]
listen_addr = 127.0.0.1
listen_port = 6432
auth_type = scram-sha-256
auth_file = /etc/pgbouncer/userlist.txt
pool_mode = transaction
max_client_conn = 1000
default_pool_size = 25

Apps connect to port 6432 (PgBouncer) instead of 5432 (PostgreSQL). Up to 1,000 clients now share 25 backends per (database, user) pool. To prove the pooler is worth it, benchmark the connection overhead it removes. The right tool is pgbench, and the flag that matters is -C, which opens a fresh connection for every transaction instead of reusing one per client - exactly the cost a pooler eliminates:

# -C reconnect per transaction, -c 10 clients, -j 2 worker threads, -t 1000 transactions each
# Run against PostgreSQL directly (port 5432) to measure raw connection overhead:
pgbench -C -c 10 -j 2 -t 1000 -p 5432 myapp

# Run the same load through PgBouncer (port 6432) and compare TPS:
pgbench -C -c 10 -j 2 -t 1000 -p 6432 myapp

A common mistake is writing pgbench -c 10 -p -j 2 -t 1000 database_name: -p is the port flag and needs an argument (it would swallow -j), and without -C every client reconnects only once, so the test never exercises connection setup. With -C, the through-PgBouncer run should show markedly higher TPS because clients reattach to warm pooled backends rather than forking a new PostgreSQL process each time. See the PostgreSQL pgbench docs for the full flag list, and the PgBouncer guide for authentication, SHOW POOLS monitoring, and pool sizing.

Frequently Asked Questions

Q: What is a connection pool and what does a connection pooler do?
A: A connection pool is a set of reusable open database connections. A connection pooler is the component that manages that pool, handing an existing connection to each client request instead of opening a new one. It reduces connection setup latency and, in transaction mode, lets many clients share far fewer server-side connections.

Q: When should I use an external pooler instead of my application's built-in pool?
A: Use an external pooler (PgBouncer, Supavisor, RDS Proxy) once you run multiple application processes or serverless functions. In-app pools only reduce setup cost within one process and cannot cap the global connection count, so N app servers multiply into N pools of backends. An external pooler enforces one database-wide ceiling.

Q: What is the difference between session, transaction, and statement pooling?
A: Session pooling holds a backend for a client's whole session and breaks nothing, but gives no multiplexing. Transaction pooling returns the backend at each commit, enabling heavy multiplexing but breaking session-scoped features like LISTEN/NOTIFY and session advisory locks. Statement pooling returns the backend after every statement and forbids multi-statement transactions.

Q: How do I size a connection pool?
A: Start with a default_pool_size near the number of database CPU cores for CPU-bound work, and somewhat higher for I/O-bound work where connections mostly wait. Watch the pooler's wait metrics (cl_waiting and maxwait in PgBouncer's SHOW POOLS); if clients queue, raise the pool size or reduce app concurrency rather than guessing from a formula.

Q: Why does transaction pooling break prepared statements and how do I fix it?
A: SQL-level PREPARE is session-scoped, so it is lost when the backend changes between transactions. Protocol-level prepared statements used by ORMs were also broken until PgBouncer 1.21.0, which tracks them per client via max_prepared_statements (default 200 since 1.24.0). Enable that, and avoid SQL-level PREPARE in transaction mode.

Q: Do managed PostgreSQL services include a connection pooler?
A: Yes. Neon and Azure Flexible Server embed PgBouncer, Supabase ships Supavisor, AWS offers RDS Proxy, and GCP Cloud SQL Enterprise Plus offers Managed Connection Pooling. All default to transaction-level reuse, so session-scoped features carry the same caveats as a self-hosted transaction-mode pooler.

Q: How do I benchmark whether a connection pooler helps?
A: Run pgbench -C against PostgreSQL directly and again through the pooler, comparing transactions per second. The -C flag opens a new connection per transaction, isolating exactly the connection setup overhead a pool removes; the pooled run should report higher TPS.

Related Reading

• PostgreSQL Connection Pooling: A PgBouncer Guide: deep PgBouncer configuration, authentication, monitoring, and the full feature-compatibility matrix.
• How to Fix PostgreSQL Error: Too Many Connections: the error pooling is meant to prevent, and how to diagnose it.
• How to Fix PostgreSQL Error: Remaining Connection Slots Are Reserved: connection-slot exhaustion and superuser_reserved_connections.
• PostgreSQL Connection String: Formats, Parameters, and Security: how to point an app at a pooler endpoint.
• How to Size a MySQL Connection Pool: the same sizing reasoning applied to MySQL's thread-per-connection model.
• Neon Postgres: Serverless PostgreSQL Explained: how Neon's integrated PgBouncer and -pooler endpoint work.
• Managed PostgreSQL Hosting Providers - Comparison: pooler options across managed providers.
• PostgreSQL Performance Tuning: Complete Guide: where pooling fits in broader tuning.