Connection Pooling

Postgres · Reference cheat sheet

📋 Overview

Each Postgres connection is expensive (memory, process). Poolers like PgBouncer multiplex many client connections onto fewer server connections — essential for serverless and high-churn apps.

🔧 Core concepts

💡 Examples

Check connections:

SELECT count(*), state FROM pg_stat_activity GROUP BY state;
SHOW max_connections;

Terminate idle (careful):

SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE state = 'idle in transaction'
  AND state_change < now() - interval '10 minutes';

App URL via pooler (sketch):

postgres://user:pass@pgbouncer-host:6432/app

⚠️ Pitfalls

• Opening a new DB connection per serverless invoke without a pooler exhausts max_connections.
• Session-level features (temp tables, advisory locks, prepared statements) break under transaction pooling.
• Pooler + ORM defaults can still stampede — size pools per instance deliberately.

🔗 Related

• roles_privileges.md
• vacuum_analyze.md
• getting_started.md
• connect_psql.md