# How postgresql works with memory? **URL:** https://forums.percona.com/t/how-postgresql-works-with-memory/18986 **Category:** PostgreSQL **Created:** [December 7, 2022, 3:05pm UTC](https://forums.percona.com/t/how-postgresql-works-with-memory/18986 "2022-12-07T15:05:49Z") **Posts on this page:** 2 **Page:** 1
### Author: ![gravitoz\_gravity](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/gravitoz_gravity/32/8601_2.png) [@gravitoz\_gravity](https://forums.percona.com/u/gravitoz_gravity) #### Post date: [December 7, 2022, 3:05pm UTC](https://forums.percona.com/t/how-postgresql-works-with-memory/18986/1 "2022-12-07T15:05:50Z")
Hi. In one recent research, i fails to understand of how PostgreSQL work with physical memory when executing a queries. Perhaps, someone had experience in the analysis of this issue. All tests was acted on PostgreSQL 12, Debian 10. In first, we have an execution plan of query. The execution plan contains HASH JOIN operations (search by query (regex “hash.+join”) shows us about 82 hash joins). In FIRST test, work\_mem on the postgresql-server is set to 256MB. The request consumes RAM in the amount of 20GB. It can be seen from HTOP (RSS - process resident memory). If you look at the execution plan by searching for the keyword “Memory Usage”, you can see that the total amount of memory consumption is much less - around 100Mb. Search by “Memory Usage”: Row 14456: Buckets: 1024Batches: 1Memory Usage: 8kB Row 14461: Buckets: 1024Batches: 1Memory Usage: 8kB Row 14472: Buckets: 1024Batches: 1Memory Usage: 8kB Row 14477: Buckets: 1024Batches: 1Memory Usage: 8kB Row 14482: Buckets: 1024Batches: 1Memory Usage: 8kB Row 14487: Buckets: 1024Batches: 1Memory Usage: 74kB Row 17343: Buckets: 1024Batches: 1Memory Usage: 15kB Row 17348: Buckets: 1024Batches: 1Memory Usage: 15kB Row 17364: Buckets: 1024Batches: 1Memory Usage: 14kB Row 17369: Buckets: 1024Batches: 1Memory Usage: 14kB Row 17374: Buckets: 4096Batches: 1Memory Usage: 373kB Row 20935: Buckets: 1024Batches: 1Memory Usage: 8kB Row 20940: Buckets: 1024Batches: 1Memory Usage: 8kB Row 20951: Buckets: 1024Batches: 1Memory Usage: 8kB Row 20956: Buckets: 1024Batches: 1Memory Usage: 8kB Row 20961: Buckets: 1024Batches: 1Memory Usage: 8kB Row 20966: Buckets: 1024Batches: 1Memory Usage: 74kB Row 22665: Buckets: 1024Batches: 1Memory Usage: 101kB Row 22973: Buckets: 1024Batches: 1Memory Usage: 10kB Row 23074: Buckets: 1024Batches: 1Memory Usage: 21kB Row 23111: Buckets: 1024Batches: 1Memory Usage: 8kB Row 23145: Buckets: 1024Batches: 1Memory Usage: 9kB Row 23149: Buckets: 1024Batches: 1Memory Usage: 8kB Row 23183: Buckets: 2048Batches: 1Memory Usage: 80kB Row 23282: Buckets: 2048Batches: 1Memory Usage: 76kB Row 23286: Buckets: 1024Batches: 1Memory Usage: 8kB Row 23290: Buckets: 1024Batches: 1Memory Usage: 8kB Row 23325: Buckets: 1024Batches: 1Memory Usage: 8kB Row 23357: Buckets: 1024Batches: 1Memory Usage: 8kB Row 23389: Buckets: 1024Batches: 1Memory Usage: 8kB Row 23421: Buckets: 2048Batches: 1Memory Usage: 106kB Row 23455: Buckets: 2048Batches: 1Memory Usage: 97kB Row 23521: Buckets: 1024Batches: 1Memory Usage: 8kB Row 23525: Buckets: 1024Batches: 1Memory Usage: 8kB Row 23557: Buckets: 1024Batches: 1Memory Usage: 8kB Row 23589: Buckets: 4096Batches: 1Memory Usage: 166kB Row 23593: Buckets: 1024Batches: 1Memory Usage: 8kB Row 23597: Buckets: 1024Batches: 1Memory Usage: 9kB Row 23719: Buckets: 1024Batches: 1Memory Usage: 8kB Row 23723: Buckets: 1024Batches: 1Memory Usage: 8kB Row 23755: Buckets: 1024Batches: 1Memory Usage: 8kB Row 23787: Buckets: 1024Batches: 1Memory Usage: 9kB Row 23852: Buckets: 1024Batches: 1Memory Usage: 9kB Row 23884: Buckets: 1024Batches: 1Memory Usage: 8kB Row 23916: Buckets: 1024Batches: 1Memory Usage: 8kB Row 23948: Buckets: 1024Batches: 1Memory Usage: 8kB Row 23980: Buckets: 1024Batches: 1Memory Usage: 10kB Row 23984: Buckets: 1024Batches: 1Memory Usage: 51kB Row 23988: Buckets: 2048Batches: 1Memory Usage: 113kB Row 24052: Buckets: 1024Batches: 1Memory Usage: 20kB Row 24056: Buckets: 1024Batches: 1Memory Usage: 13kB Row 24060: Buckets: 1024Batches: 1Memory Usage: 8kB Row 24092: Buckets: 1024Batches: 1Memory Usage: 8kB Row 24096: Buckets: 1024Batches: 1Memory Usage: 8kB Row 24128: Buckets: 1024Batches: 1Memory Usage: 9kB Row 24160: Buckets: 1024Batches: 1Memory Usage: 10kB Row 24264: Buckets: 1024Batches: 1Memory Usage: 10kB Row 24268: Buckets: 2048Batches: 1Memory Usage: 128kB Row 24279: Buckets: 1024Batches: 1Memory Usage: 100kB Row 24283: Buckets: 65536Batches: 1Memory Usage: 5289kB Row 24290: Buckets: 8192Batches: 1Memory Usage: 447kB Row 24293: Buckets: 1024Batches: 1Memory Usage: 10kB Row 24604: Buckets: 65536Batches: 1Memory Usage: 17414kB Row 24617: Buckets: 65536Batches: 1Memory Usage: 16899kB Row 24622: Buckets: 65536Batches: 1Memory Usage: 16899kB Row 24639: Buckets: 65536Batches: 1Memory Usage: 13806kB Row 24642: Buckets: 8192Batches: 1Memory Usage: 634kB Row 24645: Buckets: 1024Batches: 1Memory Usage: 10kB Row 24649: Buckets: 32768Batches: 1Memory Usage: 1795kB Row 24652: Buckets: 1024Batches: 1Memory Usage: 8kB Row 24656: Buckets: 8192Batches: 1Memory Usage: 1335kB Row 24659: Buckets: 1024Batches: 1Memory Usage: 13kB Row 24666: Buckets: 131072Batches: 1Memory Usage: 7118kB Row 24951: Buckets: 1024Batches: 1Memory Usage: 79kB Row 24955: Buckets: 1024Batches: 1Memory Usage: 10kB Row 24959: Buckets: 2048Batches: 1Memory Usage: 128kB Row 24963: Buckets: 32768Batches: 1Memory Usage: 2723kB Row 24998: Buckets: 131072Batches: 1Memory Usage: 6467kB Row 25033: Buckets: 4096Batches: 1Memory Usage: 307kB Row 25037: Buckets: 1024Batches: 1Memory Usage: 100kB Row 25041: Buckets: 4096Batches: 1Memory Usage: 295kB Row 25045: Buckets: 1024Batches: 1Memory Usage: 59kB At the same time, it is known that the memory of the postgresql backend process includes 4 types of memory: 1. temp\_buffers 2. work\_mem 3. maintenance\_work\_mem 4. vacuum\_buffers work\_mem has the following purpose: This is the amount of memory reserved for either a single sort or hash table operation in a query and it is controlled by work\_mem database parameter. A sort operation could be one of an ORDER BY, DISTINCT or Merge join and a hash table operation could be due to a hash-join, hash based aggregation or an IN subquery. A single complex query may have many number of such sort or hash table operations, and as many chunks of memory allocations defined by the work\_mem parameter will be created for each of those operations in a user connection. It is for this reason, that work\_mem should not be declared to a very big value as it might lead to aggressively utilizing all the available memory from operating system for a considerably huge query, thereby starving the operating system of RAM which might be needed for other processes. Thus, there are several questions: 1. IS IT TRUE that 20GB displayed in HTOP VSS is real physical memory? Does this memory NOT include shared memory (shared\_buffers) ? 2. How can i generally determine which operations the scheduler needed 20GB of PHYSICAL memory for? 3. How can i determine the memory assignment between the 4 types of memory described above? * * * Next step i performed - i changed the amount of work\_mem to default value (4Mb). BTW, the amount of memory consumed by the query did NOT change - it remained at the same value of 20GB. 1. Can you explain why changing the work\_mem parameter does not affect the request memory consumed? In the execution plan, there are both sorts and hash joins. ---
### Author: ![Robert\_Bernier](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/robert_bernier/32/1367_2.png) [@Robert\_Bernier](https://forums.percona.com/u/Robert_Bernier) #### Post date: [December 7, 2022, 7:19pm UTC](https://forums.percona.com/t/how-postgresql-works-with-memory/18986/2 "2022-12-07T19:19:28Z")
Hi Postgres utilizes memory for several tasks however when work\_mem is involved it consumes “free RAM memory” that is totally unrelated to the postgres memoery requirements for shared buffer and the postgres application process. Every time work\_mem is allocated it is from the remaining RAM that hasn’t been used up. Example ```auto top - 11:10:46 up 3 days, 22:54, 3 users, load average: 0.64, 0.92, 0.98 Tasks: 454 total, 1 running, 453 sleeping, 0 stopped, 0 zombie %Cpu(s): 5.2 us, 3.2 sy, 0.0 ni, 90.9 id, 0.1 wa, 0.0 hi, 0.6 si, 0.0 st MiB Mem : 7606.4 total, 553.1 free, 3836.5 used, 3216.8 buff/cache MiB Swap: 2048.0 total, 5.2 free, 2042.8 used. 2239.6 avail Mem PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND ``` In this snippet, from **top** , you can see there is **2239.6 avail Mem** and that’s where you get the RAM memory for work\_mem, maintenance\_work\_mem etc. Mind you postgres tries very hard to use RAM only memory and avoids the swap memory as much as possible. However, when there is an over allocation of assigned free RAM, such as the case when took many SQL statements use too much free RAM from very large work\_mem values, postgres will start using swap and that’s when performance can drop. Hope this helps.