Improving RAM and CPU utilization by PostgreSQL 9.6

I've been running a function on the database that is going into each table, ALTER COLUMN on all columns of a certain data type, and CAST to remove trailing zeros. About 115 tables varying from a few thousand records to a few hundred thousand records. It has been running for almost 24 hours and my approximate calculation until time of completion is about 58 hours.

I have htop up and checking on it regularly.

I should mention this is a clean install of CentOS minimal as of yesterday with really only PostgreSQL 9.6.5 on it. Two quad core CPUs and 32GB of RAM.

According to htop my mem is at 620M/31.3G and it maxes out one core at a time, never really utilizing other cores at the same time. It seems like I have a ton of head room as far as CPU and RAM utilization. It seems if I could properly setup PostgreSQL to utilize more of the CPU and RAM, process like I am running now would run much faster. Maybe I am wrong.

What are some settings that can be adjusted, whether in Postgres or in CentOS, that will allow for more utilization?

My postgresql.conf is pretty much at default settings:

```
# RESOURCE USAGE (except WAL)
#------------------------------------------------------------------------------

# - Memory -

shared_buffers = 128MB # min 128kB
# (change requires restart)
#huge_pages = try # on, off, or try
# (change requires restart)
#temp_buffers = 8MB # min 800kB
#max_prepared_transactions = 0 # zero disables the feature
# (change requires restart)
# Caution: it is not advisable to set max_prepared_transactions nonzero unless
# you actively intend to use prepared transactions.
#work_mem = 4MB # min 64kB
#maintenance_work_mem = 64MB # min 1MB
#replacement_sort_tuples = 150000 # limits use of replacement selection sort
#autovac

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.

StackExchange Database Administrators Q#186693, answer score: 14