postgresql out-of-memory

Question

I am running postgresql 9.2 on a 32-bit machine running windows xp with 4 gb of memory and I am getting out of memory errors. I suspect it has something to do with these two parameters I have changed related to memory:

shared_buffers = 1024 MB
maintenance_work_mem = 1024 MB

Before changing these parameters, I was not getting out-of-memory errors. Is this too much for a 32-bit machine?

• András Váczi: Operating system? Size of physical memory? Swap? Other software running on the same box? Without knowing these we could blame the aliens as well :)
• user972276: Sorry for the lack of detail. Added the OS and physical memory. There is other software running but they are not using a lot of resources.
• András Váczi: Check the maintenance_work_mem description in the documentation. This size (relative to the remaining portion) seems overkill. How do your typical queries look like? How much memory do they consume?
• user972276: I am restoring from a dump file to an empty database. It looks like I am running out of memory during index creation
• András Váczi: Also, on Windows, large values for shared_buffers aren't as effective. You may find better results keeping the setting relatively low and using the operating system cache more instead. The useful range for shared_buffers on Windows systems is generally from 64MB to 512MB. I'd start from lower, with both values. If and only if I hit the limits of the settings I'd raise those. If your biggest table is 200 MB, then 1 G for maintenance_work_mem sound like a waste.

Answer 1

I usually prefer to start with lower shared buffers on both Linux and Windows, and keep work mem low also. Maintenance_work_mem can be left generally the default too. Keep in mind that too low a maintenance_work_mem may slow down db restores but it won't stop it. Generally you are better off starting low and then tweaking upward to find sweet spots than starting too high and working your way down.

Secondly you are reserving half of the memory on your box for PostgreSQL and only PostgreSQL. This can have very unfortunate side effects if you are doing anything else. Since this is Windows XP I am assuming it is not a dedicated server, and you are going to do far better with conservative settings here.