Terrific -- thanks Gavin and Jeff! That's incredibly helpful for a n00b like me!
On Mon, Apr 14, 2014 at 5:29 PM, Gavin Flower <gavinflo...@archidevsys.co.nz > wrote: > In this list, please bottom post! > > I've added potentially useful advice below. > > > On 15/04/14 11:39, Nick Eubank wrote: > > Thanks Gavin -- would LOVE to. Sadly I'm in a weird situation > where my hardware is not under my control, so I'm stuck making the best of > what I have. Next time though! :) > > On Monday, April 14, 2014, Gavin Flower <gavinflo...@archidevsys.co.nz> > wrote: > >> On 15/04/14 09:46, Nick Eubank wrote: >> >> Any rules of thumb for work_mem, maintenance_work_mem, shared_buffer, >> etc. for a database that DOESN'T anticipate concurrent connections and that >> is doing lots of aggregate functions on large tables? All the advice I >> can find online on tuning >> (this<http://wiki.postgresql.org/wiki/Performance_Optimization> >> , >> this<http://media.revsys.com/talks/djangocon/2011/secrets-of-postgresql-performance.pdf> >> , this <http://www.revsys.com/writings/postgresql-performance.html> etc.) >> is written for people anticipating lots of concurrent connections. >> >> I'm a social scientist looking to use Postgres not as a database to be >> shared by multiple users, but rather as my own tool for manipulating a >> massive data set (I have 5 billion transaction records (600gb in csv) and >> want to pull out unique user pairs, estimate aggregates for individual >> users, etc.). This also means almost no writing, except to creation of new >> tables based on selections from the main table. >> >> I'm on a Windows 8 VM with 16gb ram, SCSI VMware HD, and 3 cores if >> that's important. >> >> Thanks! >> >> Well for serious database work, I suggest upgrading to Linux - you will >> get better performance out of the same hardware and probably (a year or so >> ago, I noticed some tuning options did not apply to Microsoft O/S's, but I >> don't recall the details - these options may, or may not, apply to your >> situation) more scope for tuning. Apart from anything else, your >> processing will not be slowed down by having to run anti-virus software! >> >> Note that in Linux you have a wide choice of distributions and desktop >> environments: I chose Mate (http://mate-desktop.org), some people prefer >> xfce (http://www.xfce.org), I used to use GNOME 2. >> >> >> Cheers, >> Gavin >> > Yeah, I know the feeling! > > I have a client that uses MySQL (ugh!), but I won't even bother mentioning > PostgreSQL! > > Hopefully, someone more knowledgeable will give you some good advice > specific to your O/S. > > For tables that don't change, consider a packing density of 100%. > > Take care in how you design your tables, and the column types. > > Consider carefully the queries you are likely to use, so you can design > appropriate indexes. > > Some advice will depend on the schema you plan to use, and the type of > queries. > > > Cheers, > Gavin > > >
