16

Is there a rule or something I can use to calculate a good number for max_connections, default_pool_size and max_client_conn?

The defaults are odd. PostgreSQL defaults to max_connections=100 while pgbouncer defaults to default_pool_size=20. Shouldn't default_pool_size always be higher than max_connections? Otherwise, what's the point? I thought pgbouncer was meant to let us handle more connections by lowering their overhead (by reusing PostgreSQL's connections). I'm confused.

I'm looking for advice similar to the ones found in PostgreSQL's wiki, like "this parameter should be ~50% of your memory".

I remember there was a spreadsheet for MySQL that would let you calculate these kind of parameters. It would be awesome to have something like that for PostgreSQL/pgbouncer.

2 Answers 2

13

First, please read our canonical question on Capacity Planning.
The specific advice you're asking for is capacity planning advice, and you're going to have to work that out on your own, for your particular environment.

Second, You are looking at this wrong.
The amount of memory (or any other resource) you have doesn't dictate the number of connections you set, the number of connections you need dictates how beefy a server you must purchase.
The per-connection resource requirements are given in the manual in considerable detail, as well as discussed on the Wiki you linked to. Figure out what your environment needs (or make an educated guess) and ensure the hardware you are going to run on can handle what you're going to throw at it.


Specifically re: connection limits and pool size, you should have "enough" connections to meet your application's requirements - either on a single server or through a pool/bouncer.

"Enough" is a relative number: An application that makes (and continually reuses) one connection only requires one connection. An application that establishes a connection for each end-user who logs in requires as many DB connections as it has users.

The default values for both Postgres and pgbouncer are sensible as defaults:

  • 100 database connections is a lot for the typical person throwing Postgres into an environment.
    Developers probably won't need more than 10. Anyone else will know enough to increase the number.

  • 20 connections from pgbouncer per DB pool means you can get 4 pools pointing at one server and not overwhelm the default Postgres connection limit.
    It is possible to have multiple pooled resources in pgbouncer pointing at one back-end database, and you always want some available connections on your back-end servers.

If the defaults are not suitable for your environment you are expected to change them.

Remember that pooled connections does not mean "always tie up every database connection available".
The point of pgbouncer as you noted is to reuse connections. The efficiency gain here doesn't require that you tie up every available connection, merely that you don't disconnect, reconnect, re-negotiate SSL, re-authenticate to the database, and re-run your connection setup queries every time.

2
  • 8
    I don't see the point of buying more hardware before configuring things properly. "Anyone else will know enough to increase the number". Well, where can I learn to know enough? I'm not finding much material about connections. Is it just trial and error? The spreadsheet I mentioned for MySQL used to work like a charm. Using more connections than the indicated by it would result in the server running out of memory. Right now I have 4GB, I was expecting to have to increase the defaults. Also, 20x4=80, what are the other 20 for? Feb 25, 2013 at 17:42
  • 1
    @ChocoDeveloper Please re-read my answer in its entirety (you're asking some things that I've already addressed), and spend a few minutes with the documentation I linked to. You are still looking at this backwards (see the first paragraph of my answer). Bear in mind that Postgres is NOT MySQL: You need to forget everything you think you know from your MySQL tuning experience. Postgres is more like Oracle. Study the manual, and proceed according to the instructions it gives you.
    – voretaq7
    Feb 25, 2013 at 18:00
1

Notice the documentation's definition of default_pool_size

How many server connections to allow per user/database pair.

So if the default config is a pool size of 20, out of a 100 connections total, this implies 5 distinct user/database pairs will have to each max out their pool size before they reach the overall limit. Conversely, if for example you're using pgbouncer to route to a single database via a single user, your effective connection limit is 20, not 100, so you have to set the pool size for that use case accordingly. YMMV.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .