Troubleshooting PostgreSQL Connection Pooling with PgBouncer (9824)

Technical Overview

Engineering breakdown of Troubleshooting PostgreSQL Connection Pooling with PgBouncer (9824). Bare-metal hardware performance requires isolated kernel parameters, tuned sysctl values, and a deep understanding of PgBouncer's pooling modes (session, transaction, statement). This article walks through the full stack: OS‑level TCP tuning, PgBouncer configuration, PostgreSQL max_connections, and observability tooling.

Architecture & Flow

Client → PgBouncer (listen port 6432) → PostgreSQL (port 5432). PgBouncer maintains a pool of server connections per database/user pair. In transaction pooling mode, a server connection is returned to the pool after each transaction, dramatically reducing idle connections. The diagram below (rendered by the network-topology block) shows the logical layers and where latency spikes typically appear.

Common Failure Modes & Debug Checklist

1. Connection Refused / Timeout – Verify listen_addr/listen_port and firewall rules. Use ss -ltnp | grep 6432 on the host.
2. Pool Exhaustion – SHOW POOLS; via admin console shows cl_active vs cl_waiting. Increase default_pool_size or enable reserve_pool.
3. Authentication Errors – Ensure userlist.txt uses SCRAM‑SHA‑256 hashes matching PostgreSQL pg_authid.
4. Idle Transaction Timeouts – PostgreSQL idle_in_transaction_session_timeout can kill server connections while PgBouncer still thinks they’re alive. Align both sides or disable on PG.
5. DNS / Host Resolution – PgBouncer resolves host= at startup. Use IP addresses or enable dns_zone_check.

Enable log_connections, log_disconnections, and log_pooler_errors for rapid root‑cause analysis. Correlate with pg_stat_activity on the backend.