Fwd: sensible configuration of max_connections

Hi Chris

On Fri, 7 Feb 2020, 08:36 Chris Withers, <chris(at)withers(dot)org> wrote:

> Hi All,
>
> What's a sensible way to pick the number to use for max_connections?
>

Sensible in this context is some what variable. Each connection in
PostgreSQL will be allocated a backend process. These are not the lightest
weight of things.

Each connection takes up space in shared memory, as mentioned in the manual.

> I'm looking after a reasonable size multi-tenant cluster, where the
> master handles all the load and there's a slave in case of hardware
> failure in the master.
> The machine is used to host what I suspect are mainly django
> applications, so lots of short web requests, not sure how much, if any,
> django's orm does connection pooling.
>
> I arbitrarily picked 1000 for max_connections and haven't had any
> problems, but onboarding another app that handles a few million requests
> per day on Monday and thinking about potential problems related to the
> number of available connections.
>
> What's "too much" for max_connections? What happens when you set it to
> high? What factors affect that number?
>

When sizing max_connections you need to trade off how many connections your
application will use at peak vs how much RAM and CPU you have.

Each connection is capable of allocating work_mem and has a stack etc.

As such you don't want max_connections to be able to run your system out of
RAM.

Given your situation I'd very seriously look at connection pooling using
PgBouncer or similar. That way you can run with a far smaller
max_connections and still cope with applications configured with large
usually idle connection pools.

> cheers,
>
> Chris
>

Regards,
Chris Ellis

>