Hardware and performance

Hello,
We are trying to improve the performance of our application and have been testing read only queries connecting to our stand-by DB. Also, we would like to perform ad-hoc DB dumps (via pg_dump) from the stand-by DB.

We have been testing some of the DB parameters in the config file, but see these as well:

      'connecttimeout' => 2, // Set read-only slave connect timeout in seconds. See above.

      'latency' => 0.5,      // Set read-only slave sync latency in seconds.

      'exclude_tables' => [  // Tables to exclude from read-only slave feature.

          'table1',          // Should not be used, unless in rare cases when some area of the system

          'table2',          // is malfunctioning and you still want to use readonly feature.

      ],                     // Then one can exclude offending tables while investigating.

We've noticed an error if a user runs a log report on a course when connected to the stand-by DB (some report logs can take up to 1 min. to complete):

ERROR: canceling statement due to conflict with recovery
Detail: User query might have needed to see row versions that must be removed

We think this table (mdl_logstore_standard_log) is getting updated while running the report, just wondering if it is best practice to add the table name to the exclude_tables parameter. They do keep 2 years worth of logs so there's no way to shorten the time span.

We do get the same error when trying to perform a pg_dump on the stand-by DB.

PostgreSQL version: 15.5
Moodle version: 4.1.8
PHP version: 8.1

PostgreSQL Standby DB setting:

max_standby_archive_delay = 30s
max_standby_streaming_delay = 30s

Please let me know if you need additional info/details...

Much appreciated,
Eugene