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 90 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". Connection- Related ParametersThis 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.
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.
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.
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.
If HOSTNAME_LOOKUP is False , any connection logs that you are gathering will show the IP address of each client. If HOSTNAME_LOOKUP is True , the postmaster will try to resolve the IP address into a host name and will include the hostname in the log if the resolution succeeds. Warning: this can a real performance hog if your name -resolution mechanism is not configured correctly.
If True , this parameter tells PostgreSQL to log the outgoing port number of all client connections. The PostgreSQL Administrator's Manual says that this option is "pretty useless."
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.
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. Operational ParametersThe 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.
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.
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 .
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 algorithms to perform the sort. If the amount of memory required to perform the sort exceeds SORT_MEM KB, PostgreSQL will switch from an in-memory sort to a more expensive, disk-based sort algorithm. You can adjust SORT_MEM on a per-command basis using the command SET SORT_MEM TO maximum _memory .
This parameter determines the maximum amount of memory that will be used by the VACUUM command. You can improve the performance of the VACUUM command, particularly for tables that are frequently modified, by increasing VACUUM_MEM .
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.
This parameter determines how many 16MB log segments are preallocated at each checkpoint. The WAL manager preallocates space to improve performance. If you find that write-ahead log files are being deleted (instead of being recycled), you should increase the value of WAL_FILES .
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 , 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 value ”the 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.
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. Optimizer ParametersThis 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 ).
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 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 1000 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 2500 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. Debugging/Logging ParametersThe 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.
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 HOSTNAME_LOOKUP is True , the client-address will include the client's host name 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_TIMESTAMP is set to True , each message written to the server log will be prefixed with a timestamp. Messages sent to the client will not include the timestamp.
If LOG_PID is set to True , each message written to the server log will be prefixed with the process ID of the server process. Messages sent to the client will not include the process ID.
The DEBUG_LEVEL determines the amount of detail that PostgreSQL produces when inserting debugging messages into the server log. A value of 0 (the default value) tells PostgreSQL not to log debug-related messages. Values greater than 0 increase the amount of debugging information written to the server log.
If DEBUG_PRINT_QUERY is True , PostgreSQL will write the text of every query to the server log.
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.
The SYSLOG parameter determines where server log messages are sent. If SYSLOG is set to 0 (the default value), server log messages are written to the standard output of the terminal that starts the postmaster . You can redirect the postmaster 's standard output stream by including -i filename on the command line. If SYSLOG is set to 1, server log messages are written to the postmaster 's standard output stream and to the OS syslog facility. If SYSLOG is set to 2, server log messages are written to the OS syslog facility. In addition to SYSLOG , there are two other configuration parameters related to the syslog facility: SYSLOG_FACILITY and SYSLOG_IDENT . You can use only the syslog facility if your copy of PostgreSQL was configured with --enable-syslog . See your operating system documentation for more information about the syslog facility.
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 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 StatisticsNext, let's look at the set of configuration parameters that control how PostgreSQL computes and reports performance statistics.
If SHOW_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.
If SHOW_EXECUTOR_STATS is True , the server will write execution statistics to the server log file.
If SHOW_QUERY_STATS is True , the server will write query execution statistics to the server log file.
If SHOW_BTREE_BUILD_STATS is True , the server will write statistics related to building B-Tree indexes to the server log file. You can define this parameter only if the symbol BTREE_BUILD_STATS was defined at the time that your copy of PostgreSQL was built from source code. This parameter is used only by the CREATE INDEX command and is not likely to be useful to most users.
Starting with release 7.2, PostgreSQL can gather on-going, 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. Miscellaneous ParametersFinally, we'll look at the configuration parameters that don't fit well into the other categories.
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.
If AUSTRALIAN_TIMEZONES is True , the time zones CST , EST , and SAT are interpreted as UTC+9.5 (Central Australia Standard Time), UTC+10 (Eastern Australia Standard Time), and UTC+9.5 (Central Australia Standard Time), respectively. If AUSTRALIAN_TIMEZONES is false, CST is interpreted as UTC-6 (Central Standard Time), EST is interpreted as UTC-5 (Eastern Standard 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."
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.
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 defines maximum expression depth that the parser will accept. It is very unlikely that you will ever exceed the default value.
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.
This parameter specifies whether passwords should be stored in encrypted or cleartext form in the absence of a specific choice. See Chapter 21 for more information on password-encryption options.
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. | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
EAN: 2147483647
Pages: 220