Annotated postgresql.conf

From RADION OpenLab

Conf Setting	Range	Default	Set At	-o	Documentation Says	Comments
File Locations
data_directory	directory	ConfigDir	Startup		Specifies the directory to use for data storage.	These new file location settings allow the easy administration of a PostgreSQL installation where the various configuration and monitoring files are separated from the database itself, usually to fit a particular administration file specification, or to automate test runs with different configurations. If you use these, it becomes necessary only to specify the location of the postgresql.conf file itself when starting the postmaster (using -D or PGDATA). This new approach is considered superior to symlinking, the only prior option.
hba_file	filename	ConfigDir/pg_hba.conf	Startup		Specifies the configuration file for host-based authentication (customarily called pg_hba.conf)
ident_file	filename	ConfigDir/pg_ident.conf	Startup		Specifies the configuration file for ident authentication (customarily called pg_ident.conf)
external_pid_file	filename	none	Startup		Specifies the name of an additional process-id (PID) file that the postmaster should create for use by server administration programs.	This is for administration programs and database GUIs which expect to find PostgreSQL's PID in a specific location, usually in /var. Keep in mind that this is a copy of the PID, and not the one pg_ctl checks on server start, which will be in the data directory.
Connections and Authentication
Connection Settings
listen_addresses		localhost	Startup	-h x -i	Specifies the TCP/IP address(es) on which the server is to listen for connections from client applications. The value takes the form of a comma-separated list of host names and/or numeric IP addresses. The special entry * corresponds to all available IP interfaces. If the list is empty, the server does not listen on any IP interface at all, in which case only Unix-domain sockets can be used to connect to it. The default value is localhost, which allows only local "loopback" connections to be made.	This setting replaces both “tcp_ip” and “virtual_host” from 7.4. Most users will want to set it to '' to listen on all addresses, or leave it at 'localhost” for a secure machine. Unlike previous versions, the default now supports TCP/IP connections on 127.0.0.1 so that the local web server can connect out-of-the-box. Change this after* configuring your pg_hba.conf file for secure access.
port	129 to 32768	5432	Startup	-p #	The TCP port the server listens on; 5432 by default. Note that the same port number is used for all IP addresses the server listens on.	The main reason to use an alternate port is the need to run more than one PostgreSQL server on a machine, such as during an upgrade. An alternative to this is the –with-port compile-time option, which sets the alternate port in all libraries, sparing you needing to remember the -p option with all client utilities.
max_connections	2 to 262143	100	Startup	-N #	Determines the maximum number of concurrent connections to the database server. The default is typically 100, but may be less if your kernel settings will not support it (as determined during initdb).	Keep it as low as possible for your application configuration, since each active connection requires significant system resources. Web applications serving hundreds of users should use connection pooling to reduce the number of database connections required. Raising this may require adjustment of your OS memory limits.
superuser_reserved_connections	0 To max_connections - 1	2	Startup		Determines the number of "connection slots" that are reserved for connections by PostgreSQL superusers. At most max_connections connections can ever be active simultaneously. Whenever the number of active concurrent connections is at least max_connections minus superuser_reserved_connections, new connections will be accepted only from superuser accounts.	This protects superuser access in case of a maxed-out database. Do not set it to 0 unless you are very sure that connections to your database cannot be swamped. I often set it to 1, as I only connect to the database as the superuser in the event of a problem. The default setting of 2 is in case there are administrative utilities which are continuously connected, such as autovacuum.
unix_socket_directory			Startup	-k $	Specifies the directory of the Unix-domain socket on which the server is to listen for connections from client applications. The default is normally /tmp, but can be changed at build time.	No recommendations.
unix_socket_group			Startup		Sets the group owner of the Unix domain socket. (The owning user of the socket is always the user that starts the server.) In combination with the option UNIX_SOCKET_PERMISSIONS this can be used as an additional access control mechanism for this socket type. By default this is the empty string, which uses the default group for the current user.	No recommendations.
unix_socket_permissions		0777	Startup		Sets the access permissions of the Unix domain socket. Unix domain sockets use the usual Unix file system permission set. The option value is expected to be an numeric mode specification in the form accepted by the chmod and umask system calls. (To use the customary octal format the number must start with a 0 (zero).) The default permissions are 0777, meaning anyone can connect. Reasonable alternatives are 0770 (only user and group, see also unix_socket_group) and 0700 (only user). (Note that actually for a Unix domain socket, only write permission matters and there is no point in setting or revoking read or execute permissions.)	No recommendations.
rendezvous_name			Startup		Specifies the Rendezvous broadcast name. By default, the computer name is used, specified as an empty string . This option is only meaningful on platforms that support Rendezvous.	No recommendations.
Security and Authentication
authentication_timeout	1-600 sec	600	Reload		Maximum time to complete client authentication, in seconds. If a would-be client has not completed the authentication protocol in this much time, the server breaks the connection. This prevents hung clients from occupying a connection indefinitely.	If you're running a busy web host, you may want to lower the connection timeout. Certainly you want the timeout here to match the timeout used in your middleware, otherwise you may get unnecessary unavailability or long waits during busy periods.
ssl	True, False	False	Startup	-l	Enables SSL connections.	SSL is an encrypted alternative to straight TCP/IP port access, and is a requirement for clients dealing with secure data, especially over a wireless network. PostgreSQL sends queries and data in plain text, even when the password is encrypted. SSL can be tricky to configure and troubleshoot, and not all client software can support SSL access.
password_encryption	True, False	True	Runtime		When a password is specified in CREATE USER or ALTER USER without writing either ENCRYPTED or UNENCRYPTED, this option determines whether the password is to be encrypted.	Should be left as true, both in the conf file and per connection. There is pretty much never a reason to have unencrypted database user passwords.
krb_server_keyfile			Startup		Sets the location of the Kerberos server key file.	Only used for Kerberos authentication of users.
db_user_namespace	True, False	False	Reload		If this is on, you should create users as username@dbname. When username is passed by a connecting client, @ and the database name is appended to the user name and that database-specific user name is looked up by the server. Note that when you create users with names containing @ within the SQL environment, you will need to quote the user name.	This feature supports installations (like ISPs) who need per-database users. It is awkward at best, and will be removed when a better solution to the need is created. As such, do not use this option if you can live without it.
Resource Usage
Memory
Please note: Raising many of these options will require you to set kernel options for your host operating system to increas the amount of memory allowed per process or per user. See the online documentation for suggested commands for various OSes. Unless otherswise noted, all memory options are additive to determine the total memory used by PostgreSQL.
shared_buffers	16 to 262143	1000	Startup	-B x	Sets the number of shared memory buffers used by the database server. Minimum is 2 X max_connectionsSets the number of shared memory buffers used by the database server. The default is typically 1000, but may be less if your kernel settings will not support it (as determined during initdb). Each buffer is 8192 bytes, unless a different value of BLCKSZ was chosen when building the server. This setting must be at least 16, as well as at least twice the value of max_connections; however, settings significantly higher than the minimum are usually needed for good performance. Values of a few thousand are recommended for production installations.	Setting shared_buffers requires greater discussion than is suitable for this space. Please see other articles on the topic. For quick rules-of-thumb: on a dedicated PostgreSQL server, this value should likely be between 1000 and 50,000 (8MB and 400MB). Factors that raise the recommended amount are more connections, larger active portions of your database, long complex queries, and large tables. Available RAM limits the maximum shared_buffers; you should never use more than 1/3 of your available RAM.
work_mem	64 to Int Max	1024	Runtime	-S #	Specifies the amount of memory to be used by internal sort operations and hash tables before switching to temporary disk files. The value is specified in kilobytes, and defaults to 1024 kilobytes (1 MB). Note that for a complex query, several sort or hash operations might be running in parallel; each one will be allowed to use as much memory as this value specifies before it starts to put data into temporary files. Also, several running sessions could be doing such operations concurrently. So the total memory used could be many times the value of work_mem; it is necessary to keep this fact in mind when choosing the value. Sort operations are used for ORDER BY, DISTINCT, and merge joins. Hash tables are used in hash joins, hash-based aggregation, and hash-based processing of IN subqueries.	Formerly sort_mem, this setting name has been changed to reflect its expanded role in governing more than just sorts. Work_mem is a direct tradeoff. Adjust it upwards for: large databases, complex queries, lots of available RAM. Adjust it downwards for: low available RAM, or many concurrent users. Finding the right balance spot can be hard. Another way to set this value is to monitor the Postgres temp files (in PGDATA/base/DB_OID/pgsql_tmp) and adjust sort_mem upward if you see a lot of queries swapping from these temp files. Also keep in mind that this parameter can be adjusted per connection. So if you only have a few really large queries, you can increase the work_mem for them before query execution, and leave it low for the rest of the connections.
maintenance_work_mem	1024 to Int Max	8192	Runtime		Specifies the maximum amount of memory to be used in maintenance operations, such as VACUUM, CREATE INDEX, and ALTER TABLE ADD FOREIGN KEY. The value is specified in kilobytes, and defaults to 16384 kilobytes (16 MB). Since only one of these operations can be executed at a time by a database session, and an installation normally doesn't have very many of them happening concurrently, it's safe to set this value significantly larger than work_mem. Larger settings may improve performance for vacuuming and for restoring database dumps.	Formerly vacuum_mem. Renamed to reflect its expanded role in allocating memory for index loads. The default for this is generally too low, and will result in VACUUMs and index creation tying up the system I/O and/or object locks while it swaps memory. Good settings are generally 32MB to 256MB; it depends on both the RAM you have available and the size of your largest (expected) database objects. Like work_mem, can be allocated at runtime so you can increase it temporarily for loading indexes/creating keys on very large tables.

