Improving RAM and CPU utilization by PostgreSQL 9.6

The default settings in postgresql.conf are very conservative and normally pretty low.

I suggest the following changes:

• raise shared_buffers to 1/8 of the complete memory, but not more than 4GB in total.
• set effective_cache_size to total memory available for postgresql - shared_buffers (effectively the memory size the system has for file caching)
• if you are running on SSDs you can also lower random_page_cost to 110% of seq_page_cost, but you should test this change if it has an effect. It basically makes postgres to go for index scans instead of sequentical scans

The postgres wiki has some more information about tuning the base paramters: https://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server

Also you should look into making the process run in parallel instead of one table after another, if this is possible. This would allow your process to utilize the full power of your hardware.

One possibility is to upgrade to the upcoming PostgreSQL 10 series, which has "better parallelism" as the most popular new feature. See the link for more on the improvement in parallelism.

Another possibility is probably divide the 115 tables into many groups and, e.g. run a batch file via psql -c for each group. This might be inefficient but will mostly likely solve the under-utilization issue.

Maybe you can also consider exporting and importing with \copy for better efficiency, and so on..

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

Try this website. it helped me a lot. Neat GUI environment