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;
TOP 10 memory utilizing contenders:
postgres=# SELECT * FROM pg_backend_memory_contexts ORDER BY used_bytes DESC LIMIT 10;
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
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.