Basic PostgreSQL configuration

Postgres is a very good database. But do not forget that the basic configuration of Postgres, designed from what would run even on very weak computers.

For example:

  1. With basic settings you will not be able to take advantage of the SSD because the query planner will evaluate the operation random and sequential read, with the coefficients for HDD
  2. The default value of work_mem is only 4MB. This means that only 4 MB of RAM is allocated for requests via one dedicated connect to the database, and when this value is exceeded, temporary files are created on the disk, and this is very slow

Because it is very important to configure the database to use all the resources that you are ready to provide it. After all, it is likely that your requests are slow not because of bad profiling, but because of suboptimal database settings.

Important: do Not apply settings if you do not understand how they work and what they do, you can damage the operation of the database.
In order to learn all the available settings, please refer to the official documentation

https://www.postgresql.org/docs/11/runtime-config.html

The number of settings can be discouraging and take a lot of time to understand them. Because in order to start you can use the free calculator settings. For example:

https://pgtune.leopard.in.ua/#/

The default path to the configuration file for Ubuntu

/etc/postgresql/{Version Postgres (ex 11)}/main/postgresql.conf

6 Likes