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;
On Fri, Oct 10, 2025 at 4:08 PM Rahila Syed <[email protected]> wrote:
> 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
> <https://www.postgresql.org/message-id/flat/cah2l28v8mc9hdt8qosj8trmkau_8fm_hks41neo9-6zakuz...@mail.gmail.com>
>
> 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_contexts
> <https://www.postgresql.org/docs/current/view-pg-backend-memory-contexts.html>
>
> Thank you,
> Rahila Syed
>
>
>
>
