public inbox for [email protected]
help / color / mirror / Atom feedFrom: veem v <[email protected]>
To: Adrian Klaver <[email protected]>
To: sud <[email protected]>
Cc: Ron Johnson <[email protected]>
Cc: pgsql-general <[email protected]>
Subject: Re: Alerting on memory use and instance crash
Date: Thu, 9 Oct 2025 20:51:56 +0530
Message-ID: <CAB+=1TXyqxdxaaRaQwGbqvjPHQ6gDzb=Pg1yBkUtm5uG6=2O4w@mail.gmail.com> (raw)
In-Reply-To: <[email protected]>
References: <CAD=mzVXjx-ULCM-ZdSz2RDSoL8EA1Bsd+0gBxgOCTWV_tC4GDw@mail.gmail.com>
<[email protected]>
<CAD=mzVVBHZ6480qrbPYhvpkkKqGeBpahV-+=r0xXU7O0XXQ5zw@mail.gmail.com>
<[email protected]>
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 <[email protected]>
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
> [email protected]
>
>
>
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], [email protected]
Subject: Re: Alerting on memory use and instance crash
In-Reply-To: <CAB+=1TXyqxdxaaRaQwGbqvjPHQ6gDzb=Pg1yBkUtm5uG6=2O4w@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