Skip to content

Instantly share code, notes, and snippets.

@CarterTsai
Forked from kigster/gist:4751844
Created November 8, 2015 15:11
Show Gist options
  • Save CarterTsai/817022d310015acae328 to your computer and use it in GitHub Desktop.
Save CarterTsai/817022d310015acae328 to your computer and use it in GitHub Desktop.
PostgreSQL config file for high performance writes on Joyent Cloud, used by Wanelo.com in production (4K commits/second on a dedicated 80GB instance).
max_connections = 1500 # (change requires restart)
shared_buffers = 12000MB # min 128kB, based on 80GB RAM DB
temp_buffers = 8MB # min 800kB
work_mem = 64MB # min 64kB
maintenance_work_mem = 512MB # min 1MB
wal_level = hot_standby # minimal, archive, or hot_standby
checkpoint_segments = 64 # in logfile segments, min 1, 16MB each
checkpoint_completion_target = 0.9 # checkpoint target duration, 0.0 - 1.0
max_wal_senders = 6 # max number of walsender processes
wal_keep_segments = 2000 # in logfile segments, 16MB each; 0 disables
hot_standby = 'on' # "on" allows queries during recovery
hot_standby_feedback = 'on' # we may want to experiment with it being 'off'
max_standby_streaming_delay = 30s # max delay before canceling queries
random_page_cost = 2.0 # same scale as above
effective_cache_size = 65824MB # based on 80GB RAM DB
# Logging
log_destination = 'syslog' # Valid values are combinations of
log_min_duration_statement = 80 # -1 is disabled, 0 logs all statements
log_line_prefix = ''
log_temp_files = 0 # log temporary files equal or larger
log_checkpoints = on # log checkpoints
log_timezone = 'PST8PDT'
# use stat_statements, and log stats into /tmp (RAM on SmartOS)
# note that startup script must re-recreate /tmp/pg_stats_temp_directory upon server restart
shared_preload_libraries = '$libdir/pg_stat_statements' # (change requires restart)
stats_temp_directory = '/tmp/pg_stats_temp_directory' # log stats on RAM (/tmp) instead of hard disk
autovacuum_max_workers = 3 # max number of autovacuum subprocesses
autovacuum_vacuum_scale_factor = 0.2 # fraction of table size before vacuum
autovacuum_analyze_scale_factor = 0.1 # fraction of table size before analyze
autovacuum_freeze_max_age = 1000000000 # maximum XID age before forced vacuum
vacuum_freeze_min_age = 5000000
vacuum_freeze_table_age = 500000000
timezone = 'PST8PDT'
lc_messages = 'C' # locale for system error message
lc_monetary = 'en_US.UTF-8' # locale for monetary formatting
lc_numeric = 'en_US.UTF-8' # locale for number formatting
lc_time = 'en_US.UTF-8' # locale for time formatting
default_text_search_config = 'pg_catalog.english'
# these are settings for high-throughput write load
wal_buffers = 32MB
synchronous_commit = off
wall_writer_delay = 200ms
commit_delay = 100 # microseconds
commit_siblings = 5
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment