Connection Pooling

Connection Pooling

External connection poolers like PgBouncer sit between the application and database, multiplexing many application connections onto fewer database connections to prevent connection exhaustion.

When to Use

• Application has more processes/threads than the database can handle with direct connections
• Seeing FATAL: too many connections errors in PostgreSQL logs
• Running a microservices architecture where each service opens its own connection pool
• Deploying to serverless environments where connection reuse is difficult
• Tuning PgBouncer or a similar pooler for a production workload

Instructions

Key Concepts

1. Why Pool Externally

PostgreSQL forks a backend process for each connection. Each process consumes 5-10 MB of memory. At 500 connections, that is 2.5-5 GB of memory just for connection overhead, plus contention on shared resources like ProcArrayLock. An external pooler holds a small number of database connections (20-100) and multiplexes thousands of application connections onto them.

2. PgBouncer Pool Modes

; pgbouncer.ini

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

[pgbouncer]
listen_port = 6432
pool_mode = transaction   ; session | transaction | statement
max_client_conn = 1000    ; max application connections to PgBouncer
default_pool_size = 25    ; database connections per user/database pair
reserve_pool_size = 5     ; extra connections for burst traffic
reserve_pool_timeout = 3  ; seconds before reserve pool activates

Session mode: A database connection is assigned when the client connects and released when the client disconnects. Safest but least efficient -- almost no multiplexing benefit.

Transaction mode: A database connection is assigned at BEGIN and released at COMMIT/ROLLBACK. Best balance of safety and efficiency. Most production deployments use this mode.

Statement mode: A database connection is assigned per statement. Only works for simple, autocommit queries. Breaks multi-statement transactions.

3. Transaction Mode Limitations

In transaction mode, session-level state is lost between transactions:

-- These features BREAK in transaction mode:
SET search_path = 'tenant_1';  -- lost after transaction ends
PREPARE my_stmt AS SELECT ...;  -- lost after transaction ends
LISTEN channel_name;            -- lost after transaction ends
CREATE TEMP TABLE tmp (...);    -- lost after transaction ends

-- These features WORK in transaction mode:
BEGIN; ... COMMIT;              -- transactions work normally
SET LOCAL search_path = '...';  -- scoped to transaction, OK

4. Pool Sizing Formula

default_pool_size = max_database_connections / number_of_pooler_instances

Example:
  PostgreSQL max_connections = 100
  Reserved for superuser/monitoring = 10
  Available for application = 90
  PgBouncer instances = 2

  default_pool_size = 90 / 2 = 45 per instance

The max_client_conn can be much larger because PgBouncer queues requests when all database connections are busy.

Worked Example

A SaaS platform runs 12 microservices, each maintaining a connection pool of 20 connections. That is 240 direct connections, but the PostgreSQL instance only supports 100.

; pgbouncer.ini
[databases]
saasdb = host=db.internal port=5432 dbname=saasdb

[pgbouncer]
listen_port = 6432
pool_mode = transaction
max_client_conn = 500
default_pool_size = 40
reserve_pool_size = 10
reserve_pool_timeout = 3
server_idle_timeout = 300
server_lifetime = 3600
log_connections = 0
log_disconnections = 0

Each microservice connects to PgBouncer on port 6432 instead of PostgreSQL on port 5432. PgBouncer maintains 40 real database connections and queues excess requests. The 12 services can open up to 500 connections to PgBouncer combined, but only 40-50 database connections are active at any time.

Monitor with the PgBouncer admin console:

-- Connect to PgBouncer admin
psql -p 6432 -U pgbouncer pgbouncer

-- Check pool status
SHOW POOLS;
-- cl_active: application connections actively using a database connection
-- cl_waiting: application connections waiting for a database connection
-- sv_active: database connections processing a query
-- sv_idle: database connections idle and available

-- Check connection counts
SHOW STATS;

Anti-Patterns

1. Setting default_pool_size equal to max_connections. This defeats the purpose of pooling. The pool size should be a fraction of max_connections, leaving room for superuser connections, monitoring, and burst capacity.

