Tom,
Well, why not just make a one-eighty and say that the default
postgresql.conf is *empty* (except for whatever initdb puts into it)?
I've never thought that the current contents were especially useful
as documentation; the kindest thing you can say about 'em is that they
are duplicative of the SGML documentation. For novices they aren't
even adequately duplicative.
Well, that doesn't help unless we either provide a .conf generation tool
(something I favor) or docs somewhere which explain which are the
variables to be the most concerned with instead of making users read
through all 218 of them.
Attached is the postgresql.conf.simple I used in my presentaiton. It
has an egregious math error in it (see if you can find it) but should
give you the general idea.
--Josh
# ----------------------------------------
# Simple PostgreSQL Configuration File
# ----------------------------------------
# This file provides a simple configuration with the most common options
# which most users need to modify for running PostgreSQL in production,
# including extensive notes on how to set each of these. If your configuration
# needs are more specific, then use the standard postgresql.conf, or add
# additional configuration options to the bottom of this file.
#
# This file is re-read when you send a SIGHUP to the server, or on a full
# restart. Note that on a SIGHUP simply recommenting the settings is not
# enough to reset to default value; the last explicit value you set will
# still be in effect.
#
# AvRAM: Several of the formulas below ask for "AvRAM", which is short for
# "Available RAM". This refers to the amount of memory which is available for
# running PostgreSQL. On a dedicated PostgreSQL server, you can use the total
# system RAM, but on shared servers you need to estimate what portion of RAM
# is usually available for PostgreSQL.
#
# Each setting below lists one recommended starting setting, followed by
# several alternate settings which are commented out. If multiple settings
# are uncommented, the *last* one will take effect.
# listen_addresses
# ------------------------
# listen_addresses takes a list of network interfaces the Postmaster will
# listen on. The setting below, '*', listens on all interfaces, and is only
# appropriate for development servers and initial setup. Otherwise, it
# should be restrictively set to only specific addresses. Note that most
# PostgreSQL access control settings are in the pg_hba.conf file.
listen_addresses = '*' # all interfaces
# listen_addresses = 'localhost' # unix sockets and loopback only
# listen_addresses = 'localhost,192.168.1.1' # local and one external interface
# max_connections
# ------------------------
# An integer setting a limit on the number of new connection processes which
# PostgreSQL will create. Should be set to the maximum number of connections
# which you expect to need at peak load. Note that each connection uses
# shared_buffer memory, as well as additional non-shared memory, so be careful
# not to run the system out of memory. In general, if you need more than 1000
# connections, you should probably be making more use of connection pooling.
#
# Note that by default 3 connections are reserved for autovacuum and
# administration.
max_connections = 200 # small server
# max_connections = 700 # web application database
# max_connections = 40 # data warehousing database
# shared_buffers
# ------------------------
# A memory quantity defining PostgreSQL's "dedicated" RAM, which is used
# for connection control, active operations, and more. However, since
# PostgreSQL also needs free RAM for file system buffers, sorts and
# maintenance operations, it is not advisable to set shared_buffers to a
# majority of RAM.
#
# Note that increasing shared_buffers often requires you to increase some
# system kernel parameters, most notably SHMMAX and SHMALL. See
# Operating System Environment: Managing Kernel Resources in the PostgreSQL
# documentation for more details. Also note that shared_buffers over 2GB is
# only supported on 64-bit systems.
#
# The setting below is a formula. Calculate the resulting value, then
# uncomment it. Values should be expressed in kB, MB or GB.
# shared_buffers = ( AvRAM / 4 )
# shared_buffers = 512MB # basic 2GB web server
# shared_buffers = 8GB # 64-bit server with 32GB RAM
# work_mem
# ------------------------
# This memory quantity sets the limit for the amount of non-shared RAM
# available for each query operation, including sorts and hashes. This limit
# acts as a primitive resource control, preventing the server from going
# into swap due to overallocation. Note that this is non-shared RAM per
# *operation*, which means large complex queries can use multple times
# this amount. Also, work_mem is allocated by powers of two, so round
# to the nearest binary step.
# The setting below is a formula. Calculate the resulting value, then
# uncomment it. Values should be expressed in kB, MB or GB. Maximum
# is currently 2GB.
# Most web applications should use the formula below, because their
# queries often require no work_mem.
# work_mem = ( AvRAM / max_connections ) ROUND DOWN to 2^x
# work_mem = 2MB # for 2GB server with 700 connections
# Formula for most BI/DW applications, or others running many complex
# queries:
# work_mem = ( AvRAM / ( 2 * max_connections ) ) ROUND DOWN to 2^x
# work_mem = 128MB # DW server with 32GB RAM and 40 connections
# maintenance_work_mem
# -------------------------
# This memory value sets the limit for the amount that autovacuum,
# manual vacuum, bulk index build and other maintenance routines are
# permitted to use. Setting it to a moderately high value will increase
# the efficiency of vacuum and other operations.
# The setting below is a formula. Calculate the resulting value, then
# uncomment it. Values should be expressed in kB, MB or GB.
# Maximum is currently 2GB.
# Formula for most databases
# maintenance_work_mem = ( AvRAM / 8 ) ROUND DOWN to 2^x
# maintenance_work_mem = 256MB #webserver with 2GB RAM
# maintenance_work_mem = 2GB #DW server with 32GB RAM
# max_fsm_pages
# --------------------------
# An integer which sets the maximum number of data pages with free space
# which the Postmaster will track. Setting this too low can lead to
# table bloat and need for VACUUM FULL. Should be set to the maximum number
# of data pages you expect to be updated between vacuums.
#
# Increasing this setting requires dedicated RAM and like shared_buffers
# may require to to increase system kernel parameters. Additionally, the
# recommended setting below is based on the default autovacuum settings;
# if you change the autovacuum parameters, then you may need to adjust
# this setting to match.
# The setting below is a formula. Calculate the resulting value, then
# uncomment it. DBsize is your estimate of the maximum size of the database;
# if the database is already loaded, you can get his from pg_database_size().
# For large data warehouses, use the volume of data which changes between
# batch loads as your "DBSize"
# For small databases ( less than 10GB )
# max_fsm_pages = ( ( DBsize / 8kB ) / 8 )
# max_fsm_pages = 100000 #6GB web database
# For larger databases ( Many GB to a few TB )
# max_fsm_pages = ( ( DBsize / 8kB ) / 16 )
# max_fsm_pages = 800000 #100GB OLTP database
# max_fsm_pages = 4000000 #DW loading 0.5TB data daily
# synchronous_commit
# -------------------------
# This boolean setting controls whether or not all of your transactions
# are gauranteed to be written to disk when they commit. If you are
# willing to lose up to 0.4 seconds of data in the event of an unexpected
# shutdown (as many web applications are), then you can gain substantial
# performance benefits by turning off synchronous commit. For most
# applications, however, this setting is better used on a per-session
# basis.
synchronous_commit = on #most applications
# synchronous_commit = off #if speed is more important than data
# wal_buffers
# -------------------------
# this memory setting defines how much buffer space is available for
# the Write Ahead Log. Set too low, it can become a bottleneck on
# inserts and updates; there is no benefit to setting it high, however.
# As with some of the other settings above, may require increasing
# some kernel parameters.
wal_buffers = 8MB
# checkpoint_segments
# -------------------------
# This integer defines the maximum number of 8MB transaction log segments
# PostgreSQL will create before forcing a checkpoint. For most
# high-volume OTLP databases and DW you will want to increase this
# setting significantly. Alternately, just wait for checkpoint
# warnings in the log before increasing this.
#
# Increasing this setting can make recovery in the event of unexpected
# shutdown take longer.
#
# Maximum disk space required is (checkpoint_segments * 2 + 1) * 16MB,
# so make sure you have that much available before setting it.
checkpoint_segments = 16 #normal small-medium database
# checkpoint_segments = 64 #high-volume OLTP database
# checkpoint_segments = 128 #heavy-ETL large database
# autovacuum
# ---------------------------
# autovacuum turns on a maintenance daemon which runs in the background,
# periodically cleaning up your tables and indexes. The only reason to turn
# autovacuum off is for large batch loads (ETL).
autovacuum = on #most databases
# autovacuum = off #large DW
# effective_cache_size
# --------------------------
# This memory setting tells the PostgreSQL query planner how much RAM
# is estimated to be available for caching data, in both shared_buffers and
# in the filesystem cache. This setting just helps the planner make good
# cost estimates; it does not actually allocate the memory.
# The setting below is a formula. Calculate the resulting value, then
# uncomment it.
# effective_cache_size = ( AvRAM * 0.75 )
# default_statistics_target
# --------------------------
# This integer setting determines the histogram sample size for the
# data about table contents kept by the query planner. The default
# is fine for most databases, but often users need to increase it
# either because they're running data warehouses or because they have
# a lot of poorly planned queries.
default_statistics_target = 10
# default_statistics_target = 200 #have had some bad plans
# default_statistics_target = 400 #data warehouse
# constraint_exclusion
# --------------------------
# This boolean setting should be turned "on" if you plan to use table
# partitioning. Otherwise, it should be "off".
constraint_exclusion = off #in general
# constraint_exclusion = on #if you plan to use partitioning
# log_destination & logging settings
# --------------------------
# This ENUM value determines where PostgreSQL's logs are sent. What
# setting to use really depends on your server room setup and the
# production status and OS of your server.
#
# Note that there are several dozen settings on what and how often
# to log; these will not be covered in detail in this quick
# configuration file. Instead, several common combinations are
# given.
# Syslog setup for centralized monitoring
# log_destination = 'syslog'
# syslog_facility = 'LOCAL0' #local syslog
# syslog_facility = 'log_server_name' #remote syslog
# Windows
# log_destination = 'eventlog'
# Private PostgreSQL Log
# log_destination = 'stderr'
# log_collector = on
# log_directory = '/path/to/log/dir'
# CSV logging for collecting performance statistics.
# Warning: this much logging will generate many log
# files and affect performance.
# log_destination = 'csvlog'
# log_collector = on
# log_directory = '/path/to/log/dir'
# log_duration = on
# log_temp_files = 256kB
# log_statement = 'all'
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
