When we configure the memory setting for the Postgres DB what is the
recommended memory allocation for each connection ? Is there any
formula to apply ?
There is no per-connection pre-allocated memory that a DBA would define. A session will allocate dynamically what is needed, or use the shared memory pre-allocated at server start depending on the kind of usage.
See Resource consumption in the doc about what can be defined.
I know 25% of the memory of the Server should be allocated. But how do
we allocate based on the DB connections? How do we know, what is the
maximum number of connections should be allocated?
The maximum number of client connections (
max_connections) is factored into the amount of shared memory that is pre-allocated at server start.
In Managing Kernel Resources, the doc says that its size in bytes is:
(1800 + 270 * max_locks_per_transaction) * max_connections
But memory is not the main factor in deciding the maximum number of connections, it's the raw power and number of cores of the machine, and wether a connection pooler is going to be associated to the server. The point is mostly to avoid too many concurrent active queries provoking the server to halt to a crawl.
Also in a multi-nodes environment can we allocate more connections for
each node (in Postgres-ds.xml max connections) than what is actually
allocated in the DB?
If there are multiple client nodes that point to the same PostgreSQL instance, the combined count of their connections cannot outreach
However when using a connection pooler,
connection as a word becomes ambiguous. You want to know whether it's a connection from the client to the pooler or a connection from the pooler to the database server. Generally there are much more allowed connections to the pool than to the database.