Re: Tuning Postgres for Single connection use

Gavin Flower <GavinFlower@xxxxxxxxxxxxxxxxx> · Tue, 15 Apr 2014 10:41:57 +1200



    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, this, this 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