2. Using session mode with short-lived connections. Session mode holds a database connection for the entire client session. If clients connect, run one query, and disconnect, session mode provides zero multiplexing benefit and adds latency from PgBouncer's overhead.

3. Using PREPARE statements in transaction mode. Prepared statements are session-level state. In transaction mode, the next transaction may get a different backend connection where the prepared statement does not exist, causing ERROR: prepared statement does not exist.

4. Running PgBouncer and the application on the same host without connection limits. If the application opens more connections than PgBouncer can queue, both PgBouncer and the application compete for the same CPU and memory. Run PgBouncer on a dedicated host or set strict max_client_conn limits.

5. Not monitoring cl_waiting. If cl_waiting is consistently above zero, application connections are waiting for database connections. Either increase default_pool_size (if the database can handle it) or optimize query performance to release connections faster.

PostgreSQL Specifics

• PostgreSQL 14+ built-in connection pooling is limited to connection_pool_size for parallel query workers; it does not pool client connections. External pooling is still required.
• pgbouncer_fdw provides PgBouncer stats as PostgreSQL foreign tables for integration with monitoring dashboards.
• Supabase Vibes uses PgBouncer in transaction mode by default for serverless deployments.
• AWS RDS Proxy and Google Cloud SQL Auth Proxy provide managed pooling with IAM authentication.

Details

Advanced Topics

Application-Level vs External Pooling: Most application frameworks include a built-in connection pool (e.g., HikariCP for Java, database/sql for Go). These manage connections within a single process. External poolers like PgBouncer manage connections across all processes. Use both: the application pool manages per-process concurrency, and PgBouncer manages database-level concurrency.

Multi-Database Pooling:

[databases]
db1 = host=primary.db port=5432 dbname=db1
db2 = host=primary.db port=5432 dbname=db2
db1_ro = host=replica.db port=5432 dbname=db1

Route read queries to replicas by connecting to db1_ro instead of db1.

PgBouncer Authentication:

auth_type = scram-sha-256
auth_file = /etc/pgbouncer/userlist.txt
; Or delegate to PostgreSQL:
auth_type = hba
auth_hba_file = /etc/pgbouncer/pg_hba.conf

Engine Differences

MySQL's connection model differs fundamentally: MySQL uses one thread per connection rather than one process per connection. Threads are lighter-weight (typically 256KB-1MB stack per thread vs 5-10MB per PostgreSQL process). MySQL handles more concurrent connections natively, but thread contention becomes a bottleneck beyond a few thousand connections.

ProxySQL serves a similar role to PgBouncer for MySQL. It supports query routing, connection multiplexing, and read-write splitting. ProxySQL uses a regex-based query routing system that is more feature-rich than PgBouncer but more complex to configure.

Real-World Case Studies

A ride-sharing platform ran 200 microservices, each with a pool of 10 connections, totaling 2,000 direct database connections. PostgreSQL was spending more time managing connections than processing queries. Deploying PgBouncer in transaction mode with default_pool_size = 80 and max_client_conn = 5000 reduced actual database connections from 2,000 to 80. Query latency at p99 dropped from 450ms to 120ms because PostgreSQL's shared buffer management was no longer thrashing. The cl_waiting metric showed occasional queuing during peak hours, resolved by increasing default_pool_size to 100.

Source

• PgBouncer Documentation
• PostgreSQL Connection Handling

Process

1. Deploy PgBouncer between the application and PostgreSQL; start with pool_mode = transaction.
2. Set default_pool_size to (max_connections - reserved) / pooler_instances.
3. Update application connection strings to point to PgBouncer's port.
4. Monitor SHOW POOLS for cl_waiting and sv_idle to tune pool sizing.

Harness Integration

• Type: knowledge -- this skill is a reference document, not a procedural workflow.
• No tools or state -- consumed as context by other skills and agents.
• related_skills: db-connection-sizing, db-acid-in-practice, db-isolation-levels

Success Criteria

• An external connection pooler (PgBouncer or equivalent) is deployed between the application and PostgreSQL.
• Pool mode is set to transaction unless session-level features require session mode.
• default_pool_size is calculated from max_connections and the number of pooler instances, not set arbitrarily.
• cl_waiting is monitored and remains near zero during normal traffic.