Last Updated: May 15, 2019
· bartlomiejdanek

Tune up your PostgreSQL

Open file /etc/postgresql/9.2/main/postgresql.conf and edit following parameters:

shared_buffers = 512MB
temp_buffers = 16MB
work_mem = 128MB
maintenance_work_mem = 256MB
max_stack_depth = 6MB
effective_cache_size = 512MB

You have to also configure shmax and shmall kernel parameters, open file /etc/sysctl.conf and add:

kernel.shmmax = 4294967296
kernel.shmall = 4194304

And reload kernel settings by execute:

$ sysctl -p

Restart your PostgreSQL process and enjoy better performance.
Here you can read more about resource consumption in PostgreSQL.