Database connection pooling for services that scale safely

A database connection is more than a network socket. It consumes memory in the database, a slot in the server process, and usually a slot in the application's pool. A service that opens one connection for every request can look fine at low traffic and collapse when it scales horizontally. The first step in pool design is to calculate the total possible connections across instances, workers, migrations, dashboards, and background jobs. The database must be able to serve that total with headroom.

Pooling reduces handshake latency and lets requests reuse authenticated connections, but it does not create database capacity. A pool that is too large can increase lock contention and query latency. A pool that is too small can create application-side wait time even when the database is idle. Measure both sides: active database sessions, pool occupancy, checkout wait, transaction time, and requests waiting for a connection.

A service can accept more concurrent HTTP requests than it has database connections, as long as requests that do not need the database can proceed and database work is bounded. Setting pool size equal to HTTP concurrency often creates an accidental thundering herd against the database. Choose a pool based on query duration, database CPU, lock behavior, and the number of service instances. A small pool with efficient queries can outperform a large pool that spends its time waiting on shared resources.

Use an explicit checkout timeout. Waiting forever ties up request memory and hides saturation until upstream callers time out. When a request cannot obtain a connection within its budget, return a categorized temporary failure or shed optional work. A health endpoint should not consume a connection on every probe if the platform checks it frequently. Keep liveness, readiness, and dependency health distinct.

A connection should be checked out for the smallest period that includes the required database work. Do not perform network calls, template rendering, or user prompts while a transaction is open. Commit or roll back on every path, including cancellation and exceptions. A connection returned with an open transaction can hold locks and snapshots long after the request that caused it has disappeared.

Reset session state before reuse. Time zones, roles, search paths, temporary tables, prepared statements, and configuration parameters can leak between requests if the driver or pool does not clean them. Prefer a pool configuration that resets connections automatically and add a test that sets session state in one request and verifies it is absent in the next. A connection leak and a state leak are separate failure modes.

Cloud Run and other autoscaling platforms can create many instances quickly. A pool of ten connections per instance may be safe at five instances and overwhelming at fifty. Set a maximum instance count that the database can support and choose a per-instance pool that leaves room for administrative and migration connections. Consider a managed pooler when the database engine and transaction patterns support it, but understand whether session features survive transaction pooling.

Cold starts can create connection bursts. Reuse connections within an instance and avoid opening the full pool eagerly if the driver supports lazy creation. Add jitter or a warmup budget only when it does not delay readiness. During scale-in, allow in-flight requests to finish and close idle connections cleanly. Observe connection creation rate, not just steady-state pool size, because bursts can trigger provider limits.

When a dependency becomes slow, pooled connections remain occupied longer and the queue grows. A larger pool can make the database even slower, creating a positive feedback loop. Use statement and lock timeouts, circuit breakers for optional queries, and bounded request deadlines. Shed low-priority work before critical writes. If the database is in recovery or rejecting connections, fail clearly rather than retrying every request at once.

Separate credentials and pools for migrations, background jobs, and user traffic when their failure domains differ. A backfill should not consume every connection needed by checkout or webhook receipt. Label metrics by service and operation, not by user-controlled values. The operator needs to see whether the database is saturated, the pool is mis-sized, or one query is holding connections while it waits on another system.

Start with a small pool and measure checkout wait, query latency, database CPU, locks, and throughput under representative concurrency. Increase the pool only when the database has capacity and wait time is the limiting factor. If throughput stops improving, the bottleneck is elsewhere. Keep separate measurements for transactions and idle sessions so a pool does not hide a connection leak.

Load-test scale-out and restart behavior. Kill an instance while it holds connections, deploy a new revision while traffic is active, and simulate a database failover. Verify that the pool recovers without requiring a process restart and that retries do not multiply writes. A connection pool is part of the service's reliability contract: it should make normal traffic efficient and abnormal traffic bounded.

Record maximum instances, pool size, checkout timeout, query timeout, transaction timeout, database connection budget, and the roles that use separate pools. Explain which routes can degrade when the pool is exhausted and which routes must fail loudly. Include a runbook for inspecting active sessions, blocked queries, idle-in-transaction connections, and recent deploys.

The right pool is not the largest pool. It is the smallest configuration that keeps user-facing work responsive while preserving database headroom during scale-out and recovery. By measuring total capacity, keeping transactions short, resetting state, and testing failure, you turn a hidden source of outages into an explicit, manageable component.

Pool sizing is a system-wide budget, not a number chosen independently by each container. Multiply per-instance limits by the platform's maximum scale, include migrations and admin jobs, and leave headroom for failover. Set acquisition and idle timeouts, instrument wait duration, and close connections on shutdown. A healthy pool keeps requests moving while protecting PostgreSQL from a connection storm when Cloud Run or another autoscaler adds instances quickly.

Budget connections across maximum service instances, workers, migrations, and admin tools. Set pool maximum, acquisition timeout, idle timeout, transaction timeout, and shutdown behavior explicitly. A serverless service should scale its pool from the database budget rather than equating HTTP concurrency with database concurrency.

Exercise cold starts, scale-out, slow queries, database failover, and a pool-exhaustion condition. Measure active sessions, pool occupancy, checkout wait, transaction time, and connection errors. Confirm that the application releases connections on success, timeout, cancellation, and process shutdown.

Alert on pool wait, database saturation, unexpected session growth, and idle transactions. Keep a smaller emergency pool or shed optional work during database pressure. If a pool configuration is wrong, roll back the revision and drain old sessions deliberately; do not raise the limit until the database capacity and query behavior are understood.

Use PostgreSQL connection and resource-management documentation, the chosen driver or pooler guide, and Supabase connection-pooling guidance. Record the arithmetic that justifies the configured maximum.