public inbox for [email protected]
help / color / mirror / Atom feedAlerting on memory use and instance crash
11+ messages / 5 participants
[nested] [flat]
* Alerting on memory use and instance crash
@ 2025-10-08 15:42 sud <[email protected]>
2025-10-08 16:21 ` Re: Alerting on memory use and instance crash Ron Johnson <[email protected]>
2025-10-08 16:22 ` Re: Alerting on memory use and instance crash Adrian Klaver <[email protected]>
0 siblings, 2 replies; 11+ messages in thread
From: sud @ 2025-10-08 15:42 UTC (permalink / raw)
To: pgsql-general <[email protected]>
Hi Experts,
It's postgres version 16. I have two questions on alerting as below.
1)If we want to have alerting on any node/instance that gets crashed :- In
other databases like Oracle the catalog Views like "GV$Instance" used to
give information on whether the instances are currently active/down or not.
But in postgres it seems all the pg_* views are instance specific and are
not showing information on the global/cluster level but are restricted to
instance level only. So is there any other way to query the pg_* views to
have alerts on the specific instance crash?
2)Is there a way to fetch the data from pg_* view to highlight the specific
connection/session/sqls which is using high memory in postgres?
Appreciate your guidance.
Regards
Sud
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Alerting on memory use and instance crash
2025-10-08 15:42 Alerting on memory use and instance crash sud <[email protected]>
@ 2025-10-08 16:21 ` Ron Johnson <[email protected]>
1 sibling, 0 replies; 11+ messages in thread
From: Ron Johnson @ 2025-10-08 16:21 UTC (permalink / raw)
To: pgsql-general <[email protected]>
On Wed, Oct 8, 2025 at 11:42 AM sud <[email protected]> wrote:
> Hi Experts,
>
> It's postgres version 16. I have two questions on alerting as below.
>
> 1)If we want to have alerting on any node/instance that gets crashed :- In
> other databases like Oracle the catalog Views like "GV$Instance" used to
> give information on whether the instances are currently active/down or not.
> But in postgres it seems all the pg_* views are instance specific and are
> not showing information on the global/cluster level but are restricted to
> instance level only. So is there any other way to query the pg_* views to
> have alerts on the specific instance crash?
>
In Postgresql, cluster == instance. That's a historical fluke which might
never go away. Thus, if the cluster is down, you can't access anything.
Connection poolers that use virtual IP addresses and are the modern
definition of "cluster" sit on top of individual PG clusters. Even though
the pooler auto-fails the (modern) cluster to the replica instance, PG
still thinks one cluster is down, and the former-replica cluster is now the
primary cluster.
Confusing? Yes. Just accept that *PG cluster == instance*, and that
*Postgresql
is not Oracle*.
> 2)Is there a way to fetch the data from pg_* view to highlight the
> specific connection/session/sqls which is using high memory in postgres?
>
--
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> lobster!
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Alerting on memory use and instance crash
2025-10-08 15:42 Alerting on memory use and instance crash sud <[email protected]>
@ 2025-10-08 16:22 ` Adrian Klaver <[email protected]>
2025-10-08 18:58 ` Re: Alerting on memory use and instance crash sud <[email protected]>
1 sibling, 1 reply; 11+ messages in thread
From: Adrian Klaver @ 2025-10-08 16:22 UTC (permalink / raw)
To: sud <[email protected]>; pgsql-general <[email protected]>
On 10/8/25 08:42, sud wrote:
> Hi Experts,
>
> It's postgres version 16. I have two questions on alerting as below.
>
> 1)If we want to have alerting on any node/instance that gets crashed :-
> In other databases like Oracle the catalog Views like "GV$Instance" used
> to give information on whether the instances are currently active/down
> or not. But in postgres it seems all the pg_* views are instance
> specific and are not showing information on the global/cluster level but
> are restricted to instance level only. So is there any other way to
> query the pg_* views to have alerts on the specific instance crash?
1) When you say instance do you mean database?
2) Not all system tables/views are database only.
For instance:
https://www.postgresql.org/docs/current/catalog-pg-database.html
https://www.postgresql.org/docs/current/catalog-pg-auth-members.html
https://www.postgresql.org/docs/current/catalog-pg-authid.html
https://www.postgresql.org/docs/current/view-pg-roles.html
> 2)Is there a way to fetch the data from pg_* view to highlight the
> specific connection/session/sqls which is using high memory in postgres?
>
> Appreciate your guidance.
>
> Regards
> Sud
--
Adrian Klaver
[email protected]
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Alerting on memory use and instance crash
2025-10-08 15:42 Alerting on memory use and instance crash sud <[email protected]>
2025-10-08 16:22 ` Re: Alerting on memory use and instance crash Adrian Klaver <[email protected]>
@ 2025-10-08 18:58 ` sud <[email protected]>
2025-10-08 19:06 ` Re: Alerting on memory use and instance crash Ron Johnson <[email protected]>
2025-10-08 19:54 ` Re: Alerting on memory use and instance crash Adrian Klaver <[email protected]>
0 siblings, 2 replies; 11+ messages in thread
From: sud @ 2025-10-08 18:58 UTC (permalink / raw)
To: Adrian Klaver <[email protected]>; Ron Johnson <[email protected]>; +Cc: pgsql-general <[email protected]>
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.
On Wed, Oct 8, 2025 at 9:52 PM Adrian Klaver <[email protected]>
wrote:
> On 10/8/25 08:42, sud wrote:
> > Hi Experts,
> >
> > It's postgres version 16. I have two questions on alerting as below.
> >
> > 1)If we want to have alerting on any node/instance that gets crashed :-
> > In other databases like Oracle the catalog Views like "GV$Instance" used
> > to give information on whether the instances are currently active/down
> > or not. But in postgres it seems all the pg_* views are instance
> > specific and are not showing information on the global/cluster level but
> > are restricted to instance level only. So is there any other way to
> > query the pg_* views to have alerts on the specific instance crash?
>
> 1) When you say instance do you mean database?
>
> 2) Not all system tables/views are database only.
>
> For instance:
> https://www.postgresql.org/docs/current/catalog-pg-database.html
> https://www.postgresql.org/docs/current/catalog-pg-auth-members.html
> https://www.postgresql.org/docs/current/catalog-pg-authid.html
> https://www.postgresql.org/docs/current/view-pg-roles.html
>
>
> > 2)Is there a way to fetch the data from pg_* view to highlight the
> > specific connection/session/sqls which is using high memory in postgres?
> >
> > Appreciate your guidance.
> >
> > Regards
> > Sud
>
>
> --
> Adrian Klaver
> [email protected]
>
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Alerting on memory use and instance crash
2025-10-08 15:42 Alerting on memory use and instance crash sud <[email protected]>
2025-10-08 16:22 ` Re: Alerting on memory use and instance crash Adrian Klaver <[email protected]>
2025-10-08 18:58 ` Re: Alerting on memory use and instance crash sud <[email protected]>
@ 2025-10-08 19:06 ` Ron Johnson <[email protected]>
2025-10-08 19:50 ` Re: Alerting on memory use and instance crash sud <[email protected]>
1 sibling, 1 reply; 11+ messages in thread
From: Ron Johnson @ 2025-10-08 19:06 UTC (permalink / raw)
To: pgsql-general <[email protected]>
On Wed, Oct 8, 2025 at 2:58 PM sud <[email protected]> wrote:
[snip]
> 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?
>
Yup.
> Pardon if it's a silly one to ask.
>
A Google for "what's the difference between Oracle and Postgresql" _might_
help. I've never done that, so don't know what you'll find.
As far as how Aurora works... you need to ask AWS. It's been too heavily
modified for a list dedicated to pure/unmodified Postgresql to help.
> On Wed, Oct 8, 2025 at 9:52 PM Adrian Klaver <[email protected]>
> wrote:
>
>> On 10/8/25 08:42, sud wrote:
>> > Hi Experts,
>> >
>> > It's postgres version 16. I have two questions on alerting as below.
>> >
>> > 1)If we want to have alerting on any node/instance that gets crashed :-
>> > In other databases like Oracle the catalog Views like "GV$Instance"
>> used
>> > to give information on whether the instances are currently active/down
>> > or not. But in postgres it seems all the pg_* views are instance
>> > specific and are not showing information on the global/cluster level
>> but
>> > are restricted to instance level only. So is there any other way to
>> > query the pg_* views to have alerts on the specific instance crash?
>>
>> 1) When you say instance do you mean database?
>>
>> 2) Not all system tables/views are database only.
>>
>> For instance:
>> https://www.postgresql.org/docs/current/catalog-pg-database.html
>> https://www.postgresql.org/docs/current/catalog-pg-auth-members.html
>> https://www.postgresql.org/docs/current/catalog-pg-authid.html
>> https://www.postgresql.org/docs/current/view-pg-roles.html
>>
>>
>> > 2)Is there a way to fetch the data from pg_* view to highlight the
>> > specific connection/session/sqls which is using high memory in postgres?
>> >
>> > Appreciate your guidance.
>> >
>> > Regards
>> > Sud
>>
>>
>> --
>> Adrian Klaver
>> [email protected]
>>
>
--
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> lobster!
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Alerting on memory use and instance crash
2025-10-08 15:42 Alerting on memory use and instance crash sud <[email protected]>
2025-10-08 16:22 ` Re: Alerting on memory use and instance crash Adrian Klaver <[email protected]>
2025-10-08 18:58 ` Re: Alerting on memory use and instance crash sud <[email protected]>
2025-10-08 19:06 ` Re: Alerting on memory use and instance crash Ron Johnson <[email protected]>
@ 2025-10-08 19:50 ` sud <[email protected]>
2025-10-10 10:38 ` Re: Alerting on memory use and instance crash Rahila Syed <[email protected]>
0 siblings, 1 reply; 11+ messages in thread
From: sud @ 2025-10-08 19:50 UTC (permalink / raw)
To: Ron Johnson <[email protected]>; +Cc: pgsql-general <[email protected]>
Thank you.
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.
On Thu, Oct 9, 2025 at 12:37 AM Ron Johnson <[email protected]> wrote:
> On Wed, Oct 8, 2025 at 2:58 PM sud <[email protected]> wrote:
> [snip]
>
>> 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?
>>
>
> Yup.
>
>
>> Pardon if it's a silly one to ask.
>>
>
> A Google for "what's the difference between Oracle and Postgresql" _might_
> help. I've never done that, so don't know what you'll find.
>
> As far as how Aurora works... you need to ask AWS. It's been too heavily
> modified for a list dedicated to pure/unmodified Postgresql to help.
>
>
>> On Wed, Oct 8, 2025 at 9:52 PM Adrian Klaver <[email protected]>
>> wrote:
>>
>>> On 10/8/25 08:42, sud wrote:
>>> > Hi Experts,
>>> >
>>> > It's postgres version 16. I have two questions on alerting as below.
>>> >
>>> > 1)If we want to have alerting on any node/instance that gets crashed
>>> :-
>>> > In other databases like Oracle the catalog Views like "GV$Instance"
>>> used
>>> > to give information on whether the instances are currently active/down
>>> > or not. But in postgres it seems all the pg_* views are instance
>>> > specific and are not showing information on the global/cluster level
>>> but
>>> > are restricted to instance level only. So is there any other way to
>>> > query the pg_* views to have alerts on the specific instance crash?
>>>
>>> 1) When you say instance do you mean database?
>>>
>>> 2) Not all system tables/views are database only.
>>>
>>> For instance:
>>> https://www.postgresql.org/docs/current/catalog-pg-database.html
>>> https://www.postgresql.org/docs/current/catalog-pg-auth-members.html
>>> https://www.postgresql.org/docs/current/catalog-pg-authid.html
>>> https://www.postgresql.org/docs/current/view-pg-roles.html
>>>
>>>
>>> > 2)Is there a way to fetch the data from pg_* view to highlight the
>>> > specific connection/session/sqls which is using high memory in
>>> postgres?
>>> >
>>> > Appreciate your guidance.
>>> >
>>> > Regards
>>> > Sud
>>>
>>>
>>> --
>>> Adrian Klaver
>>> [email protected]
>>>
>>
>
> --
> Death to <Redacted>, and butter sauce.
> Don't boil me, I'm still alive.
> <Redacted> lobster!
>
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Alerting on memory use and instance crash
2025-10-08 15:42 Alerting on memory use and instance crash sud <[email protected]>
2025-10-08 16:22 ` Re: Alerting on memory use and instance crash Adrian Klaver <[email protected]>
2025-10-08 18:58 ` Re: Alerting on memory use and instance crash sud <[email protected]>
2025-10-08 19:06 ` Re: Alerting on memory use and instance crash Ron Johnson <[email protected]>
2025-10-08 19:50 ` Re: Alerting on memory use and instance crash sud <[email protected]>
@ 2025-10-10 10:38 ` Rahila Syed <[email protected]>
2025-10-10 15:27 ` Re: Alerting on memory use and instance crash sud <[email protected]>
0 siblings, 1 reply; 11+ messages in thread
From: Rahila Syed @ 2025-10-10 10:38 UTC (permalink / raw)
To: sud <[email protected]>; +Cc: Ron Johnson <[email protected]>; pgsql-general <[email protected]>
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....;
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
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Alerting on memory use and instance crash
2025-10-08 15:42 Alerting on memory use and instance crash sud <[email protected]>
2025-10-08 16:22 ` Re: Alerting on memory use and instance crash Adrian Klaver <[email protected]>
2025-10-08 18:58 ` Re: Alerting on memory use and instance crash sud <[email protected]>
2025-10-08 19:06 ` Re: Alerting on memory use and instance crash Ron Johnson <[email protected]>
2025-10-08 19:50 ` Re: Alerting on memory use and instance crash sud <[email protected]>
2025-10-10 10:38 ` Re: Alerting on memory use and instance crash Rahila Syed <[email protected]>
@ 2025-10-10 15:27 ` sud <[email protected]>
2025-11-04 04:36 ` Re: Alerting on memory use and instance crash Rahila Syed <[email protected]>
0 siblings, 1 reply; 11+ messages in thread
From: sud @ 2025-10-10 15:27 UTC (permalink / raw)
To: Rahila Syed <[email protected]>; +Cc: Ron Johnson <[email protected]>; pgsql-general <[email protected]>
Thank you so much. That helps.
I am planning to use pg_stat_get_backend_memory_contexts function something
as below by joining this to the pg_stat_activity. Hope this is the right
usage. Somehow i am getting an error stating the function doesn't exist but
it might be because of the version. I will try with a higher version.
SELECT pa.pid,
pa.usename,
pa.application_name,
pa.state,
mc.name AS memory_context,
pg_size_pretty(mc.used_bytes) AS used_memory
FROM pg_stat_activity pa
JOIN LATERAL pg_stat_get_backend_memory_contexts(pa.pid) mc ON TRUE
WHERE pa.pid <> pg_backend_pid()
ORDER BY mc.used_bytes DESC;
However, is the below query, which was shared by Veem in above email thread
is also going to give similar memory consumption information i.e. Avg
memory consumption per query from pg_stat_statements?
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 Fri, Oct 10, 2025 at 4:08 PM Rahila Syed <[email protected]> wrote:
> 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....;
>
> 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
>
>
>
>
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Alerting on memory use and instance crash
2025-10-08 15:42 Alerting on memory use and instance crash sud <[email protected]>
2025-10-08 16:22 ` Re: Alerting on memory use and instance crash Adrian Klaver <[email protected]>
2025-10-08 18:58 ` Re: Alerting on memory use and instance crash sud <[email protected]>
2025-10-08 19:06 ` Re: Alerting on memory use and instance crash Ron Johnson <[email protected]>
2025-10-08 19:50 ` Re: Alerting on memory use and instance crash sud <[email protected]>
2025-10-10 10:38 ` Re: Alerting on memory use and instance crash Rahila Syed <[email protected]>
2025-10-10 15:27 ` Re: Alerting on memory use and instance crash sud <[email protected]>
@ 2025-11-04 04:36 ` Rahila Syed <[email protected]>
0 siblings, 0 replies; 11+ messages in thread
From: Rahila Syed @ 2025-11-04 04:36 UTC (permalink / raw)
To: sud <[email protected]>; +Cc: Ron Johnson <[email protected]>; pgsql-general <[email protected]>
On Fri, Oct 10, 2025 at 8:58 PM sud <[email protected]> wrote:>
> Thank you so much. That helps.
>
> I am planning to use pg_stat_get_backend_memory_contexts function
something as below by joining this to the pg_stat_activity. Hope this is
the right usage. Somehow i am getting an error stating the function doesn't
exist but it might be because of the version. I will try with a higher
version.
>
> SELECT pa.pid,
> pa.usename,
> pa.application_name,
> pa.state,
> mc.name AS memory_context,
> pg_size_pretty(mc.used_bytes) AS used_memory
> FROM pg_stat_activity pa
> JOIN LATERAL pg_stat_get_backend_memory_contexts(pa.pid) mc ON TRUE
> WHERE pa.pid <> pg_backend_pid()
> ORDER BY mc.used_bytes DESC;
>
The function pg_stat_get_backend_memory_contexts(pid) is not available in
the master branch yet;
this feature is still under development. That is the reason you are getting
the error stating
function doesn't exist.
When I apply the latest patch on this proposed here, [1]
that contains the said function, and run your query , I get something like
follows:
postgres=# SELECT pa.pid,
pa.usename,
pa.application_name,
pa.state,
mc.name AS memory_context,
pg_size_pretty(mc.used_bytes) AS used_memory
FROM pg_stat_activity pa
JOIN LATERAL pg_get_process_memory_contexts(pa.pid, false) mc ON TRUE
WHERE pa.pid <> pg_backend_pid()
ORDER BY mc.used_bytes DESC;
pid | usename | application_name | state | memory_context |
used_memory
-------+---------+------------------+-------+--------------------------+-------------
36876 | rahila | | | TopMemoryContext |
202 kB
36875 | | | | TopMemoryContext |
200 kB
36868 | | | | TopMemoryContext |
176 kB
36869 | | | | TopMemoryContext |
164 kB
36866 | | | | TopMemoryContext |
164 kB
36867 | | | | TopMemoryContext |
164 kB
36874 | | | | TopMemoryContext |
164 kB
36865 | | | | TopMemoryContext |
164 kB
36876 | rahila | | | CacheMemoryContext |
141 kB
36875 | | | | CacheMemoryContext |
141 kB
36874 | | | | Timezones | 99
kB
36868 | | | | Timezones | 99
kB
36876 | rahila | | | Timezones | 99
kB
36866 | | | | Timezones | 99
kB
36869 | | | | Timezones | 99
kB
36865 | | | | Timezones | 99
kB
36875 | | | | Timezones | 99
kB
36867 | | | | Timezones | 99
kB
36876 | rahila | | | WAL record construction | 42
kB
36865 | | | | WAL record construction | 42
kB
36869 | | | | WAL record construction | 42
kB
36875 | | | | WAL record construction | 42
kB
36868 | | | | WAL record construction | 42
kB
36874 | | | | WAL record construction | 42
kB
36866 | | | | WAL record construction | 42
kB
36867 | | | | WAL record construction | 42
kB
36876 | rahila | | | GUC hash table | 21
kB
36869 | | | | GUC hash table | 21
kB
36868 | | | | GUC hash table | 21
kB
36867 | | | | GUC hash table | 21
kB
36875 | | | | GUC hash table | 21
kB
36865 | | | | GUC hash table | 21
kB
36866 | | | | GUC hash table | 21
kB
36874 | | | | GUC hash table | 21
kB
36875 | | | | smgr relation table | 11
kB
36876 | rahila | | | smgr relation table | 11
kB
36866 | | | | smgr relation table | 11
kB
36868 | | | | smgr relation table | 11
kB
36865 | | | | smgr relation table | 11
kB
36867 | | | | smgr relation table | 11
kB
36868 | | | | GUCMemoryContext | 11
kB
36876 | rahila | | | GUCMemoryContext | 11
kB
36866 | | | | GUCMemoryContext | 11
kB
36865 | | | | GUCMemoryContext | 11
kB
36867 | | | | GUCMemoryContext | 11
kB
36869 | | | | GUCMemoryContext | 11
kB
36874 | | | | GUCMemoryContext | 11
kB
36875 | | | | GUCMemoryContext | 11
kB
36876 | rahila | | | Relcache by OID |
8648 bytes
36875 | | | | Relcache by OID |
8648 bytes
36875 | | | | PgStat Shared Ref Hash |
8552 bytes
36876 | rahila | | | PgStat Shared Ref Hash |
8552 bytes
36874 | | | | PgStat Shared Ref Hash |
8552 bytes
36875 | | | | Portal hash |
7576 bytes
36867 | | | | LOCALLOCK hash |
7576 bytes
36874 | | | | LOCALLOCK hash |
7576 bytes
36866 | | | | LOCALLOCK hash |
7576 bytes
36865 | | | | LOCALLOCK hash |
7576 bytes
36868 | | | | LOCALLOCK hash |
7576 bytes
36876 | rahila | | | Portal hash |
7576 bytes
36875 | | | | LOCALLOCK hash |
7576 bytes
36876 | rahila | | | LOCALLOCK hash |
7576 bytes
36868 | | | | Pending Ops Table |
7576 bytes
36869 | | | | LOCALLOCK hash |
7576 bytes
36874 | | | | PrivateRefCount |
5520 bytes
36876 | rahila | | | PrivateRefCount |
5520 bytes
36866 | | | | PrivateRefCount |
5520 bytes
36865 | | | | PrivateRefCount |
5520 bytes
36869 | | | | PrivateRefCount |
5520 bytes
36875 | | | | PrivateRefCount |
5520 bytes
36867 | | | | PrivateRefCount |
5520 bytes
36868 | | | | PrivateRefCount |
5520 bytes
36876 | rahila | | | PgStat Pending |
504 bytes
36875 | | | | PgStat Pending |
504 bytes
36875 | | | | PgStat Shared Ref |
456 bytes
36866 | | | | MdSmgr |
400 bytes
36876 | rahila | | | PgStat Shared Ref |
384 bytes
36874 | | | | PgStat Shared Ref |
312 bytes
36875 | | | | Autovacuum database list |
312 bytes
36865 | | | | MdSmgr |
272 bytes
36876 | rahila | | | MdSmgr |
256 bytes
36875 | | | | MdSmgr |
256 bytes
36867 | | | | MdSmgr |
256 bytes
36874 | | | | MdSmgr |
240 bytes
36866 | | | | ErrorContext |
240 bytes
36876 | rahila | | | TopPortalContext |
240 bytes
36865 | | | | ErrorContext |
240 bytes
36876 | rahila | | | TransactionAbortContext |
240 bytes
36876 | rahila | | | TopTransactionContext |
240 bytes
36867 | | | | ErrorContext |
240 bytes
36875 | | | | ErrorContext |
240 bytes
36868 | | | | Checkpointer |
240 bytes
36868 | | | | MdSmgr |
240 bytes
36868 | | | | Pending ops context |
240 bytes
36868 | | | | ErrorContext |
240 bytes
36875 | | | | TopPortalContext |
240 bytes
36869 | | | | Background Writer |
240 bytes
36869 | | | | MdSmgr |
240 bytes
36874 | | | | ErrorContext |
240 bytes
36875 | | | | Autovacuum Launcher |
240 bytes
36869 | | | | ErrorContext |
240 bytes
36875 | | | | TransactionAbortContext |
240 bytes
36875 | | | | TopTransactionContext |
240 bytes
36874 | | | | Wal Writer |
240 bytes
36876 | rahila | | | ErrorContext |
240 bytes
(105 rows)
> However, is the below query, which was shared by Veem in above email
thread is also going to give similar memory consumption information i.e.
Avg memory consumption per query from pg_stat_statements?
This gives the memory consumed by reading in temporary files for a
particular statement or query
It does not give the complete picture of memory usage by a PostgreSQL
process. Apart
from temp_blks_read, a PostgreSQL process allocates more memory which can
be
viewed by a utility like pg_backend_memory_contexts for the backend process
attached to
the current session.
Thank you,
Rahila Syed
[1] PostgreSQL: Enhancing Memory Context Statistics Reporting
<https://www.postgresql.org/message-id/flat/CAH2L28v8mc9HDt8QoSJ8TRmKau_8FM_HKS41NeO9-6ZAkuZKXw@mail....;
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Alerting on memory use and instance crash
2025-10-08 15:42 Alerting on memory use and instance crash sud <[email protected]>
2025-10-08 16:22 ` Re: Alerting on memory use and instance crash Adrian Klaver <[email protected]>
2025-10-08 18:58 ` Re: Alerting on memory use and instance crash sud <[email protected]>
@ 2025-10-08 19:54 ` Adrian Klaver <[email protected]>
2025-10-09 15:21 ` Re: Alerting on memory use and instance crash veem v <[email protected]>
1 sibling, 1 reply; 11+ messages in thread
From: Adrian Klaver @ 2025-10-08 19:54 UTC (permalink / raw)
To: sud <[email protected]>; Ron Johnson <[email protected]>; +Cc: pgsql-general <[email protected]>
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]
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Alerting on memory use and instance crash
2025-10-08 15:42 Alerting on memory use and instance crash sud <[email protected]>
2025-10-08 16:22 ` Re: Alerting on memory use and instance crash Adrian Klaver <[email protected]>
2025-10-08 18:58 ` Re: Alerting on memory use and instance crash sud <[email protected]>
2025-10-08 19:54 ` Re: Alerting on memory use and instance crash Adrian Klaver <[email protected]>
@ 2025-10-09 15:21 ` veem v <[email protected]>
0 siblings, 0 replies; 11+ messages in thread
From: veem v @ 2025-10-09 15:21 UTC (permalink / raw)
To: Adrian Klaver <[email protected]>; sud <[email protected]>; +Cc: Ron Johnson <[email protected]>; pgsql-general <[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]
>
>
>
^ permalink raw reply [nested|flat] 11+ messages in thread
end of thread, other threads:[~2025-11-04 04:36 UTC | newest]
Thread overview: 11+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2025-10-08 15:42 Alerting on memory use and instance crash sud <[email protected]>
2025-10-08 16:21 ` Ron Johnson <[email protected]>
2025-10-08 16:22 ` Adrian Klaver <[email protected]>
2025-10-08 18:58 ` sud <[email protected]>
2025-10-08 19:06 ` Ron Johnson <[email protected]>
2025-10-08 19:50 ` sud <[email protected]>
2025-10-10 10:38 ` Rahila Syed <[email protected]>
2025-10-10 15:27 ` sud <[email protected]>
2025-11-04 04:36 ` Rahila Syed <[email protected]>
2025-10-08 19:54 ` Adrian Klaver <[email protected]>
2025-10-09 15:21 ` veem v <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox