Appendix I. Configuring Postgres Pro for 1C Solutions

You can install and use Postgres Pro with 1C solutions in a client/server model. For optimal performance and stability, modify the following settings in the postgresql.conf configuration file of Postgres Pro server:

  1. Increase the maximum number of allowed concurrent connections to the database server, up to 1000 connections. 1C solutions can open a large number of connections, even if not all of them are used, so it is recommended to allow not less than 500 connections.

     max_connections = 1000 

  2. To ensure that temporary tables are handled correctly, modify the following parameters:

    • Increase the buffer size for temporary tables:

       temp_buffers = 32MB 

    • Increase the number of allowed locks of tables or indexes per transaction to 256:

       max_locks_per_transaction = 256 

      Typically, 1C solutions use a lot of temporary tables. Every backend process usually contains multiple temporary tables. When closing a connection, Postgres Pro tries to drop all temporary tables in a single transaction, so this transaction may use a lot of locks. If the number of locks exceeds the max_locks_per_transaction value, the transaction will fail, leaving multiple orphaned temporary tables.

  3. Enable backslash escapes in all strings, and switch off the warning about using the backslash escape symbol:

     standard_conforming_strings = off escape_string_warning = off 

  4. Set the effective_cache_size parameter to at least half of RAM available on the system. Postgres Pro query optimizer performance depends on the amount of allocated RAM.

  5. Optimize query planning using online_analyze and plantuner extensions, as follows:

    • Add online_analyze and plantuner to the shared_preload_libraries variable.

       shared_preload_libraries = 'online_analyze, plantuner' 
    • Enable automatic analysis of temporary tables when they are modified:

       online_analyze.table_type = 'temporary' 
    • Tune Postgres Pro optimizer to improve planning for recently created empty tables:

       plantuner.fix_empty_table = 'on' 
    • Suppress detailed messages from the online_analyze extension:

       online_analyze.verbose = 'off' 
close