For large installations of the Flexi system, it is necessary to configure the PostgreSQL instance in the postgresql.conf file (exact location in Flexi directory locations on your computer):
Increase the number of connections max_connections (approximately 30 per potential client, even more when using the REST API or web/mobile access). If you set a value greater than 200, you also need to 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 possible to set even more 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 anyone for whom this number was not enough).
Windows
If we are updating from a version where we replaced
postgresql.conf, we restore the default value thereIn the main
postgresql.confwe only add a reference to the subfolder containing additional configuration files (include_dir = 'conf.d')In the
conf.dsubfolder we keep our own.conffiles. The file names follow the format:0xx_flexi_popis.conf(e.g.051_flexi_main.conf)Postgres loads files in alphabetical order by name, and files loaded later take precedence. Therefore, users should create a file named
yxx_cokoliv.confwhere y> 0(e.g.:101_custom.conf) if needed. This allows them 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 they may be overwritten during reinstallation.
On MS Windows, you may encounter issues with more than 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, removing the PostgreSQL process or directory from scans, or even disabling NOD32, will not help. The only solutions are to uninstall it or to use the latest version of PostgreSQL.
Linux
This addresses the same issues described above, but on Linux systems:
in the PostgreSQL configuration, we set the
jit = offoption (this is generally considered a performance-improving setting, but in our case it actually slows down queries)the installation no longer modifies the default database configuration file
postgresql.conf(the only thing it may add is theinclude_dir = 'conf.d'option)all important settings are then placed in the
conf.dsubdirectory in separate files (e.g.53-jit-off.conf)
Don't forget to set up automatic backups!
Vacuum
By default, the autovacuum process runs automatically. In some cases, for example when 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 at https://pgtune.leopard.in.ua/#/ has proven effective. The script calculates additional configuration values such as shared_buffers, effective_cache_size, etc., based on the configured number of connections.