Franck PachotDatabase Developer Advocate
All minibooks
Database field guide · 24
Pools, sessions, admission, and failure diagnosis
Treat database connections as bounded stateful resources, from listener and authentication handshakes to pooling, serverless bursts, and session cleanup.
Build the mental model before choosing the mechanism.
This minibook was AI-generated from Franck Pachot's archived blog posts. Links to the original articles are included for source context and verification.01
Opening a database connection traverses naming, networking, transport security, authentication, and session initialization. An error at each layer needs different evidence.
02
A connected client may own a process, memory, transaction context, prepared statements, temporary objects, and observability identity. Idle does not mean free.
03
A pool reuses established connections and queues demand above its size. It improves admission control only when checkout deadlines, validation, reset behavior, and transaction boundaries are correct.
04
Transaction pooling and database-resident connection managers can route successive transactions through different server processes. Features tied to one backend may no longer persist across calls.
05
Functions can scale clients faster than a database can create sessions. A bounded intermediary, concurrency limit, and backpressure protect the database better than unlimited connection retries.
06
Client exceptions show the attempted endpoint and phase; server listeners, authentication logs, and session views show what arrived. Correlating timestamps and request identity separates refusal, timeout, and post-connect failure.
07
Concrete mechanics, diagnostic evidence, and executable patterns to carry into a real system.
A per-instance pool limit multiplies by replicas, workers, regions, and overlapping deployments. Reserve capacity for administration and background jobs before dividing the remainder.
usable_connections = database_limit - admin_reserve - job_reserve
per_instance_pool = usable_connections // maximum_app_instances
# Include blue/green overlap and autoscaling maximums.Checkout timeout limits queueing, connection timeout limits establishment, and statement timeout limits execution. They protect different phases and should produce distinguishable telemetry.
pool.max_size = 20
pool.checkout_timeout = '2s'
connect_timeout = '5s'
statement_timeout = '10s'
# Retry only operations whose outcome is known or idempotent.Group sessions by application, state, and transaction age. A pool with many idle backends may be oversized; idle-in-transaction sessions are correctness and cleanup defects.
SELECT application_name, state, count(*),
max(now()-xact_start) AS oldest_transaction
FROM pg_stat_activity
WHERE backend_type = 'client backend'
GROUP BY application_name, state
ORDER BY count(*) DESC;Capture endpoint, DNS result, route, TLS phase, authentication result, and server log timestamp in order. This prevents a generic connection error from collapsing unrelated failures.
1. Resolve host and selected address.
2. Test TCP reachability from the application network.
3. Verify TLS name, trust, and wallet/certificate expiry.
4. Correlate listener and authentication logs.
5. Check session limits and pool saturation.08
Optional deep dives with the complete experiments and product-version context behind this guide.