While managing the database, the most difficult thing to manage is speed. The speed of the database is affected by hardware, configuration and sometimes even the design of the system is also responsible for the bad performance. As the performance of PostgreSQL is based on hardware and even on our system itself, therefore, PostgreSQL should be configured with compatibility and stability in mind. But the default configuration is not operational for different types of workloads. Therefore, we need to change it accordingly.
Here, in this article, we will some basic configuration parameters that can help in Postgres query performance tuning
Configuration parameters
When we have to modify these configuration parameters, then we have to edit the file $ PGDATA / PostgreSQL.conf. While making changes in the configuration parameters, you should keep in your mind that some of these configuration parameters update demand restart of the system.
- max_connections
This parameter determines the maximum number of connections to the database simultaneously. Some of the memory resources can be configured per client. Thus, maximum clients are allowed to suggest the max amount of memory usage.
- superuser_reserved_connections
Some of the connections are reserved for superusers. These are used only when the limit of max_connection has approached.
- shared_buffers
This configuration parameter can help in allotting the specific for the shared memory buffers. In case, you have a specific database server with 1 GB memory then the reasonable initial shared buffer will be 25% of your total system’s memory. To have the larger configurations for the shared_buffers usually require the corresponding in max_wal_size. It will let you extend the process of writing a huge amount of modified data for a long time.
- temp_buffers
This configuration parameter lets you set the max number of temporary buffers allowed for usage during each session. The temporary buffers are local and they are used for accessing the temporary tables only. The temporary buffers will be assigned to a session as required but the maximum number of buffers cannot exceed the limit given by temp-buffers.
- work_mem
Work-mem allots the max amount of memory that can be used by the internal operations of ORDER BY, DISTINCT, hash tables, and JOIN. It specifies the memory used before writing files on a disk. While configuring this memory value, it is imperative to consider various sessions executing these operations simultaneously. Each operation will be allowed to utilize the amount of memory specific by this config parameter. This memory is used before writing data in the temp files. In the old version of PostgreSQL, this option is known as sort_mem.
- maintenance_work_mem
It allots the maximum memory for the maintenance operations on the database like CREATE INDEX, VACUUM, creates INDEX, and ALTER TABLE ADD FOREIGN KEY. As only one maintenance operation can be executed at one time by a session, therefore, an installation does not have many of them running at the same time. Therefore, this memory can be large as compared to the work-mem. The larger size configuration can help in improving the operational efficiency of VACUUM and database restores.
- fsync
When fsync is activated, then the PostgreSQL database will try to ensure that all updates are written to the disk. It will help you to make sure that the database recovery is possible to a consistent state even after the OS or hardware crash. Though disabling fsync helps in improving the performance, it can lead to the loss of data in case of power failure or system crash. In case you can create a database from external data then you should improve the performance of the database by deactivating sync.
- checkpoint_segments (PostgreSQL < 9.5)
The config parameter helps to set the max number of record file segments between automatic WAL control points. Usually, the size of each segment is 16 MB. If you will increase this parameter, then it will automatically increase the time required for recovering the faults. The database system that receives a huge volume of traffic will suffer if this parameter is set to a very low value. It is recommended that the value of checkpoint-segments should be set higher with various data modifications.
In addition to this, you should save the WAL files on the disk instead of PGDATA. It is quite useful for both security and balancing the writing during hardware crashes.
- max_wal_size (PostgreSQL >= 9.5)
max-wal-size is a configuration parameter that describes the Maximum WAL size allowed to grow among various control points. It is possible to increase the WAL size in special cases. The size of WAL can exceed max_wal_size in special circumstances. With the increase in this parameter, then the amount of fault recovery time also increases.
min_wal_size (PostgreSQL >= 9.5)
If the WAL value is below the specified value, then it can be recycled for the use in future at a checkpoint rather than being deleted.