Re: Workaround for working_mem max value in windows?

On Tue, Apr 15, 2014 at 6:36 PM, Nick Eubank <nickeubank@gmail.com> wrote:

Hi all,

A few years ago someone said postgres windows can't set working_mem above about 2 GB (www.postgresql.org/message-id/17895.1315869622@sss.pgh.pa.us -- seems to be same for maintenance_working_mem ). Im finding limit still present.

 I'm doing single user, single connection data intensive queries and would like to set a higher value on windows to better use 16gb built in ram (don't control platform, so can't jump to Linux). 

Anyone found a work around?

Before worrying much about that, I'd just give it a try at the highest value it will let you set and see what happens.

If you want to do something like hashed aggregate that would have been predicted to fit in 6GB but not in 1.999GB, then you will lose out on the hash agg by not being able to set the memory higher.  On the other hand, if your queries want to use sorts that will spill to disk anyway, the exact value of work_mem usually doesn't matter much as long as it not absurdly small (1MB absurdly small for analytics, 64MB is probably not).  In fact very large work_mem can be worse in those cases, because large priority queue heaps are unfriendly to the CPU cache.  (Based on Linux experience, but I don't see why that would not carry over to Windows)

Frankly I think you've bitten off more than you can chew.  600GB of csv is going to expand to probably 3TB of postgresql data once loaded.  If you can't control the platform, I'm guessing your disk array options are no better than your OS options are.

ACID compliance is expensive, both in storage overhead and in processing time, and I don't think you can afford that and probably don't need it.  Any chance you could give up on databases and get what you need just using pipelines of sort, cut, uniq, awk, perl, etc. (or whatever their Window equivalent is)?

Cheers,

Jeff