Postgresql – Can we allocate 10 gb RAM to postgres out of 30gb

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.

Yes, PostgreSQL performance is influenced by the amount of RAM available to PostgreSQL, among many other factors.

PostgreSQL benefits from RAM for:

• The buffer cache in shared_buffers. Think of this as the database's first level cache.
• work_mem for performing sorts, hash joins, etc
• maintenance_work_mem for index building etc
• Operating system disk cache - RAM that appears to be "unused" will be used by the OS to cache disk blocks, so they're much faster for PostgreSQL to read. This is estimated with effective_cache_size.

It's not as simple as "more RAM good" though.

Lots of slow RAM will be worse than less fast RAM for a database that fits entirely in RAM on both systems. Once you have enough RAM to cache the indexes and tables in heavy use, more RAM generally makes little difference.

NUMA (non-uniform memory architecture) machines can sometimes have performance issues related to NUMA page migration because the Linux kernel doesn't understand PostgreSQL's use of shared memory properly. This can have a drastic influence on performance, so a "bigger" machine can be slower. Appropriate kernel tuning will solve this issue.

There's a cost to maintaining shared_buffers, so too big a shared_buffers can slow things down. It's a balance between having enough workspace and the maintenance cost of that workspace.

effective_cache_size tells the PostgreSQL query planner how much system RAM is thought to be used for disk cache, and will help it choose better plans. If unset or incorrectly set the planner is less likely to pick the correct plan for a system.

A machine with tons of RAM and a pathetic disk subsystem might still be slower than one with little RAM and really fast disks - depending a lot on the workload.

RAM makes less difference for write-heavy workloads, it's mostly beneficial for read-heavy workloads.

You might be noticing a theme here - it depends on what you're doing, and no single system spec like amount of RAM can be taken in isolation. It'd be nice if it was simpler, but it isn't.