Optimal PostgreSQL setup for Windows 2008 R2

Question

I have PostgreSQL 9.2 setup on Windows 2008 R2 64bit with 96 gigs of RAM and 8 cores. What would the optimal settings be for shared_buffers, effective_cache_size, work_mem, etc.? I realize that these values vary a lot between Linux and Windows so any help for best practices would be greatly appreciated!

Answer 1

In general, the consensus is that shared_buffers is less important to increase on Windows than it is on Linux. Again a lot of this is very workload specific.

I would probably start with a max connections of 20 unless you have a lot of idle connections as the norm. Start with defaults for work_mem and shared_buffers and experiment with tweaking upwards. Always run test queries twice and discard the first one just to ensure comparable caching. work_mem should be tweaked upwards if you have a lot of sorts or aggregates which would use a lot of memory per operation. shared_buffers may need to go higher if you need to reserve more space in memory specifically for PostgreSQL. However assuming you aren't running anything else that is a memory hog on the machine, I would start with default values and see how far those get you. In most setups these should be sufficient for decent performance (occasionally even on Linux, lower shared_buffers can be helpful) and then watch for problems.