Connection Pooling with PgBouncer AI Prompt

Configure PgBouncer connection pooling for this PostgreSQL deployment.

Max connections PostgreSQL can handle: {{max_connections}}
Application connection demand: {{app_connections}} (peak concurrent connections from app servers)
Workload: {{workload}} (short OLTP transactions vs long-running analytics queries)

1. Why connection pooling:
   - Each PostgreSQL connection consumes ~5-10MB RAM and a process
   - With 500 app server threads each holding an open connection → 500 Postgres processes → OOM
   - PgBouncer maintains a small pool of actual database connections; app connections are multiplexed

2. Pooling modes:

   Session pooling:
   - App connection holds a server connection for its entire lifetime
   - No statement restriction; full PostgreSQL feature support
   - Limited benefit: only helps when connections are idle for long periods

   Transaction pooling (recommended for most OLTP apps):
   - App connection holds a server connection only during a transaction
   - Server connection returned to pool after COMMIT/ROLLBACK
   - 100x reduction in required server connections for typical apps
   - Restriction: prepared statements and advisory locks do not work in transaction mode
     Fix: use named prepared statements via protocol-level support (pgBouncer >= 1.21)

   Statement pooling:
   - Server connection returned after every single statement
   - Most aggressive pooling; does not support multi-statement transactions
   - Use only for read-only single-statement workloads

3. pgbouncer.ini configuration:
   [databases]
   production = host=localhost port=5432 dbname=production

   [pgbouncer]
   pool_mode = transaction
   max_client_conn = 2000
   default_pool_size = 25   # = max_connections / number_of_databases
   min_pool_size = 5
   reserve_pool_size = 5
   server_idle_timeout = 600
   client_idle_timeout = 0

4. Monitoring PgBouncer:
   Connect to the PgBouncer admin: psql -p 6432 pgbouncer
   SHOW POOLS;   -- active/waiting clients, server connections
   SHOW STATS;   -- requests per second, average query time
   Alert on: cl_waiting > 0 for more than 5 seconds (connection queue building up)

5. PgBouncer in Kubernetes:
   - Deploy as a sidecar or as a shared deployment per database cluster
   - Use environment variable injection for credentials (never hardcode passwords)

Return: pgbouncer.ini configuration, pool size calculation, mode recommendation, and monitoring setup.