postgresql-tuning.conf

# PostgreSQL tuning suggestions (adjust for your machine).
# See PgTune: http://pgtune.leopard.in.ua/
# 
# To apply these changes, edit the existing postgresql.conf that is in use on
# your system.
# - Arch: /var/lib/postgres/data/postgresql.conf
# - Ubuntu: /etc/postgresql/{{.pg_version}}/main/postgresql.conf
# - Mac: /usr/local/var/postgres/postgres.conf
# Be sure to restart PostgreSQL after saving the config.

# Always use the autovacuum process, especially for normal operation! For fast
# queries during normal operation, it is critical to have regular table
# statistics collected by the autovacuum process.
autovacuum = on

# This is the most important setting for fast initial database population.
# OK for general use on a stable system. Insert with alacrity.
synchronous_commit = off

# Decent values for PostgreSQL 11.x on a 6-8GB system with 2 cores, leaving some
# RAM for the dcrdata process:
max_connections = 32
shared_buffers = 1792MB
effective_cache_size = 5376MB
maintenance_work_mem = 448MB
max_worker_processes = 2
work_mem = 28MB # scale this down with increasing #cores and #connections!
max_parallel_workers_per_gather = 1
max_parallel_workers = 2 # v10+ only
wal_buffers = 16MB
max_wal_size = 2GB
min_wal_size = 1GB
checkpoint_completion_target = 0.9
default_statistics_target = 100

# Drive type-specific settings
random_page_cost = 1.1 # for SSD/SAN
effective_io_concurrency = 200 # for SSD/SAN, maybe higher for NVMe controllers
# random_page_cost = 4 # for HDD
# effective_io_concurrency = 2 # for HDD

# Large import/insert only. Reverse for normal use.
full_page_writes = off