Tuning connection-related parameters

In this recipe, we will talk about tuning the connection-related server parameters.

How to do it...

The following are the connection-related parameters that usually require tuning:

• listen_addresses
• port
• max_connections
• superuser_reserved_connections
• unix_socket_directory
• unix_socket_permissions

These parameters can be set to appropriate values in the postgresql.conf file and these parameter can be set at server start. The changes made to these parameters will only come into effect once the PostgreSQL server is restarted, either using the restart mode of the pg_ctl command or using the stop mode and then followed by the start mode of the pg_ctl command. This can be done as follows:

pg_ctl -D $PGDATA restart

Here, $PGDATA refers to the environment variable that refers to the data directory location of the PostgreSQL server.

How it works...

Here, we will talk about the connection-related parameters discussed in the preceding section:

• listen_addresses: The standard behavior of PostgreSQL is to respond to connections from localhost. This default behavior usually needs alterations to allow the PostgreSQL server to be accessed via the standard TCP/IP protocol widely used in client-server communications. The de facto practice is to set this value to * to allow the PostgreSQL server to listen to all incoming connections. Otherwise, we need to mention a comma-separated list of IP addresses where PostgreSQL needs to be responded. This gives an additional security control over reaching the database instance directly from any IP address.
• port: This parameter defines the port on which the PostgreSQL server will listen for incoming client connection requests. The default value of this parameter is set to 5432. If there are multiple PostgreSQL instances running on the same server, the value of the port parameter for every PostgreSQL instance needs to be altered to avoid port conflicts.
• max_connections: This parameter governs the maximum number of concurrent client connections that the server can support. This is a very important parameter setting to other parameters such as work_mem that are dependent on this because memory resources can be allocated on a per-client basis, so the maximum number of clients in effect suggests the maximum possible memory use. The default value of this parameter is 100. If the requirement is to support thousands of client connections, connection pooling software should be used to reduce the connection overhead. On a Linux server with 8 GB of RAM, a value of 140 is a good start. Increasing the value of this parameter will cause the PostgreSQL server to request more shared memory from the operating system.
• superuser_reserved_connections: This parameter governs the number of connection slots reserved for superusers. The default value of this parameter is set to 3. This parameter can only be set at server restart.
• unix_socket_directory: This parameter defines the directory to be used for the Unix socket connections to the server. The default location is /tmp, but it can be changed at build time. This parameter comes into effect during server start. This parameter is irrelevant on Windows, which does not have socket connections.
• unix_socket_permissions: This parameter defines the access permissions of the Unix domain socket. The default value is 0777, which means anyone can connect using socket connections. This parameter value can be set only at server start.