Memory usage of PostgreSQL

Question

I'm using postgreSQL 9.3 database and I have increased the shared_buffers and effective_cache_size in the settings.

Will postgreSQL always take this amount of memory or only when needed?

I'm running my database on an Amazon EC2 instance and I will monitor the memory with a CloudWatch custom metric and I'm now thinking about it if the memory I set in the configuration will always be reserved i.e. appears to be used in the cloudWatch metric.

If this is the case, how can I monitor the actual used memory of postgreSQL?

Answer 1

shared_buffers are statically taken at startup and are never resized. effective_cache_size is just a hint to the optimizer. it is never allocated. it merely gives a hint of what is going on. so shared_buffers is what you see as taken.

• machinery: Thank you for the answer. But it is possible for postgreSQL to go beyond the shared_buffers size, i.e. to take more memory?
• Hans-Jürgen Schönig: well, shared_buffers are not the only memory area allocated statically. my personal rule of thumb is: shared_buffers + 10% is roughly what you see as statically allocated memory. the rest is process local memory.

Answer 2

shared_buffers is the statically allocated shared cache all PostgreSQL backends share.

in addition to this, each back-end has private memory which it uses for query plans, query text, prepared statements, cursors, and much more. It also has some query working memory, controlled by work_mem, which is used for in-memory sorts/joins/etc, and has maintenance_work_mem that's used for things like index builds.

PostgreSQL as a whole always uses more than shared_buffers. It has other small shared memory segments that're used for many things, as well as the per-backend working memory.