postgres windows efficient memory usage

Question

I'm using Postgres 9.6 (64 bit) on Windows 10 on a laptop with 8 GB RAM for dev purposes. The application is batch mass data processing with the large table having 10 mio records.

I've read various Postgres tuning guide, and also previous questions/answers raised here, and I tried several of the suggestions, but without great success.

I know my laptop is not large but watching the performance monitor then, for a Query, I see Postgres mostly writing (to the disc), with a tiny bit of reading, and one of the cores mostly utilized. What I'm interested in is memory. I'm wondering why Postgres doesn't make use of it; it stay's at 5.7GB "used" but 8GB are available. My conclusion is that Postgres decides to write temp data to a file (memory mapped file), rather then to use the memory. If that is true, may be I can tune Windows and allow for more (files) pages in memory. Anyhow my gut feeling is this has something to do with Postres on Windows, rather then being a generic Postgres question.

Does anybody know how I can configure Postgres and/or Windows so that Postgres makes better use of the (free) memory available?

Thanks a lot for your help

Juergen

Answer 1

If it is a dedicated database machine, set shared_buffers to – say – 2GB and increase work_mem so that most sorts and hashes can be performed in memory.

For more specific questions, ask with more detail.