My question is, How to know the amount of memory allocated to PostgreSQL and whether it is sufficient or not.
closed as too broad by a_horse_with_no_name, McNets, Andriy M, mustaccio, LowlyDBA Nov 14 '17 at 14:04
Please edit the question to limit it to a specific problem with enough detail to identify an adequate answer. Avoid asking multiple distinct questions at once. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.
@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.
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
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
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
work_mem parameter limits the amount of
RAM used per operation, i.e. per execution plan node, not per statement.