The options listed in this file can be set in
/etc/postgresql/postgresql.conf or on the command line of the postmaster
or by the SQL command SET.

If an unknown option is found in this file, the postmaster will abort.


Planner and Optimizer Tuning
============================


CPU_INDEX_TUPLE_COST (floating point)

Sets the query optimizer's estimate of the cost of processing each index
tuple during an index scan. This is measured as a fraction of the cost
of a sequential page fetch.


CPU_OPERATOR_COST (floating point)

Sets the optimizer's estimate of the cost of processing each operator
in a WHERE clause. This is measured as a fraction of the cost of a
sequential page fetch.


CPU_TUPLE_COST (floating point)

Sets the query optimizer's estimate of the cost of processing each
tuple during a query. This is measured as a fraction of the cost of a
sequential page fetch.


EFFECTIVE_CACHE_SIZE (floating point)

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 8kB apiece.


ENABLE_HASHJOIN (boolean)

Enables or disables the query planner's use of hash-join plan types. The
default is on. This is mostly useful to debug the query planner.


ENABLE_INDEXSCAN (boolean)

Enables or disables the query planner's use of index scan plan types. The
default is on. This is mostly useful to debug the query planner.


ENABLE_MERGEJOIN (boolean)

Enables or disables the query planner's use of merge-join plan types. The
default is on. This is mostly useful to debug the query planner.


ENABLE_NESTLOOP (boolean)

Enables or disables the query planner's use of nested-loop join
plans. It's not possible to suppress nested-loop joins entirely, but
turning this variable off discourages the planner from using one if there
is any other method available. The default is on. This is mostly useful
to debug the query planner.


ENABLE_SEQSCAN (boolean)

Enables or disables the query planner's use of sequential scan plan
types. It's not possible to suppress sequential scans entirely, but
turning this variable off discourages the planner from using one if there
is any other method available. The default is on. This is mostly useful
to debug the query planner.


ENABLE_SORT (boolean)

Enables or disables the query planner's use of explicit sort steps. It's
not possible to suppress explicit sorts entirely, but turning this
variable off discourages the planner from using one if there is any other
method available. The default is on. This is mostly useful to debug the
query planner.


ENABLE_TIDSCAN (boolean)

Enables or disables the query planner's use of TID scan plan types. The
default is on. This is mostly useful to debug the query planner.


GEQO (boolean)

Enables or disables genetic query optimization, which is an algorithm
that attempts to do query planning without exhaustive search. This is
on by default. See also the various other GEQO_ settings.


GEQO_EFFORT (integer)
GEQO_GENERATIONS (integer)
GEQO_POOL_SIZE (integer)
GEQO_RANDOM_SEED (integer)
GEQO_SELECTION_BIAS (floating point)

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 relations 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.


GEQO_THRESHOLD (integer)

Only use genetic query optimization for queries with at least this many
relations involved. The default is 11. For fewer relations it is probably
more efficient to use the deterministic, exhaustive planner.


KSQO (boolean)

The Key Set Query Optimizer  (KSQO) causes the query planner to convert
queries whose WHERE clause contains many OR'ed AND clauses (such as
"WHERE (a=1 AND b=2) OR (a=2 AND b=3) ...") into a UNION query. This
method can be faster than the default implementation, but it doesn't
necessarily give exactly the same results, since UNION implicitly adds
a SELECT DISTINCT clause to eliminate identical output rows. KSQO is
commonly used when working with products like Microsoft Access, which
tend to generate queries of this form.

The KSQO algorithm used to be absolutely essential for queries with many
OR'ed AND clauses, but in PostgreSQL 7.0 and later the standard planner
handles these queries fairly successfully. Hence the default is OFF.


RANDOM_PAGE_COST (floating point)

Sets the query optimizer's estimate of the cost of a non-sequentially
fetched disk page. This is measured as a multiple of the cost of a
sequential page fetch.

Note: Unfortunately, there is no well-defined method of determining ideal
values for the family of "COST" variables that were just described. You
are encouraged to experiment and share your findings.



Logging and Debugging
=====================


DEBUG_ASSERTIONS (boolean)

Turns on various assertion checks. This is a debugging aid. If you are
experiencing strange problems or crashes you might want to turn this on,
as it might expose programming mistakes. To use this option, the macro
USE_ASSERT_CHECKING must be defined when PostgreSQL is built (see the
configure option --enable-cassert). Note that DEBUG_ASSERTIONS defaults
to ON if PostgreSQL has been built this way.

In Debian, PostgreSQL is not built with --enable-cassert, so this option
cannot be used.


DEBUG_LEVEL (integer)

