Tune your PostgreSQL installation

1. Find out where the postgresql.conf file is.  On some systems (ubuntu), it's in /etc/postgresql/9.4/main or similar
2. Run "pgtune" and let it create a new config file, tuned to the system (you may have to install it first)... Example: pgtune -i postgresql.conf -o postgresql.conf.pgtune -T Web
3. Look at the bottom of postgresql.conf.pgtune, add these entries to your postgresql.conf

#

------------------------------------------------------------------------------

# CUSTOMIZED OPTIONS

#------------------------------------------------------------------------------

#custom_variable_classes = ''           # list of custom variable class

names

maintenance_work_mem = 1GB # pgtune wizard 2017-03-13

checkpoint_completion_target = 0.7 # pgtune wizard 2017-03-13

effective_cache_size = 22GB # pgtune wizard 2017-03-13

work_mem = 160MB # pgtune wizard 2017-03-13

wal_buffers = 4MB # pgtune wizard 2017-03-13

checkpoint_segments = 8 # pgtune wizard 2017-03-13

shared_buffers = 7680MB # pgtune wizard 2017-03-13

max_connections = 200 # pgtune wizard 2017-03-13

You can see "22GB" above... My sample system has 32GB of ram.

Restart postgresql. It might complain about "shared memory".  That needs to be placed in /etc/sysctl.conf... Examples: kernel.shmmax = 16870928384

Don't use the above number!  Use the number provided by the postgresql logs (or console) during restart.

Reload /etc/sysctl.conf, if necessary, restart postgresql, if necessary.

# sysctl -p

# /etc/init.d/postgres restart

 

If you don't tune postgresql, its performance will be lousy... Maybe not as bad as Derby.