Adjust the batch size of export of relations with numerous nested associations
Problem
Related to https://gitlab.com/gitlab-org/manage/import/import-export-performance/-/issues/4+. Root cause and solution described in https://gitlab.com/gitlab-org/manage/import/import-export-performance/-/issues/4#note_1766921962.
On exporting project, customer got error PG::QueryCanceled: ERROR: canceling statement due to statement timeout
The problem could be caused by the number of other SQLs executed on the same connection.
Reducing the BATCH_SIZE from 100 to a lower number would solve the problem as less SQLs would be executed.
In the past, to fix #212355 (closed), which was a similar problem, a feature flag was added to reduce the batch size, but the feature flag was removed, and the batch size was returned to a higher value.
Possible solution
In order to address the issue, we can adjust the batch size based on the exported relation. For instance, for merge requests and CI relations that have numerous nested associations, we can use a smaller batch size, while for other relations, we can use a larger batch size.