The higher this value is set, the more "debugging" output of various
sorts is generated in the server log during operation. This option is
0 by default, which means no debugging output. Values up to about 4
currently make sense.

   1: trace connections
	2: 1 + backend trace
   3: 1 + 2 + dump environment to standard error when postmaster starts

DEBUG_PRINT_PARSE (boolean)
DEBUG_PRINT_PLAN (boolean)
DEBUG_PRINT_REWRITTEN (boolean)
DEBUG_PRINT_QUERY (boolean)
DEBUG_PRETTY_PRINT (boolean)

For any executed query, prints either the query, the parse tree, the
execution plan, or the query rewriter output to the server log.

DEBUG_PRETTY_PRINT selects are nicer but longer output format.


HOSTNAME_LOOKUP (boolean)

By default, connection logs only show the IP address of the connecting
host. If you want it to show the host name you can turn this on,
but depending on your host name resolution setup it might impose a
non-negligible performance penalty. This option can only be set at
server start.


LOG_CONNECTIONS (boolean)

Prints a line informing about each successful connection to the server
log. This is off by default, although it is probably very useful. This
option can only be set at server start.


LOG_PID (boolean)

Prefixes each server log message with the process id of the backend
process. This is useful to sort out which messages pertain to which
connection. The default is off.


LOG_TIMESTAMP (boolean)

Prefixes each server log message with a timestamp. The default is off.


SHOW_QUERY_STATS (boolean)
SHOW_PARSER_STATS (boolean)
SHOW_PLANNER_STATS (boolean)
SHOW_EXECUTOR_STATS (boolean)

For each query, write performance statistics of the respective module
to the server log. This is a crude profiling instrument.

SHOW_BTREE_BUILD_STATS

Only available if BTREE_BUILD_STATS was defined at build time


SHOW_SOURCE_PORT (boolean)

Shows the outgoing port number of the connecting host in the connection
log messages. You could trace back the port number to find out what
user initiated the connection. Other than that it's pretty useless and
therefore off by default. This option can only be set at server start.


SYSLOG (integer)

PostgreSQL allows the use of syslog for logging. If this option is set
to 1, messages go both to syslog and the standard output. A setting of 2
sends output only to syslog. (Some messages will still go to the standard
output/error.) The default is 0, which means syslog is off. This option
must be set at server start. To use syslog, the build of PostgreSQL must
be configured with the --enable-syslog option.  This is the case in the
Debian distribution.


SYSLOG_FACILITY (string)

This option determines the syslog "facility" to be used when syslog
is enabled. 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.

If you use syslog, you need to take steps to handle the output in the
syslog configuration.  Since implementations of syslogd differ, you need
to consult the documentation for the version you have installed.


SYSLOG_IDENT (string)

If logging to syslog is enabled, this option determines the program name
used to identify PostgreSQL messages in syslog log messages. The default is "postgres".


TRACE_NOTIFY (boolean)

Generates a great amount of debugging output for the LISTEN and NOTIFY
commands.



Lock tracing
============

These operations are only available if PostgreSQL was build with LOCK_DEBUG
turned on.

TRACE_LOCK (boolean)
TRACE_USERLOCKS (boolean)
TRACE_SPINLOCKS (boolean)
DEBUG_DEADLOCKS (boolean)
TRACE_LOCK_OIDMIN (integer)
TRACE_LOCK_TABLE (integer)



General operation
=================


DEADLOCK_TIMEOUT (integer)

This is the amount of time, in milliseconds, to wait on a lock before
checking to see if there is a deadlock condition or not. The check
for deadlock is relatively slow, so we don't want to run it every
time we wait for a lock. We (optimistically?) assume that deadlocks
are not common in production applications, and just wait on the lock
for awhile before starting to ask questions about whether it can ever
get unlocked. Increasing this value reduces the amount of time wasted
in needless deadlock checks, but slows down reporting of real deadlock
errors. The default is 1000 (i.e., one second), which is probably about
the smallest value you would want in practice. On a heavily loaded
server you might want to raise it. Ideally the setting should exceed
your typical transaction time, so as to improve the odds that the lock
will be released before the waiter decides to check for deadlock. This
option can only be set at server start.


FSYNC (boolean)

If this is option is on, the Postgres backend will use the fsync() system
call in several places to make sure that updates are physically written
to disk and will not hang around in the write caches. This increases
the chance that a database installation will still be usable after a
operating system or hardware crashes by a large amount. (Crashes of the
database server itself do not affect this consideration.)

However, this operation slows down Postgres, because at all
those points it has to block and wait for the operating system to flush
the buffers. Without fsync, the operating system is allowed to do its
best in buffering, sorting,and delaying writes, so this can give a
performance increase. On the other hand, if the system crashes, parts of the
data of a transaction that has already been committed -- according to the
information on disk -- will not yet be on the disk. Inconsistent data
(i.e., data corruption) is therefore likely to occur.

