由 John Doe 四月 10, 2025
你有没有遇到过数据库后端进程内存占用大的情况？现在，PostgreSQL 提供了可以获取后端内存使用详情的函数 pg_get_process_memory_contexts。
特性提交日志
添加用于获取进程内存上下文统计信息的函数。
这添加了一个用于从后端以及辅助进程中检索内存上下文统计信息和相关信息的函数。其预期的使用场景是：在集群面临内存压力或出现意外的内存使用特征时进行调试。
当调用该函数时，它会向指定的进程发送一个信号，要求该进程将其内存上下文的统计信息提交到动态共享内存中。每个内存上下文都会返回详细信息，若上下文的数量超出了最大的共享内存分配量，则随后会返回一个累计总和。每个进程在这方面最多只能使用 1 兆字节的内存。
用户也可以明确请求获取一个摘要信息，这将返回顶级内存上下文（TopMemoryContext）以及所有较低层级上下文的累计总和。
为了避免在繁忙的进程上造成阻塞，调用者需要指定在超时之前进行重试的秒数。如果在设定的超时时间内没有发布统计信息，则会返回最后已知的统计信息；如果之前没有发布过统计信息，则返回空值（NULL）。这使得即使目标进程暂时出现拥塞，监控类的查询也能够持续发布信息。上下文记录中包含一个时间戳，用于指示这些记录的提交时间。
讨论：https://postgr.es/m/CAH2L28v8mc9HDt8QoSJ8TRmKau_8FM_HKS41NeO9-6ZAkuZKXw@mail.gmail.com
示例
如果你想了解后端进程的内存使用详情，PostgreSQL 18 引入了一个新的功能。虽然从 PostgreSQL 14 开始，你可以通过 pg_backend_memory_contexts 系统视图来查看当前会话的内存上下文，但此前无法获取其他后端进程的相关信息。
从 PostgreSQL 14 开始，pg_backend_memory_contexts 视图会显示与当前会话关联的服务端进程的内存上下文，例如：
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
...
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)
如上文所述，这些信息仅适用于当前会话关联的后端进程。
从 PostgreSQL 18 开始，你也可以获取其他后端进程的内存统计信息。为此，新增了一个函数：
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)
我们来实际测试一下。假设有一个会话，它的后端进程 ID 为：
select version();
version
--------------------------------------------------------------------
PostgreSQL 18devel on x86_64-linux, compiled by gcc-14.2.1, 64-bit
(1 row)
select pg_backend_pid();
pg_backend_pid
----------------
31291
(1 row)
在另一个会话中，我们可以通过以下方式获取该 PID 的内存上下文摘要（第二个参数启用摘要，第三个参数是等待更新统计信息的秒数）：
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
如果关闭摘要模式（summary=false），可以看到完整的上下文信息：
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)
如你在上文所见，有许多带有 “index info” 的条目在摘要视图中并非直接可见。原因在于，当你查看摘要时，系统会进行聚合操作。所有 “index info” 条目都被聚合到 “CacheMemoryContext” 之下，我们可以轻松验证这一点：
select count(*) from pg_get_process_memory_contexts(31291,false,2) where name = 'index info';
count
-------
87
(1 row)
这与摘要中报告的 88 次聚合非常接近。排除所有系统索引后，我们会得到如下情况：
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)
-- 系统索引
select count(*) from pg_get_process_memory_contexts(31291,false,2) where name = 'index info' and ident ~ 'pg_';
count
-------
87
(1 row)
在第一个会话中创建一个新表并在该表上创建一个索引，情况将变为：
-- 第一个会话
create table t ( a int );
create index i on t(a);
-- 第二个会话
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)
这也会增加我们上面统计的聚合数量：
select count(*) from pg_get_process_memory_contexts(31291,false,2) where name = 'index info';
count
-------
98
(1 row)
但为什么是增加到 98 而不是 89 呢？因为额外的系统索引也被加载了：
select count(*) from pg_get_process_memory_contexts(31291,false,2) where name = 'index info' and ident ~ 'pg_';
count
-------
97
(1 row)
你可以继续对其他内存上下文进行额外的测试，从而了解其工作原理。
非常不错的新功能，现在你可以查看有问题的进程的内存上下文了。感谢社区的所有相关人员。
参考
提交日志：https://git.postgresql.org/pg/commitdiff/042a66291b04f473cbc72f95f07438abd75ae3a9