PostgreSQL initialization parameters

PostgreSQL configuration parameters are set in the postgresql.conf file. Changes to most of these settings require restarting the PostgreSQL database cluster.

The following are some recommendations for alterations you may want to make in your PostgreSQL to improve its performance with ArcSDE.

Note:

Settings and parameters can vary depending on your operating system. Be sure to read the PostgreSQL documentation for information on the use and setting of these parameters.

max_connections

The default max_connections for PostgreSQL is 100. One ArcGIS client connection equates to two PostgreSQL connections.

When deciding how to set the PostgreSQL max_connections parameter, take into account the number of connections used by the connecting application and how many different client applications could be connected at one time. For example, if one user, utilizing an ArcSDE service connection, connects to the database from ArcMap and ArcCatalog, that's two connections for each of those applications for a total of four connections for that one user. If, in addition, that same user runs a geoprocessing script, that script holds two connections for as long as it takes to execute the script.

Also take into account any Web applications that are connected to the database. For ArcIMS, connections are counted per thread, per spatial server. By default, each type of server uses two threads. Some service types use more than one type of server; therefore, the number of connections is the number of services multiplied by the number of threads. For ArcGIS Server Web applications, connections are counted by service instances; the number of connections is equal to the number of instances that are running. (Be aware that a Web application can use more than one service; therefore, the number of connections is the number of services multiplied by the number of running instances.) For custom applications, you'll need to determine how many connections each application is using and factor that into your estimate of necessary connections.

In addition, the number of connections you set aside for superusers through the super_user_reserved connections PostgreSQL parameter must be taken into consideration. When the number of concurrent connections reaches max_connections minus superuser_reserved_connections, new connections are accepted for superusers. If you keep the default settings for both parameters, that means that once the 97th concurrent connection is made, only superusers can connect using the remaining three connections.

If you alter this parameter value, restart the PostgreSQL database cluster. Also note that if you increase this value, you may also have to increase the shared_buffers value, since its minimum value is dependent on the number of max_connections.

shared_buffers

The shared_buffers parameter sets the amount of memory the PostgreSQL database server uses for shared memory buffers. The default is 32 MB, but may be less if your UNIX kernel settings will not support it. This setting must be 128 KB or greater and at least 16 KB multiplied by the value of the max_connections setting. PostgreSQL documentation indicates that you need to set this parameter significantly higher than the minimum value for good performance. They recommend several tens of megabytes for production installations.

If you alter this parameter, you must restart the database cluster.

max_fsm_pages and max_fsm_relations

These two parameters control the size of the shared free-space map, which tracks the locations of unused space in the database. Max_fsm_pages sets the maximum number of disk pages for which free space will be tracked in the shared free-space map. Six bytes of shared memory are consumed for each page. This setting must be at least 16 times the max_fsm_relations value. The default is chosen by initdb depending on the amount of available memory and can range from 20 KB to 200 KB pages. Max_fsm_relations sets the maximum number of relations for which free space will be tracked in the shared free-space map. The default is one thousand relations. Both these parameters can only be set at server start.

You can execute a database-wide VACUUM VERBOSE command to help you determine whether the current settings are adequate. The last few lines of the VACUUM VERBOSE display messages to help you determine this. A NOTICE message is also printed during this operation if the current settings are too low.

wal_buffers

The wal_buffers parameter specifies the amount of memory used in shared memory for write ahead log (WAL) data. The default is 64 KB. You only need to set this value large enough to hold the amount of WAL data generated by one typical transaction, since the data is written out to disk at every committed transaction. This parameter can only be set at server start.

Note:

Increasing the values of the preceding parameters may cause PostgreSQL to request more System V shared memory than your Linux operating system's default configuration allows. Consult the "Managing Kernel Resources" topic in the PostgreSQL documentation for information on changing the shared memory allocation.