PostgreSQL optimization and authorizations

PostgreSQL 9.3.x documentation (English): https://www.postgresql.org/docs/9.3/static/runtime-config-resource.html#RUNTIME-CONFIG-RESOURCE-MEMORY

Main configuration file is postgresql.conf located in PostgreSQL instance data directory.

• Use ProlineAdmin GUI
• Parameters description and optimization

A shortcut “Proline Admin” is available in the Windows Start Menu, under the Proline folder.

Execute the start.sh script located in the folder obtained after Proline Admin GUI archive file extraction.

Click the Configuration button on the application main page. This opens the configuration window. Its first tab (Configuration files) has a section to reference the PostgreSQL data directory. This field must be filled to configure PostgreSQL. Click Apply to save changes and go on in this window.

Note: The data directory is defined when installing PostgreSQL on the machine. This is the folder in which you will find the “postgresql.conf” and the “pg_hba.conf” files.

You can then edit two major files in PostgreSQL configuration.

Warning: Many changes will be effective only after a restart of the PostgreSQL service, which is not yet handled by ProlineAdmin GUI (needs to be done manually).

The pg_hba.conf file lists the IP addresses that are allowed to connect to your databases. You will often have to edit this file to first set up Proline. For example, you may have to allow your own machine to establish connection, or a set of machines working together…

Select the PostgreSQL authorizations tab to easily edit this file.

The first section allows you to change the PostreSQL data directory. It is synchronized with the Configuration files and the PostreSQL optimization tabs.

The icon near the 'Database' field opens a window to help you design the database name. As shown in the screenshot above, keeping the mouse over a column name for a few seconds will display a tooltip to help you understand how to create a new connection.

Note : pg_ha.conf file also accepts some keywords for the 'adress' field (in addition to the IP/CIDR mode). However, ProlineAdmin GUI is not yet able to handle these. If such lines are read within pg_hba.conf, the 'IP adress' and 'IPs count' fields will be left blank, and must be filled with the appropriate IP adress/IPs count pair to be registered.

Note: for advanced users, there is more documentation inside the pg_hba.conf file (located in the PostgreSQL data directory).

Click Apply to save changes and go on in this window ; click OK to save changes and go back to the main view ; or just go to another tab without saving changes for now.

Click 'Edit postgresl.conf' to easily edit this file. The easiest way to use it is to simply click on Set all to optimized value, then Apply.

The first section allows you to change the PostreSQL data directory. It is synchronized with the Configuration files and the PostreSQL authorizations tabs.

Each line under this block corresponds to a tuneable parameter. If the first box of the line isn't checked, then it will be commented in the configuration file (not taken into account). You can use either the sliders or the fields and boxes to change the values. Keeping the mouse over a parameter name for a few seconds will display a tooltip to help you understand what is this parameter about.

As you can see, at the top of the modal window stand 2 buttons:

• 'Set all to optimized value': compute, for each parameter, the most appropriate value, given your machine properties and the application (Proline's usage);
• 'Set all to default value': set default value (compute it if needed) for each parameter.

Click Apply to save changes and go on in this window ; click OK to save changes and go back to the main view ; or just go to another tab without saving changes for now.

Following recommended memory sizes are given for a server with 16 GiB of physical memory and about 8 GiB dedicated for the PostgreSQL instance.

• max_connections: Number of concurrent SQL sessions (each Proline Server task can use 1 to 5 SQL sessions, each Proline-Studio instance can use some SQL sessions).

Default value 100

• tcp_keepalives_idle: Number of seconds before sending a keepalive packet on an otherwise idle TCP connection. Help with broken router / firewall and checking for dead peers.

Default value 0 (2 hours) → 300 (5 minutes)

• shared_buffers Use about 1/4 of physical memory dedicated to the PostgreSQL instance.

Default value 32MB → 2048MB

• checkpoint_segments = Use (shared_buffers / 16) ; max. 64 or 256 for write-heavy bulk loading.

Default value 3 → 128

• checkpoint_completion_target = 0.9 for high value of checkpoint_segments.

Default value 0.5 → 0.9

• temp_buffers Per session Used for temporary tables creation.

Default value 8MB → 512MB

• work_mem = Several per session Used for hashing, sorting and IN operator when processing queries.

Default value 1MB → 4MB to 64MB

• maintenance_work_mem Used for intial index creation and VACUUM operations.

Default value 16MB → 1024MB

• effective_cache_size Assumption about the effective size of the disk cache to optimize index use (Monitor physical memory allocated by system to disk cache operations).

Default value 128MB → 4096MB