Thank you so much. That helps.
I am planning to use pg_stat_get_backend_memory_contexts function something as below by joining this to the pg_stat_activity. Hope this is the right usage. Somehow i am getting an error stating the function doesn't exist but it might be because of the version. I will try with a higher version.
SELECT pa.pid,
pa.usename,
pa.application_name,
pa.state,
mc.name AS memory_context,
pg_size_pretty(mc.used_bytes) AS used_memory
FROM pg_stat_activity pa
JOIN LATERAL pg_stat_get_backend_memory_contexts(pa.pid) mc ON TRUE
WHERE pa.pid <> pg_backend_pid()
ORDER BY mc.used_bytes DESC;
However, is the below query, which was shared by Veem in above email thread is also going to give similar memory consumption information i.e. Avg memory consumption per query from pg_stat_statements?
WITH block_size AS (
SELECT setting::int AS size FROM pg_settings WHERE name = 'block_size'
)
SELECT
query,
calls,
pg_size_pretty(temp_blks_read * bs.size) AS temp_read_in_bytes,
pg_size_pretty(temp_blks_written * bs.size) AS temp_written_in_bytes
FROM pg_stat_statements, block_size bs
WHERE temp_blks_read > 0 OR temp_blks_written > 0
ORDER BY temp_blks_written DESC
LIMIT 10;
Hi,
The other question I had was , are there any pg_* views using which, we are able to see which session/connection is using the highest amount of memory? I don't see any such columns in pg_stats_activity
From a purely postgresql database point of view, this feature is being developed, you can view it here :
PostgreSQL: Enhancing Memory Context Statistics Reporting Basically, this lets you provide the pid of any PostgreSQL process to an sql function, which then returns its memory usage statistics.
Once this feature is committed, for obtaining memory usage statistics of any postgresql session you would need to run
SELECT pg_backend_pid() which will give you the pid of the postgresql backend.
You can then pass it to SELECT pg_get_process_memory_contexts(pid, ..), which will return the memory consumption data.
This is for future reference.
At the moment, you can use the following function on the connection whose memory you wish to inspect.
This works only for local connection i.e you can't use this function to query the statistics of any other
postgresql process or connection.
PostgreSQL: Documentation: 18: 53.5. pg_backend_memory_contextsThank you,
Rahila Syed
