work_mem and shared_buffers question

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.

On Mon, 10 Feb, 2020, 1:34 PM Ayub M, <hiayub@gmail.com> wrote:

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?

On Mon, Feb 10, 2020 at 3:51 AM Naresh g <naresh5310@gmail.com> wrote:

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.

On Mon, 10 Feb, 2020, 1:34 PM Ayub M, <hiayub@gmail.com> wrote:

1. 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?
2. Does the memory specified for work_mem, fully gets allocated to
all sessions irrespective of whether they do any sorting or hashing
operations?

--
Regards,
Ayub

--
Regards,
Ayub

No, as I said, as per my observation your connection will not use your
work mem unless there is a sort happening,

but

Yes, every connection uses certain amount of RAM, some documents say it 5
MB and some say it is 1.9 MB.

Expert review may be needed on my observation.

On Mon, 10 Feb, 2020, 4:00 PM Ayub M, <hiayub@gmail.com> wrote: