Understanding High-Concurrency Connection Issues in PostgreSQL and the Benefits of Using pgBouncer
The article explains how thousands of simultaneous client connections to a single PostgreSQL primary can cause severe write‑performance degradation, how introducing a connection pool such as pgBouncer reduces active backend processes dramatically, and why external pooling is preferred over built‑in solutions, illustrated with real‑world examples and a reference to the open‑source Pigsty distribution.
Yesterday a client case revealed that directly connecting thousands of client sessions to a single PostgreSQL primary caused the write performance to collapse: over 4,000 connections with about 1,500 active resulted in INSERT latency of hundreds of milliseconds instead of microseconds.
Switching to a connection pool (pgBouncer) reduced the number of active server‑side connections from 1,500 to just 2, allowing the same 20,000 TPS to be handled smoothly because all INSERT requests were queued on the two pooled connections, eliminating LWLock contention.
The article also discusses PostgreSQL’s multi‑process architecture, where each client connection spawns a separate backend process, leading to heavy OS overhead and poor scalability under high‑concurrency workloads, especially compared with MySQL’s thread model.
It argues that adding a built‑in connection pool to PostgreSQL is unnecessary; an external pool offers greater flexibility for load balancing, failover, read/write splitting, and other operational concerns, aligning with the Unix philosophy of “one program does one thing well.”
For newcomers who are accustomed to opening a database connection per HTTP request, the lack of an internal pool can appear as a flaw, but the problem is already solved at the architecture level by using tools like pgBouncer.
The piece also mentions that many enterprises now bundle PostgreSQL with external components (connection pool, backup, monitoring, HA) into a “complete car” – a fully managed database service – and highlights the open‑source Pigsty distribution, which provides a ready‑to‑use PostgreSQL cluster with built‑in connection pooling, monitoring, and high availability via simple YAML configuration.
Overall, the article provides practical guidance on diagnosing high‑concurrency performance issues in PostgreSQL, demonstrates the effectiveness of connection pooling, and recommends external pooling solutions over modifying the core database engine.
IT Services Circle
Delivering cutting-edge internet insights and practical learning resources. We're a passionate and principled IT media platform.
How this landed with the community
Was this worth your time?
0 Comments
Thoughtful readers leave field notes, pushback, and hard-won operational detail here.