9

From PostgreSQL docs:

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!

3
  • DBaaS provider, who proposes a 2 core server with 500 concurrent connections Could you provide a link to the exact proposal? Nov 15, 2014 at 10:23
  • @ErwinBrandstetter There you go: elephantsql.com/plans.html Nov 17, 2014 at 12:58
  • Thanks. Well, 500 connections seem more like the advertised maximum, not the proposed workload for best performance here. Nov 17, 2014 at 13:11

3 Answers 3

12

"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.

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.

0

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!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.