postgresql-9.6-OLTP-optimized-settings

#------------------------------------------------------------------------------
# CUSTOMIZED OPTIONS
#------------------------------------------------------------------------------
#
# this is from a Windows Server 2008 
# with 2 drives in SAS array
# and 32 GB of memory

# memory consumption
max_connections = 64 			        # we don't use much more
shared_buffers = 2048MB			      # increase from the default 128kB
effective_cache_size = 8192MB     # we have enough to spent
work_mem = 512MB				          # important for dumps
maintenance_work_mem = 1024MB 		# important for restore

# disk buffers
min_wal_size = 2GB                # this is 9.6 specific
max_wal_size = 4GB                #
wal_buffers = 16MB

default_statistics_target = 500

# effective_io_concurrency = 1		# SSD can handle 5, RAID is good with 2, but pg won't start on windows with this one
random_page_cost = 2.0				# modern disks are good enough to prefer this approach. the default is 4, we lower to 2

# checkpoint_segments=2048          # deprecated in 9.5, use min/max wal size
checkpoint_completion_target = 0.9  # allow more data to be accumulated before flush
checkpoint_timeout = 16min				  # range 30s-1h

# parallel execution related
max_worker_processes = 12				    # (change requires restart)
max_parallel_workers_per_gather = 6		# taken from the pool of max_worker_processes
max_locks_per_transaction = 2048    # needed by VisionR
min_parallel_relation_size = 4MB    # i've lowered it from 8MB as we have many small tables

this is from a running system with VisionR5