Permalink
Switch branches/tags
Nothing to show
Find file Copy path
Fetching contributors…
Cannot retrieve contributors at this time
122 lines (96 sloc) 4.58 KB
## extra.10.conf
# ===========================
#
# This file contains the 20 "also-rans", settings which can be ignored by most
# users, but are probably among the second run of settings that you want to
# adjust given a chance. As with simple.conf, AvRAM is your amount of available
# RAM for Postgres.
# General
# ------------
# authentication_timeout = 20s
# set to the same as your application's connection timeout
# cluster_name = 'postgres-1'
# give this Postgres instance a distinct name in case it is running on the same
# machine as other postgreses.
# event_source = 'postgres-1'
# give it a distinct name in the logs, too
# Performance
# -----------
# synchronous_commit = off
# if limited data loss is acceptable (up to 400ms), and your storage is very
# high-latency, then turning off sync commit can be a big performance win.
# wal_buffers = 128MB
# increasing this to as much as 128MB has been shown to have a performance
# benefit in servers with more than 8 cores and a heavy concurrent workload.
# wal_compression = on
# compressing WAL writes can have a significant performance benefit, especially
# when additional writing for logical replication is turned on. If you are seeing
# IOwaits on the WAL drive, try this.
# stats_temp_directory = '/run/postgresql/pg_stat_tmp'
# moving the stats temp directory to a tmpfs or other ramdisk will reduce
# IOPS from statistics updating. The drawback is losing your update count
# stats on a crash, but that's a good tradeoff for most people.
# autovacuum = off
# most users want autovacuum on and should be prevented from turning it off.
# however, for analytics databases which are updated by bulk load, where
# manual VACUUMs and ANALYZEs are part of the load procedure, can and should
# disable autovacuum.
# cpu_index_tuple_cost = 0.001
# cpu_operator_cost = 0.0005
# If you want to give the query planner a slight nudge towards using more indexes
# especially multi-column indexes, try lowering its estimate of CPU requirements
# for them.
# default_statistics_target = 1000
# for analytics DBs and/or tables over 10 million records, it can be useful
# to increase the sample size for statistics. It's better to do this by table
# and column, however.
# effective_io_concurrency = 4
# have, fast, high-concurrency storage? Let the query planner know.
# replacement_sort_tuples = 0
# this setting's default is anti-performance in 10 due to improvements elsewhere,
# and it will be removed from Postgres 11.
# old_snapshot_threshold = 60min
# this setting is a way of minimizing the impact long-running statements or
# transactions have on the ability of the database to do maintenance and
# update indexes. If set, applications need to be prepared to re-run long
# batch jobs in the event of a "snapshot too old" failure.
# File Security
# --------------
# unix_socket_directories = '/run/postgresql' #varies by OS
# group = 'postgres'
# permissions = 770
# some installers put the PostgreSQL unix socket in /tmp, which offers several
# possible security exploits, even in containers.
# Parallel Query
# --------------
#
# The default settings for parallel query seem like they offer just enough
# parallel to be annoying. You're going to want to either increase them to
# make actual use of parallel query, or disable the feature. Heres's some
# recommendations for either case.
# Disable parallel query by default:
# max_parallel_workers = 0
# Enable parallel query by default for an analytics database:
# max_background_workers = Cores + 2 # more if you have worker extensions
# max_parallel_workers = ( Cores )
# max_parallel_workers_per_gather = ( 2 * Cores ) / ( # of expected sessions )
# Example: 32 core machine, for an analytics database which usually supports
# around 4 parallel reports, and has 2 extra background workers (a
# partition manager):
# max_background_workers = 36
# max_parallel_workers = 32
# max_parallel_workers_per_gather = 16
# Tuple Freezing
# -------------------
# "Freezing" is important long-term maintenance that Postgres does to clean
# out old transactionIDs from the database files. The default settings are
# overly conservative for efficiency, so if you have a database that goes
# through more than a hundred million XIDs per month, you may want to change
# them. More info here:
# http://www.databasesoup.com/2012/09/freezing-your-tuples-off-part-1.html
# This has become less of a concern with the Freeze Map, but still needs
# tweaking.
# autovacuum_freeze_max_age = 500000000
# vacuum_freeze_min_age = 50000 #should really be 1 hour of XIDs
# vacuum_freeze_table_age = 400000000
# vacuum_multixact_freeze_min_age = 100000 #2x vac_freeze_min_age