Postgres

Sizing Pool size ≈ CPU cores * 2–4 per service instance; avoid per-request opens. For PgBouncer tx-mode: disable session features; avoid session-prepared statements. Timeouts & limits Set ConnMaxLifetime, ConnMaxIdleTime, MaxOpenConns, MaxIdleConns. Add statement timeouts; enforce context deadlines on queries. Instrumentation Track pool acquire latency, in-use/idle, wait count, timeouts. Log slow queries; sample EXPLAIN ANALYZE in staging for heavy ones. Hygiene Use prepared statements judiciously; reuse sqlx.Named/pgx prepared for hot paths. Prefer keyset pagination; cap result sizes; parameterize everything. Checklist Pool sized and monitored. Query timeouts set; slow logs reviewed. No per-request connections; connections closed via context cancellation.

Pooling Use a pool (pg/pgbouncer); size = (CPU * 2–4) per app instance; avoid per-request connections. For PgBouncer in transaction mode, avoid session features (temp tables, session prep statements). Query hygiene Parameterize queries; prevent plan cache thrash; set statement timeout. Add indexes; avoid wild % patterns; paginate with keyset when possible. Monitor slow queries; cap max rows returned; avoid huge JSON blobs. App settings Set statement_timeout, idle_in_transaction_session_timeout. Use prepared statements judiciously; for PgBouncer, prefer server-prepared off or use pgbouncer session mode. Pool instrumentation: queue wait time, checkout latency, timeouts. OS/DB basics Keep Postgres on same AZ/region; latency kills. Tune work_mem, shared_buffers, effective_cache_size appropriately (DB side). Use EXPLAIN (ANALYZE, BUFFERS) in staging for heavy queries. Checklist Pool sized and monitored; PgBouncer for many short connections. Query timeouts set; slow logs monitored. Key indexes present; pagination optimized. App-level metrics for pool wait, query latency, error rates.