# Pathrule Pattern: Database Connections, Pooling, and Capacity (1.0.0) # ::pathrule:package:database-connection-pooling ### [RULE] Budget pools from database capacity across the whole fleet (path: /infra) A pool maximum applies to one process or runtime instance. Autoscaling and rolling deployments multiply it, and background services often share the same database without appearing in the web-service configuration. - Inventory every process type that can connect, its maximum replicas or concurrency, number of pools, and whether old and new versions overlap during deployment. - Reserve capacity for administration, monitoring, maintenance, failover, and a degraded state rather than assigning the database maximum to application pools. - Set per-process pool limits from the fleet budget and dependency concurrency, then cap autoscaling where connection capacity is the hard limit. - Recalculate after adding workers, tenants with dedicated databases, read replicas, or new deployment environments; pool capacity is infrastructure design, not a library default. See /src/db for the adjacent decision or procedure that completes this constraint. --- ### [RULE] Bound connection acquisition and always release ownership (path: /src/db) When the pool is exhausted, callers can wait indefinitely and consume request or job concurrency while the database sees no new statement. Error, timeout, and cancellation paths are where leaked ownership accumulates. - Set an acquisition timeout shorter than the caller's total deadline and return a capacity-specific failure with pool wait evidence. - Use structured transaction or connection helpers that release in a finalization path after success, rollback, error, and cancellation. - Do not keep a connection checked out while performing HTTP calls, user think time, queue waits, large file work, or computation that can occur outside the database boundary. - Discard connections the driver marks broken or whose session reset cannot be proven; returning a poisoned session to the pool spreads one failure across callers. See /ops/runbooks for the adjacent decision or procedure that completes this constraint. --- ### [RULE] Reset session state before reuse (path: /src/db) A pooled connection is a reused session. State set by one request can silently affect the next request unless it is transaction-local, explicitly reset, or prohibited by the pooling mode. - Prefer transaction-local settings for tenant, role, timeout, and tracing context so commit or rollback clears them automatically. - Never return a connection with an open or failed transaction; roll back and verify clean state in the connection wrapper. - Avoid temporary tables, prepared-statement assumptions, and session variables when a transaction pooler can route successive transactions to different server sessions. - Test two callers sequentially on one pool connection with different tenants, roles, timezones, and failures to prove state isolation. See /infra for the adjacent decision or procedure that completes this constraint. --- ### [MEMORY] Pool wait and query time are separate signals (path: /src/db) An endpoint can spend most of its latency waiting for a connection while the eventual statement executes quickly. Looking only at database query duration hides the admission queue in the application. - Record pool size, idle, checked out, waiters, acquisition duration, timeouts, and connection errors per process and aggregate them across the fleet. - Trace acquisition as a separate span or phase from SQL execution and attach the logical operation without logging raw sensitive parameters. - Correlate wait growth with replica count, request concurrency, long transactions, database CPU and I/O, and deployment overlap. - Alert on sustained saturation and wait percentiles before every caller reaches its timeout and begins retrying simultaneously. See /ops/runbooks for the rule or workflow that puts this decision into practice. --- ### [MEMORY] External poolers change the session contract (path: /infra) A transaction pooler improves multiplexing by assigning a server session only for a transaction, but application code cannot assume the same session handles later work. Some prepared statements, temporary objects, locks, and variables depend on that continuity. - Document the selected pooling mode and which session features are prohibited or require driver-specific configuration. - Keep each unit of consistency in one explicit transaction so a transaction pooler cannot split related statements across sessions. - Size application-side pools as concurrency queues even when an external pooler exists; removing all local bounds can overwhelm the pooler with clients. - Test failover, pooler restart, server connection recycling, and prepared-statement behavior under the exact driver and deployment configuration. See /src/db for the rule or workflow that puts this decision into practice. --- ### [SKILL] investigate-pool-exhaustion (path: /) --- name: investigate-pool-exhaustion description: Investigate database connection exhaustion, acquisition timeouts, or sudden pool wait growth. --- # Investigate Pool Exhaustion Run this procedure when the affected surface changes, before the result is promoted to production. Record evidence for every step instead of accepting a plausible-looking result. 1. Capture fleet replica counts, deploy overlap, pool configuration, active and idle connections, waiters, database limits, and administrative headroom. 2. Separate acquisition time from SQL execution and identify processes, endpoints, jobs, or tenants with the longest checked-out duration. 3. Trace representative connection ownership through transaction, error, cancellation, streaming, and remote-call paths to find unreleased or unnecessarily held sessions. 4. Reduce intake or concurrency before increasing pool size; confirm the database has CPU, memory, and lock capacity for any additional active work. 5. Reproduce the dominant leak or saturation path, add a regression test and ownership evidence, then verify waits and timeouts recover across a rolling deployment. ## Exit criteria The change is complete only when the expected behavior, failure behavior, and rollback path have all been exercised with representative data. Preserve the evidence with the change so the next operator can repeat the same checks.