How to calculate max_connections for PostgreSQL and default_pool_size for pgbouncer?

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.

Solution 1:

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.

Solution 2:

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.