MIME-Version: 1.0
References: <CAD=mzVXjx-ULCM-ZdSz2RDSoL8EA1Bsd+0gBxgOCTWV_tC4GDw@mail.gmail.com>
 <9f175444-6bfc-4c6e-a609-e552d85c45a1@aklaver.com> <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>
In-Reply-To: <CAH2L28uRJcPy36jFd7TjGxm9YjAqdOLKZLwc7NxPoC4S=_DuJw@mail.gmail.com>
From: sud <suds1434@gmail.com>
Date: Fri, 10 Oct 2025 20:57:47 +0530
Message-ID: <CAD=mzVWY5b-atBYZsiHj3beVpo30OFFULhKLeH_S10TEJHwn5A@mail.gmail.com>
Subject: Re: Alerting on memory use and instance crash
To: Rahila Syed <rahilasyed90@gmail.com>
Cc: Ron Johnson <ronljohnsonjr@gmail.com>, 
	pgsql-general <pgsql-general@lists.postgresql.org>
Content-Type: multipart/alternative; boundary="0000000000002960c50640cf90a5"
Archived-At: <https://www.postgresql.org/message-id/CAD%3DmzVWY5b-atBYZsiHj3beVpo30OFFULhKLeH_S10TEJHwn5A%40mail.gmail.com>
Precedence: bulk

--0000000000002960c50640cf90a5
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

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 =3D '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=E2=80=AFPM Rahila Syed <rahilasyed90@gmail.com=
> 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_8=
FM_HKS41NeO9-6ZAkuZKXw@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
>
>
>
>

--0000000000002960c50640cf90a5
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr">Thank you so much. That helps.<div><br></div><div>I am pla=
nning to use=C2=A0<span style=3D"font-family:monospace">pg_stat_get_backend=
_memory_contexts function=C2=A0</span>something as below by=C2=A0joining th=
is to the pg_stat_activity. Hope this is the right usage. Somehow i am gett=
ing an error stating the function doesn&#39;t exist but it might be=C2=A0be=
cause of the version. I will try with a higher version.</div><div><br></div=
><div><font face=3D"monospace">SELECT=C2=A0 =C2=A0pa.pid,<br>=C2=A0 =C2=A0 =
pa.usename,<br>=C2=A0 =C2=A0 pa.application_name,<br>=C2=A0 =C2=A0 pa.state=
,<br>=C2=A0 =C2=A0 <a href=3D"http://mc.name">mc.name</a> AS memory_context=
,<br>=C2=A0 =C2=A0 pg_size_pretty(mc.used_bytes) AS used_memory<br>FROM=C2=
=A0 =C2=A0 pg_stat_activity pa<br>JOIN LATERAL=C2=A0 =C2=A0 pg_stat_get_bac=
kend_memory_contexts(pa.pid) mc ON TRUE<br>WHERE=C2=A0 =C2=A0 pa.pid &lt;&g=
t; pg_backend_pid()<br>ORDER BY=C2=A0=C2=A0 =C2=A0 mc.used_bytes DESC;</fon=
t></div><div><br></div><div><div>However, is the below query, which was sha=
red by Veem in above email thread is also going to give similar memory cons=
umption information i.e. Avg memory consumption per query=C2=A0from pg_stat=
_statements?</div><div></div></div><div><font face=3D"monospace"><br></font=
></div><div><font face=3D"monospace">WITH block_size AS (<br>=C2=A0 SELECT =
setting::int AS size FROM pg_settings WHERE name =3D &#39;block_size&#39;<b=
r>)<br>SELECT<br>=C2=A0 query,<br>=C2=A0 calls,<br>=C2=A0 pg_size_pretty(te=
mp_blks_read * bs.size) AS temp_read_in_bytes,<br>=C2=A0 pg_size_pretty(tem=
p_blks_written * bs.size) AS temp_written_in_bytes<br>FROM pg_stat_statemen=
ts, block_size bs<br>WHERE temp_blks_read &gt; 0 OR temp_blks_written &gt; =
0<br>ORDER BY temp_blks_written DESC<br>LIMIT 10;</font></div><div><br></di=
v></div><br><div class=3D"gmail_quote gmail_quote_container"><div dir=3D"lt=
r" class=3D"gmail_attr">On Fri, Oct 10, 2025 at 4:08=E2=80=AFPM Rahila Syed=
 &lt;<a href=3D"mailto:rahilasyed90@gmail.com">rahilasyed90@gmail.com</a>&g=
t; wrote:<br></div><blockquote class=3D"gmail_quote" style=3D"margin:0px 0p=
x 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex"><div d=
ir=3D"ltr"><div dir=3D"ltr">Hi,<br><br></div><div class=3D"gmail_quote"><bl=
ockquote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-lef=
t:1px solid rgb(204,204,204);padding-left:1ex"><div dir=3D"ltr"><div>The ot=
her 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 do=
n&#39;t=C2=A0see any such columns=C2=A0in pg_stats_activity</div></div></bl=
ockquote><div><br>From a purely postgresql database point of view,=C2=A0 th=
is feature is being developed, you can view it here :=C2=A0

<a href=3D"https://www.postgresql.org/message-id/flat/CAH2L28v8mc9HDt8QoSJ8=
TRmKau_8FM_HKS41NeO9-6ZAkuZKXw@mail.gmail.com" target=3D"_blank">PostgreSQL=
: Enhancing Memory Context Statistics Reporting</a>=C2=A0<br>Basically, thi=
s lets you provide the pid of any PostgreSQL process to an sql function, wh=
ich then returns its memory usage statistics.=C2=A0=C2=A0<br>Once this feat=
ure is committed, for obtaining=C2=A0memory usage statistics of any postgre=
sql session you would need to run=C2=A0<br>SELECT pg_backend_pid() which wi=
ll give you the pid of the postgresql backend. <br>You can then pass it to =
SELECT pg_get_process_memory_contexts(pid, ..),=C2=A0 which will return the=
 memory consumption data.=C2=A0<br>This is for future reference.<br><br>At =
the moment, you can use the following function on the connection whose memo=
ry you wish to inspect.=C2=A0=C2=A0<br>This works only for local connection=
 i.e you can&#39;t use this function to query the=C2=A0statistics of any ot=
her=C2=A0<br>postgresql process or connection.=C2=A0<br><a href=3D"https://=
www.postgresql.org/docs/current/view-pg-backend-memory-contexts.html" targe=
t=3D"_blank">PostgreSQL: Documentation: 18: 53.5.=C2=A0pg_backend_memory_co=
ntexts</a><br><br>Thank you,</div><div>Rahila Syed<br><br><br>=C2=A0</div><=
/div></div>
</blockquote></div>

--0000000000002960c50640cf90a5--