My question is, How to know the amount of memory allocated to PostgreSQL and whether it is sufficient or not.
@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.