Re: Tuning Postgres for Single connection use

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 <GavinFlower(at)archidevsys(dot)co(dot)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
>