Connection Scaling

Connection scaling is concerned with the database science of increasing the number of users connected to a database without performance degradation. PostgreSQL can support thousands of users. For OLTP type SQL requests, it does not correlate into thousands of simultaneously occurring SQL requests being sent to the database. Why? Because most OLTP transactions are small and fast, usually in the sub-second range, even as low as 10-20 milliseconds. If you look at it from a time slice perspective, even though you might have hundreds of requests being done within the same second, in actually, most of them are not occurring at the same exact time within that second. Hence, a hundred concurrent active transactions per second may realistically result in as little as 3-10 concurrent, active connections at any given moment. Taking a step back and looking at the bird’s eye view of the situation, an optimized PG environment can normally handle between 30k to 70k TPS (Transactions Per Second).

Connection Scaling can usually be achieved in one or more of the following ways:

1. Use a connection pooler.
2. Load-balance the read requests.
3. Increase the number of vCPUs.
4. Optimize SQL statements to run as efficiently as possible, i.e., reduce their duration.

CPU utilization is everything when talking about scaling connections in PostgreSQL since every connection is a separate operating system process, unlike other DBMS vendors that may use threads instead. vCPUs are the number of processing threads that a chipset provides per core multiplied by the number of occupied sockets.

It all boils down to this in a PostgreSQL world: Limit the number of concurrent, active connections.

The way to detect the true, concurrent, active connections is to simply query the pg_stat_activity table where state in (‘active’,’idle in transaction’) . What comes back is the true, concurrent, active connections. It is this number that concerns us as PG DBAs.

In my personal experience at many client sites over the course of 11 years, I have observed that you start to experience degraded performance when the concurrent, active connections count approaches or exceeds this number:

(number of vCPUs * 2) + (number of vCPUs / 2)

Hence, the following examples of CPU tolerance:

08 vCPUs max range: 16-20 concurrent, active connections
16 vCPUs max range: 32-40 concurrent, active connections
32 vCPUs max range: 64-80 concurrent, active connections

This formula seems to work regardless of whether the underlying hardware is rotating hard disks or SSDs, or whether the data is on disk or in memory.

— Michael Vitale,  Postgres DBA on Team Elephas