Memory allocation in PostgreSQL [closed]

Question

My question is, How to know the amount of memory allocated to PostgreSQL and whether it is sufficient or not.

Answer 1

@Arun Raut, As you said that you want to know that memory (RAM) allocation in PostgreSQL.

What processes use much RAM and why?

Shared memory is accessible by all postgres server processes.

Normally the most part of it is shared_buffers. As per PostgreSQL BOL Here PostgreSQL suggest to use 25% of RAM, though often less values are used.

The wal_buffer are normally much smaller, 1/32 of shared_buffer is default. Anyway, you are allowed to set it to arbitrarily large value.

The amount of memory used for table and advisory locks is

About 270 * max_locks_per_transaction
* (max_connections + max_prepared_transactions) bytes

you are probably safe, unless you are doing something tricky using lots advisory locks and increase max_locks_per_transaction to really large values

Same for max_pred_locks_per_transaction - predicate locks are used only for non-default transaction isolation levels, make sure not to increase this setting too much.

No more than autovaccum_max_workers workers, each uses maintenance_work_mem or autovaccum_work_mem of RAM.

Ideally, your tables are not too large and your RAM is not too small , so you can afford setting autovaccum_work_mem to reflect your smallest table size.

Practically, you will autovaccum_work_mem to cover all the small tables in your DB, whatever that means.

Backends and their bgworkers are the most important , as there might be quite a few of them, namely max_connections and max_workers.

The work_mem parameter limits the amount of RAM used per operation, i.e. per execution plan node, not per statement.

For your further ref Here, Here and https://stackoverflow.com/questions/30328861/most-impactful-postgres-settings-to-tweak-when-host-has-lots-of-free-ram