A Node.js API starts timing out during a traffic spike. CPU is low, so the deployment scales from six replicas to twelve. Each process opens its own PostgreSQL pool. The database now spends more time managing sessions, and the API becomes even slower. Scaling the application has amplified the bottleneck rather than removed it.
1. Start with a database-wide connection budget
A pool limit is local to a process, not a cluster-wide limit. Count every application, worker process, migration job and background consumer that can connect to the same database. Include surge replicas during rolling deployments and any overlapping jobs.
max_connections = 200
reserved and operational margin = 30
other services and jobs = 50
available to this API = 120
peak pods including surge = 15
processes per pod = 2
pool max per process = 4
This example gives an upper bound, not an optimal setting. Four connections per process may still be too many for the workload, and the margin must include PostgreSQL reserved slots and operational access. Repeat the calculation for each database endpoint, including replicas used for reads. Autoscaling must respect the assumptions in this budget.
2. Make queueing visible in Node.js
With node-postgres, construct a pool once per process rather than once per request. A small pool naturally queues callers when all clients are checked out. That protects the database only if the queue itself is bounded by admission control and a sensible timeout.
import { Pool } from 'pg';
export const pool = new Pool({
connectionString: process.env.DATABASE_URL,
max: 4,
connectionTimeoutMillis: 500,
idleTimeoutMillis: 30_000,
statement_timeout: 2000,
});
pool.on('error', (error) => {
console.error('idle PostgreSQL client error', error);
});
export function poolSnapshot() {
return {
total: pool.totalCount,
idle: pool.idleCount,
waiting: pool.waitingCount,
};
}
These timeouts are illustrative. Measure acquisition latency separately from query latency. A rising waiting count and normal execution time suggest local contention; rising execution time can indicate a database bottleneck. A timeout on a caller is not proof that a statement has stopped executing. Use server-side statement timeouts and explicit cancellation semantics where supported. Limit inbound concurrency before callers pile up in memory, and return a documented overload response rather than retrying immediately.
3. Keep a checked-out client for the shortest useful interval
For one statement use pool.query(). For a transaction use one checked-out client for all statements and always release it. Do not perform network calls while holding a transaction open. Long idle transactions can hold locks and delay vacuum cleanup, even when the application is not doing useful work.
const client = await pool.connect();
try {
await client.query('BEGIN');
await client.query(
'UPDATE jobs SET state = $1 WHERE id = $2',
['ready', jobId]
);
await client.query('COMMIT');
} catch (err) {
try {
await client.query('ROLLBACK');
} catch {
client.release(true);
throw err;
}
client.release();
throw err;
}
client.release();
In production, encapsulate this lifecycle in a tested transaction helper. A failed commit may leave the outcome uncertain; do not blindly repeat business writes. Use a durable operation identity when retries are necessary.
4. Diagnose the database before turning the dial
SELECT application_name, state, wait_event_type,
count(*) AS sessions
FROM pg_stat_activity
WHERE backend_type = 'client backend'
GROUP BY application_name, state, wait_event_type
ORDER BY sessions DESC;
Set a meaningful application name per service. Compare active and idle sessions, lock waits, CPU, storage latency and query duration against the same time interval. The view is a snapshot, so sample over time. Visibility into other users' queries depends on privileges. If lock waits dominate, adding connections often increases contention. If a few queries dominate CPU, fix plans and data access first.
5. Introduce PgBouncer only with a compatibility plan
Transaction pooling multiplexes clients onto server connections between transactions. It helps when many application sessions are mostly idle, but session-scoped assumptions can break: temporary tables that persist across transactions, session advisory locks and session settings require careful review. Prepared statement support depends on the PgBouncer version and configuration. Test the actual ORM and driver combination rather than assuming transparent compatibility.
Maintain separate budgets for application-to-pooler clients and pooler-to-database connections. Multiple pooler replicas can each establish their own server pools. A pooler cannot make expensive queries cheap, and it introduces another component whose availability and metrics matter.
6. Test the deployment, not just the query
- Run a load test at a fixed arrival rate and compare p95 and p99 latency, throughput, pool acquisition time and database CPU for several pool sizes.
- Roll the deployment during the test, including maximum surge and old pods draining. Watch connection counts until termination completes.
- Inject a slow query, a lock wait and a database reconnect event. Confirm bounded waiting and recovery without a retry storm.
- Test a client leak deliberately in a disposable environment. Alert on sustained waiting and acquisition time rather than connection count alone.
- Verify shutdown stops accepting requests, drains in-flight work and closes the pool within the pod termination budget.
Official references
- node-postgres pool API
- node-postgres pool sizing
- PostgreSQL statistics views
- PgBouncer pooling feature matrix