Skip to main content

Configuration of PostgreSQL for Large ABRA Flexi Installations

This article describes our years of experience running PostgreSQL under heavy load and with many users, and serves as a set of recommendations.

Written by Petr Pech

For large installations of the Flexi system, it is essential to configure the PostgreSQL instance in the postgresql.conf file (exact location in Flexi Directory Locations on Your Computer):

  • Increase the number of connections via max_connections (approximately 30 per potential client, and even more when using the REST API or web/mobile access). If you set a value greater than 200, you should also check the shared_buffers value.

  • Increase the number of locks per transaction — at minimum to the number of tables: [Lock Management] max_locks_per_transaction = 512

It is also possible to set a higher number of locks (for example, if a company has not applied updates for a long time, 500 may not be sufficient; however, we have not yet encountered a case where this limit proved inadequate).
​

Windows

  • If upgrading from a version where postgresql.conf was replaced, restore it to the default

  • In the main postgresql.conf, only add a reference to the subdirectory containing additional configuration files (include_dir = 'conf.d')

  • In the conf.d subdirectory, keep your own .conf files. File names follow the format: 0xx_flexi_description.conf (e.g., 051_flexi_main.conf)

  • Postgres loads files in alphabetical order by name, with files loaded later taking precedence. Users should therefore create a file named yxx_anything.conf where y > 0 (e.g., 101_custom.conf) if they wish to override our configuration. Such a file will not be modified during reinstallation.

  • Users should not modify the main postgresql.conf or our .conf files, as these may be overwritten during reinstallation.

On MS Windows, you may encounter issues with more than 125 connections:

PostgreSQL wiki: Cannot run with more than about 125 connections

Useful information for configuration: http://postgres.cz/wiki/Desatero

Under heavy disk load, the following setting can help: checkpoint_segments. The optimal value is 32. The setting is: checkpoint_segments = 32

If you are using ESET NOD32 antivirus on your server, you may encounter additional issues. In this case, excluding the PostgreSQL process or directory from scans, or disabling NOD32, will not help. The only solutions are to uninstall the antivirus or to use the latest version of PostgreSQL.

Linux

  • Addresses the same issues described above, but for Linux systems:

    • In the PostgreSQL configuration, set the option jit = off (this is generally intended as a performance-enhancing setting, but in our case it actually slows down queries)

    • The installation no longer modifies the default database configuration file postgresql.conf (the only addition it may make is the option include_dir = 'conf.d')

    • All important settings are then placed in the conf.d subdirectory in separate files (e.g., 53-jit-off.conf)

Don't forget to configure automatic backups!

Vacuum

By default, the autovacuum process runs automatically. In some cases — for example, if the database grows beyond a certain threshold — manual vacuum may stop working. It is therefore advisable to increase certain parameters.

PGTune

For PostgreSQL configuration, the pgtune tool available at https://pgtune.leopard.in.ua/#/ has proven very useful. The script calculates additional configuration values such as shared_buffers, effective_cache_size, and others based on the specified number of connections.

Did this answer your question?