PG V14.8-1 , client using Postgresql JDBC driver we found 40MB process memory per backend, from Operating system and memorycontext dump "Grand total:", both mached. But from details, we found almost of entry belong to "CacheMemoryContext", from this line CacheMemoryContext: 8737352 total in 42 blocks; 1021944 free (215 chunks); 7715408 used, but there are thousands of lines of it's child, the sum of blocks much more than "8737352" total in 42 blocks
CacheMemoryContext: 8737352 total in 42 blocks; 1021944 free (215 chunks); 7715408 used
CachedPlan: 4096 total in 3 blocks; 888 free (0 chunks); 3208 used: xxxxxxx
CachedPlanSource: 2048 total in 2 blocks; 440 free (0 chunks); 1608 used: xxxxxxx
unnamed prepared statement: 8192 total in 1 blocks; 464 free (0 chunks); 7728 used
CachedPlan: 66560 total in 7 blocks; 15336 free (0 chunks); 51224 used: xxxxxxx
CachedPlan: 8192 total in 4 blocks; 2456 free (0 chunks); 5736 used: xxxxxxx
CachedPlan: 33792 total in 6 blocks; 14344 free (1 chunks); 19448 used: xxxxxxx
...
SPI Plan: 1024 total in 1 blocks; 600 free (0 chunks); 424 used
CachedPlanSource: 4096 total in 3 blocks; 1152 free (0 chunks); 2944 used: xxxxxxx
CachedPlanQuery: 4096 total in 3 blocks; 848 free (0 chunks); 3248 used
SPI Plan: 1024 total in 1 blocks; 600 free (0 chunks); 424 used
CachedPlanSource: 4096 total in 3 blocks; 1472 free (0 chunks); 2624 used: xxxxxxx
CachedPlanQuery: 4096 total in 3 blocks; 1464 free (0 chunks); 2632 used
SPI Plan: 1024 total in 1 blocks; 600 free (0 chunks); 424 used
index info: 2048 total in 2 blocks; 824 free (0 chunks); 1224 used: xxxxxxx
index info: 2048 total in 2 blocks; 824 free (0 chunks); 1224 used: xxxxxxx
index info: 2048 total in 2 blocks; 528 free (1 chunks); 1520 used: xxxxxxx
index info: 2048 total in 2 blocks; 528 free (1 chunks); 1520 used: xxxxxxx
index info: 2048 total in 2 blocks; 824 free (0 chunks); 1224 used: xxxxxxx
index info: 2048 total in 2 blocks; 448 free (1 chunks); 1600 used: xxxxxxx
index info: 3072 total in 2 blocks; 696 free (1 chunks); 2376 used: xxxxxxx
index info: 2048 total in 2 blocks; 824 free (0 chunks); 1224 used: xxxxxxx
index info: 2048 total in 2 blocks; 656 free (2 chunks); 1392 used: xxxxxxx
index info: 3072 total in 2 blocks; 1160 free (2 chunks); 1912 used: xxxxxxx
index info: 2048 total in 2 blocks; 904 free (1 chunks); 1144 used: xxxxxxx
index info: 2048 total in 2 blocks; 904 free (1 chunks); 1144 used: xxxxxxx
index info: 2048 total in 2 blocks; 904 free (1 chunks); 1144 used: xxxxxxx
WAL record construction: 49768 total in 2 blocks; 6360 free (0 chunks); 43408 used
PrivateRefCount: 8192 total in 1 blocks; 1576 free (0 chunks); 6616 used
MdSmgr: 32768 total in 3 blocks; 10104 free (7 chunks); 22664 used
LOCALLOCK hash: 65536 total in 4 blocks; 18704 free (13 chunks); 46832 used
Timezones: 104120 total in 2 blocks; 2616 free (0 chunks); 101504 used
ErrorContext: 8192 total in 1 blocks; 7928 free (0 chunks); 264 used
Grand total: 34558032 bytes in 8798 blocks; 9206536 free (2484 chunks); 25351496 used
Our application use Postgresql JDBC driver with default parameters(maxprepared statement 256), there are many triggers, functions in this database, and a few functions run sql by an extension pg_background. We have thousands of connections and have big concern why have thousands of entrys of cached SQL ? that will consume huge memory , anyway to limit the cached plan entry to save memory consumption? Or it looks like an abnormal behavior or bug to see so many cached plan lines.
Attached please see details, the detail of SQL got masked sensitive information, this backend has huge lines so using MemoryContextStatsDetail(TopMemoryContext) instead to dump all lines.