postgresql: tweak for large databases

go to pgtune,

input your hardware and desired configuration and copy the recommended parameters at the bottom of your portgresql .conf file.

https://pgtune.leopard.in.ua/#/

scrap notes:

honestly after tweaking the setting with no much better performance, I changed the db to a server on an SSD, and the performance changed completely. So the conclusion is that the hard drive has a great importance on the query speed for large databases.

 

iotop -o

 

 

 

 

configure checkpoints for large dbs

 

postgresql.conf

 

max_wal_size
min_wal_size
checkpoint_timeout
checkpoint_completion_target

 

 

https://www.postgresql.org/docs/12/populate.html

 

 

disable autocommit

 

from cli:

\set AUTOCOMMIT off

 

wal_level = minimal

 

 

 

 

Leave a Reply

Your email address will not be published. Required fields are marked *