Tune PostgreSQL performance CentOS#

After installing, initializing, and bootstrapping PostgreSQL, you can edit the configuration file to improve database performance.

Edit the PostgreSQL configuration file#

Depending on your system setup and the total amount of memory you can allocate to PostgreSQL, you may wish to tune the database configuration.

The extract below is just an example for reference, it is not a silver bullet.

Typical PostgreSQL configuration parameters that you can tweak to improve the database performance affect:

The maximum amount of concurrent connections.
The size of buffer and working memory, as well as of maintenance working memory.

To tune the PostgreSQL database configuration:

Edit the /media/pgsql/11/data/postgresql.conf configuration file:

# With the amount of Celery workers and other Python processes the
# number of required connections can exceed the default value of 100.
# Try increasing it to 500.
# See:
# https://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server#max_connections
max_connections = 500

# Tune the PostgreSQL buffer and work memory
shared_buffers = 1024MB
work_mem = 8MB

# Random page lookups are cheap on SSD disks
random_page_cost = 1.5

# Maintenance operations are usually not performed concurrently,
# so they need significantly more memory to run faster.
maintenance_work_mem = 1GB

After editing the database configuration, restart the PostgreSQL service to retrieve the changes and to make them effective:
```
# Restart the PostgreSQL service
systemctl restart postgresql-11
```
Lastly, make sure that the database is up and running by checking the PostgreSQL status:
```
# Check the PostgreSQL service status
systemctl status postgresql-11
```