How to understand the PostgreSQL discrepancy between connection pool recommendation ((2 * n_cores) + n_disks) and support for 100s of connections?

Question

From PostgreSQL docs:

• Number of Database Connections -> How to Find the Optimal Database Connection Pool Size

  for optimal throughput the number of active connections should be somewhere near ((core_count * 2) + effective_spindle_count)

• Tuning Your PostgreSQL Server -> max_connections

  Generally, PostgreSQL on good hardware can support a few hundred connections.

For me - not an experienced DBA - there's a discrepancy somewhere in here, especially looking at the offerings of some DB-as-a-Service providers.

For example, at this time Amazon RDS's largest machine (db.r3.8xlarge) has 32 vCPUs, which according to the first formula would perhaps manage to run optimally with 100 connections in the pool, given many disks. Wouldn't it though run very badly with the "few hundred connections" from the second formula?

Even more extreme is the discrepancy for another DBaaS provider, who proposes a 2 core server with 500 concurrent connections. How could this possibly work well?

If I'm misunderstanding something, please let me know. Many thanks!

Answer 1

"Can support" != "optimal throughput".

You can use lots of connections, but it's slower.

If you use fewer connections and queue work, you get the same amount of work done in a smaller time.

Even more extreme is the discrepancy for another DBaaS provider, who proposes a 2 core server with 500 concurrent connections. How could this possibly work well?

Either they're using a connection pooling frontend like PgBouncer in transaction pooling mode, or it won't work well.

People like big numbers though, so they'll give you big numbers.

They're actually hurting performance by doing so. PostgreSQL has some costs that scale linearly with max_connections, so even if the connections are not used it still has a performance impact.

Additionally, even idle connections have some further housekeeping costs.

If the connections are actively working, then you also have contention on system resources and on internal locks.

I routinely run into people who're having PostgreSQL performance issues - and who try to solve them by adding more connections, more workers in their application, etc. Especially people running queuing systems. It's surprisingly hard to persuade them that lowering the number of workers will make the system go faster, and that their original performance issues stemmed from having too many in the first place.

Answer 2

A lot of applications have poor connection discipline, keeping connections open even when they are not being used.

Setting a high connection limit is cheap insurance against these applications. Until something changes and the applications decide to actively use all of those connections, then the insurance becomes pretty expensive.

Answer 3

An important distinction to make between the two claims compared in the question is that the first is a rough formulation for the number of active connections at any one time. The second claim is for the setting that you put in place for the maximum allowed that Postgres will accept. These are two separate things.

When you go back and read the Optimal Database Connection Pool Size article, you will find that it suggests that you set your active connection pooling at the client side, as opposed to the server side. They also suggest that you leave sufficient capacity in your max_connections value to accommodate fixed connections, such as hands-on client activity and administrative activity. You don't want to set your max_connections to the active connection limit of your workers or you might not be able to psql in when you need to!