I have always been curious to look at a certain Postgres backend connection’s memory consumption. With this new pg_backend_memory_contexts view, we can now take a closer look at how much of memory is exactly allocated for a given Postgres backend process – be it to simply get an understanding of the total memory used, or even if we intend to identify the TOP 10 memory utilizing contenders.

This is how we can use the pg_backend_memory_contexts view:

Total Memory by session:

postgres=# SELECT pg_size_pretty(sum(used_bytes)) AS “TotalMem by session” FROM pg_backend_memory_contexts;

Get Total Memory Consumed By Each Session in Postgres using pg_backend_memory_contexts

TOP 10 memory utilizing contenders:
postgres=# SELECT * FROM pg_backend_memory_contexts ORDER BY used_bytes DESC LIMIT 10;

Get Top 10 Memory Contenders in Postgres using pg_backend_memory_contexts

or a little more granular (but complex to understand) way would be to do this:
postgres=# SELECT name,pg_size_pretty(sum(used_bytes)) FROM pg_backend_memory_contexts

Get Top Memory Consumption in Postgres using pg_backend_memory_contexts

Did you ever have a need to identify memory utilization in Postgres backend? How did you do it? Let us know by entering your comments below.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>