public inbox for [email protected]  

help / color / mirror / Atom feed

From: sud <[email protected]>
To: Rahila Syed <[email protected]>
Cc: Ron Johnson <[email protected]>
Cc: pgsql-general <[email protected]>
Subject: Re: Alerting on memory use and instance crash
Date: Fri, 10 Oct 2025 20:57:47 +0530
Message-ID: <CAD=mzVWY5b-atBYZsiHj3beVpo30OFFULhKLeH_S10TEJHwn5A@mail.gmail.com> (raw)
In-Reply-To: <CAH2L28uRJcPy36jFd7TjGxm9YjAqdOLKZLwc7NxPoC4S=_DuJw@mail.gmail.com>
References: <CAD=mzVXjx-ULCM-ZdSz2RDSoL8EA1Bsd+0gBxgOCTWV_tC4GDw@mail.gmail.com>
	<[email protected]>
	<CAD=mzVVBHZ6480qrbPYhvpkkKqGeBpahV-+=r0xXU7O0XXQ5zw@mail.gmail.com>
	<CANzqJaCpM18oHAYpZHk9cW=0zjxMGKLKZOPCi1eHGm4ssb4ZsA@mail.gmail.com>
	<CAD=mzVV5upya19WHE0do-ievkCccgZaPydKKM_Z5p+n+moa69A@mail.gmail.com>
	<CAH2L28uRJcPy36jFd7TjGxm9YjAqdOLKZLwc7NxPoC4S=_DuJw@mail.gmail.com>

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-6ZAkuZKXw@mail....;
>
> 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
>
>
>
>

view thread (11+ messages)  latest in thread

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected], [email protected], [email protected]
  Subject: Re: Alerting on memory use and instance crash
  In-Reply-To: <CAD=mzVWY5b-atBYZsiHj3beVpo30OFFULhKLeH_S10TEJHwn5A@mail.gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox