Re: Alerting on memory use and instance crash

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(dot)klaver(at)aklaver(dot)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(dot)klaver(at)aklaver(dot)com
>
>
>