Configuring Your PostgreSQL Runtime Environment

After you have finished installing the PostgreSQL distribution, you may want to review the runtime configuration options.

Permanent configuration options should be defined in the file $PGDATA/ postgresql.conf. The postgresql.conf file is a plain text file that you can maintain with your favorite editor (vi, emacs, and so on). When you create a new database cluster, the initdb program will create a default postgresql.conf file for you. postgresql.conf is arranged as a series of option= value pairs; blank lines are ignored and any text that follows an octothorpe (#) is treated as a comment. Here is a snippet from a postgresql.conf file created by initdb:

# # Connection Parameters # #tcpip_socket = false #ssl = false #max_connections = 32 #port = 5432 #hostname_lookup = false #show_source_port = false #unix_socket_directory = " #unix_socket_group = " #unix_socket_permissions = 0777

PostgreSQL supports a large number of runtime configuration options (more than 150: at last count). In the next few sections, you'll see a description of each parameter and the parameter's default value. Default values can come from four sources: a hard-wired default value that you can't adjust without changing the source code, a symbolic value that can be changed only by editing the include/pg_config.h header file, a compile-time configuration option, or a command-line option to the postmaster.

Some of the options can be modified at runtime using the SET command; others can be defined only before starting the postmaster. The sections that follow document the modification time for each parameter.

Parameters with a Modify Time of "Postmaster startup" can be changed only by modifying the postgresql.conf file and restarting the postmaster.

Parameters labeled SIGHUP can be modified after the postmaster process has started. To modify a SIGHUP option, edit the postgresql.conf configuration file and send a SIGHUP signal to the postmaster process. You can use the pg_ctl reload command to signal the postmaster.

The parameters that you can change with the SET command are labeled with a modification time of "SET command".

File Locations

The PostgreSQL server expects to find data files, configuration files, and authorization/ authentication files in the $PGDATA directory tree. Starting with PostgreSQL version 8.0, you can move bits and pieces to different locations in your filesystem. For example, you may have a policy that states that all configuration files must be stored in the /etc directory and all data files must be stored in the /var directory tree. If you're using an older version of PostgreSQL, you can use symbolic links to relocate PostgreSQL components. If you're using a PostgreSQL version 8.0 or later, you can customize the placement of each component by modifying a small collection of runtime parameters: CONFIG_FILE, HBA_FILE, IDENT_FILE, and EXTERNAL_PID_FILE. You must tell the postmaster how to find the postgresql.conf file (it can't look in postgresql.conf to find the location of postgresql.conf).

DATA_DIRECTORY tells the postmaster how to find your database cluster. If you don't modify the DATA_DIRECTORY parameters, PostgreSQL expects to find the cluster in the directory that you specify (following the -D flag) when you start the postmaster, or in $PGDATA. The directory named by DATA_DIRECTORY should contain the base, global, pg_clog, pg_subtrans, pg_tblspc, and pg_xlog subdirectories that make up a database cluster.

The CONFIG_FILE parameter tells the postmaster how to find the postgresql.conf configuration file. If you've moved the postgresql.conf file to a location other than $PGDATA/postgresql.conf, you must tell the postmaster the name of the file on the postmaster (or pg_ctl) command line. This is one of the few parameters that you cannot include in the postgresql.conf configuration file (CONFIG_FILE tells the postmaster how to find postgresql.conf).

This parameter tells the postmaster how to find the pg_hba.conf file. pg_hba.conf contains the host-based authentication rules that control access to the cluster. See Chapter 23 for more information regarding host-based authentication.

IDENT_FILE tells the postmaster how to find the ident map file (used by the ident authentication method). See Chapter 23 for more information about the ident authentication method.

When the postmaster begins servicing a cluster, it records its own process ID in the $PGDATA/postmaster.pid file. The pg_ctl program knows how to find the postmaster by reading the PID file. Other applications (such as the Linux/Unix SysVInit facility) may also use PID files to locate and communicate with a running service (such as the postmaster). To make it easier to interface a third-party service manager with the postmaster, you can ask PostgreSQL to create a second PID file in a separate location by setting EXTERNAL_PID_FILE to the name of the file that you want PostgreSQL to create.

Security-Related Parameters

If true, the SSL parameter tells the postmaster to negotiate with clients over the use of SSL-secured connections. SSL is a protocol that encrypts the data stream flowing between the client and the server. If SSL is true, and the client supports SSL, the data stream will be encrypted; otherwise, PostgreSQL data will be sent in clear-text form. You can override this parameter by invoking the postmaster with the -l flag.

When you execute a CREATE USER (or ALTER USER ... PASSWORD) command without an explicit encryption clause, PostgreSQL examines the PASSWORD_ENCRYPTION parameter to determine whether it should store the password in encrypted or plain-text form (in other words, PASSWORD_ENCRYPTION specifies the default password encryption policy). If PASSWORD_ENCRYPTION is true, PostgreSQL will store passwords in encrypted form (unless the CREATE USER or ALTER USER command explicitly requests an UNENCRYPTED password). If PASSWORD_ENCRYPTION is False, PostgreSQL will store passwords in plain-text form (unless the CREATE USER or ALTER USER command explicitly requests an ENCRYPTED password).

When you create a user account (with the createuser program or the CREATE USER command), PostgreSQL stores the account information in a cluster-wide table (pg_shadow). That means that a new user can access any database defined within the cluster (subject to the privileges assigned to the database). If you enable DB_USER_ NAMESPACE, you can create user accounts specific to each database in addition to creating cluster-wide accounts. To create a database-specific account, specify an account name of the form " username@ database-name" ("bruce@movies" for example). To create a cluster-wide account, use the syntax " username@" (in other words, omit the database name). In either case, you must enclose the name in double quotes.

You can only include an @ in an account name if DB_USER_NAMESPACE is trUE.

It's a little tricky to actually use per-database user accounts because you can end up with a mix of cluster-wide and per-database accounts. For example, assume that you've created two accounts: sheila@ and bruce@movies. With DB_USER_NAMESPACE enabled, sheila can only connect to the movies database using the following syntax:

$ psql -U "sheila@" movies

But user bruce must specify

$ psql -U bruce movies

With DB_USER_NAMESPACE enabled, PostgreSQL follows a modified procedure to locate the correct user account. If the name that you provide contains an @, PostgreSQL strips off everything that follows the @ and searches for the username in the cluster-wide account table (pg_shadow). If the name that you provide does not include an @, PostgreSQL adds an @ and appends the database name, then searches for the resulting account name in pg_shadow. Note that, in either case, PostgreSQL searches for the account name in pg_shadow (a cluster-wide table).

To summarize, with DB_USER_NAMESPACE enabled

• Add @ database-name when you create a per-database account
• Add @ to create a cluster-wide account
• Include the trailing @ when you log in to a cluster-wide account
• Omit the @ (and trailing database name) when you log in to a per-database account

The PostgreSQL reference documentation warns that DB_USER_NAMESPACE is an interim solution that may be withdrawn in a future release.

This parameter defines the maximum amount of time (in seconds) that the postmaster will wait for a client to complete the authentication process. If the timeout period expires, the postmaster will sever the connection with the client.

If you are using Kerberos to authenticate clients, the server keyfile is normally located in /etc/srvtab (for Kerberos 4) or $SYSCONFDIR/krb5.keytab (for Kerberos 5). You can specify an alternate (possibly more secure) location using the KRB_SERVER_KEYFILE parameter.

Connection-Related Parameters

This section looks at the connection-related configuration parameters. Notice that most of the connection-related parameters must be defined at the time that the postmaster starts.

The LISTEN_ADDRESSES parameter tells the postmaster which network interface(s) it should service when listening for client connection requests. By default, the postmaster listens for connection requests arriving on the loopback interface (address 127.0.0.1, typically known as localhost.localdomain). If you want to provide service to other computers, set LISTEN_ADDRESSES to the IP address (or hostname) of your local network interface. If you are running PostgreSQL on a multi-homed computer (that is, a computer with more than one network interface), you can list multiple IP addresses (or hostnames), separated by commas. To tell the postmaster to service all network interfaces, set LISTEN_ADDRESSES='*'. Don't forget to configure the pg_hba.conf (host-based authentication) file if you service any externally connected network interfaces.

LISTEN_ADDRESSES was introduced in release 8.0 and replaces the VIRTUAL_HOST and TCP_IP parameters used in prior releases.

Rendezvous is Apple's implementation of the ZEROCONF service discovery protocol. ZEROCONF is a protocol that provides service naming and service discovery features to a local area network without the need for manually configured central directory servers. When you use ZEROCONF, you can address computers (and printers and PostgreSQL servers) by name without creating a DNS server or a DHCP server. Instead, each service in your network advertises itself on your local area network. Other computers listen for the advertisements and discover available services as they appear. PostgreSQL can advertise itself as a ZEROCONF service on computers that support Rendezvous (or the open-source alternative, howl, published by http://www.porchdogsoft.com).

To use Rendezvous, you must specify with-rendezvous when you configure the PostgreSQL source code. You must also set RENDEZVOUS_NAME to the name that you want your PostgreSQL server to advertise. PostgreSQL will always advertise itself as a service of type "_postgresql._tcp.".

This parameter determines whether the postmaster listens for connection requests coming from a TCP/IP socket. If TCPIP_SOCKET is false, the postmaster will listen for connection requests coming only from a Unix local domain socket. If TCPIP__SOCKET is true, the postmaster will listen for connection requests coming from a TCP/IP socket, as well as listening for local connection requests. You can override this variable by invoking the postmaster with the -i flag.

This parameter has been replaced by LISTEN_ADDRESSES starting in PostgreSQL version 8.0.

PostgreSQL reserves at least this many connections (out of the maximum allowed MAX_CONNECTIONS) for users that hold superuser privileges within the cluster. With the default value (2), you ensure that your cluster superuser can still connect to the database, even if all other connections have been allocated to non-privileged users. Remember that some maintenance processes (like pg_autovacuum) may consume connections that you reserve for superusers so be sure to set SUPERUSER_RESERVED_CONNECTIONS to a higher number if you're running those processes.

postmaster -l

The MAX_CONNECTIONS parameter determines the maximum number of concurrent client connections that the postmaster will accept. You can increase (or decrease) the maximum number of connections by invoking the postmaster with the -n connections parameter. You also can change the default value for MAX_CONNECTIONS by invoking configure with the with-maxbackends= connections option when you build PostgreSQL from source code.

This parameter determines which TCP/IP port the postmaster should listen to. When a remote client application wants to connect to a PostgreSQL server, it must connect to a TCP/IP port where a postmaster is listening for connection requests. The client and server must agree on the same port number. You can override this parameter by invoking the postmaster with the -p port parameter. You can also change the default value for PORT by invoking configure with the with-pgport= port when you build PostgreSQL from source code.

The postmaster always listens for local connection requests using a Unix domain socket. The socket's device file is normally found in the /tmp directory. You can move the socket device file to a different directory by using the UNIX_SOCKET_DIRECTORY configuration parameter or by invoking the postmaster with the -k directory parameter. You also can change the default value for this parameter by defining the DEFAULT PGSOCKET_DIR directory when you configure and build PostgreSQL from source code.

This parameter determines the owning group of the Unix local domain socket (see previous entry for more information). If UNIX_SOCKET_GROUP is undefined (or empty), the socket will be created using the default group for the user that starts the postmaster. The PostgreSQL Administrator's Manual suggests that you can use this parameter, along with UNIX_SOCKET_PERMISSION, to restrict local connections to a specific group.

This parameter determines the permissions assigned to the Unix local domain socket. By default, the socket is created with permissions of 0777 (meaning readable and writable by anyone). By changing the socket permissions, you can restrict local connection requests by user ID or group ID. For example, if you create a group named postgresusers, set UNIX_SOCKET_GROUP to postgresusers, and set UNIX__SOCKET_PERMISSIONS to 0060. Only users in the postgresusers group will be able to connect through the local domain socket.

If the postmaster is running on a host that supports multiple IP addresses (for example, has multiple network adapters), you can use the VIRTUAL_HOST parameter to tell the postmaster to listen for connection requests on a specific IP address. If you don't specify a VIRTUAL_HOST, the postmaster will listen on all network adapters.

This parameter has been replaced by LISTEN_ADDRESSES starting in PostgreSQL version 8.0

Operational Parameters

The next set of parameters forms a group of loosely related options that affect how the PostgreSQL server operates. Most of these options affect performance and are therefore related to the options shown in the next section.

Determines how long the BGWRITER process will sleep after writing a collection of modified pages to disk. See the section titled "The PostgreSQL BGWRITER Process" (in this chapter) for more information.

When the BGWRITER process awakens, it searches through the shared memory pool to find pages that have been modified and, therefore, must be written to disk. BGWRITER does not flush all modified pages to disk every time it wakes up. Instead, BGWRITER chooses a fraction (based on BGWRITER_PERCENT) of the least-recently-used (and modified) pages and writes only those pages to disk. BGWRITER writes no more than BGWRITER MAXPAGES during each cycle (that is, the BGWRITER process writes BGWRITER_MAXPAGES or modified_page_count * (BGWRITER_PERCENT/100), whichever is smaller).

The BGWRITER process writes no more than BGWRITER_MAXPAGES pages to disk during each cycle. See the section titled "The PostgreSQL BGWRITER Process" (in this chapter) for more information.

When PostgreSQL reads data from (or writes data to) disk, it first transfers the data into a cache stored in shared memory. This cache is shared by all clients connected to a single cluster. Disk I/O (and cache I/O) is performed in 8KB chunks (each chunk is called a page). The SHARED_BUFFERS parameter determines how many 8KB pages will be created in the shared cache. The default value, 64, is usually sufficient for a small number of users, but should be increased as your user count grows. See Chapter 4 for more information. You can change the default value for SHARED_BUFFERS by defining the DEF_NBUFFERS environment variable when you configure and build PostgreSQL from source code. You can also override SHARED_BUFFERS by invoking the postmaster with the -B nbuffers command-line parameter.

When PostgreSQL needs to write new data into a table, it searches the table for free space. If free space cannot be found within the table, the file holding the table is enlarged. The free-space manager caches free-space information in shared memory for better performance. The MAX_FSM_RELATIONS parameter determines the maximum number of tables that the free-space manager will manage at one time. If the cache becomes full, old free-space information will be removed from the cache to make room. This parameter is related to the MAX_FSM_PAGES parameter.

This parameter (along with MAX_FSM_RELATIONS) determines the size of the free-space cache used by the free-space manager. The free-space cache contains, at most, MAX_FSM_PAGES worth of data from, at most, MAX_FSM_RELATIONS different tables.

These two parameters have no effect on read operations, but can affect the performance of INSERT and UPDATE commands.

When PostgreSQL processes a query, it transforms the query from string form into an execution plan. An execution plan is a sequence of operations that must be performed in order to satisfy the query. A typical execution plan might include steps to scan through an entire table and sort the results. If an execution plan includes a Sort or Hash operation, PostgreSQL can use two different algorithm families to perform the sort (or hash). If the amount of memory required to perform the sort (or hash) exceeds WORK_MEM KB, PostgreSQL will switch from an in-memory algorithm to a more expensive, disk-based algorithm. You can adjust WORK_MEM on a per-command basis using the command SET WORK_MEM TO maximum_memory.

This parameter was named SORT_MEM prior to PostgreSQL release 8.0.

MAINTENANCE_WORK_MEM specifies the maximum amount of memory PostgreSQL will allocate for maintenance operations (specifically, VACUUM, index creation, and foreign key creation).

This parameter was named VACUUM_MEM prior to PostgreSQL release 8.0.

This parameter specifies the maximum size of the server execution stack. Your operating system may impose a lower limit, or, in most cases, a higher limit. PostgreSQL checks its own stack size at critical points where complex queries may cause deep recursion (and therefore overflow the stack). If PostgreSQL detects that the size of the stack has exceeded MAX_STACK_DEPTH (measure in kilobytes), it will issue an error 54001 (stack depth limit exceeded). If you see that error, you may consider increasing MAX_STACK_DEPTH. Be aware, however, that if PostgreSQL overflows the operating system-imposed limit before reaching MAX_STACK_DEPTH, the most likely result is a nasty crash, smoke, and a few flames.

MAX_STACK_DEPTH replaces the (now obsolete) MAX_EXPR_DEPTH parameter starting in PostgreSQL release 8.0.

This parameter defines maximum number of files that PostgreSQL opens for any given server process. PostgreSQL uses a file-descriptor caching mechanism to extend the number of files that are logically open without having to have each file physically opened, so if you see any error messages suggesting that you have Too Many Open Files, you should reduce this parameter.

When an application (such as the PostgreSQL server) writes data to disk, the operating system usually buffers the modifications to improve performance. The OS kernel flushes modified buffers to disk at some time in the future. If your host operating system (or hardware) experiences a crash, not all buffers will be written to disk. If you set the FSYNC parameter to true, PostgreSQL will occasionally force the kernel to flush modified buffers to disk. Setting FSYNC to true improves reliability with little performance penalty.

Starting with release 8.0, you can reduce the performance hit that you suffer while VACUUM or ANALYZE commands are executing. When you fire off a VACUUM or ANALYZE command, the server keeps track of the cost of executing that command. The cost is estimated based on the number of pages found in the shared memory buffer, the number of pages found on-disk, and the number of pages modified. When the cost exceeds VACUUM_COST_LIMIT, the server forces the VACUUM (or ANALYZE) command to take a short nap before continuing. The nap lasts for VACUUM_COST_DELAY milliseconds. When the command wakes up again, the server resets the execution cost to zero. By napping from time to time, the VACUUM and ANALYZE commands reduce contention with other (presumably more important) processes.

When PostgreSQL computes the cost of executing a VACUUM or ANALYZE command, is assigns an estimated cost to each type of operation. For example, when PostgreSQL reads a page from disk (on behalf of a VACUUM or ANALYZE command), it adds 10 units (the default value for VACUUM_COST_PAGE_MISS) to the cost of executing the command. When a VACUUM or ANALYZE command writes a modified page back to disk, PostgreSQL adds 20 units (the default value for VACUUM_COST_PAGE_DIRTY) to the cost of executing the command. Assuming the default cost estimates, a VACUUM command would nap for VACUUM_COST_DELAY milliseconds after reading 20 pages from disk or reading 200 pages from the shared memory buffer, or writing 10 modified pages back to disk, or any combination that adds up to VACUUM_COST_LIMIT units.

By default, VACUUM_COST_DELAY is set to zero, which means that the lazy VACUUM feature is effectively disabled.

VACUUM_COST_DELAY determines the amount of time (measured in milliseconds) that a VACUUM or ANALYZE command will nap when the cost of executing the command exceeds VACUUM_COST_LIMIT. When the command reawakens, the server resets the execution cost back to zero.

By default, VACUUM_COST_DELAY is set to zero, which means that the lazy VACUUM feature is disabled.

This parameter determines the estimated cost of finding a required page in the shared memory buffer (as a result of executing a VACUUM or ANALYZE command). VACUUM_COST_PAGE_HIT is used by the lazy VACUUM feature to estimate the cost of executing a VACUUM or ANALYZE command.

This parameter determines the estimated expense of reading a page from disk (as a result of executing a VACUUM or ANALYZE command). VACUUM_COST_PAGE_MISS is used by the lazy VACUUM feature to estimate the cost of executing a VACUUM or ANALYZE command.

This parameter determines the estimated expense of writing a modified page back to disk (as a result of executing a VACUUM or ANALYZE command). VACUUM_COST_PAGE_DIRTY is used by the lazy VACUUM feature to estimate the cost of executing a VACUUM or ANALYZE command.

Use the PRELOAD_LIBRARIES parameter to preload shared libraries into the postmaster. PRELOAD_LIBRARIES should contain a comma-separated list of entries of the form:

library-name[ : initializer-name ]

Each entry in the list specifies a shared library and the name of an initialization function within that library (the initializer name is optional). For example, to preload the PL/pgSQL and PL/Perl language processors, you would include the following entry in the postgresql.conf configuration file:

preload_libraries = 'plpgsql.so:plpgsql_init, plperl.so:plperl_init'

If the library-name does not include a path separator character (that is, a / or character), the postmaster searches the directories named by DYNAMIC_LIBRARY_PATH to find the library.

When you preload a library into the postmaster, the postmaster incurs the overhead of loading and initializing that library. When the postmaster spawns a server process to service a new client, the server process inherits the preloaded, fully initialized library from the postmaster. Use PRELOAD_LIBRARIES with cautionit moves initialization overhead out of the server process and into the postmaster, but that means that every server process contains a copy of the shared library (and every server process becomes a bit larger). If you don't preload a library, the library is only loaded on-demand; if a server process doesn't actually use the library, it's never loaded.

Write-Ahead Log Parameters

The parameters described in this section influence PostgreSQL's write-ahead log (WAL).

When a transaction makes a change to a PostgreSQL table, the change is applied to the heap (and/or index) pages that are cached in shared memory. All changes are also logged to a write-ahead log. The write-ahead log is also cached in shared memory. When a transaction is committed, the write-ahead log is flushed to disk, but the changes made to the actual data pages may not be transferred from shared memory to disk until some point in the future. The size of the shared write-ahead cache is determined by WAL_BUFFERS. The default value of 8 creates a shared write-ahead cache of eight 8KB pages.

The write-ahead log files are divided into 6MB segments. Every so often, PostgreSQL will need to move all modified data (heap and index) pages from the shared-memory cache to disk. This operation is called a checkpoint. Log entries made prior to a checkpoint are obsolete and the space consumed by those stale entries can be recycled. If PostgreSQL never performed a checkpoint, the write-ahead logs would grow without bound. The interval between checkpoints is determined by the CHECKPOINT_SEGMENTS and CHECKPOINT_TIMEOUT parameters. A checkpoint will occur every CHECKPOINT TIMEOUT seconds or when the number of newly filled segments reaches CHECKPOINT_SEGMENTS.

This parameter determines the maximum amount of time that can elapse between checkpoints. You may see a checkpoint occur before CHECKPOINT_TIMEOUT seconds has elapsed if the CHECKPOINT_SEGMENTS threshold has been reached.

If PostgreSQL executes a CHECKPOINT more often CHECKPOINT_WARNING (measured in seconds), the BGWRITER processes records a warning (checkpoints are occurring too frequently) to the server log. If you see this message, you should consider increasing CHECKPOINT_SEGMENTS.

When a transaction is committed, the WAL must be flushed from shared-memory to disk. PostgreSQL pauses for COMMIT_DELAY microseconds so that other server processes can sneak their commits into the same flush operation. The default for this parameter is 0, meaning that the WAL will be flushed to disk immediately after each COMMIT.

The COMMIT_DELAY (described previously) is a waste of time if there are no other transactions active at the time you COMMIT (if there are no other transactions, they can't possibly try to sneak in a COMMIT). The WAL manager will not delay for COMMIT_DELAY microseconds unless there are at least COMMIT_SIBLINGS TRansactions active at the time you COMMIT your changes.

When the WAL manager needs to flush cached write-ahead pages to disk, it can use a variety of system calls. The legal values for WAL_SYNC_METHOD vary by host type. It's not very likely that you will ever need to adjust this valuethe default value is chosen by the configure program at the time PostgreSQL is built from source code. See the PostgreSQL Administrator's Guide for more information.

If ARCHIVE_COMMAND is defined, PostgreSQL archives each WAL (write-ahead-log) segment before recycling that segment. ARCHIVE_COMMAND should specify the command that PostgreSQL executes to archive the segment file. The PostgreSQL reference documentation shows the following examples:

archive_command = 'cp "%p" /mnt/server/archivedir/"%f"' archive_command = 'copy "%p" /mnt/server/archivedir/"%f"' # Windows

PostgreSQL replaces %p with the complete pathname of the segment file and replaces %f with the filename component of the segment file. PostgreSQL executes the specified command whenever a WAL segment file fills up. If the command succeeds (that is, the command exists with a status value of zero), PostgreSQL recycles the segment file.

If ARCHIVE_COMMAND is defined, you can use PostgreSQL's PITR (point in time recovery) features to restore a database to the state it was in at an exact point in time. If ARCHIVE_COMMAND is blank (which is the default), PITR is disabled. See the section labeled "Point In Time Recovery" (in this chapter) for more information.

Optimizer Parameters

This section looks at the configuration options that directly influence the PostgreSQL optimizer. The first seven options can be used to enable or disable execution strategies. Some of these options affect how the optimizer estimates execution costs. The last set of options control the PostgreSQL Genetic query optimizer (GEQO).

When you ANALYZE a table, PostgreSQL gathers statistics that describe the distribution of values within each column. By default, PostgreSQL stores the 10 most commonly seen values for each column along with the bounds for 10 histogram buckets (see Chapter 4 for more information). The optimizer uses these statistics to find the least expensive plan for each query. You can adjust the number of sample values that ANALYZE will store on a column-by-column basis (see ALTER TABLE ... SET STATISTICS). If you don't specify a sample size for a given column, ANALYZE stores the number of samples specified by DEFAULT_STATISTICS_TARGET.

In general, you should define per-column statistics targets using ALTER TABLE ... SET STATISTICS instead of increasing DEFAULT_STATISTICS_TARGET. If you increase DEFAULT_STATISTICS_TARGET, it will take longer to ANALYZE the tables in your database, but the optimizer should become accurate and more predictable.

This parameter affects the estimated cost of performing a sequential scan on a table. Setting ENABLE_SEQSCAN to False does not completely disable sequential scans; it simply raises the estimated cost so that sequential scans are not likely to appear in the execution plan. A sequential scan may still appear in the execution plan if there is no other way to satisfy the query (for example, if you have defined no indexes on a table).

This parameter is most often used to force PostgreSQL to use an index that it would not otherwise use. If you are tempted to force PostgreSQL to use an index, you probably need to VACUUM ANALYZE your table instead.

Setting ENABLE_INDEXSCAN to False increases the estimated cost of performing an index scan so that it is unlikely to appear in an execution plan.

Setting ENABLE_TIDSCAN to False increases the estimated cost of performing a TID scan so that it is unlikely to appear in an execution plan. Because a TID scan is generated only when you have a WHERE clause that specifically mentions the CTID pseudo-column, this parameter is seldom used.

The ENABLE_SORT parameter is used to increase the estimated cost of a sort operation so that it is unlikely to appear in an execution plan (set ENABLE_SORT to False to increase the estimated cost). Sort operations are often required (in the absence of a useful index) when intermediate results must appear in a specific order. For example, both input sets to the MergeJoin operator must appear in sorted order. Of course, an ORDER BY clause can be satisfied using a sort operation. When results are required in a specific order, the only alternative to a sort operation is to use an index scan, thus it makes little sense to disable sorts and index scans at the same time.

Setting ENABLE_NESTLOOP to False increases the estimated cost of performing a nested loop operation so that it is unlikely to appear in an execution plan. The Nested Loop operator, described in Chapter 4, is one of three algorithms that PostgreSQL can use to join two tables. Setting ENABLE_NESTLOOP to False makes it more likely that PostgreSQL will choose a MergeJoin or HashJoin operator over a Nested Loop operator.

Setting ENABLE_MERGEJOIN to False increases the estimated cost of performing a MergeJoin operation so that it is unlikely to appear in an execution plan. Setting ENABLE_MERGEJOIN to False makes it more likely that PostgreSQL will choose a NestedLoop or HashJoin operator over a MergeJoin operator.

Setting ENABLE_HASHJOIN to False increases the estimated cost of performing a HashJoin operation so that it is unlikely to appear in an execution plan. Setting ENABLE_HASHJOIN to False makes it more likely that PostgreSQL will choose a NestedLoop or MergeJoin operator over a HashJoin operator.

Setting ENABLE_HASHAGG to False increases the estimated cost of performing a HashAggregate operation so that it is unlikely to appear in an execution plan. Setting ENABLE_HASHAGG to False makes it more likely that PostgreSQL will choose a Sort operator followed by an Aggregate or GroupAggregate operator. The HashAggregate operator makes it possible to GROUP related rows without first sorting them.

Setting KSQO to true (the default value for this parameter is False) gives PostgreSQL permission to rewrite certain WHERE clauses in order to optimize queries that involve many OR operators. The Key Set Query Optimizer is largely obsolete as of PostgreSQL release 7.0 so the KSQO parameter is rarely used. See Chapter 3, "Run-time Configuration," of the PostgreSQL Administrator's Guide for more information about the Key Set Query Optimizer.

When estimating the cost of an execution plan, PostgreSQL needs to make an educated guess about the cost of reading a random page from disk into the shared buffer cache. To do so, it needs to know the likelihood of finding a given page in the OS cache. The EFFECTIVE_CACHE_SIZE parameter tells PostgreSQL how much of the OS disk cache is likely to be given to your server process.

This parameter is used only when estimating the cost of an IndexScan or Sort operator (when the sort will overflow SORT_MEM bytes and switch from an in-memory sort to an on-disk sort).

Increasing the EFFECTIVE_CACHE_SIZE makes the cost estimator assume that any given page is more likely to be found in the cache. Decreasing the EFFECTIVE_CACHE_SIZE tells PostgreSQL that any given page is less likely to be found in the cache (and will therefore incur more expense).

RANDOM_PAGE_COST specifies the cost of loading a random page into the shared buffer cache. A sequential page fetch is assumed to cost 1 unit; the default value for RANDOM_PAGE_COST means that PostgreSQL assumes that it is four times as expensive to load a random page than a sequentially accessed page.

CPU_TUPLE_COST specifies the cost of processing a single tuple within a heap (data) page. With the default value of 0.01, PostgreSQL assumes that it is 100 times more expensive to load a sequential page from disk than to process a single tuple.

CPU_INDEX_TUPLE_COST specifies the cost of processing a single index entry. With the default value of 0.001, PostgreSQL assumes that it is 1,000 times more expensive to load a sequential page from disk than to process a single tuple.

CPU_OPERATOR_COST specifies the cost of processing a single operator (such as >= or !=) in a WHERE clause. With the default value of 0.0025, PostgreSQL assumes that it is 2,500 times more expensive to load a sequential page from disk than to process a single operator.

The planner/optimizer works in three phases. The first phase examines the query parse tree and builds a set of execution plans. The second phase assigns a cost to the execution plan by estimating the expense of each step of the plan. The final phase chooses the least expensive alternative and discards the other plans.

Many queries can be evaluated by two or more execution plans. For example, if you have defined an index on the tape_id column, the following query:

SELECT * FROM tapes ORDER BY tape_id;

results in at least two execution plans. One plan scans through the entire table from beginning to end and sorts the results into the desired order (this plan includes a SeqScan operator and a Sort operator). The second plan reads through the entire table using the tape_id index (this plan includes an IndexScan operator). For complex queries, especially queries involving many tables, the number of alternative plans becomes large.

The job of the Genetic Query Optimizer (or GEQO, for short) is to reduce the number of alternatives that must be evaluated by eliminating plans that are likely to be more expensive than plans already seen. The next seven parameters control the GEQO. The GEQO algorithm is too complex to try to describe in the space available, so I will include the descriptions provided in the PostgreSQL Administrator's Guide for each of the GEQO-related parameters.

If GEQO is set to true, PostgreSQL will use the Genetic Query Optimizer to eliminate plans that are likely to be expensive. If GEQO is set to False, the planner/optimizer will produce every possible execution plan and find the least expensive among the alternatives.

GEQO_SELECTION_BIAS is the selective pressure within the population. Values can be from 1.50 to 2.00; the latter is the default.

Use genetic query optimization to plan queries with at least GEQO_THRESHOLD 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

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

GEQO_EFFORT is used to calculate a default for generations. Valid values are between 1 and 80; 40 being the default.

GEQO_GENERATIONS specifies the number of iterations in the algorithm. The number must be a positive integer. If 0 is specified, GEQO_EFFORT * LOG2(GEQO_POOL_SIZE) is used. The runtime of the algorithm is roughly proportional to the sum of pool size and generations.

GEQO_RANDOM_SEED can be set to get reproducible results from the algorithm. If GEQO_RANDOM_SEED is set to -1, the algorithm behaves nondeterministically.

PostgreSQL's query optimizer usually ignores the order in which you specify tables in a query that inner-joins multiple tables. Instead, the optimizer tries to find the join ordering that produces the best execution plan. For example, when the optimizer encounters a query such as

SELECT * FROM table1 JOIN table2 ON table1.value = table2.value JOIN table3 ON table2.value = table3.value

It can choose from any of the following join plans:

table1 JOIN (table2 JOIN table3) table1 JOIN (table3 JOIN table2) table2 JOIN (table1 JOIN table3) table2 JOIN (table3 JOIN table1) table3 JOIN (table1 JOIN table2) table3 JOIN (table2 JOIN table1)

You can force the optimizer to honor the join order specified in each query by setting JOIN_COLLAPSE_LIMIT to 1. You can also reduce the amount of effort that the optimizer puts into evaluating alternative plans by setting JOIN_COLLAPSE_LIMIT to some number greater than 1 (but less than GEQO_THRESHOLD).

When the PostgreSQL query planner encounters a query that contains a subquery, such as

SELECT * FROM table1, table2, ( SELECT * FROM table3 WHERE table3.column = value ) WHERE table1.column = table2.column

It collapses the subquery (the SELECT * FROM table3 WHERE table3.column = value part) into the parent so that the optimizer can consider the entire package at once instead of planning the subquery in a separate pass. For example, given the previous query, the planner would collapse the subquery into the parent resulting a query that looks like this:

SELECT * FROM table1, table2, table3 WHERE table1.column = table2.column AND table3.column = value

The optimizer can often come up with a better execution plan by examining a single query than by planning two separate queries. However, if the FROM clause becomes too complex, the planner can spend a great deal of time evaluating alternative join plans. PostgreSQL strikes a balance by collapsing subqueries only when the resulting FROM list contains no more than FROM_COLLAPSE_LIMIT items.

Debugging/Logging Parameters

The next set of configuration parameters relates to debugging and logging. You may notice that the user can change most of the debugging options (using the SET command). You must be a cluster superuser to change any of the logging options.

The LOG_DESTINATION parameter determines where PostgreSQL writes log messages. Valid choices are STDERR, SYSLOG, and EVENTLOG (SYSLOG is only valid on Linux/Unix systems and EVENTLOG is only valid on Windows hosts). You can specify more than one destination (just separate them with commas). By default, PostgreSQL writes log messages to the postmaster's STDERR stream (which you can redirect with the -l path-name command-line argument or the REDIRECT_STDERR configuration parameter). If the postmaster is running on a Linux or Unix host, PostgreSQL can write messages to the syslog facility (see man syslog for more information). If the postmaster is running on a Windows host, PostgreSQL can write messages to the Windows event log.

If LOG_DESTINATION specifies that PostgreSQL should write log messages to STDERR, you can ask redirect the STDERR stream by setting REDIRECT_STDERR to TRue. When REDIRECT_STDERR is TRue, PostgreSQL creates a log file (whose name is determined by the LOG_FILENAME configuration parameter) in a directory whose name is determined by the LOG_DIRECTORY configuration parameter.

You can also redirect the postmaster's STDERR stream by including the -l log-filename command-line option when you start the postmaster using pg_ctl, but REDIRECT_STDERR offers a significant advantage. When you use REDIRECT_ERROR, PostgreSQL will automatically rotate the server log into a new file when it grows too large (or too old). You can specify the location of the log files with the LOG_DIRECTORY and LOG_FILENAME configuration parameters. You can control the aging of log files with the LOG_ROTATION_AGE, LOG_ROTATION_SIZE, and LOG_TRUNCATE_ON_ROTATION configuration parameters.

If you are sending server log messages to syslog, you can use the SYSLOG_FACILITY parameter to classify PostgreSQL-related messages. Most syslog implementations let you redirect each message classification to a different destination (to a text file, the system console, a particular user, or a remote system). SYSLOG_FACILITY is used to specify the classification that you want PostgreSQL to use when sending messages to syslog. Your choices for this parameter are LOCAL0, LOCAL1, ... LOCAL7. You want to choose a value other than the default if you already have software that uses LOCAL0.

If you are sending server log messages to syslog, each message is prefixed with the string specified by the SYSLOG_IDENT parameter.

If you are using REDIRECT_STDERR to create rotating server logs, LOG_DIRECTORY specifies the name of the directory where PostgreSQL will place the log files.

If you are using REDIRECT_STDERR to create rotating server logs, LOG_FILENAME specifies the template that PostgreSQL uses to create log filenames. Note that LOG_FILENAME doesn't define the actual filename, just a pattern from which PostgreSQL will generate the actual name. You can't specify the final filename yourself because PostgreSQL rotates log files as they grow too large (or too old). If LOG_FILENAME does not contain a % character, PostgreSQL appends a dot and the current date and time (measured in number of seconds since Jan. 01 1970, 00:00:00 UTC) to the end of the string that you specify. If LOG_FILENAME contains a %, PostgreSQL searches for date/time macros in the string and expands the macros shown in Table 21.5.

If you are using REDIRECT_STDERR to create rotating server logs, LOG_ROTATION_AGE determines how often PostgreSQL creates a new log and retires the old one. PostgreSQL will create a new log when LOG_ROTATION_AGE minutes have elapsed, or when the log size exceeds LOG_ROTATION_SIZE, whichever occurs first. Note that LOG_ROTATION_AGE is measured in minutes, not seconds. To disable time-based log rotation, set LOG_ROTATION_AGE to 0.

If you are using REDIRECT_STDERR to create rotating server logs, LOG_ROTATION_SIZE determines the size at which PostgreSQL creates a new log file and retires the old one. PostgreSQL will create a new log file when the size of the current log exceeds LOG_ROTATION_SIZE or when LOG_ROTATION_AGE minutes have elapsed, whichever occurs first. LOG_ROTATION_SIZE is measured in kilobytes (the default, 10240 kilobytes, creates a new log every 10MB).

If you are using REDIRECT_STDERR to create rotating server logs, LOG_TRUNCATE_ON_ ROTATION determines when PostgreSQL will overwrite old log files and when PostgreSQL will append to old log files. If this parameter is FALSE, PostgreSQL will always append to existing log file. If LOG_TRUNCATE_ON_ROTATION is TRUE, PostgreSQL will overwrite existing log files when LOG_ROTATION_AGE minutes have elapsed. PostgreSQL will not overwrite existing log files when LOG_ROTATION_SIZE forces a new log file, even if LOG_TRUNCATE_ON_ROTATION is TRUE.

This parameter determines which messages are recorded in the server log. Valid values, in decreasing order of severity, are PANIC, FATAL, LOG, ERROR, WARNING, NOTICE, INFO, DEBUG1, DEBUG2, DEBUG3, DEBUG4, and DEBUG5. LOG_MIN_MESSAGES specifies the least important message that PostgreSQL should write to the server log. For example, the default value (NOTICE) tells PostgreSQL to record NOTICE messages along with all messages more severe than NOTICE. If you want a more detailed (and faster growing) log, set LOG_MIN_MESSAGES to a category of lower severity. If you want a less detailed (and slower growing) log, set LOG_MIN_MESSAGES to a category of higher severity.

This parameter is nearly identical to LOG_MIN_MESSAGES except that CLIENT_MIN_ MESSAGES controls which messages are sent to the client.

When the PostgreSQL server decides (based on LOG_MIN_MESSAGES) to record a message in the server log, it examines LOG_MIN_ERROR_STATEMENT to determine whether to include the text of the SQL statement that produced the message. If the severity of the message is at least as high as LOG_MIN_ERROR_STATEMENT, PostgreSQL records the text of the SQL statement in the log. There is a relationship between LOG_MIN_MESSAGES and LOG_MIN_ERROR_STATEMENT. LOG_MIN_MESSAGES determines whether a given message is recorded in the server log; LOG_MIN_ERROR_STATEMENT affects the content of those messages that actually make it to the log. You usually want LOG_MIN_ERROR_STATEMENT to be of equal or higher severity than LOG_MIN_MESSAGES. For example, with LOG_MIN_MESSAGES=WARNING and LOG_MIN_ERROR_STATEMENT=ERROR, the server will record every message of severity WARNING or greater and messages of severity ERROR (or greater) will include the text of the SQL statement that produced the message.

Every message reported by the server will contain a variety of components. Each message contains a severity (PANIC, FATAL, LOG, ERROR, and so on), an error message, and an error code (error codes are listed in Appendix A of the PostgreSQL reference documentation). PostgreSQL also records the location, in the PostgreSQL source code, where the error occurred. The location includes the name of the source file, the line number within that file, and, on some hosts, the name of the function that threw the error.

Some messages contain extra detail. For example, if you try to DROP a USER that happens to own a database, PostgreSQL will report a message (of severity ERROR) that states user name cannot be dropped. That message will contain the extra detail The user owns database database-name. A few messages will contain a hint that gives you a bit of advice about how to solve the problem. Some messages are generated indirectly. For example, when you execute a SELECT statement that calls a function written in PL/pgSQL, that function may generate an error. When PostgreSQL reports a "nested" error, it records the text of the SELECT statement and the context of the error (in the case of an error thrown by a PL/pgSQL function, the context indicates the name of the function and the line number where the error occurred).

LOG_ERROR_VERBOSITY determines which message components PostgreSQL will write to the server log. It does not affect the number of messages written to the log (see LOG_MIN_MESSAGES), only the level of detail in each message. PostgreSQL 8.0 defines three levels: TERSE, DEFAULT, and VERBOSE. When LOG_ERROR_VERBOSITY is set to TERSE, PostgreSQL records the error message and severity and discards the rest of the message details. When LOG_ERROR_VERBOSITY is set to DEFAULT, PostgreSQL records the error message, severity, detail, hint, and context. If you set LOG_ERROR_VERBOSITY to VERBOSE, PostgreSQL will record the entire content of the message in the server log. Table 21.6 summarizes LOG_ERROR_VERBOSITY.

This option determines which SQL statements PostgreSQL will record in the server log. The default for this option is 'NONE', which means that the server will not write any SQL statements to the server log. You can also choose 'ALL', 'DDL', or 'MOD'. If LOG_STATEMENT is set to 'ALL', PostgreSQL will record the text of every statement in the server log. Set LOG_STATEMENT to 'DDL' to force PostgreSQL to record the text of all DDL statements (CREATE, ALTER, DROP, GRANT, REVOKE, or COMMENT). If LOG_STATEMENT is set to 'MOD', PostgreSQL will record the text of all INSERT, DELETE, UPDATE, trUNCATE, and COPY FROM statements as well as all DDL statements.

PostgreSQL will only record the text of a statement when it's first evaluated by the query planner. If you PREPARE a statement and then EXECUTE it multiple times, the text of the prepared statement only appears in the log once. The procedural language handlers (PL/pgSQL, PL/Perl, ...) cache query plans as well.

You may see some statements appear in the server log even though it seems that they should have been filtered out by LOG_STATEMENT. If a statement produces an error at least as severe as LOG_MIN_ERROR_STATEMENT, PostgreSQL will record the text of the statement. If a statement takes more than LOG_MIN_DURATION_STATEMENT to execute (measured in milliseconds), PostgreSQL will record the text of the statement (and the duration). That means that some statements will appear in the log two or three times.

LOG_DURATION works in conjunction with LOG_STATEMENT. If LOG_DURATION is trUE, PostgreSQL records the amount of time that it takes to execute every statement that makes it through the LOG_STATEMENT filter.

Use LOG_MIN_DURATION_STATEMENT to find long-running queries. PostgreSQL records the text (and duration) of every statement that takes longer than LOG_MIN_DURATION_ STATEMENT milliseconds to execute. For example, to see queries that take more than 30 seconds to execute, set LOG_MIN_DURATION_STATEMENT to 3000. Set LOG_MIN_ DURATION_STATEMENT to -1 to disable duration-dependent logging.

You may see some statements appear in the log multiple times even though you've only executed them once. A statement may be written to the log file by LOG_MIN_ DURATION_STATEMENT, LOG_MIN_ERROR_STATEMENT, or LOG_STATEMENT.

When PostgreSQL writes a message to the server log, it can prefix the message with a string of your choice. To change the prefix (which is blank by default), set LOG_LINE_PREFIX to the string you want to appear in the log. PostgreSQL defines a number of macros that you can include in the string. PostgreSQL expands each macro into a specific bit of information as shown in Table 21.7.

If SILENT_MODE is set to true, all logging and debugging messages are suppressed. If SILENT_MODE is set to true (the default), the postmaster will write log and debug messages to the log destination. You can specify where log messages will be written by invoking the postmaster with the -i log-file-name command-line option.

If LOG_CONNECTIONS is set to TRue, the postmaster will log each successful client connection. The log message produced by this parameter is of the form:

connection: host=client-address user=user database=database

If LOG_HOSTNAME is true, the client-address will include the client's hostname and IP address; otherwise, only the client's IP address is shown.

If SHOW_SOURCE_PORT is true, the client-address will also include the port number used by the client side of the connection. (Note: SHOW_SOURCE_PORT shows the client's port number, not the server's port number.)

If LOG_DISCONNECTIONS is set to TRue, the postmaster will log each successful client disconnect. The log message is of the form:

disconnection: session time: duration user=name database=db host=client-addr port=port

If LOG_HOSTNAME is true, the client-addr will include the client's hostname and IP address; otherwise, only the client's IP address is shown.

Every server process examines LOG_HOSTNAME to decide whether to resolve the client IP address into a hostname. If LOG_HOSTNAME is true, client connect and disconnect messages will include the name of the client host. If LOG_HOSTNAME is False, client connect and disconnect message include only the IP address of the client host.

Be aware that LOG_HOSTNAME is a bit of a misnomerif you set LOG_HOSTNAME to TRue, PostgreSQL will convert the client IP address into a hostname even if you've disabled LOG_CONNECTIONS and LOG_DISCONNECTIONS. If you see a noticeable delay when starting a new server process, check LOG_HOSTNAME. In many configurations, the process of resolving an IP address into a hostname can consume a significant amount of time.

If DEBUG_PRINT_PARSE is true, PostgreSQL will write a textual representation of the parse tree of each query to the server log.

PostgreSQL implements views using a set of rules that rewrite queries from the point of view seen by the user to the form required to evaluate the view.

If DEBUG_PRINT_REWRITTEN is TRue, PostgreSQL will write the rewritten form of each query to the server log.

If DEBUG_PRINT_PLAN is TRue, PostgreSQL will write the execution plan of each command to the server log. Turning on DEBUG_PRINT_PLAN is similar to using the EXPLAIN command-DEBUG_PRINT_PLAN gives a much more detailed (and much less readable) plan.

If DEBUG_PRETTY_PRINT is true, the log entries for DEBUG_PRINT_PARSE, DEBUG_PRINT_REWRITTEN, and DEBUG_PRINT_PLAN are formatted for consumption by mere mortals. If DEBUG_PRETTY_PRINT is False, the log entries just mentioned are packed very tightly and can be very difficult to read.

This parameter enables (or disables) assertion checking in the PostgreSQL server. The PostgreSQL developers sprinkle assertion checks throughout the PostgreSQL source code to assert that certain prerequisites are in place. For example, a developer may assert that a particular function was invoked with the correct number of arguments. Assertions check for things that should never happen. If an assertion check fails, something has gone wrong and it's likely to be something out of your controlit may be a bug in PostgreSQL or an unexpected failure in the PostgreSQL environment. If DEBUG ASSERTIONS is TRue (and you've configured the PostgreSQL source code with the enable-cassert flag), PostgreSQL will verify all assertions and report any failures. If DEBUG_ASSERTIONS is False, PostgreSQL ignores any assertion failures. If you did not include the enable-cassert flag when you built PostgreSQL from source code, the PostgreSQL server won't even verify the assertions (so you won't see any assertion failure messages).

If you are trying to debug the PostgreSQL server (or perhaps a shared library loaded by the server), you may have trouble attaching a debugger (such as GDB) to the backend process at the right point in time. The PRE_AUTH_DELAY parameter can help. If PRE_AUTH_DELAY is non-zero, the backend process sleeps for that number of seconds immediately after it has been spawned by the postmaster and before authenticating the client application. If you attach a debugger to the backend process while it's sleeping, you can step through the authentication code (or set breakpoints on functions invoked later).

If you plan to attach a debugger to a PostgreSQL process, be sure to include the enable-debug flag when you configure the PostgreSQL source code.

When PostgreSQL detects that page has been damaged (that is, corrupted) it usually reports an error (code XX001, invalid page header in block block-number of relation name) and aborts the command that you're executing. If you see this error, the safest remedy is to restore the damaged table from an archive. If you don't have a current archive, you can recover some of the data in the table (but not the data in the damaged page) by setting ZERO_DAMAGED_PAGES to true. When ZERO_DAMAGED_PAGES is true, PostgreSQL destroys the data on the damaged page but leaves the rest of the table intact.

If trACE_NOTIFY is true, the server will write debug messages regarding the NOTIFY and LISTEN commands to the server log.

If TRACE_LOCKS is true, the server will write debug messages that detail locking operations within the server. This parameter can be set only if the symbol LOCK_DEBUG was defined when your copy of PostgreSQL was built from source code. trACE_LOCKS is rarely used except by the PostgreSQL developers, but the output can be useful if you want to understand how PostgreSQL manages locking.

If trACE_LOCKS is true, trACE_LOCK_OIDMIN specifies the set of tables for which lock information is logged. If the OID (object ID) of a table's pg_class entry is less than TRACE_LOCK_OIDMIN, PostgreSQL will not log locking information for that table. The default value (16384) was chosen to prevent log messages about locking performed on system tables (system tables have OIDs less than 16384). This parameter can be set only if the symbol LOCK_DEBUG was defined when your copy of PostgreSQL was built from source code.

If trACE_LOCKS is False, you can tell PostgreSQL that it should still log locking information for a specific table by setting trACE_LOCK_TABLE to the OID of that table's entry in pg_class. This parameter can be set only if the symbol LOCK_DEBUG was defined when your copy of PostgreSQL was built from source code.

If trACE_USERLOCKS is TRue, the server will write debug messages concerning the LOCK TABLE command to the server log. This parameter can be set only if the symbol LOCK_DEBUG was defined when your copy of PostgreSQL was built from source code. TRACE_USERLOCKS is rarely used except by the PostgreSQL developers, but the output can be useful if you want to understand how PostgreSQL manages locking.

If trACE_LWLOCKS is TRue, the server will write debug messages concerning the lightweight locks that PostgreSQL uses to coordinate multiple server processes. This parameter can be set only if the symbol LOCK_DEBUG was defined when your copy of PostgreSQL was built from source code. TRACE_LWLOCKS is rarely used except by the PostgreSQL developers.

If DEBUG_DEADLOCKS is true, the server will log lock queue information whenever a deadlock is detected. A deadlock occurs when two (or more) transactions need to lock two (or more) resources (such as a row or table), but the transactions are blocking each other from proceeding.

This parameter can be set only if the symbol LOCK_DEBUG was defined when your copy of PostgreSQL was built from source code.

Performance Statistics

Next, let's look at the set of configuration parameters that control how PostgreSQL computes and reports performance statistics.

If LOG_PARSER_STATS is true, the server will write parser statistics to the server log file. For each command, PostgreSQL logs parser statistics, parse analysis statistics, and query rewriter statistics.

This parameter is named SHOW_PARSER_STATS in PostgreSQL versions 7.3 and older.

If LOG_EXECUTOR_STATS is true, the server will write execution statistics to the server log file.

This parameter is named SHOW_EXECUTOR_STATS in PostgreSQL versions 7.3 and older.

If LOG_PLANNER_STATS is true, the server will write parser statistics to the server log file.

This parameter is named SHOW_PLANNER_STATS in PostgreSQL versions 7.3 and older.

If LOG_STATEMENT_STATS is true, the server will write statement execution statistics to the server log file.

This parameter is named SHOW_STATEMENT_STATS in PostgreSQL version 7.4, and SHOW_QUERY_STATS in versions 7.3 and older.

Starting with release 7.2, PostgreSQL can gather ongoing, clusterwide usage statistics in a set of system tables and views. These tables are described in detail in Chapter 4. You must set the STATS_START_COLLECTOR to true if you want PostgreSQL to maintain the information in these tables.

If STATS_RESET_ON_SERVER_START is TRue, the statistics captured by the performance monitor will be reset (that is, zeroed out) each time the postmaster starts. If this parameter is False, the performance statistics will accumulate.

If STATS_COMMAND_STRING is true, each PostgreSQL server will send the currently executing command string to the performance monitor. This command string is displayed in the current_query column of the pg_stat_activity view.

If STATS_ROW_LEVEL is TRue, the performance monitor will gather information regarding the number of tuples processed in each table. When you gather row-level statistics, PostgreSQL records the number of sequential scans and index scans performed on each table, as well the number of tuples processed for each type of scan. The performance monitor also records the number of tuples inserted, updated, and deleted. The row-level information gathered by the performance monitor is found in the pg_stat views described in Chapter 4.

If STATS_BLOCK_LEVEL is TRue, the performance monitor will gather information regarding the number of blocks (also known as pages) processed in each table. When you gather block-level statistics, PostgreSQL records the number of heap blocks read, the number of index blocks read, the number of TOAST heap blocks read, and the number of TOAST index blocks read. The performance monitor also records the number of times each type of block was found in the shared buffer cache.

The block-level information gathered by the performance monitor is found in the pg_statio views described in Chapter 4. TOAST blocks are also described in Chapter 4.

If DEBUG_SHARED_BUFFERS is non-zero, the ARC shared buffer manager will periodically write performance and debugging information to the server log. DEBUG_SHARED_ BUFFERS specifies the interval between each report (measured in seconds).

Per-session Parameters

Every client application inherits a set of per-session parameters. You can change any per-session parameter by executing a SET statement. When you modify a per-session statement (at runtime), the change affects only your session. Every session inherits a fresh set of per-session parameters from the postmaster.

This parameter determines whether the EXPLAIN VERBOSE command displays a query tree in unindented (machine readable) form or indented (wizard readable) form. There are few people in this world who can make sense of either form, but you'll find them all on the pgsql_hackers mailing list. Some client applications may change this parameter, but it's not something a human is likely to need.

When you create a new function written in a language such as PL/pgSQL, PostgreSQL usually validates the body of the function to check for syntax errors and references to undefined variables. If the procedural language handler finds an error, PostgreSQL refuses to save the function. In most cases, that's a useful feature because you don't want to litter your server with functions that won't compile. However, there are two occasions when it may be useful to suppress the error checking until later. First, if you're restoring function definitions from a backup, you may run into forward references that cause the restore to fail. For example, if you restore a function that refers to the customers table, but you haven't yet restored the definition of the customers table, PostgreSQL will abort the restore. Second, if you're importing data from an external source, you may want to import function definitions before you define any tables (just a matter of convenience), in which case you may also run into forward references. In either case, you can tell PostgreSQL to defer any syntax (and reference) checks until the first time the function is invoked by setting CHECK_FUNCTION_BODIES to False.

SEARCH_PATH determines which schemas PostgreSQL will search when you refer to an object without specifying a schema name (SEARCH_PATH is a comma-separated list of schema names). SEARCH_PATH also determines the order in which the search progresses. When you create a new object (an index, a table, or a function) without explicitly specifying a schema, PostgreSQL places the object in the first schema found in SEARCH_PATH.

PostgreSQL defines three special schema names: $user, pg_catalog, and pg_temp_ nnn. $user translates into your PostgreSQL username (or, more precisely, the value of the SESSION_USER parameter). PostgreSQL will not search the $user schema unless it appears in your SEARCH_PATH. The PostgreSQL data dictionary (pg_class, pg_attribute, and so on) is defined in the pg_catalog schema: PostgreSQL always searches the pg_catalog schema, even if it does not appear in SEARCH_PATH. If pg_catalog does not appear in SEARCH_PATH, PostgreSQL searches pg_catalog before any of the schemas that do appear in SEARCH_PATH. Temporary tables are defined in a schema named pg_temp_ nnn (where nnn is a unique number that identifies your session). PostgreSQL always searches pg_temp_ nnn before searching any other schema (which means that PostgreSQL will always find a temporary object before it finds a permanent object of the same name unless you explicitly specify a schema name when you refer to the object).

When you create a table or an index without explicitly identifying a target tablespace, PostgreSQL stores the object in the DEFAULT_TABLESPACE. If DEFAULT_TABLESPACE is blank (or if it identifies a nonexistent tablespace), PostgreSQL stores the object the in the default tablespace of the current database.

If AUSTRALIAN_TIMEZONES is TRue, the time zones ACST (UTC+9.5), CST (UTC+10.5), EST (UTC+10), EAST (UTC+10) and SAT (UTC+9.5) are interpreted as Central Australia Standard Time, Australian Central Standard Time, Australian Eastern Standard Time, Australian Eastern Standard Time, and South Australian Standard Time, respectively.

If AUSTRALIAN_TIMEZONES is False, ACST is interpreted as UTC-4 (Atlantic Summer Time) CST is interpreted as UTC-6 (Central Standard Time), EST is interpreted as UTC-5 (Eastern Standard Time), EAST is interpreted as UTC-6 (Easter Island Time), and SAT is interpreted as an abbreviation for Saturday.

PostgreSQL's support for time zones is described in Chapter 2, "Working with Data in PostgreSQL."

PostgreSQL usually decides whether you can modify a table (or sequence) by checking the permissions granted to your user (and group). You can write-protect your entire database by setting DEFAULT_TRANSACTION_READ_ONLY to true in the postgresql.conf configuration file. You can also write-protect your session by setting DEFAULT_ TRANSACTION_READ_ONLY to true with a SET statement. But be aware that this parameter doesn't give you any real protection because any given user can SET DEFAULT_ TRANSACTION_READ_ONLY TO FALSE. DEFAULT_TRANSACTION_READ_ONLY simply makes it less likely that you'll modify the database when you don't mean to.

If STATEMENT_TIMEOUT is a value other than zero, PostgreSQL will cancel any command that takes longer than STATEMENT_TIMEOUT to execute (measured in milliseconds). When PostgreSQL cancels a query, it throws error 57014 (canceling query due to user request).

This parameter defines default transaction isolation level for all transactions. The valid choices for this parameter are 'READ COMMITTED' and 'SERIALIZABLE'. transaction isolation levels are described in the section titled "Transaction Isolation" in Chapter 3.

You can modify the transaction isolation level for an individual transaction using the SET TRANSACTION ISOLATION LEVEL command. You can also change the default isolation level for a PostgreSQL session using the command SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL [READ COMMITTED | SERIALIZABLE], but I've never be able focus my attention long enough to enter that command.

This parameter changes the number of digits that PostgreSQL will display for floating point values (REAL, DOUBLE PRECISION, FLOAT4, FLOAT8, POINT, LINE, LSEG, BOX, PATH, POLYGON, and CIRCLE). By default, PostgreSQL displays 6 digits for single-precision data types (REAL and FLOAT4) and 15 digits for double-precision types (DOUBLE PRECISION, FLOAT8, and the geometric types). To increase the number of digits displayed, set EXtrA_FLOAT_DIGITS to a positive number (1 or 2). To decrease the number of digits, set EXtrA_FLOAT_DIGITS to a negative value (-1 through -15).

The CLIENT_ENCODING parameter tells PostgreSQL which character set to use when sending data to (or reading data from) the client application. See Chapter 22 for more information.

This parameter determines the (human) language that the PostgreSQL server will use when it generates an error (or other) message. See Chapter 22 for more information.

LC_NUMERIC determines which characters the server uses when it converts numbers to (and from) character form. In particular, LC_NUMERIC defines the decimal point character, the grouping character, and the rules for using the grouping character. See Chapter 22 for more information.

Not currently used by PostgreSQL see Chapter 22 for more information.

LC_MONETARY defines the local currency symbol ($), the international currency symbol (USD), and positive/negative sign conventions used when PostgreSQL converts monetary values to and from character form. LC_MONETARY also defines the decimal point character, grouping character and grouping rules used to convert monetary values. See Chapter 22 for more information.

The TIMEZONE parameter determines the server's (assumed) offset from UTC. If TIMEZONE is not set (or is set to the string 'UNKNOWN'), PostgreSQL queries the host operating system to find the timezone.

The DYNAMIC_LIBRARY_PATH determines which directories PostgreSQL searches to find dynamically loaded functions (that is, external functions defined with the CREATE FUNCTION command). This parameter should be defined as a colon-separated list of the absolute directory. The DYNAMIC_LIBRARY_PATH is consulted only when PostgreSQL needs to load a dynamic object module that does not include a directory name. If DYNAMIC_LIBRARY_PATH is defined but empty, PostgreSQL will not use a search path, and each external function must include a directory name.

Miscellaneous Parameters

Finally, we'll look at the configuration parameters that don't fit well into the other categories.

If you've written an application that works with old versions of PostgreSQL, you may have a few SELECT commands that are ill-formed. Older versions of PostgreSQL would automatically repair a faulty FROM clause that omitted tables referenced elsewhere in the query. If ADD_MISSING_FROM is true, PostgreSQL will continue to repair faulty FROM clauses. It's a good idea to disable this feature so you don't accidentally write bad SELECT commands.

When DEFAULT_WITH_OIDS is true, PostgreSQL automatically adds an OID (object ID) column to every table that you create unless you explicitly specify WITHOUT OID in the CREATE TABLE command. When DEFAULT_WITH_OIDS is False, PostgreSQL does not add an OID column to every table unless you explicitly specify WITH OID.

This parameter, along with MAX_CONNECTIONS, determines the size of PostgreSQL's shared lock table. Any given transaction can hold more than MAX_LOCKS_PER_ TRANSACTION locks, but the total number of locks cannot exceed MAX_CONNECTIONS * MAX_LOCKS_PER_TRANSACTION. PostgreSQL locking is described in Chapter 9, "Multi-Version Concurrency Control," of the PostgreSQL User's Manual.

A deadlock occurs when one transaction holds a lock required by a second transaction and the second transaction holds a lock required by the first. This situation is called a deadlock because neither transaction can move forward (each transaction is waiting for the lock held by the other transaction). There may be more than two transactions involved in a deadlock.

The PostgreSQL lock manager checks for deadlock whenever a server process waits for a lock longer than DEADLOCK_TIMEOUT (measured in milliseconds). Checking for a deadlock is a relatively expensive operation so the lock manager assumes that a blocked lock request will eventually be satisfied. If DEADLOCK_TIMEOUT milliseconds elapses before a lock is satisfied, PostgreSQL searches through all locks to find out whether a deadlock has occurred.

The default value for this parameter is one secondyou may want to increase this value on heavily loaded systems (increasing DEADLOCK_TIMEOUT will reduce the load imposed by deadlock checks, but will increase the amount of time that elapses before a real deadlock is detected).

REGEX_FLAVOR determines the regular-expression style recognized by the PostgreSQL backend. PostgreSQL supports three different flavors: advanced (the default flavor), extended, and basic. The advanced flavor offers the largest collection of features. See the PostgreSQL reference documentation for more information.

Prior to release 7.1, a SELECT command would not include data from descendant tables unless an asterisk was appended to the table name. Starting with release 7.1, data is included from all descendant tables unless the keyword ONLY is included in the FROM clause.

In other words, in release 7.1, the default behavior of PostgreSQL's inheritance feature was reversed. If you find that you need the pre-7.1 behavior, set SQL_INHERITANCE to false.

Inheritance is described in Chapter 3.

If TRANSFORM_NULL_EQUALS is TRue, the PostgreSQL parser will translate expressions of the form expression = NULL to expression IS NULL. In most cases, it's a bad idea to set this parameter to true because there is a semantic difference between = NULL and IS NULL. The expression expression = NULL should always evaluate to NULL, regardless of the value of expression. the only time that you should consider setting this parameter to true is when you are using Microsoft Access as a client application: Access can generate queries that are technically incorrect but are still expected to function.

Read-only Parameters

PostgreSQL defines a number of read-only parameters (also known as preset parameters). The values of some read-only parameters are determined at the time you build PostgreSQL from source code. Other values are fixed when you create a database cluster. You cannot change these parameters, but you will find them in the pg_settings system view.

This parameter reflects the version of the PostgreSQL server that you are connected to.

The BLOCK_SIZE parameter reflects the size of a disk block (as seen by the PostgreSQL server). When a PostgreSQL process reads data from (or writes data to) disk, it always reads (or writes) a block at a time. You can set BLOCK_SIZE by modifying src/include/ pg_config_manual.h and then rebuilding PostgreSQL from source code.

MAX_INDEX_KEYS reflects the maximum number of segments (or keys) allowed in an index. You can set MAX_INDEX_KEYS by modifying src/include/pg_config_manual.h (look for INDEX_MAX_KEYS) and then rebuilding PostgreSQL from source code. The PostgreSQL source code states that FUNC_MAX_ARGS and MAX_INDEX_KEYS must be equal to the same value.

MAX_FUNCTION_ARGS reflects the maximum number of arguments allowed in a function. You can set MAX_FUNCTION_ARGS by modifying src/include/pg_config_manual.h (look for FUNC_MAX_ARGS) and then rebuilding PostgreSQL from source code. The PostgreSQL source code states that FUNC_MAX_ARGS and MAX_INDEX_KEYS must be equal to the same value.

The MAX_IDENTIFIER_LENGTH parameter determines the maximum number of characters PostgreSQL will store when you create a named object (you can create and refer to objects with longer names, but they must be unique within the first MAX_IDENTIFIER_ LENGTH characters). To change this value, modify the NAMEDATALEN symbol in src/include/postgres_ext.h and rebuild PostgreSQL from source code.

If INTEGER_DATETIMES is False, PostgreSQL stores TIMESTAMP, TIME, and INTERVAL values as DOUBLE PRECISION floating point numbers. If INTEGER_DATETIMES is TRue, PostgreSQL stores TIMESTAMP, TIME, and INTERVAL values as BIGINT numbers. See the PostgreSQL reference documentation (section 8.5, "Date/Time Types") for more information regarding the tradeoffs involved. To enable INTEGER_DATETIMES, include the enable-integer-datetimes flag when you configure the PostgreSQL source code.

This parameter reflects the encoding (character set) of the database that you are connected to. See Chapter 22 for more information.

LC_COLLATE determines the order in which string values are sorted (to satisfy an ORDER BY clause or to create an index). LC_COLLATE is consulted when you create a database cluster using the initdb command. See Chapter 22 for more information.

LC_CTYPE is consulted when the server needs to classify a character as uppercase, lowercase, printable, whitespace, and so on. Like LC_COLLATE, LC_CTYPE is recorded in $PGDATA/global/pg_control when you create a database cluster. See Chapter 22 for more information.