Re: Alerting on memory use and instance crash

On Fri, Oct 10, 2025 at 8:58 PM sud <suds1434(at)gmail(dot)com> 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(at)mail(dot)gmail(dot)com>