Database Connection Pooling: Why It Matters and How to Set It Up

The Connection Problem

Opening a database connection is expensive. PostgreSQL, for example, spawns a new OS process for every connection — each consuming roughly 5–10 MB of memory. A typical web server under load might try to open hundreds of simultaneous connections. Without pooling, your database runs out of resources fast.

Connection pooling solves this by maintaining a fixed pool of pre-opened connections that multiple application threads share.

How Connection Pooling Works

App threads: [T1] [T2] [T3] [T4] [T5] [T6] ... [T100]
                 ↓    ↓    ↓    ↓    ↓    ↓
Pool:        [C1] [C2] [C3] [C4] [C5]   ← fixed connections
                 ↓
Database:    PostgreSQL (5 backend processes)

100 application threads share 5 database connections. Threads borrow a connection, execute their query, and return it immediately. The database never sees more than 5 concurrent connections.

PostgreSQL: pg_bouncer

PgBouncer is the most popular connection pooler for PostgreSQL. It runs as a lightweight proxy between your app and the database.

Install and configure pgbouncer.ini:

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

[pgbouncer]
listen_port    = 6432
listen_addr    = *
auth_type      = md5
auth_file      = /etc/pgbouncer/userlist.txt
pool_mode      = transaction   ; or session / statement
max_client_conn = 1000
default_pool_size = 20
min_pool_size  = 5
reserve_pool_size = 5
server_idle_timeout = 600

Start PgBouncer:

pgbouncer -d /etc/pgbouncer/pgbouncer.ini

Your app now connects to port 6432 instead of 5432. PgBouncer handles the rest.

Pool Modes Explained

Transaction mode is the recommended default for most web applications.

Node.js: Built-in Pooling with pg

The pg (node-postgres) library includes a built-in pool:

npm install pg

# Environment config
export DATABASE_URL="postgresql://user:pass@localhost:5432/myapp"
export DB_POOL_MAX=20
export DB_POOL_IDLE_TIMEOUT=10000

Pool configuration options:

# Recommended pool settings for production
max=20            # Maximum connections in the pool
min=2             # Minimum idle connections
idleTimeoutMillis=10000   # Close idle connections after 10s
connectionTimeoutMillis=3000  # Error if no connection available in 3s

Diagnosing Pool Exhaustion

Pool exhaustion happens when all connections are in use and new requests time out. Signs:

-- Check active connections on PostgreSQL
SELECT count(*), state
FROM   pg_stat_activity
WHERE  datname = 'myapp'
GROUP  BY state;

-- state = 'active'  : queries running
-- state = 'idle'    : connections held but idle (pool bloat)
-- state = 'idle in transaction' : transaction not committed!

Long-running idle in transaction connections are the most common pool exhaustion cause. Fix: always commit or rollback promptly.

-- Kill idle-in-transaction connections older than 5 minutes
SELECT pg_terminate_backend(pid)
FROM   pg_stat_activity
WHERE  state = 'idle in transaction'
  AND  now() - state_change > interval '5 minutes';

Sizing Your Pool

A common formula:

pool_size = (number_of_cpu_cores * 2) + effective_spindle_count

For a 4-core server with SSDs: pool_size = (4 * 2) + 1 = 9 — round up to 10–15.

For cloud databases, check the instance's max_connections setting:

SHOW max_connections;
-- Typical value: 100
-- Reserve ~10 for superuser/monitoring, give the rest to the pool

If you have 3 app servers each with a pool of 20, that's 60 connections to the database — comfortably under 100.

MySQL Connection Pooling

MySQL uses threads instead of processes, so connections are cheaper — but pooling still matters at scale:

# Check current connections
mysql -e "SHOW STATUS LIKE 'Threads_connected';"
mysql -e "SHOW VARIABLES LIKE 'max_connections';"

# Set max_connections (my.cnf)
[mysqld]
max_connections = 200
wait_timeout = 28800
interactive_timeout = 28800

PandaStack and Connection Pooling

[PandaStack](https://dashboard.pandastack.io) includes managed connection pooling for PostgreSQL and MySQL — powered by PgBouncer under the hood. You get a pooled connection string alongside the direct connection string in the dashboard. Switch to the pooled URL in production and immediately reduce connection pressure on your database.

All managed databases (PostgreSQL, MySQL, Redis, MongoDB) are available from the Databases panel. Docs: [docs.pandastack.io](https://docs.pandastack.io).

Summary

• Database connections are expensive OS resources; pooling reuses them efficiently
• Use transaction-mode pooling (PgBouncer) for the highest concurrency
• Size your pool based on CPU cores and max_connections
• Monitor idle in transaction connections — they starve the pool
• Always set connectionTimeoutMillis so your app fails fast instead of hanging