The configuration file of PostgreSQL is the postgresql.conf file in the database directory. The settings of the parameters in this file has a great influence on the performance of PostgreSQL database, pay attention to the configuration of the five parameters below.
- Shared buffers
PostgreSQL interact with the kernel and disk through shared buffer, so you should set the parameter as larger as possible to store more data.
The default value of the shared buffers parameter shared_buffers is 1024KB, it should be set as at least 128 KB. Generally, you can set it as 10% of the actual RAM, for example 50000(400M).
- Work memory
When performing the sort operations, the EnterpriseDB will decide whether to split a large result to several temporary files whose size are similar to the work memory according to the size of the work memory, obviously the splitting will lower the sorting speed. Increase the size of the work memory will improve the sorting speed.
The default value of the work_mem parameter in postgresql.conf is 1024KB, generally, it can be set as 2%-4% of the actual RAM according to the size of the sorting result set, such as 81920(80M).
- Effective cache size
Effective cache size is the maximum cache that PostgreSQL can use, it should be as large as possible for independent PostgreSQL server.
The effective_cache_size parameter in postgresql.conf can be set as 3.5G(437500) if the memory is 4G.
- Maintenance work memory
Maintenance work memory is used only in CREATE INDEX, VACUUM, etc. The using frequency is not high, but these instruction may cost lots of resources, so it's better to complete the command as soon as possible.
You should set the maintence_work_mem parameter to a larger value, such as 512M(524288).
- Maximum connections
max_connections*work_mem may exceed the actual memory, so it is needed to set the maximum connections. For example, set work_mem as 2% of the actual memory, in extreme cases, if 50 queries all have sorting needs and use 2% of the memory, this may lead to the creating of swap and the system performance will be decrease rapidly.
The default value of the max_connections in postgresql.conf is 100.