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>
 <af93f593-7bb6-404a-ac5f-f43ab244fe5f@aklaver.com>
In-Reply-To: <af93f593-7bb6-404a-ac5f-f43ab244fe5f@aklaver.com>
From: veem v <veema0000@gmail.com>
Date: Thu, 9 Oct 2025 20:51:56 +0530
Message-ID: <CAB+=1TXyqxdxaaRaQwGbqvjPHQ6gDzb=Pg1yBkUtm5uG6=2O4w@mail.gmail.com>
Subject: Re: Alerting on memory use and instance crash
To: Adrian Klaver <adrian.klaver@aklaver.com>, sud <suds1434@gmail.com>
Cc: Ron Johnson <ronljohnsonjr@gmail.com>, 
	pgsql-general <pgsql-general@lists.postgresql.org>
Content-Type: multipart/alternative; boundary="0000000000005b43af0640bb5d5d"
Archived-At: <https://www.postgresql.org/message-id/CAB%2B%3D1TXyqxdxaaRaQwGbqvjPHQ6gDzb%3DPg1yBkUtm5uG6%3D2O4w%40mail.gmail.com>
Precedence: bulk

--0000000000005b43af0640bb5d5d
Content-Type: text/plain; charset="UTF-8"

My 2cents:-
In regards to the memory consumption question of OP:- Wouldn't the column
"temp_blks_read" and "temp_blks_written" in pg_stats_statements provide
details around the memory consumption i.e. when the query exceeds the
work_mem then it tries occupying the temp blocks. Something as below.
Correct me if I'm wrong.

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 Thu, 9 Oct 2025 at 01:24, Adrian Klaver <adrian.klaver@aklaver.com>
wrote:

> On 10/8/25 11:58, sud wrote:
> > Thank you.
> > My understanding may be wrong here.And my apology as I am using the
> > example of Oracle again even though these two are not the same. But
> > being worked for a long time in Oracle so trying to understand exactly
> > how it's different.
> >
> > In oracle RAC(real application cluster) database, we have single
> > databases with multiple nodes/instances/memory, which means the
> > underlying storage is same but the memory/cpu of each of those instances
> > are different and any of the instances can be down but the database
> > still operates routing the application traffic of the downed node to
> > others. Similarly even in AWS Aurora postgres also there can be multiple
> > instances like Writer and Reader instances/nodes and the underlying
> > storage being the same. So I was thinking of any such cluster level pg_*
> > views available by querying which we would be able to know if any one of
> > the nodes is down ?   Also , I don't see any such pg_* view which can
> > show the statistics of all the instances combinely i.e. cluster level
> > statistics.
> >
> > Do you mean in normal Postgres it's alway a single instance/memory and
> > single storage attached? then I also do not see any such cluster level
> > views in aws aurora postgres too? Pardon if it's a silly one to ask.
> >
>
> It would be helpful if you specified exactly what variety of Postgres
> you are using and it's version.
>
> If you are using AWS Aurora Postgres then you will need to look at pages
> like this:
>
>
> https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/AuroraPostgreSQL.Managing.html
>
> This list is for the community version of Postgres and it's been a long
> time since AWS saw fit to have someone on the list and when they where
> here they did not really provide answers.
>
> --
> Adrian Klaver
> adrian.klaver@aklaver.com
>
>
>

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

<div dir=3D"ltr"><div>My 2cents:-</div><div>In regards to the memory consum=
ption question of OP:- Wouldn&#39;t the column &quot;temp_blks_read&quot; a=
nd &quot;temp_blks_written&quot; in pg_stats_statements provide details aro=
und the memory consumption i.e. when the query exceeds the work_mem then it=
 tries occupying the temp blocks. Something as below. Correct me if I&#39;m=
 wrong.=C2=A0</div><div><br></div><div>WITH block_size AS (<br>=C2=A0 SELEC=
T setting::int AS size FROM pg_settings WHERE name =3D &#39;block_size&#39;=
<br>)<br>SELECT<br>=C2=A0 query,<br>=C2=A0 calls,<br>=C2=A0 pg_size_pretty(=
temp_blks_read * bs.size) AS temp_read_in_bytes,<br>=C2=A0 pg_size_pretty(t=
emp_blks_written * bs.size) AS temp_written_in_bytes<br>FROM pg_stat_statem=
ents, 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;<br></div></div><br><div=
 class=3D"gmail_quote gmail_quote_container"><div dir=3D"ltr" class=3D"gmai=
l_attr">On Thu, 9 Oct 2025 at 01:24, Adrian Klaver &lt;<a href=3D"mailto:ad=
rian.klaver@aklaver.com">adrian.klaver@aklaver.com</a>&gt; wrote:<br></div>=
<blockquote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-=
left:1px solid rgb(204,204,204);padding-left:1ex">On 10/8/25 11:58, sud wro=
te:<br>
&gt; Thank you.<br>
&gt; My understanding may be wrong here.And my apology as I am using the <b=
r>
&gt; example of Oracle again even though these=C2=A0two are not the same. B=
ut <br>
&gt; being worked for a long time in Oracle so trying to understand exactly=
 <br>
&gt; how it&#39;s different.<br>
&gt; <br>
&gt; In oracle RAC(real application cluster) database, we have single <br>
&gt; databases with multiple nodes/instances/memory, which means the <br>
&gt; underlying storage is same but the memory/cpu of each of those instanc=
es <br>
&gt; are different and any of the instances can be=C2=A0down but the databa=
se <br>
&gt; still operates routing the application traffic of the downed node to <=
br>
&gt; others. Similarly even in AWS Aurora postgres also there can be multip=
le <br>
&gt; instances like Writer and Reader instances/nodes and the underlying <b=
r>
&gt; storage being the same. So I was thinking of any=C2=A0such cluster lev=
el pg_* <br>
&gt; views available=C2=A0by querying which we would be able to know if any=
 one of <br>
&gt; the nodes is down ?=C2=A0 =C2=A0Also , I don&#39;t see any such pg_* v=
iew which can <br>
&gt; show the statistics of all the instances combinely i.e. cluster level =
<br>
&gt; statistics.<br>
&gt; <br>
&gt; Do you mean in normal Postgres it&#39;s alway a single instance/memory=
 and <br>
&gt; single storage attached? then I also do not see any such cluster level=
 <br>
&gt; views in aws aurora postgres too? Pardon if it&#39;s a silly one to as=
k.<br>
&gt; <br>
<br>
It would be helpful if you specified exactly what variety of Postgres <br>
you are using and it&#39;s version.<br>
<br>
If you are using AWS Aurora Postgres then you will need to look at pages <b=
r>
like this:<br>
<br>
<a href=3D"https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/Aur=
oraPostgreSQL.Managing.html" rel=3D"noreferrer" target=3D"_blank">https://d=
ocs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/AuroraPostgreSQL.Managi=
ng.html</a><br>
<br>
This list is for the community version of Postgres and it&#39;s been a long=
 <br>
time since AWS saw fit to have someone on the list and when they where <br>
here they did not really provide answers.<br>
<br>
-- <br>
Adrian Klaver<br>
<a href=3D"mailto:adrian.klaver@aklaver.com" target=3D"_blank">adrian.klave=
r@aklaver.com</a><br>
<br>
<br>
</blockquote></div>

--0000000000005b43af0640bb5d5d--