This option is the subject of an eternal debate in the Postgres user and
developer communities. Some always leave it off, some turn it off only
for bulk loads, where there is a clear restart point if something goes
wrong, some leave it on just to be on the safe side. Because it is the
safe side, on is also the default. If you trust your operating system,
your utility company, and your hardware, you might want to disable it.
In 7.1 the benefits of disabling it are a lot fewer than in previous
versions.


KRB_SERVER_KEYFILE (string)

Sets the location of the Kerberos server key file. See the section called
Kerberos authentication in Chapter 5 of the Admin manual for details.


MAX_CONNECTIONS (integer)

Determines how many concurrent connections the database server will
allow. The Debian default is 64. There is also a compiled-in hard upper
limit on this value, which is typically 1024 (both numbers can be altered
when compiling the server). This parameter can only be set at server start.


MAX_EXPR_DEPTH (integer)

Sets the maximum expression nesting depth that the parser will accept. The
default value is high enough for any normal query, but you can raise
it if you need to. (But if you raise it too high, you run the risk of
backend crashes due to stack overflow.)


PORT (integer)

The TCP port the server listens on; 5432 by default. This option can
only be set at server start.

Debian uses port 5431 for dumping data when packages are upgraded.  Do not
use 5431 here.


SHARED_BUFFERS (integer)

Sets the number of shared memory buffers the database server will use. The
Debian default is 128. Each buffer is typically 8192 bytes. This option can
only be set at server start.  This parameter should be at least twice
MAX_CONNECTIONS and at least 16.


SILENT_MODE (bool)

Runs postmaster silently. If this option is set, postmaster
will automatically run in background and any controlling ttys are
disassociated, thus no messages are written to stdout or stderr (same
effect as postmaster's -S option). Unless some logging system such as
syslog is enabled, using this option is discouraged since it makes it
impossible to see error messages.


SORT_MEM (integer)

Specifies the amount of memory to be used by internal sorts and hashes
before resorting to temporary disk files. The value is specified in
kilobytes, and defaults to 512 kilobytes. Note that for a complex query,
several sorts and/or hashes might be running in parallel, and each one
will be allowed to use as much memory as this value specifies before it
starts to put data into temporary files.


SQL_INHERITANCE (bool)

This controls the inheritance semantics, in particular whether
subtables are included into the consideration of various commands by
default. This was not the case in versions prior to 7.1. If you need the
old behaviour you can set this variable to off, but in the long run you
are encouraged to change your applications to use the ONLY keyword to
exclude subtables. See the SQL language reference and the User's Guide
for more information about inheritance.


SSL (boolean)

Enables SSL connections. Please read the Admin manual section called Secure
TCP/IP Connections with SSL before using this. The default is off.

This is equivalent to postmaster's short option -l.


TCPIP_SOCKET (boolean)

If this is true, then the server will accept TCP/IP connections. Otherwise
only local Unix domain socket connections are accepted. It is off by
default. This option can only be set at server start.

This is equivalent to postmaster's short option -i.


UNIX_SOCKET_DIRECTORY (string)

Specifies the directory of the Unix-domain socket on which the postmaster
is to listen for connections from client applications. The default
is normally /tmp, but can be changed at build time.

On Debian systems, the build-time default is /var/run/postgresql.


UNIX_SOCKET_GROUP (string)

Sets the group owner of the Unix domain socket. (The owning user of the
socket is always the user that starts the postmaster.) 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. This option can
only be set at server start.


UNIX_SOCKET_PERMISSIONS (integer)

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 would be 0770 (only user and group, see also under
UNIX_SOCKET_GROUP) and 0700 (only user). (Note that actually for a Unix
socket, only write permission matters and there is no point in setting
or revoking read or execute permissions.)

This access control mechanism is independent from the one described in
Chapter 5 of the Admin manual. This option can only be set at server start.


VIRTUAL_HOST (string)

Specifies the TCP/IP hostname or address on which the postmaster is to
listen for connections from client applications.  Defaults to listening
on all configured addresses (including localhost).



WAL options
===========


CHECKPOINT_TIMEOUT (integer)

Time in between automatic checkpoints.


WAL_BUFFERS (integer)

The number of WAL buffers in shared memory.  The default is 8.


WAL_FILES (integer)

May specify that from 1 to 64 be created when a new segment is required.


WAL_DEBUG (integer)

Set to non-zero to get debugging output from WAL.


COMMIT_DELAY (integer)

Not yet useful.