Appendix A - Tuning Database Parameters : Tuning of PostgreSQL Parameters
  

Tuning of PostgreSQL Parameters

PostgreSQL comes with default values that work across different systems with varying resource availability so they are really the lowest common denominator to get it running everywhere. This section contains some values to tweak when there is more memory to play with.
Make sure that you know what the setting does, before changing it. Do not just increase some memory values, thinking that it will magically fix everything. Consult the manual, found on http://www.postgresql.org/docs/9.4/static/runtime-config-resource.html and http://www.postgresql.org/docs/9.4/static/runtime-config-query.html, before changing any settings.
On http://pgtune.leopard.in.ua/ you can find a calculator that gives some sense of what to tweak. It will calculate values for some recommended settings, based on how much total RAM your machine has. Set max_connections to 100, as that is what is the default value is for a PostgreSQL installation, and should be enough for small to mid-sized installations. Consider increasing the number of connections, if you have a large installation with only a central Queue Agent.
These are the main settings to tweak (values are for a machine with 13GB of memory). Note however that the changes to memory values and cache sizes do not mean that that much will be consumed by PostgreSQL at all times.
Table 1: Changes to postgresql.conf.
Setting
New value (Default)
Description
max_connections
100 (100)
Do not change this without consulting the manual! This works in conjunction with work_mem and some other stuff and can lead to excessive memory usage if not changed appropriately. If you increase this to say 200, make sure to lower work_mem.
shared_buffers
256MB (128MB)
Keep this low. Recommendation is to use 25% of available memory but some say this value doesn't provide much benefit. effective_cache_size seems to give more value.
effective_cache_size
8GB (4GB)
See memory values in performance tab in Task Manager (Windows) for the system's "Cache" value and compare that to available free RAM and use a value that is somewhere in that vicinity. This seems to have most potential for improving performance. High values favor use of indexes in the DB. Some just set this value to the amount of available free RAM.
work_mem
16MB (4MB)
This can be tricky to set as it can cause excessive memory use for complex queries if many clients run the same queries at the same time. So the worst case scenario can be high (luckily Orchestra doesn't have many overly complex queries).
autovacuum_work_mem
512MB (-1 i.e. uses maintenance_work_mem value)
The amount of memory that the auto vacuum process is allowed to consume the most. Setting this high can speed up vacuum work.
checkpoint_segments
32 (3)
 
 
checkpoint_completion_target
0.7 (0.5)
 
 
wal_buffers
16MB (4MB)
 
 
random_page_cost
3 (4)
If there is lots of memory for the database i.e. lots of it is cached (Available memory higher than size of DB), and/or the DB machine uses SSD disks for the DB, then lowering this value can provide some performance gains.
Make sure to restart PostgreSQL when done. A reload will not be sufficient, as some settings require a restart.
To see what settings the PostgreSQL installation currently has, use the following query:
SHOW ALL