Tuning EN

en | ru

Fine-tuning

Many server applications initially come with a basic configuration that allows them to run on the simplest and lowest performance devices. Such systems need to be adapted to the servers and operating systems on which they are used. These actions can significantly (many times or dozens of times) increase the utilisation of server resources and so significantly improve the performance of the system as a whole.

Although this step is not obligatory, we strongly recommend making this adjustment.

PostgreSQL

There are special calculators available for calculating server performance parameters to make such calculations easier. Use one of the calculators to calculate the parameters:

• https://pgtune.leopard.in.ua/
• http://pgconfigurator.cybertec.at/

You need to specify the server specifications, Postgres version, etc. The output will give you a set of recommended parameter values which you should put in the /etc/postgresql/13/main/postgresql.conf file and then reboot PostgreSQL:

sudo systemctl restart postgres

More information on PostgreSQL optimisation can be found on the Internet. For example, at the following links:

• https://wiki.postgresql.org/wiki/Performance_Optimization
• https://ruhighload.com/post/%D0%A2%D1%8E%D0%BD%D0%B8%D0%BD%D0%B3+%D0%B1%D0%B0%D0%B7%D1%8B+Postgres

PostgreSQL's file system architecture is designed to be fast at the expense of file size. PostgreSQL does not waste time trying to update data in the same location on disk as the data being updated. It simply marks the updated data as deleted and adds a new entry to the file. This maximises system performance but makes the files very fragmented. Vacuuming helps to get rid of fragmentation. By default, after PostgreSQL is installed, an automatic vacuuming process is added which monitors the state of the files and performs low-priority defragmentation in parallel with PostgreSQL, if there is no load on the file system. Never disable autovacuuming, otherwise the database files will grow to enormous sizes.

In addition, you may find it useful to know how to start vacuuming manually and the procedure for rebuilding the indexes. These operations can in some cases significantly speed up the database: https://dba.stackexchange.com/questions/135881/postgresql-error-failed-to-re-find-parent-key-in-index