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