Thanks, for q2 - if work_mem is a limit before spilling onto disk, is there a min amount of memory which gets allocated to each session when it starts?
1. Yes, by default it is advisable to set 25% of RAM to shared buffers because community version postgres highly depends on OS cache, which means when you fetch something from disk, it is first copied to OS cache (75%) then to shared buffers(25%).
Two copies of data will be there in your system RAM.
The copy is called buffered IO.
Amazon Aurora eliminates this buffered IO, hence it is not required you stick to restrict 25% of RAM.
2. Work_mem is just setting, if you sort something out your session uses than memory other wise it just lies at OS.
shared_buffers - In a regular PostgreSQL installation, say I am allocating 25% of my memory to shared_buffers that means it leaves 75% for rest such as OS, page cache and work_mems etc. Is my understanding correct? If so, AWS Aurora for Postgres uses 75% of memory for shared_buffers, then it would leave just 25% for other things?
Does the memory specified for work_mem, fully gets allocated to all sessions irrespective of whether they do any sorting or hashing operations?