PostgreSQL configuration

PostgreSQL can easily be tuned using the third-party-tool pgtune . It is licensed under a standard three-clause BSD license. pgtune works by taking an existing postgresql.conf file as an input and making changes to it based on the amount of RAM in your server and a suggested workload. pgtune then outputs a new file with suggestions.

The following example is for a server with 16 GB of RAM and a suggested workload of 100 simultaneous connections to the server.

1. Enter the following command line:# ./pgtune -i $PGDATA/postgresql.conf -o $PGDATA/postgresql.conf.pgtune --type MIXED --connections=100
    The values recommended by pgtune are appended at the end of the postgresql.conf.pgtune file, for example: 

   
   #------------------------------------------------------------------------------
   # pgtune run on 2013-07-08
   # based on 16445412 KB RAM, platform Linux
   #------------------------------------------------------------------------------
   default_statistics_target = 100
   maintenance_work_mem = 960MB
   checkpoint_completion_target = 0.9
   effective_cache_size = 11GB
   work_mem = 80MB
   wal_buffers = 16MB
   checkpoint_segments = 32
   shared_buffers = 3840MB
   

2. Add the following additional values at the end of the postgresql.conf.pgtune file: 

   
   bgwriter_lru_maxpages = 1000
   bgwriter_lru_multiplier = 4.0
   random_page_cost = 2.0
   cpu_tuple_cost = 0.03
   log_autovacuum_min_duration = 0
   autovacuum_max_workers = 5
   autovacuum_vacuum_cost_delay = 10ms
   

3. Run the following commands to apply the new parameters and values to the original postgresql.conf file by renaming the postgresql.conf.pgtune pgtune file to postgresql.conf and then restarting PostgreSQL: 

   
   mv $PGDATA/postgresql.conf $PGDATA/postgresql.conf.sav
   mv $PGDATA/postgresql.conf.pgtune $PGDATA/postgresql.conf
   chown postgres:postgres $PGDATA/postgresql.conf
   chmod 0644 $PGDATA/postgresql.conf
   service postgresql restart