Free Space Map
max_fsm_pages	1000 to Int Max	20000	Startup		Sets the maximum number of disk pages for which free space will be tracked in the shared free-space map. Six bytes of shared memory are consumed for each page slot. This setting must be more than 16 * max_fsm_relations	A proper FSM setting can eliminate or at least postpone your need to run VACUUM FULL and REINDEX. The best way to set it is as follows: 1) figure out the VACUUM (regular) frequency of your database based on write activity; 2) run the database under normal production load, and run VACUUM VERBOSE ANALYZE instead of VACUUM, saving the output to a file; 3) calculate the maximum total number of pages reclaimed between VACUUMs based on the output, and use that. Alternately, if you are using Autovacuum, you can base this as a percentage of the total data pages in your database, to match the autovacuum percent. Regardless, little memory is required per page (about 6 bytes) so it's better to be generous than stingy. Please note that databases with high “peak” activity (bursts of 1 million updates but nothing else for minutes or hours) this number can be impossible to tune perfectly. Inserted rows are not significant for FSM. Finally, if your database server is short on RAM, increasing FSM to needed values may be counter-productive.
max_fsm_relations	10 to Int Max	1000	Startup		Sets the maximum number of relations (tables and indexes) for which free space will be tracked in the shared free-space map. Roughly fifty bytes of shared memory are consumed for each slot.	Few users will need to adjust this number, but it's worth checking. You should have at least as many FSM_relations as you have tables in all databases, including template databases and system schema. Postgres develops odd performance quirks if it is does not have enough FSM_relations.
max_stack_depth					Specifies the maximum safe depth of the server's execution stack. The ideal setting for this parameter is the actual stack size limit enforced by the kernel (as set by ulimit -s or local equivalent), less a safety margin of a megabyte or so. The safety margin is needed because the stack depth is not checked in every routine in the server, but only in key potentially-recursive routines such as expression evaluation. Setting the parameter higher than the actual kernel limit will mean that a runaway recursive function can crash an individual backend process. The default setting is 2048 KB (two megabytes), which is conservatively small and unlikely to risk crashes. However, it may be too small to allow execution of complex functions.	Used to be max_expr_depth, and the units there did not match the units used by most system kernels. In the event that you exceed this parameter, you'll get a specific error message. At that time, increase it cautiously; many operating systems have stack limits as low as 8MB.
Kernel Resource Usage
max_files_per_process	25 to Int Max	1000	Startup		Sets the maximum number of simultaneously open files allowed to each server subprocess. The default is 1000. If the kernel is enforcing a safe per-process limit, you don't need to worry about this setting. But on some platforms (notably, most BSD systems), the kernel will allow individual processes to open many more files than the system can really support when a large number of processes all try to open that many files. If you find yourself seeing "Too many open files" failures, try reducing this setting. This option can only be set at server start.	Per the docs, mainly used for BSD. Don't bother with it unless you get a “too many files” message.
preload_libraries	File path	Empty	Startup		This variable specifies one or more shared libraries that are to be preloaded at server start. A parameterless initialization function can optionally be called for each library. To specify that, add a colon and the name of the initialization function after the library name. For example '$libdir/mylib:mylib_init' would cause mylib to be preloaded and mylib_init to be executed. If more than one library is to be loaded, separate their names with commas. If a specified library or initialization function is not found, the server will fail to start. PostgreSQL procedural language libraries may be preloaded in this way, typically by using the syntax '$libdir/plXXX:plXXX_init' where XXX is pgsql, perl, tcl, or python. By preloading a shared library (and initializing it if applicable), the library startup time is avoided when the library is first used. However, the time to start each new server process may increase slightly, even if that process never uses the library. So this option is recommended only for libraries that will be used in most sessions.	This is only useful for specific specialized database purposes. For example, a mapping database might gain some small performance by preloading the GIS libraries. For most systems, this is better left alone.
Vacuum Delay
vacuum_cost_delay		0	Runtime		The length of time, in milliseconds, that the process will sleep when the cost limit has been exceeded. The default value is 0, which disables the cost-based vacuum delay feature. Positive values enable cost-based vacuuming. Note that on many systems, the effective resolution of sleep delays is 10 milliseconds; setting vacuum_cost_delay to a value that is not a multiple of 10 may have the same results as setting it to the next higher multiple of 10.	This setting is extremely valuable when vacuuming large tables which otherwise might tie up I/O for long periods or hold locks blocking numerous queries. Turning on vacuum delay, essentially, breaks up vacuuming any large table into segments defined as specific quantities of work, between which vacuum goes to sleep for the time defined in this setting. This has the overall effect of increasing the time required to vacuum, possibly by several multiples, but reducing the overall system impact of that vacuum, by as much as 85%. Reasonable delay settings are between 50ms and 200ms.
vacuum_cost_page_hit		1	Runtime		The estimated cost for vacuuming a buffer found in the shared buffer cache. It represents the cost to lock the buffer pool, lookup the shared hash table and scan the content of the page.	This setting should probably be left alone in favor of manipulating vacuum_cost_limit.
vacuum_cost_page_miss		10	Runtime		The estimated cost for vacuuming a buffer that has to be read from disk. This represents the effort to lock the buffer pool, lookup the shared hash table, read the desired block in from the disk and scan its content.	This setting should probably be left alone in favor of manipulating vacuum_cost_limit.
vacuum_cost_page_dirty		20	Runtime		The estimated cost charged when vacuum modifies a block that was previously clean. It represents the extra I/O required to flush the dirty block out to disk again.	This setting should probably be left alone in favor of manipulating vacuum_cost_limit.
vacuum_cost_limit		200	Runtime		The accumulated cost that will cause the vacuuming process to sleep.	Lower this in order to break up vacuuming into more “segments”. A really aggressive combination might be vacuum_cost_delay of 200ms and vacuum_cost_limit of 50; this could result in vacuum taking 10 times as long with almost no database performance impact. Most DBAs will want to be more moderate.
Background Writer
bgwriter_delay		200	Startup		Specifies the delay between activity rounds for the background writer. In each round the writer issues writes for some number of dirty buffers (controllable by the following parameters). The selected buffers will always be the least recently used ones among the currently dirty buffers. It then sleeps for bgwriter_delay milliseconds, and repeats.	The Background Writer is a new feature, designed to alleviate checkpoint spikes. We are still doing testing on bgwriter settings at OSDL; there are no recommendations at this time.
bgwriter_percent		1	Startup		In each round, no more than this percentage of the currently dirty buffers will be written (rounding up any fraction to the next whole number of buffers).	We are still doing testing on bgwriter settings at OSDL; there are no recommendations at this time.
bgwriter_maxpages		100	Startup		In each round, no more than this many dirty buffers will be written	We are still doing testing on bgwriter settings at OSDL; there are no recommendations at this time.
WAL Options
Settings
fsync	True, False	True	Startup	-F (off)	If this option is on, the PostgreSQL server will use the fsync() system call in several places to make sure that updates are physically written to disk. This insures that a database cluster will recover to a consistent state after an operating system or hardware crash. However, using fsync() results in a performance penalty: when a transaction is committed, PostgreSQL must wait for the operating system to flush the write-ahead log to disk. When fsync is disabled, the operating system is allowed to do its best in buffering, ordering, and delaying writes. This can result in significantly improved performance. However, if the system crashes, the results of the last few committed transactions may be lost in part or whole. In the worst case, unrecoverable data corruption may occur. (Crashes of the database server itself are not a risk factor here. Only an operating-system-level crash creates a risk of corruption.)	Turn WAL off (fsync=false) only for a read-only database or one where the database can be regenerated from external software. While RAID plus UPSes can do a lot to protect your data, turning off fsync means that you will be restoring from backup in the event of hardware or power failure. On the other hand, WAL imposes significant penalties on database writes, especially in single-disk systems. Essentially you are doubling the amount of read/write activity required for each update, plus requiring you to disable performance-enhancing disk-caching features of your OS and hardware. So, if your data is disposable, turing Fsync off is worth consideration. If WAL is off, the rest of the options in this section are irrelevant.
wal_sync_method	fsync, fdatasync, open_sync, open_datasync	Varies by platform	Startup		Method used for forcing WAL updates out to disk. Possible values are FSYNC (call fsync() at each commit), FDATASYNC (call fdatasync() at each commit), OPEN_SYNC (write WAL files with open() option O_SYNC), or OPEN_DATASYNC (write WAL files with open() option O_DSYNC). Not all of these choices are available on all platforms.	The system call used to sync the WAL to disk. Defaults have been set for each OS based on OS documentation, but no in-depth comparative tests have been posted. It's possible that changing the method could improve write speed on your platform, but don't monkey with it unless you have the time and resources to run comparative and failure tests.
wal_buffers	4 to Int Max	8	Startup		Number of disk-page buffers allocated in shared memory for WAL data. The default is 8. The setting need only be large enough to hold the amount of WAL data generated by one typical transaction.	Increasing this parameter has been shown to have minimal effect, even on very busy OLTP systems. If you know that you will have very large transactions, you may want to increase this just to be safe (to 16 – 64) but focus your tuning more on checkpoint_segments.
commit_delay	0-100000	0	Runtime		Time delay between writing a commit record to the WAL buffer and flushing the buffer out to disk, in microseconds. A nonzero delay can allow multiple transactions to be committed with only one fsync() system call, if system load is high enough that additional transactions become ready to commit within the given interval. But the delay is just wasted if no other transactions become ready to commit. Therefore, the delay is only performed if at least commit_siblings other transactions are active at the instant that a server process has written its commit record.	These two settings are configured together for an environment with a high volume of small transactions. When set, they allow a group of otherwise unrelated transactions to be flushed to disk at the same time, with possible significant performance gain. However, this is a tradeoff against waiting a few milliseconds extra on each transaction. If you want to test if this improves performance for you, a good starting point is commit_delay of 500 (½ millisecond).
commit_siblings	1 -1000	5	Runtime		Minimum number of concurrent open transactions to require before performing the COMMIT_DELAY delay. A larger value makes it more probable that at least one other transaction will become ready to commit during the delay interval.	If using commit_delay, you'll want to vary this setting depending on the average length of a transaction in your system. If transactions are very short (simple 1-row update/insert statements) then you'll want a low setting as simultaneous commit is probable; if some transactions are longer, you'll want to raise it to avoid unnecessary use of the commit_delay.
Checkpoints
checkpoint_segments	1 to Int Max	3	Startup		Maximum distance between automatic WAL checkpoints, in log file segments (each segment is normally 16 megabytes).	This is the most effective setting for dealing with large updates, data loading, and heavy OLTP activity. For any system with heavy write activity, you'll want to raise this to at least 8; on systems with very large write loads (such as loads of several GB of data), as much as 128 (and we've used 256 for DBT2 testing). However, this does require a significant amount of disk space for the xlog ( ( 2 x segments + 1 ) x 16mb, to be precise), and is a limited improvement if your xlog is not on a separate disk resource from the data.
checkpoint_timeout	30 to 3600	300	Startup		Maximum time between automatic WAL checkpoints, in seconds.	Increase this setting dramatically (up to 30 minutes) for large data loads. For other purposes, settings between 3min and 10min is the useful range; use higher settings for write activity which comes in bursts. Increasing checkpoint timeouts is currently limited by the increased impact that disk sync has with longer times.
checkpoint_warning	0 to Int Max	0	Startup		Send a message to the server logs if checkpoints caused by the filling of checkpoint segment files happens more frequently than this number of seconds. Zero turns off the warning.	Useful for detecting if checkpoint_segments needs to be increased. Turn it on during development periods and scan the log for warnings; several of them generally means an increase is warranted.
Archiving
archive_command	shell command		Startup		The shell command to execute to archive a completed segment of the WAL file series. If this is an empty string (the default), WAL archiving is disabled. Any %p in the string is replaced by the absolute path of the file to archive, and any %f is replaced by the file name only. Use %% to embed an actual % character in the command. For more information see Section 22.3.1. It is important for the command to return a zero exit status if and only if it succeeds	This setting turns on the new Point In Time Recovery feature by providing a shell command to archive (copy) completed WAL segments to another location. See discussion on backup and recovery for further information on how to use this.
Query Tuning
Planner Methods
enable_hashagg enable_hashjoin enable_indexscan enable_mergejoin enable_nestloop enable_seqscan enable_sort enable_tidscan	True, False	True	Runtime	-fi -fm -fn -fs -ft*	Enables or disables the query planner's use of the respective plan types. The default is on. This is used for debugging the query planner.	These options are pretty much only for use in query testing; frequently one sets “enable_seqscan = false” in order to determine if the planner is unnecessarily discarding an index, for example. However, it would require very unusual circumstances to change any of them to false in the .conf file. In fact, if you find yourself doing so, then there are probably other query tuning settings you've overlooked and should be modifying instead.
Planner Cost Constants
effective_cache_size	0 to Double	1000	Runtime		Sets the optimizer's assumption about the effective size of the disk cache (that is, the portion of the kernel's disk cache that will be used for PostgreSQL data files). This is measured in disk pages, which are normally 8 kB each.	Primarily set the planner's estimates of the relative likelihood of a particular table or index being in memory, and will thus have a significant effect on whether the planner chooses indexes over seqscans, as well as a few other query structures. As such, should be set to about 2/3 of your available RAM to ensure that the planner is adequately informed. Many times, DBAs wanting to turn enable_seqscan off need to change this setting instead.
random_page_cost	0 to Double	4	Runtime		Sets the planner's estimate of the cost of a nonsequentially fetched disk page. This is measured as a multiple of the cost of a sequential page fetch. A higher value makes it more likely a sequential scan will be used, a lower value makes it more likely an index scan will be used.	Useful values of this setting range between 2.0 and 4.0, the lower for a fast CPU, fast I/O, and a database that fits completely in RAM, and higher if your CPU or disk bandwidth are taxed, or if your main tables and their indexes are several times larger than available RAM. Do not ever set this lower than 1.5; if query problems seem to indicate doing so, then there are probably other settings (like effective_cache_size) that need adjustment. When testing the effect of different settings, remember to test a variety of queries from your workload, and not just one problem query.
cpu_tuple_cost cpu_index_tuple_cost cpu_operator_cost	0 to Double	0.01 0.001 0.0025	Runtime		Sets the query optimizer's estimate of the CPU cost of processing each tuple, index lookup, and where clause item (respectively) during a query. This is measured as a fraction of the cost of a sequential page fetch.	These default costs are fairly arbitrary, which is why they are available as adjustable settings. However, no-one in the community has been able to convincingly demonstrate better cost defaults, and more often changes have an adverse effect on some queries. So unless you have a great deal of time for query testing, it's better to leave these three settings alone.
Genetic Estimate Query Optimizer
geqo	True, false	True	Runtime		Enables or disables genetic query optimization, which is an algorithm that attempts to do query planning without exhaustive searching. This is on by default. See also the various other GEQO_ settings.	GEQO was introduced in PostgreSQL 6.5 as a way of dealing with join optimization queries with too many tables for an exhaustive analysis by the planner. It's important to realize that GEQO queries will, as a rule, be slower to execute than regular queries. It's designed to kick in when otherwise query planning would swamp your CPU. If you find that your application is making use of GEQO a lot, it's probably advisable to start writing queries with an explicit join order, as you can exercise more discrimination than the algorithm.
geqo_threshold	2 to Int Max	11	Runtime		Use genetic query optimization to plan queries with at least this many FROM items involved. (Note that a JOIN construct counts as only one FROM item.) The default is 11. For simpler queries it is usually best to use the deterministic, exhaustive planner. This parameter also controls how hard the optimizer will try to merge subquery FROM clauses into the upper query.	It's possible that, on machines with very fast CPUs (dual Opteron, for example) raising this threshold slightly (such as to 14) is warranted. However, previous advice to raise it to 20 turned out to be based on an unusual test case and has since been disproven.
geqo_selection_bias geqo_pool_size geqo_effort geqo_generations geqo_random_seed	1.5-2.0	2.0 0 1 0 -1	Runtime		Various tuning parameters for the genetic query optimization algorithm: The pool size is the number of individuals in one population. Valid values are between 128 and 1024. If it is set to 0 (the default) a pool size of 2^(QS+1), where QS is the number of FROM items in the query, is taken. The effort is used to calculate a default for generations. Valid values are between 1 and 80, 40 being the default. Generations specifies the number of iterations in the algorithm. The number must be a positive integer. If 0 is specified then Effort * Log2(PoolSize) is used. The run time of the algorithm is roughly proportional to the sum of pool size and generations. The selection bias is the selective pressure within the population. Values can be from 1.50 to 2.00; the latter is the default. The random seed can be set to get reproducible results from the algorithm. If it is set to -1 then the algorithm behaves non-deterministically.
Other Query Modifiers
default_statistics_target	1 -1000	10	Runtime		Sets the default statistics target for table columns that have not had a column-specific target set via ALTER TABLE SET STATISTICS. Larger values increase the time needed to do ANALYZE, but may improve the quality of the planner's estimates.	Has no effect until your next ANALYZE. Generally not recommended as a way of improving statistics overall except for unusual databases; for one thing, collecting increased statistics on wide columns (large text, for example) can be burdensome enough to be counter-productive. For a database which is almost entirely numerical, modest increases (to 100, for example) may have overall benefit; otherwise, try increasing statistics on specific columns.
from_collapse_limit	0 to Int Max	8	Runtime		The planner will merge sub-queries into upper queries if the resulting FROM list would have no more than this many items. Smaller values reduce planning time but may yield inferior query plans. The default is 8. It is usually wise to keep this less than GEQO_THRESHOLD.	As with many other settings in this section, you want only to change this for specific unfixable queries at runtime. Decreasing it should force materialization of some subqueries if that is desired. Most DBAs won't want to change it at all.
join_collapse_limit	1 to Int Max	8	Runtime		The planner will flatten explicit inner JOIN constructs into lists of FROM items whenever a list of no more than this many items would result. Usually this is set the same as FROM_COLLAPSE_LIMIT. Setting it to 1 prevents any flattening of inner JOINs, allowing explicit JOIN syntax to be used to control the join order. Intermediate values might be useful to trade off planning time against quality of plan.	This option is designed for those of us who like writing our queries using explicit JOIN syntax (e.g. “a join b using (1) join c using (2)”), but would still like the planner to select the join order for best execution. Particularly, people switching from MS SQL Server will want to enable this option with a moderately high value, as that database does JOIN collapsing automatically. As above, keep this setting below geqo_threshold.
Logging and Debugging Options
Where To Log
log_destination	stderr, syslog, eventlog	stderr	Startup		PostgreSQL supports several methods for logging server messages, including stderr and syslog. On Windows, eventlog is also supported. Set this option to a list of desired log destinations separated by commas.	This is analogous to the old “syslog” setting, but with the cryptic codes removed. Also supports the Win32 “eventlog”. When setting up your server, it's important to decide how you want to log PostgreSQL messages: either to syslog, which is easier for overall system administration, or to a private PostgreSQL log, which is better for debugging database problems. Of course, you can log to both, but that's probably an excess of output.
redirect_stderr					This option allows messages sent to stderr to be captured and redirected into log files. This option, in combination with logging to stderr, is often more useful than logging to syslog, since some types of messages may not appear in syslog output (a common example is dynamic-linker failure messages).	This is the new “log rotation” feature. It also replaces the -l command line switch for pg_ctl, and/or command-line redirect. It is only applicable if you chose “stderr” above, and the following 5 options only take effect if you choose this one. You can use redirect_stderr and turn rotation off in order to have create the same effect as the old -l option.
log_directory	directory	pg_log	Startup		When redirect_stderr is enabled, this option determines the directory in which log files will be created. It may be specified as an absolute path, or relative to the cluster data directory	Defaults to a “pg_log” directory in your PGDATA, which is probably not a wise choice if you have other disks/arrays available. /var/pg_log is popular.
log_filename	special	postgresql-%Y-%m-%d_%H%M%S.log	Startup		When redirect_stderr is enabled, this option sets the file names of the created log files. The value is treated as a strftime pattern, so %-escapes can be used to specify time-varying file names. If no %-escapes are present, PostgreSQL will append the epoch of the new log file's open time. For example, if log_filename were server_log, then the chosen file name would be server_log.1093827753 for a log starting at Sun Aug 29 19:02:33 2004 MST.	File name for each rotational log segment, with escapes. The default should suit most DBAs. If your logs never go over size, it can be simpler to include only the date. Another possible variation is to have the log record only the hour, or only the day of the week, in order to prevent getting more than a certain number of logs. See log_truncate below.
log_rotation_age	0 to Int Max	1440	Startup		When redirect_stderr is enabled, this option determines the maximum lifetime of an individual log file. After this many minutes have elapsed, a new log file will be created. Set to zero to disable time-based creation of new log files.	The default (24 hours) is suitable for most installations.
log_rotation_size	0 to Int Max	10240	Startup		When redirect_stderr is enabled, this option determines the maximum size of an individual log file. After this many kilobytes have been emitted into a log file, a new log file will be created. Set to zero to disable size-based creation of new log files.	The default (10MB) is suitable for most installations.
log_truncate_on_rotation	True, False	False	Startup		When redirect_stderr is enabled, this option will cause PostgreSQL to truncate (overwrite), rather than append to, any existing log file of the same name. However, truncation will occur only when a new file is being opened due to time-based rotation, not during server startup or size-based rotation. When false, pre-existing files will be appended to in all cases. For example, using this option in combination with a log_filename like postgresql-%H.log would result in generating twenty-four hourly log files and then cyclically overwriting them.	This setting can be combined with log_filename, above, to create a 7-day or 24-hour (or 60-minute, for that matter) continuous replacement of logs.
syslog_facility	LOCAL#	LOCAL0	Startup		When logging to syslog is enabled, this option determines the syslog "facility" to be used. You may choose from LOCAL0, LOCAL1, LOCAL2, LOCAL3, LOCAL4, LOCAL5, LOCAL6, LOCAL7; the default is LOCAL0. See also the documentation of your system's syslog daemon.	No recommendations.
syslog_ident		postgres	Startup		When logging to syslog is enabled, this option determines the program name used to identify PostgreSQL messages in syslog logs. The default is postgres.	Those running multiple versions of PostgreSQL on the same machine will want to remember to change this string to indicate which server.
When to Log
client_min_messages log_min_messages log_min_error_statement	debug5, debug4, debug3, debug2, debug1, info, notice, warning, error, log, fatal, panic	notice notice panic	Runtime Superuser Superuser	-d x	This controls how much message detail is written to the server logs and the client. Valid values are DEBUG5, DEBUG4, DEBUG3, DEBUG2, DEBUG1, INFO, NOTICE, WARNING, ERROR, LOG, FATAL, and PANIC. Later values send less detail to the logs. The default is NOTICE. Note that LOG has a different precedence here than in CLIENT_MIN_MESSAGES. client_min_messages outputs to the client session; log_min_messages to the log, and log_min_error_statement controls recording of SQL errors to the log.	Raising debug levels is always good for testing applications; DEBUG1 is a good setting for general troubleshooting. NOTICE is suitable for general production, and thourougly tested systems can probably be reduced to ERROR or even FATAL. The cost is greater use of disk space, some minor performance cost for output (usually < 5%). However, the performance cost increases significantly if your logs are on the same disk/array as WAL or your database, as heavy debug output will take I/O away from database activity. The impact of debug5 on a high-transaction single-disk system can be quite high. This caution applys to all of the loggin options below.
log_error_verbosity	terse, default, verbose	default	Superuser		Controls the amount of detail written in the server log for each message that is logged. Valid values are TERSE, DEFAULT, and VERBOSE, each adding more fields to displayed messages.	What setting you use here depends on your production status, and what log-monitoring tools you are using.
log_min_duration_statement	-1 to Int Max	-1	Superuser		Sets a minimum statement execution time (in milliseconds) that causes a statement to be logged. All SQL statements that run for the time specified or longer will be logged with their duration. Setting this to zero will print all queries and their durations. Minus-one (the default) disables the feature. For example, if you set it to 250 then all SQL statements that run 250ms or longer will be logged. Enabling this option can be useful in tracking down unoptimized queries in your applications	This setting is extremely useful for second-stage database tuning. Once you've taken care the bulk of the indexing and performance issues, log_min_duration_statement will allow you to log only the slowest (and possibly still broken) queries.
silent_mode	True, False	False	Startup		Runs the server silently. If this option is set, the server will automatically run in background and any controlling terminals are disassociated (same effect as postmaster's -S option). The server's standard output and standard error are redirected to /dev/null, so any messages sent to them will be lost. Unless syslog logging is selected or redirect_stderr is enabled, using this option is discouraged because it makes it impossible to see error messages.	The documentation pretty much covers it.
What to Log
debug_print_parse debug_print_rewritten debug_print_plan debug_pretty_print	True, false	false			These flags enable various debugging output to be sent to the server log. For each executed query, print either the query text, the resulting parse tree, the query rewriter output, or the execution plan. DEBUG_PRETTY_PRINT indents these displays to produce a more readable but much longer output format.	Can be useful for detecting common slow queries if you are able to wade through the voluminous log output. Particularly useful in interactive log watching when procedures hang; you can sometimes see exaclty what step hangs (sometimes you can't, though, because the log waits on the database).
log_connections log_disconnections	True, false	False	Startup		log_connections outputs a line to the server log detailing each successful connection. log_disconnections outputs a line in the server log similar to log_connections but at session termination, and includes the duration of the session.	Essential logging items for any secure application.
log_hostname	True, False	False	Startup		By default, connection logs only show the IP address of the connecting