Another great feature was committed for PostgreSQL 18 if you are interested how memory is used by a backend process. While you can take a look at the memory contexts for your current session since PostgreSQL 14, there was no way to retrieve that information for another backend.
Since PostgreSQL 14 there is the pg_backend_memory_contexts catalog view. This view displays the memory contexts of the server process attached to the current session, e.g.:
postgres=# select * from pg_backend_memory_contexts;
name | ident | type | level | path | total_bytes | total_nblocks | free_bytes | free_chunks | used_bytes
------------------------------------------------+------------------------------------------------+------------+-------+-----------------------+-------------+---------------+------------+-------------+------------
TopMemoryContext | | AllocSet | 1 | {1} | 174544 | 7 | 36152 | 20 | 138392
Record information cache | | AllocSet | 2 | {1,2} | 8192 | 1 | 1640 | 0 | 6552
RegexpCacheMemoryContext | | AllocSet | 2 | {1,3} | 1024 | 1 | 784 | 0 | 240
collation cache | | AllocSet | 2 | {1,4} | 8192 | 1 | 6808 | 0 | 1384
TableSpace cache | | AllocSet | 2 | {1,5} | 8192 | 1 | 2152 | 0 | 6040
Map from relid to OID of cached composite type | | AllocSet | 2 | {1,6} | 8192 | 1 | 2544 | 0 | 5648
Type information cache | | AllocSet | 2 | {1,7} | 24624 | 2 | 2672 | 0 | 21952
Operator lookup cache | | AllocSet | 2 | {1,8} | 24576 | 2 | 10816 | 4 | 13760
search_path processing cache | | AllocSet | 2 | {1,9} | 8192 | 1 | 5656 | 8 | 2536
RowDescriptionContext | | AllocSet | 2 | {1,10} | 8192 | 1 | 6920 | 0 | 1272
MessageContext | | AllocSet | 2 | {1,11} | 32768 | 3 | 1632 | 0 | 31136
Operator class cache | | AllocSet | 2 | {1,12} | 8192 | 1 | 616 | 0 | 7576
smgr relation table | | AllocSet | 2 | {1,13} | 32768 | 3 | 16904 | 9 | 15864
PgStat Shared Ref Hash | | AllocSet | 2 | {1,14} | 9264 | 2 | 712 | 0 | 8552
PgStat Shared Ref | | AllocSet | 2 | {1,15} | 8192 | 4 | 3440 | 5 | 4752
PgStat Pending | | AllocSet | 2 | {1,16} | 16384 | 5 | 15984 | 58 | 400
TopTransactionContext | | AllocSet | 2 | {1,17} | 8192 | 1 | 7776 | 0 | 416
TransactionAbortContext | | AllocSet | 2 | {1,18} | 32768 | 1 | 32528 | 0 | 240
Portal hash | | AllocSet | 2 | {1,19} | 8192 | 1 | 616 | 0 | 7576
TopPortalContext | | AllocSet | 2 | {1,20} | 8192 | 1 | 7688 | 0 | 504
Relcache by OID | | AllocSet | 2 | {1,21} | 16384 | 2 | 3608 | 3 | 12776
CacheMemoryContext | | AllocSet | 2 | {1,22} | 8487056 | 14 | 3376568 | 3 | 5110488
LOCALLOCK hash | | AllocSet | 2 | {1,23} | 8192 | 1 | 616 | 0 | 7576
WAL record construction | | AllocSet | 2 | {1,24} | 50200 | 2 | 6400 | 0 | 43800
PrivateRefCount | | AllocSet | 2 | {1,25} | 8192 | 1 | 608 | 0 | 7584
MdSmgr | | AllocSet | 2 | {1,26} | 8192 | 1 | 7296 | 0 | 896
GUCMemoryContext | | AllocSet | 2 | {1,27} | 24576 | 2 | 8264 | 1 | 16312
Timezones | | AllocSet | 2 | {1,28} | 104112 | 2 | 2672 | 0 | 101440
ErrorContext | | AllocSet | 2 | {1,29} | 8192 | 1 | 7952 | 0 | 240
RegexpMemoryContext | ^(.*memory.*)$ | AllocSet | 3 | {1,3,30} | 13360 | 5 | 6800 | 8 | 6560
PortalContext | <unnamed> | AllocSet | 3 | {1,20,31} | 1024 | 1 | 608 | 0 | 416
relation rules | pg_backend_memory_contexts | AllocSet | 3 | {1,22,32} | 8192 | 4 | 3840 | 1 | 4352
index info | pg_toast_1255_index | AllocSet | 3 | {1,22,33} | 3072 | 2 | 1152 | 2 | 1920
index info | pg_toast_2619_index | AllocSet | 3 | {1,22,34} | 3072 | 2 | 1152 | 2 | 1920
index info | pg_constraint_conrelid_contypid_conname_index | AllocSet | 3 | {1,22,35} | 3072 | 2 | 1016 | 1 | 2056
index info | pg_statistic_ext_relid_index | AllocSet | 3 | {1,22,36} | 2048 | 2 | 752 | 2 | 1296
index info | pg_index_indrelid_index | AllocSet | 3 | {1,22,37} | 2048 | 2 | 680 | 2 | 1368
index info | pg_db_role_setting_databaseid_rol_index | AllocSet | 3 | {1,22,38} | 3072 | 2 | 1120 | 1 | 1952
index info | pg_opclass_am_name_nsp_index | AllocSet | 3 | {1,22,39} | 3072 | 2 | 1048 | 1 | 2024
index info | pg_foreign_data_wrapper_name_index | AllocSet | 3 | {1,22,40} | 2048 | 2 | 792 | 3 | 1256
index info | pg_enum_oid_index | AllocSet | 3 | {1,22,41} | 2048 | 2 | 824 | 3 | 1224
index info | pg_class_relname_nsp_index | AllocSet | 3 | {1,22,42} | 3072 | 2 | 1080 | 3 | 1992
index info | pg_foreign_server_oid_index | AllocSet | 3 | {1,22,43} | 2048 | 2 | 824 | 3 | 1224
index info | pg_publication_pubname_index | AllocSet | 3 | {1,22,44} | 2048 | 2 | 824 | 3 | 1224
index info | pg_statistic_relid_att_inh_index | AllocSet | 3 | {1,22,45} | 3072 | 2 | 872 | 2 | 2200
index info | pg_cast_source_target_index | AllocSet | 3 | {1,22,46} | 3072 | 2 | 1080 | 3 | 1992
index info | pg_language_name_index | AllocSet | 3 | {1,22,47} | 2048 | 2 | 824 | 3 | 1224
index info | pg_transform_oid_index | AllocSet | 3 | {1,22,48} | 2048 | 2 | 824 | 3 | 1224
index info | pg_collation_oid_index | AllocSet | 3 | {1,22,49} | 2048 | 2 | 680 | 2 | 1368
index info | pg_amop_fam_strat_index | AllocSet | 3 | {1,22,50} | 3248 | 3 | 840 | 0 | 2408
index info | pg_index_indexrelid_index | AllocSet | 3 | {1,22,51} | 2048 | 2 | 680 | 2 | 1368
index info | pg_ts_template_tmplname_index | AllocSet | 3 | {1,22,52} | 3072 | 2 | 1296 | 3 | 1776
index info | pg_ts_config_map_index | AllocSet | 3 | {1,22,53} | 3072 | 2 | 1192 | 2 | 1880
index info | pg_opclass_oid_index | AllocSet | 3 | {1,22,54} | 2048 | 2 | 680 | 2 | 1368
index info | pg_foreign_data_wrapper_oid_index | AllocSet | 3 | {1,22,55} | 2048 | 2 | 792 | 3 | 1256
index info | pg_publication_namespace_oid_index | AllocSet | 3 | {1,22,56} | 2048 | 2 | 792 | 3 | 1256
index info | pg_event_trigger_evtname_index | AllocSet | 3 | {1,22,57} | 2048 | 2 | 824 | 3 | 1224
index info | pg_statistic_ext_name_index | AllocSet | 3 | {1,22,58} | 3072 | 2 | 1296 | 3 | 1776
index info | pg_publication_oid_index | AllocSet | 3 | {1,22,59} | 2048 | 2 | 824 | 3 | 1224
index info | pg_ts_dict_oid_index | AllocSet | 3 | {1,22,60} | 2048 | 2 | 824 | 3 | 1224
index info | pg_event_trigger_oid_index | AllocSet | 3 | {1,22,61} | 2048 | 2 | 824 | 3 | 1224
index info | pg_conversion_default_index | AllocSet | 3 | {1,22,62} | 2224 | 2 | 216 | 0 | 2008
index info | pg_operator_oprname_l_r_n_index | AllocSet | 3 | {1,22,63} | 3248 | 3 | 840 | 0 | 2408
index info | pg_trigger_tgrelid_tgname_index | AllocSet | 3 | {1,22,64} | 3072 | 2 | 1296 | 3 | 1776
index info | pg_extension_oid_index | AllocSet | 3 | {1,22,65} | 2048 | 2 | 824 | 3 | 1224
index info | pg_enum_typid_label_index | AllocSet | 3 | {1,22,66} | 3072 | 2 | 1296 | 3 | 1776
index info | pg_ts_config_oid_index | AllocSet | 3 | {1,22,67} | 2048 | 2 | 824 | 3 | 1224
index info | pg_user_mapping_oid_index | AllocSet | 3 | {1,22,68} | 2048 | 2 | 824 | 3 | 1224
index info | pg_opfamily_am_name_nsp_index | AllocSet | 3 | {1,22,69} | 3072 | 2 | 1192 | 2 | 1880
index info | pg_foreign_table_relid_index | AllocSet | 3 | {1,22,70} | 2048 | 2 | 824 | 3 | 1224
index info | pg_type_oid_index | AllocSet | 3 | {1,22,71} | 2048 | 2 | 680 | 2 | 1368
index info | pg_aggregate_fnoid_index | AllocSet | 3 | {1,22,72} | 2048 | 2 | 680 | 2 | 1368
index info | pg_constraint_oid_index | AllocSet | 3 | {1,22,73} | 2048 | 2 | 824 | 3 | 1224
index info | pg_rewrite_rel_rulename_index | AllocSet | 3 | {1,22,74} | 3072 | 2 | 1152 | 3 | 1920
index info | pg_ts_parser_prsname_index | AllocSet | 3 | {1,22,75} | 3072 | 2 | 1296 | 3 | 1776
index info | pg_ts_config_cfgname_index | AllocSet | 3 | {1,22,76} | 3072 | 2 | 1296 | 3 | 1776
index info | pg_ts_parser_oid_index | AllocSet | 3 | {1,22,77} | 2048 | 2 | 824 | 3 | 1224
index info | pg_publication_rel_prrelid_prpubid_index | AllocSet | 3 | {1,22,78} | 3072 | 2 | 1264 | 2 | 1808
index info | pg_operator_oid_index | AllocSet | 3 | {1,22,79} | 2048 | 2 | 680 | 2 | 1368
index info | pg_namespace_nspname_index | AllocSet | 3 | {1,22,80} | 2048 | 2 | 680 | 2 | 1368
index info | pg_ts_template_oid_index | AllocSet | 3 | {1,22,81} | 2048 | 2 | 824 | 3 | 1224
index info | pg_amop_opr_fam_index | AllocSet | 3 | {1,22,82} | 3072 | 2 | 904 | 1 | 2168
index info | pg_default_acl_role_nsp_obj_index | AllocSet | 3 | {1,22,83} | 3072 | 2 | 1160 | 2 | 1912
index info | pg_collation_name_enc_nsp_index | AllocSet | 3 | {1,22,84} | 3072 | 2 | 904 | 1 | 2168
index info | pg_publication_rel_oid_index | AllocSet | 3 | {1,22,85} | 2048 | 2 | 824 | 3 | 1224
index info | pg_range_rngtypid_index | AllocSet | 3 | {1,22,86} | 2048 | 2 | 824 | 3 | 1224
index info | pg_ts_dict_dictname_index | AllocSet | 3 | {1,22,87} | 3072 | 2 | 1296 | 3 | 1776
index info | pg_type_typname_nsp_index | AllocSet | 3 | {1,22,88} | 3072 | 2 | 1080 | 3 | 1992
index info | pg_opfamily_oid_index | AllocSet | 3 | {1,22,89} | 2048 | 2 | 680 | 2 | 1368
index info | pg_statistic_ext_oid_index | AllocSet | 3 | {1,22,90} | 2048 | 2 | 824 | 3 | 1224
index info | pg_statistic_ext_data_stxoid_inh_index | AllocSet | 3 | {1,22,91} | 3072 | 2 | 1264 | 2 | 1808
index info | pg_class_oid_index | AllocSet | 3 | {1,22,92} | 2048 | 2 | 680 | 2 | 1368
index info | pg_proc_proname_args_nsp_index | AllocSet | 3 | {1,22,93} | 3072 | 2 | 1048 | 1 | 2024
index info | pg_partitioned_table_partrelid_index | AllocSet | 3 | {1,22,94} | 2048 | 2 | 792 | 3 | 1256
index info | pg_range_rngmultitypid_index | AllocSet | 3 | {1,22,95} | 2048 | 2 | 824 | 3 | 1224
index info | pg_transform_type_lang_index | AllocSet | 3 | {1,22,96} | 3072 | 2 | 1296 | 3 | 1776
index info | pg_attribute_relid_attnum_index | AllocSet | 3 | {1,22,97} | 3072 | 2 | 1080 | 3 | 1992
index info | pg_proc_oid_index | AllocSet | 3 | {1,22,98} | 2048 | 2 | 680 | 2 | 1368
index info | pg_language_oid_index | AllocSet | 3 | {1,22,99} | 2048 | 2 | 824 | 3 | 1224
index info | pg_namespace_oid_index | AllocSet | 3 | {1,22,100} | 2048 | 2 | 680 | 2 | 1368
index info | pg_amproc_fam_proc_index | AllocSet | 3 | {1,22,101} | 3248 | 3 | 840 | 0 | 2408
index info | pg_foreign_server_name_index | AllocSet | 3 | {1,22,102} | 2048 | 2 | 824 | 3 | 1224
index info | pg_attribute_relid_attnam_index | AllocSet | 3 | {1,22,103} | 3072 | 2 | 1296 | 3 | 1776
index info | pg_publication_namespace_pnnspid_pnpubid_index | AllocSet | 3 | {1,22,104} | 3072 | 2 | 1264 | 2 | 1808
index info | pg_conversion_oid_index | AllocSet | 3 | {1,22,105} | 2048 | 2 | 824 | 3 | 1224
index info | pg_user_mapping_user_server_index | AllocSet | 3 | {1,22,106} | 3072 | 2 | 1264 | 2 | 1808
index info | pg_subscription_rel_srrelid_srsubid_index | AllocSet | 3 | {1,22,107} | 3072 | 2 | 1264 | 2 | 1808
index info | pg_sequence_seqrelid_index | AllocSet | 3 | {1,22,108} | 2048 | 2 | 824 | 3 | 1224
index info | pg_extension_name_index | AllocSet | 3 | {1,22,109} | 2048 | 2 | 824 | 3 | 1224
index info | pg_conversion_name_nsp_index | AllocSet | 3 | {1,22,110} | 3072 | 2 | 1296 | 3 | 1776
index info | pg_authid_oid_index | AllocSet | 3 | {1,22,111} | 2048 | 2 | 680 | 2 | 1368
index info | pg_auth_members_member_role_index | AllocSet | 3 | {1,22,112} | 3072 | 2 | 1160 | 2 | 1912
index info | pg_subscription_oid_index | AllocSet | 3 | {1,22,113} | 2048 | 2 | 824 | 3 | 1224
index info | pg_parameter_acl_oid_index | AllocSet | 3 | {1,22,114} | 2048 | 2 | 824 | 3 | 1224
index info | pg_tablespace_oid_index | AllocSet | 3 | {1,22,115} | 2048 | 2 | 680 | 2 | 1368
index info | pg_parameter_acl_parname_index | AllocSet | 3 | {1,22,116} | 2048 | 2 | 824 | 3 | 1224
index info | pg_shseclabel_object_index | AllocSet | 3 | {1,22,117} | 3072 | 2 | 1192 | 2 | 1880
index info | pg_replication_origin_roname_index | AllocSet | 3 | {1,22,118} | 2048 | 2 | 792 | 3 | 1256
index info | pg_database_datname_index | AllocSet | 3 | {1,22,119} | 2048 | 2 | 680 | 2 | 1368
index info | pg_subscription_subname_index | AllocSet | 3 | {1,22,120} | 3072 | 2 | 1296 | 3 | 1776
index info | pg_replication_origin_roiident_index | AllocSet | 3 | {1,22,121} | 2048 | 2 | 792 | 3 | 1256
index info | pg_auth_members_role_member_index | AllocSet | 3 | {1,22,122} | 3072 | 2 | 1160 | 2 | 1912
index info | pg_database_oid_index | AllocSet | 3 | {1,22,123} | 2048 | 2 | 680 | 2 | 1368
index info | pg_authid_rolname_index | AllocSet | 3 | {1,22,124} | 2048 | 2 | 680 | 2 | 1368
GUC hash table | | AllocSet | 3 | {1,27,125} | 32768 | 3 | 11696 | 6 | 21072
ExecutorState | | AllocSet | 4 | {1,20,31,126} | 49200 | 4 | 13632 | 3 | 35568
tuplestore tuples | | Generation | 5 | {1,20,31,126,127} | 32768 | 3 | 13360 | 0 | 19408
printtup | | AllocSet | 5 | {1,20,31,126,128} | 8192 | 1 | 7952 | 0 | 240
Table function arguments | | AllocSet | 5 | {1,20,31,126,129} | 8192 | 1 | 7912 | 0 | 280
ExprContext | | AllocSet | 5 | {1,20,31,126,130} | 32768 | 3 | 5656 | 4 | 27112
pg_get_backend_memory_contexts | | AllocSet | 6 | {1,20,31,126,130,131} | 16384 | 2 | 5664 | 3 | 10720
(131 rows)
This is quite some information, but as said above, this information is only available for the backend process which is attached to the current session.
Starting with PostgreSQL 18, you can most probably get those statistics for other backends as well. For this a new function was added:
postgres=# \dfS pg_get_process_memory_contexts
List of functions
Schema | Name | Result data type | Argument da>
------------+--------------------------------+------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------->
pg_catalog | pg_get_process_memory_contexts | SETOF record | pid integer, summary boolean, retries double precision, OUT name text, OUT ident text, OUT type text, OUT path integer[], OUT level integer, OUT total_bytes bigint, OUT >
(1 row)
Let’s play a bit with this. Suppose we have a session which reports this backend process ID:
postgres=# select version();
version
--------------------------------------------------------------------
PostgreSQL 18devel on x86_64-linux, compiled by gcc-14.2.1, 64-bit
(1 row)
postgres=# select pg_backend_pid();
pg_backend_pid
----------------
31291
(1 row)
In another session we can now ask for the summary of the memory contexts for the PID we got in the first session like this (the second parameter turns on the summary, the third is the waiting time in seconds for updated statistics):
postgres=# select * from pg_get_process_memory_contexts(31291,true,2);
name | ident | type | path | level | total_bytes | total_nblocks | free_bytes | free_chunks | used_bytes | num_agg_contexts | stats_timestamp
------------------------------+-------+----------+--------+-------+-------------+---------------+------------+-------------+------------+------------------+------------------------------
TopMemoryContext | | AllocSet | {1} | 1 | 141776 | 6 | 5624 | 11 | 136152 | 1 | 2025-04-08 13:37:38.63979+02
| | ??? | | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 2025-04-08 13:37:38.63979+02
search_path processing cache | | AllocSet | {1,2} | 2 | 8192 | 1 | 5656 | 8 | 2536 | 1 | 2025-04-08 13:37:38.63979+02
RowDescriptionContext | | AllocSet | {1,3} | 2 | 8192 | 1 | 6920 | 0 | 1272 | 1 | 2025-04-08 13:37:38.63979+02
MessageContext | | AllocSet | {1,4} | 2 | 16384 | 2 | 7880 | 2 | 8504 | 2 | 2025-04-08 13:37:38.63979+02
Operator class cache | | AllocSet | {1,5} | 2 | 8192 | 1 | 616 | 0 | 7576 | 1 | 2025-04-08 13:37:38.63979+02
smgr relation table | | AllocSet | {1,6} | 2 | 16384 | 2 | 4664 | 3 | 11720 | 1 | 2025-04-08 13:37:38.63979+02
PgStat Shared Ref Hash | | AllocSet | {1,7} | 2 | 9264 | 2 | 712 | 0 | 8552 | 1 | 2025-04-08 13:37:38.63979+02
PgStat Shared Ref | | AllocSet | {1,8} | 2 | 4096 | 3 | 1760 | 3 | 2336 | 1 | 2025-04-08 13:37:38.63979+02
PgStat Pending | | AllocSet | {1,9} | 2 | 8192 | 4 | 7832 | 28 | 360 | 1 | 2025-04-08 13:37:38.63979+02
TopTransactionContext | | AllocSet | {1,10} | 2 | 8192 | 1 | 7952 | 0 | 240 | 1 | 2025-04-08 13:37:38.63979+02
TransactionAbortContext | | AllocSet | {1,11} | 2 | 32768 | 1 | 32528 | 0 | 240 | 1 | 2025-04-08 13:37:38.63979+02
Portal hash | | AllocSet | {1,12} | 2 | 8192 | 1 | 616 | 0 | 7576 | 1 | 2025-04-08 13:37:38.63979+02
TopPortalContext | | AllocSet | {1,13} | 2 | 8192 | 1 | 7952 | 1 | 240 | 1 | 2025-04-08 13:37:38.63979+02
Relcache by OID | | AllocSet | {1,14} | 2 | 16384 | 2 | 3608 | 3 | 12776 | 1 | 2025-04-08 13:37:38.63979+02
CacheMemoryContext | | AllocSet | {1,15} | 2 | 737984 | 182 | 183208 | 221 | 554776 | 88 | 2025-04-08 13:37:38.63979+02
LOCALLOCK hash | | AllocSet | {1,16} | 2 | 8192 | 1 | 616 | 0 | 7576 | 1 | 2025-04-08 13:37:38.63979+02
WAL record construction | | AllocSet | {1,17} | 2 | 50200 | 2 | 6400 | 0 | 43800 | 1 | 2025-04-08 13:37:38.63979+02
PrivateRefCount | | AllocSet | {1,18} | 2 | 8192 | 1 | 2672 | 0 | 5520 | 1 | 2025-04-08 13:37:38.63979+02
MdSmgr | | AllocSet | {1,19} | 2 | 8192 | 1 | 7936 | 0 | 256 | 1 | 2025-04-08 13:37:38.63979+02
GUCMemoryContext | | AllocSet | {1,20} | 2 | 57344 | 5 | 19960 | 7 | 37384 | 2 | 2025-04-08 13:37:38.63979+02
Timezones | | AllocSet | {1,21} | 2 | 104112 | 2 | 2672 | 0 | 101440 | 1 | 2025-04-08 13:37:38.63979+02
Turning off the summary, gives you the full picture:
postgres=# select * from pg_get_process_memory_contexts(31291,false,2) order by level, name;
name | ident | type | path | level | total_bytes | total_nblocks | free_bytes | free_chunks | used_bytes | num_agg_contexts | stats_timestamp
---------------------------------------+------------------------------------------------+----------+------------+-------+-------------+---------------+------------+-------------+------------+------------------+-------------------------------
TopMemoryContext | | AllocSet | {1} | 1 | 141776 | 6 | 5624 | 11 | 136152 | 1 | 2025-04-08 13:38:02.508423+02
CacheMemoryContext | | AllocSet | {1,15} | 2 | 524288 | 7 | 101280 | 1 | 423008 | 1 | 2025-04-08 13:38:02.508423+02
ErrorContext | | AllocSet | {1,22} | 2 | 8192 | 1 | 7952 | 4 | 240 | 1 | 2025-04-08 13:38:02.508423+02
GUCMemoryContext | | AllocSet | {1,20} | 2 | 24576 | 2 | 8264 | 1 | 16312 | 1 | 2025-04-08 13:38:02.508423+02
LOCALLOCK hash | | AllocSet | {1,16} | 2 | 8192 | 1 | 616 | 0 | 7576 | 1 | 2025-04-08 13:38:02.508423+02
MdSmgr | | AllocSet | {1,19} | 2 | 8192 | 1 | 7936 | 0 | 256 | 1 | 2025-04-08 13:38:02.508423+02
MessageContext | | AllocSet | {1,4} | 2 | 16384 | 2 | 2664 | 4 | 13720 | 1 | 2025-04-08 13:38:02.508423+02
Operator class cache | | AllocSet | {1,5} | 2 | 8192 | 1 | 616 | 0 | 7576 | 1 | 2025-04-08 13:38:02.508423+02
PgStat Pending | | AllocSet | {1,9} | 2 | 8192 | 4 | 7832 | 28 | 360 | 1 | 2025-04-08 13:38:02.508423+02
PgStat Shared Ref | | AllocSet | {1,8} | 2 | 4096 | 3 | 1760 | 3 | 2336 | 1 | 2025-04-08 13:38:02.508423+02
PgStat Shared Ref Hash | | AllocSet | {1,7} | 2 | 9264 | 2 | 712 | 0 | 8552 | 1 | 2025-04-08 13:38:02.508423+02
Portal hash | | AllocSet | {1,12} | 2 | 8192 | 1 | 616 | 0 | 7576 | 1 | 2025-04-08 13:38:02.508423+02
PrivateRefCount | | AllocSet | {1,18} | 2 | 8192 | 1 | 2672 | 0 | 5520 | 1 | 2025-04-08 13:38:02.508423+02
Relcache by OID | | AllocSet | {1,14} | 2 | 16384 | 2 | 3608 | 3 | 12776 | 1 | 2025-04-08 13:38:02.508423+02
RowDescriptionContext | | AllocSet | {1,3} | 2 | 8192 | 1 | 6920 | 0 | 1272 | 1 | 2025-04-08 13:38:02.508423+02
search_path processing cache | | AllocSet | {1,2} | 2 | 8192 | 1 | 5656 | 8 | 2536 | 1 | 2025-04-08 13:38:02.508423+02
smgr relation table | | AllocSet | {1,6} | 2 | 16384 | 2 | 4664 | 3 | 11720 | 1 | 2025-04-08 13:38:02.508423+02
Timezones | | AllocSet | {1,21} | 2 | 104112 | 2 | 2672 | 0 | 101440 | 1 | 2025-04-08 13:38:02.508423+02
TopPortalContext | | AllocSet | {1,13} | 2 | 8192 | 1 | 7952 | 1 | 240 | 1 | 2025-04-08 13:38:02.508423+02
TopTransactionContext | | AllocSet | {1,10} | 2 | 8192 | 1 | 7952 | 0 | 240 | 1 | 2025-04-08 13:38:02.508423+02
TransactionAbortContext | | AllocSet | {1,11} | 2 | 32768 | 1 | 32528 | 0 | 240 | 1 | 2025-04-08 13:38:02.508423+02
WAL record construction | | AllocSet | {1,17} | 2 | 50200 | 2 | 6400 | 0 | 43800 | 1 | 2025-04-08 13:38:02.508423+02
GUC hash table | | AllocSet | {1,20,111} | 3 | 32768 | 3 | 11696 | 6 | 21072 | 1 | 2025-04-08 13:38:02.508423+02
index info | pg_ts_dict_oid_index | AllocSet | {1,15,46} | 3 | 2048 | 2 | 824 | 3 | 1224 | 1 | 2025-04-08 13:38:02.508423+02
index info | pg_event_trigger_oid_index | AllocSet | {1,15,47} | 3 | 2048 | 2 | 824 | 3 | 1224 | 1 | 2025-04-08 13:38:02.508423+02
index info | pg_conversion_default_index | AllocSet | {1,15,48} | 3 | 2224 | 2 | 216 | 0 | 2008 | 1 | 2025-04-08 13:38:02.508423+02
index info | pg_operator_oprname_l_r_n_index | AllocSet | {1,15,49} | 3 | 2224 | 2 | 216 | 0 | 2008 | 1 | 2025-04-08 13:38:02.508423+02
index info | pg_trigger_tgrelid_tgname_index | AllocSet | {1,15,50} | 3 | 3072 | 2 | 1296 | 3 | 1776 | 1 | 2025-04-08 13:38:02.508423+02
index info | pg_extension_oid_index | AllocSet | {1,15,51} | 3 | 2048 | 2 | 824 | 3 | 1224 | 1 | 2025-04-08 13:38:02.508423+02
index info | pg_enum_typid_label_index | AllocSet | {1,15,52} | 3 | 3072 | 2 | 1296 | 3 | 1776 | 1 | 2025-04-08 13:38:02.508423+02
index info | pg_ts_config_oid_index | AllocSet | {1,15,53} | 3 | 2048 | 2 | 824 | 3 | 1224 | 1 | 2025-04-08 13:38:02.508423+02
index info | pg_user_mapping_oid_index | AllocSet | {1,15,54} | 3 | 2048 | 2 | 824 | 3 | 1224 | 1 | 2025-04-08 13:38:02.508423+02
index info | pg_opfamily_am_name_nsp_index | AllocSet | {1,15,55} | 3 | 3072 | 2 | 1192 | 2 | 1880 | 1 | 2025-04-08 13:38:02.508423+02
index info | pg_foreign_table_relid_index | AllocSet | {1,15,56} | 3 | 2048 | 2 | 824 | 3 | 1224 | 1 | 2025-04-08 13:38:02.508423+02
index info | pg_type_oid_index | AllocSet | {1,15,57} | 3 | 2048 | 2 | 680 | 2 | 1368 | 1 | 2025-04-08 13:38:02.508423+02
index info | pg_aggregate_fnoid_index | AllocSet | {1,15,58} | 3 | 2048 | 2 | 824 | 3 | 1224 | 1 | 2025-04-08 13:38:02.508423+02
index info | pg_constraint_oid_index | AllocSet | {1,15,59} | 3 | 2048 | 2 | 824 | 3 | 1224 | 1 | 2025-04-08 13:38:02.508423+02
index info | pg_rewrite_rel_rulename_index | AllocSet | {1,15,60} | 3 | 3072 | 2 | 1296 | 3 | 1776 | 1 | 2025-04-08 13:38:02.508423+02
index info | pg_ts_parser_prsname_index | AllocSet | {1,15,61} | 3 | 3072 | 2 | 1296 | 3 | 1776 | 1 | 2025-04-08 13:38:02.508423+02
index info | pg_ts_config_cfgname_index | AllocSet | {1,15,62} | 3 | 3072 | 2 | 1296 | 3 | 1776 | 1 | 2025-04-08 13:38:02.508423+02
index info | pg_ts_parser_oid_index | AllocSet | {1,15,63} | 3 | 2048 | 2 | 824 | 3 | 1224 | 1 | 2025-04-08 13:38:02.508423+02
index info | pg_publication_rel_prrelid_prpubid_index | AllocSet | {1,15,64} | 3 | 3072 | 2 | 1264 | 2 | 1808 | 1 | 2025-04-08 13:38:02.508423+02
index info | pg_operator_oid_index | AllocSet | {1,15,65} | 3 | 2048 | 2 | 824 | 3 | 1224 | 1 | 2025-04-08 13:38:02.508423+02
index info | pg_namespace_nspname_index | AllocSet | {1,15,66} | 3 | 2048 | 2 | 680 | 2 | 1368 | 1 | 2025-04-08 13:38:02.508423+02
index info | pg_ts_template_oid_index | AllocSet | {1,15,67} | 3 | 2048 | 2 | 824 | 3 | 1224 | 1 | 2025-04-08 13:38:02.508423+02
index info | pg_amop_opr_fam_index | AllocSet | {1,15,68} | 3 | 3072 | 2 | 1192 | 2 | 1880 | 1 | 2025-04-08 13:38:02.508423+02
index info | pg_default_acl_role_nsp_obj_index | AllocSet | {1,15,69} | 3 | 3072 | 2 | 1160 | 2 | 1912 | 1 | 2025-04-08 13:38:02.508423+02
index info | pg_collation_name_enc_nsp_index | AllocSet | {1,15,70} | 3 | 3072 | 2 | 1192 | 2 | 1880 | 1 | 2025-04-08 13:38:02.508423+02
index info | pg_publication_rel_oid_index | AllocSet | {1,15,71} | 3 | 2048 | 2 | 824 | 3 | 1224 | 1 | 2025-04-08 13:38:02.508423+02
index info | pg_range_rngtypid_index | AllocSet | {1,15,72} | 3 | 2048 | 2 | 824 | 3 | 1224 | 1 | 2025-04-08 13:38:02.508423+02
index info | pg_ts_dict_dictname_index | AllocSet | {1,15,73} | 3 | 3072 | 2 | 1296 | 3 | 1776 | 1 | 2025-04-08 13:38:02.508423+02
index info | pg_type_typname_nsp_index | AllocSet | {1,15,74} | 3 | 3072 | 2 | 1296 | 3 | 1776 | 1 | 2025-04-08 13:38:02.508423+02
index info | pg_opfamily_oid_index | AllocSet | {1,15,75} | 3 | 2048 | 2 | 824 | 3 | 1224 | 1 | 2025-04-08 13:38:02.508423+02
index info | pg_statistic_ext_oid_index | AllocSet | {1,15,76} | 3 | 2048 | 2 | 824 | 3 | 1224 | 1 | 2025-04-08 13:38:02.508423+02
index info | pg_statistic_ext_data_stxoid_inh_index | AllocSet | {1,15,77} | 3 | 3072 | 2 | 1264 | 2 | 1808 | 1 | 2025-04-08 13:38:02.508423+02
index info | pg_class_oid_index | AllocSet | {1,15,78} | 3 | 2048 | 2 | 680 | 2 | 1368 | 1 | 2025-04-08 13:38:02.508423+02
index info | pg_proc_proname_args_nsp_index | AllocSet | {1,15,79} | 3 | 3072 | 2 | 1048 | 1 | 2024 | 1 | 2025-04-08 13:38:02.508423+02
index info | pg_partitioned_table_partrelid_index | AllocSet | {1,15,80} | 3 | 2048 | 2 | 792 | 3 | 1256 | 1 | 2025-04-08 13:38:02.508423+02
index info | pg_range_rngmultitypid_index | AllocSet | {1,15,81} | 3 | 2048 | 2 | 824 | 3 | 1224 | 1 | 2025-04-08 13:38:02.508423+02
index info | pg_transform_type_lang_index | AllocSet | {1,15,82} | 3 | 3072 | 2 | 1296 | 3 | 1776 | 1 | 2025-04-08 13:38:02.508423+02
index info | pg_attribute_relid_attnum_index | AllocSet | {1,15,83} | 3 | 3072 | 2 | 1080 | 3 | 1992 | 1 | 2025-04-08 13:38:02.508423+02
index info | pg_proc_oid_index | AllocSet | {1,15,84} | 3 | 2048 | 2 | 680 | 2 | 1368 | 1 | 2025-04-08 13:38:02.508423+02
index info | pg_language_oid_index | AllocSet | {1,15,85} | 3 | 2048 | 2 | 824 | 3 | 1224 | 1 | 2025-04-08 13:38:02.508423+02
index info | pg_namespace_oid_index | AllocSet | {1,15,86} | 3 | 2048 | 2 | 824 | 3 | 1224 | 1 | 2025-04-08 13:38:02.508423+02
index info | pg_amproc_fam_proc_index | AllocSet | {1,15,87} | 3 | 3248 | 3 | 912 | 0 | 2336 | 1 | 2025-04-08 13:38:02.508423+02
index info | pg_foreign_server_name_index | AllocSet | {1,15,88} | 3 | 2048 | 2 | 824 | 3 | 1224 | 1 | 2025-04-08 13:38:02.508423+02
index info | pg_attribute_relid_attnam_index | AllocSet | {1,15,89} | 3 | 3072 | 2 | 1296 | 3 | 1776 | 1 | 2025-04-08 13:38:02.508423+02
index info | pg_publication_namespace_pnnspid_pnpubid_index | AllocSet | {1,15,90} | 3 | 3072 | 2 | 1264 | 2 | 1808 | 1 | 2025-04-08 13:38:02.508423+02
index info | pg_conversion_oid_index | AllocSet | {1,15,91} | 3 | 2048 | 2 | 824 | 3 | 1224 | 1 | 2025-04-08 13:38:02.508423+02
index info | pg_user_mapping_user_server_index | AllocSet | {1,15,92} | 3 | 3072 | 2 | 1264 | 2 | 1808 | 1 | 2025-04-08 13:38:02.508423+02
index info | pg_subscription_rel_srrelid_srsubid_index | AllocSet | {1,15,93} | 3 | 3072 | 2 | 1264 | 2 | 1808 | 1 | 2025-04-08 13:38:02.508423+02
index info | pg_sequence_seqrelid_index | AllocSet | {1,15,94} | 3 | 2048 | 2 | 824 | 3 | 1224 | 1 | 2025-04-08 13:38:02.508423+02
index info | pg_extension_name_index | AllocSet | {1,15,95} | 3 | 2048 | 2 | 824 | 3 | 1224 | 1 | 2025-04-08 13:38:02.508423+02
index info | pg_conversion_name_nsp_index | AllocSet | {1,15,96} | 3 | 3072 | 2 | 1296 | 3 | 1776 | 1 | 2025-04-08 13:38:02.508423+02
index info | pg_authid_oid_index | AllocSet | {1,15,97} | 3 | 2048 | 2 | 680 | 2 | 1368 | 1 | 2025-04-08 13:38:02.508423+02
index info | pg_subscription_oid_index | AllocSet | {1,15,99} | 3 | 2048 | 2 | 824 | 3 | 1224 | 1 | 2025-04-08 13:38:02.508423+02
index info | pg_parameter_acl_oid_index | AllocSet | {1,15,100} | 3 | 2048 | 2 | 824 | 3 | 1224 | 1 | 2025-04-08 13:38:02.508423+02
index info | pg_tablespace_oid_index | AllocSet | {1,15,101} | 3 | 2048 | 2 | 824 | 3 | 1224 | 1 | 2025-04-08 13:38:02.508423+02
index info | pg_parameter_acl_parname_index | AllocSet | {1,15,102} | 3 | 2048 | 2 | 824 | 3 | 1224 | 1 | 2025-04-08 13:38:02.508423+02
index info | pg_shseclabel_object_index | AllocSet | {1,15,103} | 3 | 3072 | 2 | 1192 | 2 | 1880 | 1 | 2025-04-08 13:38:02.508423+02
index info | pg_replication_origin_roname_index | AllocSet | {1,15,104} | 3 | 2048 | 2 | 792 | 3 | 1256 | 1 | 2025-04-08 13:38:02.508423+02
index info | pg_database_datname_index | AllocSet | {1,15,105} | 3 | 2048 | 2 | 680 | 2 | 1368 | 1 | 2025-04-08 13:38:02.508423+02
index info | pg_subscription_subname_index | AllocSet | {1,15,106} | 3 | 3072 | 2 | 1296 | 3 | 1776 | 1 | 2025-04-08 13:38:02.508423+02
index info | pg_replication_origin_roiident_index | AllocSet | {1,15,107} | 3 | 2048 | 2 | 792 | 3 | 1256 | 1 | 2025-04-08 13:38:02.508423+02
index info | pg_auth_members_role_member_index | AllocSet | {1,15,108} | 3 | 3072 | 2 | 1160 | 2 | 1912 | 1 | 2025-04-08 13:38:02.508423+02
index info | pg_database_oid_index | AllocSet | {1,15,109} | 3 | 2048 | 2 | 680 | 2 | 1368 | 1 | 2025-04-08 13:38:02.508423+02
index info | pg_authid_rolname_index | AllocSet | {1,15,110} | 3 | 2048 | 2 | 680 | 2 | 1368 | 1 | 2025-04-08 13:38:02.508423+02
index info | pg_auth_members_member_role_index | AllocSet | {1,15,98} | 3 | 3072 | 2 | 1160 | 2 | 1912 | 1 | 2025-04-08 13:38:02.508423+02
index info | pg_db_role_setting_databaseid_rol_index | AllocSet | {1,15,24} | 3 | 3072 | 2 | 1120 | 1 | 1952 | 1 | 2025-04-08 13:38:02.508423+02
index info | pg_opclass_am_name_nsp_index | AllocSet | {1,15,25} | 3 | 3072 | 2 | 1192 | 2 | 1880 | 1 | 2025-04-08 13:38:02.508423+02
index info | pg_foreign_data_wrapper_name_index | AllocSet | {1,15,26} | 3 | 2048 | 2 | 792 | 3 | 1256 | 1 | 2025-04-08 13:38:02.508423+02
index info | pg_enum_oid_index | AllocSet | {1,15,27} | 3 | 2048 | 2 | 824 | 3 | 1224 | 1 | 2025-04-08 13:38:02.508423+02
index info | pg_class_relname_nsp_index | AllocSet | {1,15,28} | 3 | 3072 | 2 | 1296 | 3 | 1776 | 1 | 2025-04-08 13:38:02.508423+02
index info | pg_foreign_server_oid_index | AllocSet | {1,15,29} | 3 | 2048 | 2 | 824 | 3 | 1224 | 1 | 2025-04-08 13:38:02.508423+02
index info | pg_publication_pubname_index | AllocSet | {1,15,30} | 3 | 2048 | 2 | 824 | 3 | 1224 | 1 | 2025-04-08 13:38:02.508423+02
index info | pg_statistic_relid_att_inh_index | AllocSet | {1,15,31} | 3 | 3072 | 2 | 1160 | 2 | 1912 | 1 | 2025-04-08 13:38:02.508423+02
index info | pg_cast_source_target_index | AllocSet | {1,15,32} | 3 | 3072 | 2 | 1296 | 3 | 1776 | 1 | 2025-04-08 13:38:02.508423+02
index info | pg_language_name_index | AllocSet | {1,15,33} | 3 | 2048 | 2 | 824 | 3 | 1224 | 1 | 2025-04-08 13:38:02.508423+02
index info | pg_transform_oid_index | AllocSet | {1,15,34} | 3 | 2048 | 2 | 824 | 3 | 1224 | 1 | 2025-04-08 13:38:02.508423+02
index info | pg_collation_oid_index | AllocSet | {1,15,35} | 3 | 2048 | 2 | 824 | 3 | 1224 | 1 | 2025-04-08 13:38:02.508423+02
index info | pg_amop_fam_strat_index | AllocSet | {1,15,36} | 3 | 2224 | 2 | 216 | 0 | 2008 | 1 | 2025-04-08 13:38:02.508423+02
index info | pg_index_indexrelid_index | AllocSet | {1,15,37} | 3 | 2048 | 2 | 680 | 2 | 1368 | 1 | 2025-04-08 13:38:02.508423+02
index info | pg_ts_template_tmplname_index | AllocSet | {1,15,38} | 3 | 3072 | 2 | 1296 | 3 | 1776 | 1 | 2025-04-08 13:38:02.508423+02
index info | pg_ts_config_map_index | AllocSet | {1,15,39} | 3 | 3072 | 2 | 1192 | 2 | 1880 | 1 | 2025-04-08 13:38:02.508423+02
index info | pg_opclass_oid_index | AllocSet | {1,15,40} | 3 | 2048 | 2 | 680 | 2 | 1368 | 1 | 2025-04-08 13:38:02.508423+02
index info | pg_foreign_data_wrapper_oid_index | AllocSet | {1,15,41} | 3 | 2048 | 2 | 792 | 3 | 1256 | 1 | 2025-04-08 13:38:02.508423+02
index info | pg_publication_namespace_oid_index | AllocSet | {1,15,42} | 3 | 2048 | 2 | 792 | 3 | 1256 | 1 | 2025-04-08 13:38:02.508423+02
index info | pg_event_trigger_evtname_index | AllocSet | {1,15,43} | 3 | 2048 | 2 | 824 | 3 | 1224 | 1 | 2025-04-08 13:38:02.508423+02
index info | pg_statistic_ext_name_index | AllocSet | {1,15,44} | 3 | 3072 | 2 | 1296 | 3 | 1776 | 1 | 2025-04-08 13:38:02.508423+02
index info | pg_publication_oid_index | AllocSet | {1,15,45} | 3 | 2048 | 2 | 824 | 3 | 1224 | 1 | 2025-04-08 13:38:02.508423+02
pg_get_remote_backend_memory_contexts | | AllocSet | {1,4,23} | 3 | 16384 | 2 | 6568 | 3 | 9816 | 1 | 2025-04-08 13:38:02.508423+02
(111 rows)
As you can see above there are many entries with “index info” which are not directly visible in the summary view. The reason is the aggregation when you go for the summary. All “index info” entries are aggregated into under “CacheMemoryContext” and we can easily verify this:
postgres=# select count(*) from pg_get_process_memory_contexts(31291,false,2) where name = 'index info';
count
-------
87
(1 row)
… which is very close to the 88 aggregations reported in the summary. Excluding all the system/catalog indexes we get the following picture:
postgres=# select * from pg_get_process_memory_contexts(31291,false,2) where name = 'index info' and ident !~ 'pg_';
name | ident | type | path | level | total_bytes | total_nblocks | free_bytes | free_chunks | used_bytes | num_agg_contexts | stats_timestamp
------+-------+------+------+-------+-------------+---------------+------------+-------------+------------+------------------+-----------------
(0 rows)
-- system/catalog indexes
postgres=# select count(*) from pg_get_process_memory_contexts(31291,false,2) where name = 'index info' and ident ~ 'pg_';
count
-------
87
(1 row)
Creating a new table and an index on that table in the first session will change the picture to this:
-- first session
postgres=# create table t ( a int );
CREATE TABLE
postgres=# create index i on t(a);
CREATE INDEX
postgres=#
-- second session
postgres=# select * from pg_get_process_memory_contexts(31291,false,2) where name = 'index info' and ident !~ 'pg_';
name | ident | type | path | level | total_bytes | total_nblocks | free_bytes | free_chunks | used_bytes | num_agg_contexts | stats_timestamp
------------+-------+----------+-----------+-------+-------------+---------------+------------+-------------+------------+------------------+-------------------------------
index info | i | AllocSet | {1,16,26} | 3 | 2048 | 2 | 776 | 3 | 1272 | 1 | 2025-04-08 13:44:55.496668+02
(1 row)
… and this will also increase the aggregation count we did above:
postgres=# select count(*) from pg_get_process_memory_contexts(31291,false,2) where name = 'index info';
count
-------
98
(1 row)
… but why to 98 and not to 89? Because additional system indexes have also been loaded (I let it as an exercise to you find out which ones those are):
postgres=# select count(*) from pg_get_process_memory_contexts(31291,false,2) where name = 'index info' and ident ~ 'pg_';
count
-------
97
(1 row)
You can go on and do additional tests for the other memory contexts to get an idea how that works. Personally, I think this is a great new feature because you can now have a look at the memory contexts of problematic processes. Thanks to all involved, details here.
![Thumbnail [60x60]](https://www.dbi-services.com/blog/wp-content/uploads/2022/08/DWE_web-min-scaled.jpg)
![Thumbnail [90x90]](https://www.dbi-services.com/blog/wp-content/uploads/2024/01/HME_web.jpg)
![Thumbnail [90x90]](https://www.dbi-services.com/blog/wp-content/uploads/2022/08/HER_web-min-scaled.jpg)