###Optimal Settings for Postgres
log_destination = 'csvlog'
log_directory = 'pg_log'
logging_collector = on (rotates files)
log_filename = 'postgres-%Y-%m-%d_%H%M%S'
log_rotation_age = 1d
log_rotation_size = 1GB
log_min_duration_statement = 250ms (log statements that take > 250ms to execute)
log_checkpoints = on
log_connections = on
log_disconnections = on
log_lock_waits = on (log when a lock waits more than 1 sec, which is the default values)
log_temp_files = 0 (log whenever temp files are created)
shared_buffers = 25% of memory to 8GB # allocated at startup
work_mem = (2 * {RAM})/max_connections # allocated on demand
maintenance_work_mem = {RAM}/16 # allocated on demand
effective_cache_size = {RAM}/2 # allocated on demand
max_connections = no more than 400
wal_buffers = 16MB
checkpoint_completion_target = 0.9
# Depends on restart time - higher values give better performance but does reduce the startup time for # postgres for a dirty restart
checkpoint_timeout = 10m-30m
checkpoint_segments = 32 # To start.
effective_io_concurrency — Set to the number of I/O channels; otherwise, ignore it.
random_page_cost — 3.0 for a typical RAID10 array, 2.0 for a SAN, 1.1 for Amazon EBS.
# Needs pg_stat_statements extension. See extras section for setting it up
pg_stat_statements.max = 1000
pg_stat_statements.track = top # top,all,none
pg_stat_statements.save = off
###Extras
Set the shared_preload_libraries to pg_stat_statements in postgresql.conf to help get some sweet analytics about queries on DB
shared_preload_libraries = 'pg_stat_statements'
#psql
create extension "pg_stat_statements";
select * from pg_stat_statements;