Re: Tuning Postgres for Single connection use

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