JRehkemper.de

PostgreSQL Tuning

When installing a PostgreSQL Database, you should tune your configuration. The default settings are aimed to run on very low end hardware. Since your system is probably more powerfuly you can safely adjust these parameters.

This guide is based on the official wiki of postgresql.

The following is a list of parameters that you should consider to change.

Config File

The configuration file for Postgres is located here.

/var/lib/pgsql/data/postgresql.conf

listen_addresses

By default it is set to localhost. If you want to make your database available over the network, either set it to the ip-address of your interface or to * to listen on all interfaces.

listen_addresses = '*'

max_connections

This sets the maximum of concurrent connections. You can safely set it to a few hundret connections. If you need more than that, you should consider using pooling software like pgbouncer.

shared_buffers

On a modern Linux system with more than 1GB of Memory you should set this to roughly 1/4 of your RAM size.

effective_cache_size

This should be the amount of memory the DB may occupy without interfering with the OS. This is normally around 50% to 75% of the total memory.
If you have to little effective_cache_size the queryplaner may not use indices since they will not fit into the cache. If you choose to large values, your system may start swapping, since the dataset is to large.
If you have your system already installed, but Postgres is not yet running, take the output of free -h and use the available value.

huge_pages

This will control whether Postgres uses HugePages as shared buffer or not.
The default value is try. This means Postgres will request HugePages, but will start with normal memory pages as well.
If you set it to on it will only start with HugePages configured and off will never use them.

profile picture of the author

Jannik Rehkemper

I'm an professional Linux Administrator and Hobby Programmer. My training as an IT-Professional started in 2019 and ended in 2022. Since 2023 I'm working as an Linux Administrator.