PostgreSQL connections consume server resources; more connections do not automatically create more throughput. Use bounded application pools or a compatible external pooler, keep transactions short, budget pool wait and statement time, cancel abandoned work, and distinguish session state from transaction state. Test retrieval together with ingestion, maintenance, and failover at realistic concurrency.
Start with database capacity and number of application instances, reserve connections for administration, migrations, monitoring, and replication, then allocate bounded pools. Apply Little's Law as an estimate: concurrent database work is approximately throughput times time-in-database. Measure before adding connections.
Autoscaling 50 instances with pools of 20 creates 1,000 possible connections. A global budget or external pooler prevents multiplication.
SET LOCAL hnsw.ef_search, role/context variables for RLS, and statement timeouts belong in one transaction. Return a connection only after commit/rollback and state cleanup. Never perform slow model calls while holding a database transaction or pooled connection.
The outer deadline must cancel inner work. A timed-out HTTP request should not leave an expensive vector query running unnoticed.
Simulate pool saturation, slow queries, lock contention, dropped clients, transaction errors, and database restart. Inspect pg_stat_activity for idle transactions and query age. Mix search, ingestion, and index/maintenance load and observe tail latency.
Given database max connections, reserved capacity, app instance range, query latency, throughput, and background jobs [values], propose a pool budget and timeout hierarchy. Include autoscaling math, session-state risks, cancellation, failover, and mixed-load tests.
Verification contract: A saturation test must queue or reject predictably without connection storms, tenant-state leakage, or abandoned queries.