Optimize performance by Tunning PostgreSQL Database.

PGTune — Postgres tuning

What is it? 🤔

PGTune aka as Postgres Tuning. PGTune calculates configuration for PostgreSQL based on the maximum performance for a given hardware configuration.

Why do we need it? 🤔

Let’s take an example to understand the need, Let’s say we have Aws Postgres Box with the following configuration provided.

# DB Version: 11
# OS Type: linux
# DB Type: web
# Total Memory (RAM): 14 GB
# CPUs num: 8
# Connections num: 100
# Data Storage: ssd

Now even if you have higher configuration box if you see your default Postgressql.conf file, You will observe it is still using default configurations and gives performance according to it. So without Postgres Tuning no use of providing High memory and CPUs to Postgres Box. 😑

And that’s the reason we do need to Tune it manually by checking into conf file. 👍 💁

How To Implement it in your production 📝

1. Use PGTune website to create the configuration which will be suitable for the available space of your hardware.

Don’t know where to Look and do changes?

Here it is…!

1. Go to your aws box by doing ssh —

2. Connect to your database

sudo psql -U aa@xyz -d dbname

2. Open postgressql.conf file in vim.

sudo vim /etc/postgressql/11/main/postgressql.conf

3. You need to provide basic information about your hardware configuration, where is working PostgreSQL database. use the following command to check this.

3.1 check your hardware free size —

free -g

3.2 check available CPU’s by using following command

lscpu | egrep ‘Model name|Socket|Thread|NUMA|CPU\(s\)’

Once you provide the required information hit Generate Button, It will generate configurations.

Use this configuration to add into your hardware configuration, where is working PostgreSQL database in this path.

sudo vim /etc/postgressql/11/main/postgressql.conf

4. After adding configurations into your postgressql.conf file. Restart your Postgres database to apply the configurations.

sudo service postgressql restart

If you have found this useful Click the 💚 below to show your support and share it with other fellow Medium users. stay tuned.