Set a Connection Pool Budget

Size database pools from the server's connection limit, reserve capacity for operations, and verify the total across replicas and background workers.

~25 mindatabasecapacityreliability

Task

Prevent a database from running out of connections when the application scales horizontally or adds a second pool.

When to use this

Each API instance opens a 50-connection pool. During an autoscale event, 20 instances attempt 1,000 connections against a database that allows 100, causing connection errors and an outage.

Prerequisites

The database's maximum connection setting
The maximum number of application replicas
An inventory of background workers and migration tools that also connect

Steps

1. Calculate the budget from the server limit minus reserved connections for admin, replication, and migrations.

// Example: 100 server connections - 10 reserved = 90 usable
const maxReplicas = 12;
const poolsPerReplica = 2; // API + worker
const perPool = Math.floor(90 / (maxReplicas * poolsPerReplica)); // 3

Expected output: The per-pool limit fits the worst-case replica count, not the current count.

2. Configure each pool with the computed limit and a connection timeout shorter than the request deadline.

const pool = new Pool({
  connectionString: process.env.DATABASE_URL,
  max: 3,
  connectionTimeoutMillis: 1_000,
  idleTimeoutMillis: 30_000,
});

Expected output: A request waits at most one second for a connection and then fails with a clear error.

3. Verify the total under a scale test and query the server for current and maximum connections.

psql "$DATABASE_URL" -c "SELECT count(*), current_setting('max_connections')::int FROM pg_stat_activity;"

Expected output: The observed connection count stays below the budget at maximum replicas.

Troubleshooting

Problem

The pool waits dominate latency

Solution

The pool is too small for the workload or queries hold connections too long. Measure wait time and query duration before increasing the size.

Problem

Connections spike during deploys

Solution

Old and new replicas overlap. Include the deploy surge in the worst-case replica count and drain old connections.

Limitations

Serverless or autoscaled platforms can exceed the planned replica count; use a proxy such as PgBouncer or a managed pooler.
A pool budget does not fix slow queries; it only bounds concurrency.

The total matters, not the per-pool number

A pool size is a per-process setting. The database sees the sum across every replica, worker, migration job, and admin session. Size from the worst-case replica count, not the current one, and reserve headroom for operations. A pool of 50 on 20 replicas is 1,000 connections before any worker starts.

max_connections 100
reserved 10
usable 90
max replicas 12 x 2 pools = 24 pools
per pool = 3

Fail fast when the pool is empty

A connection timeout bounds how long a request waits for a pool slot. Set it below the request deadline and return a clear 503 or 500 rather than hanging. If waiting for a connection is the dominant latency, the pool is too small or queries are holding connections too long; increasing the pool without fixing query duration moves the bottleneck to the database.

Verify under scale

Run a scale test to the maximum replica count and query the server for active connections. Include the deploy overlap and background jobs. If the platform can burst beyond the planned count, use a database proxy or a managed pooler so the database sees a bounded number of sessions.