Managing postgres connections

Managing Postgres Connections

Overview

Postgres connections are expensive — each costs CPU and memory. They should be long-lived and pooled. High connection churn wastes resources, and connection storms trigger internal Postgres locks that produce nasty, hard-to-reproduce edge cases.

When to Use

• Setting up how your app connects to Postgres.
• Hitting too many connections / remaining connection slots are reserved.
• Seeing latency spikes or lock contention under load that correlates with new connections.
• Running many app instances against one database.

Quick Reference

Option	When	Notes
External pooler (pgbouncer)	Preferred	Sits in front of Postgres; one pool for all app instances
In-memory pooler (e.g. pgxpool for Go)	Fallback	Per-process; use when you can't run an external pooler
Open/close per request	Never	Churn burns CPU/memory and invites connection storms

Rules

• Keep connections long-lived — acquire from a pool, return to the pool, don't reconnect per query.
• Prefer an external pooler (pgbouncer) so all instances share one bounded set of server connections.
• If you can't guarantee an external pooler (e.g. shipping software that runs against arbitrary customer databases), use an in-memory pooler like pgxpool as a fallback.
• Bound the pool size — more connections is not more throughput; past a point it's pure contention.

Common Mistakes

• Opening a fresh connection per request or per query — the storm pattern.
• No pooler at all, with many app instances each opening connections directly — you exhaust max_connections and hit internal lock edge cases.
• Setting the pool max very high "to be safe" — large connection counts increase lock contention inside Postgres rather than helping.