PostgreSql allocate memory for each connection

Question

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 ?

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?

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?

Answer 1

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

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.

• kds: Thanks Daniel for the update. With regards to the final comments, What I need to know from both aspects ? Also what is the best connection pool handling tool we can implement with PostgreSQL. I hope we could use Pgpool. Any other options ?
• Daniel Vérité: @kds: the reference to postgres-ds.xml suggests you use JBoss. You want to check what they recommend for pooling connections. I don't have any experience with it personally.
• eckes: I am wondering where the Formular with the 270 bytes lock table entry is found nowadays. Cant find it in the manual after 8.4 postgresql.org/docs/8.4/…?
• Daniel Vérité: @eckes: please ask a new question so it gets noticed.