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.