Notes

• Please take a snapshot of the vm and a backup of the config files that are to be modified before making any changes
• Please restart postgres for the changes to take effect.

Config changes wrt postgresql.conf

File: /etc/postgresql/8.4/main/postgresql.conf

Definitions

max_connections

• Determines the maximum number of concurrent connections to the database server.
• The default is typically 100 connections.
• Increasing this improves concurrency but will result in an increase in memory usage.

shared_buffers

• The shared_buffers configuration parameter determines how much memory is dedicated to PostgreSQL to use for caching data.
• If you have a system with 1GB or more of RAM, a reasonable starting value for shared_buffers is 1/4 of the system memory.
• Please make sure to check and update the operating system's default allowed shared memory setting when changing this parameter.

Kernel settings to update when changing shared_buffers

Kernel shmmax and shmall should be updated if the shared buffers setting is increased beyond the system's default allowed shared memory.

• To see the current system's settings check the output of sysctl -A|grep shm
• Please check the example config section to see recommended values for these settings.

effective_cache_size

• This indicates the amount of memory that is available for disk caching.
• This value is used by the PostgreSQL query planner to better plan the execution of a query. Setting this too low will result in table indexes not being used during query execution.

default_statistics_target

• Postgres collects statistics about each of the tables in the database to help with planning query execution. default_statistics_target specifies how many records to use as a sample set to collect statistics from.
• The default value is 100 (in this case 300 * 100 queries will be considered for statistics)
• Increasing this value allows query planner to plan query execution more efficiently.

random_page_cost

• This is a constant used by query planner to estimate the time taken for making index scans.
• The default value is 4.0.
• It is recommended to not change the default as increasing this value could result in the query planner estimating index scans to take longer and deciding to use sequential scans.

effective_io_concurrency

• Sets the number of concurrent disk I/O operations that PostgreSQL expects can be executed simultaneously.
• Raising this value will increase the number of I/O operations that any individual PostgreSQL session attempts to initiate in parallel.
• The allowed range is 1 to 1000. The default value is 1

work_mem

• work_mem specifies how much memory is available for caching the results of certain operations in a query such as sorting and hash tables.

maintenance_work_mem

• Specifies the maximum amount of memory to be used by maintenance operations such as vacum.

checkpoint_timeout

• checkpoint_timeout indicates how often postgres should checkpoint data, ie, to persist the data from transaction logs (WAL - write ahead logs) to permanent storage.
• The default value is 5 min

checkpoint_completion_target

• checkpoint_completion_target parameter indicates how long the postgres has got to persist data from transaction logs to permanent storage before the next checkpoint occurs.
• This is calculated as (checkpoint_timeout - 2min) / checkpoint_timeout
• The default value of checkpoint_completion_target is 0.5
• Increasing this value reduces the number of times postgres writes data from memory to permanent storage.

wal_buffers

• This indicates the amount of memory that can be allocated for storing WAL data.
• The default number of WAL buffers is 8. Increasing this value will correspondingly increase shared memory usage.

checkpoint_segments

• Indicates the maximum number of transaction log segments allowed between checkpoints
• The default is 3
• Increasing this allows better write performance by reducing the frequency of checkpoints.
• Note that increasing this can increase the amount of time needed for crash recovery

Example config

1 gb ram, 2 cpus

postgresql.conf

max_connections = 200  
shared_buffers = 256MB  
effective_cache_size = 768MB  
maintenance_work_mem = 64MB  
checkpoint_completion_target = 0.7  
wal_buffers = 7864kB  
default_statistics_target = 100  
random_page_cost = 4  
effective_io_concurrency = 2  
work_mem = 1310kB  
checkpoint_segments = 32

sysctl.conf

kernel.shmmax=536870912  
kernel.shmall=131072

16 gb ram, 4 cpus

postgresql.conf

max_connections = 200  
shared_buffers = 4GB  
effective_cache_size = 12GB  
maintenance_work_mem = 1GB  
checkpoint_completion_target = 0.7  
wal_buffers = 16MB  
default_statistics_target = 100  
random_page_cost = 4  
effective_io_concurrency = 2  
work_mem = 20971kB  
checkpoint_segments = 32

sysctl.conf

kernel.shmmax=8589934592  
kernel.shmall=2097152

References

• https://pgtune.leopard.in.ua/ - you can use this website to get the recommended settings for postgres in your vm (please specify the DB version as 9.2, DB Type as Web application)

Other changes that can be done to improve performance

Stop all postgres servers running except 8.4

• Check the status of postgres - sudo service postgresql status.
• If the output indicates multiple versions of postgres running on the vm, please stop all versions except 8.4 (/etc/init.d/postgres/9.1/ stop)