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>
In-Reply-To: <CAD=mzVV5upya19WHE0do-ievkCccgZaPydKKM_Z5p+n+moa69A@mail.gmail.com>
From: Rahila Syed <rahilasyed90@gmail.com>
Date: Fri, 10 Oct 2025 16:08:43 +0530
Message-ID: <CAH2L28uRJcPy36jFd7TjGxm9YjAqdOLKZLwc7NxPoC4S=_DuJw@mail.gmail.com>
Subject: Re: Alerting on memory use and instance crash
To: sud <suds1434@gmail.com>
Cc: Ron Johnson <ronljohnsonjr@gmail.com>, 
	pgsql-general <pgsql-general@lists.postgresql.org>
Content-Type: multipart/alternative; boundary="0000000000003db8410640cb86e8"
Archived-At: <https://www.postgresql.org/message-id/CAH2L28uRJcPy36jFd7TjGxm9YjAqdOLKZLwc7NxPoC4S%3D_DuJw%40mail.gmail.com>
Precedence: bulk

--0000000000003db8410640cb86e8
Content-Type: text/plain; charset="UTF-8"

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.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

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

<div dir=3D"ltr"><div dir=3D"ltr">Hi,<br><br></div><div class=3D"gmail_quot=
e gmail_quote_container"><blockquote class=3D"gmail_quote" style=3D"margin:=
0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex">=
<div dir=3D"ltr"><div>The other question I had was , are there any pg_* vie=
ws using which, we are able to see which session/connection is using the hi=
ghest amount of memory? I don&#39;t=C2=A0see any such columns=C2=A0in pg_st=
ats_activity</div></div></blockquote><div><br>From a purely postgresql data=
base point of view,=C2=A0 this 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">PostgreSQL: Enhancing Memory=
 Context Statistics Reporting</a>=C2=A0<br>Basically, this lets you provide=
 the pid of any PostgreSQL process to an sql function, which then returns i=
ts memory usage statistics.=C2=A0=C2=A0<br>Once this feature is committed, =
for obtaining=C2=A0memory usage statistics of any postgresql session you wo=
uld need to run=C2=A0<br>SELECT pg_backend_pid() which will give you the pi=
d of the postgresql backend. <br>You can then pass it to SELECT pg_get_proc=
ess_memory_contexts(pid, ..),=C2=A0 which will return the memory consumptio=
n data.=C2=A0<br>This is for future reference.<br><br>At the moment, you ca=
n use the following function on the connection whose memory you wish to ins=
pect.=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 other=C2=A0<br>postg=
resql process or connection.=C2=A0<br><a href=3D"https://www.postgresql.org=
/docs/current/view-pg-backend-memory-contexts.html">PostgreSQL: Documentati=
on: 18: 53.5.=C2=A0pg_backend_memory_contexts</a><br><br>Thank you,</div><d=
iv>Rahila Syed<br><br><br>=C2=A0</div></div></div>

--0000000000003db8410640cb86e8--