Typical changes to postgresql.conf for large database installations
If you run a postgresql database with few connections but large jobs, here are some recommended settings for postgrsql.conf. Assume a sever with 75GB of RAM.
listen_address='localhost' ==> listen_address='*' # according to http://rhaas.blogspot.com/2012/03/tuning-sharedbuffers-and-walbuffers.html, # shared_buffers should be 25% of RAM, but no higher than 8GB. shared_buffers = 32MB ==> shared_buffers = 8GB temp_buffers = 8MB ==> temp_buffers = 80MB work_mem = 1MB ==> work_mem = 1GB # set higher on a per-session basis maintenance_work_mem = 16MB ==> maintenance_work_mem = 20GB # set quite high; used to build indexes and do data loads # according to http://rhaas.blogspot.com/2012/03/tuning-sharedbuffers-and-walbuffers.html, # wal_buffers should be 3% of shared_buffers up to a maximum of 16MB, the size of a single WAL segment. wal_buffers = 64kB ==> wal_buffers = 16MB # according to https://www.packtpub.com/article/server-configuration-tuning-postgresql, # checkpoint segments should be much higher than the default of 3: checkpoint_segments = 3 ==> checkpoint_segments = 64 # if you are on RAID 10, the cost of accessing a random page should be much closer to the cost # of sequential IO. (In fact, if you are on SSDs, seq_page_cost and random_page_cost should equal each other.) random_page_cost = 4.0 ==> random_page_cost = 2.0 # according to http://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server, # "Setting effective_cache_size to 1/2 of total memory would be a normal conservative setting, # and 3/4 of memory is a more aggressive but still reasonable amount." effective_cache_size = 128MB ==> effective_cache_size = 37GB # settings for logging: log_destination = 'stderr' logging_collector = on log_directory = 'pg_log' log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log' log_truncate_on_rotation = on log_rotation_age = 1d log_rotation_size = 0 log_min_messages = debug1 # obviously rather verbose; drop to warning if your logs fill up log_line_prefix = '%t'