If you are working on Postgres and facing issues of slow queries then tuning the Postgres server can be a good point to optimize the performance by tuning the Postgres database.

In this article, we will talk about increasing the size of shared_buffers value.
The shared_buffers configuration parameter determines how much memory PostgreSQL has to use for caching data. This is probably the most important setting. Setting it to 1/4 of RAM should be OK for modern systems.

You can check the current value of shared_buffers by running the below SQL in Postgres.

show shared_buffers;
-- Output: 128MB

By default its value is 128 MB. we are going to change it to 4GB by editing postgresql.conf file.

Typically this file is found at /usr/local/var/postgres/postgresql.conf. Check if you have any other file that is being used in Postgres. In my case, this file was at
/Library/PostgreSQL/13/data/postgresql.conf
and
/Users/piyush/Library/Application Support/Postgres/var-13/postgresql.conf

Search for shared_buffers = 128MB and change the value of 128MB to 4GB.

Search for shared_buffers = 128MB and change the value of 128MB to 4GB.

Search for work_mem and change the value to 150 MB.

We need to restart the Postgres server and again run the query show shared_buffers; you will see the updated value. This is all about tuning the Postgres database.

Happy Coding!!