Re: WIP/PoC for parallel backup

As far I understand, parallel backup is not a mandatory performance feature, rather, one at user's discretion. This IMHO indicates that it will benefit some users and it may not others.

Taking a backup is an I/O intensive workload. So by parallelizing it through multiple worker threads/processes, creates an overhead of its own. So what precisely are we optimizing here. Looking at a running database system in any environment, I see the following potential scenarios playing out. These are probably clear to everyone here, but I'm listing these for completeness and clarity.

Locally Running Backup:
(1) Server has no clients connected other than base backup.
(2) Server has other clients connected which are actively performing operations causing disk I/O.

Remotely Running Backup:
(3) Server has no clients connected other than remote base backup.
(4) Server has other clients connected which are actively performing operations causing disk I/O.

Others:
(5) Server or the system running base backup has other processes competing for disk or network bandwidth.

Generally speaking, I see that parallelization could potentially benefit in scenarios (2), (4) and (5) with the reason being that having more than one thread increases the likelihood that backup will now get a bigger time slice for disk I/O and network bandwidth. With (1) and (3), since there are no competing processes, addition of multiple threads or processes will only increase CPU overhead whilst still getting the same network and disk time slice. In this particular case, the performance will degrade.

IMHO, that’s why by adding other load on the server, perhaps by running pgbench simultaneously may show improved performance for parallel backup. Also, running parallel backup on a local laptop more often than yields improved performance.

There are obviously other factors that may impact the performance like the type of I/O scheduler being used whether CFQ or some other.

IMHO, parallel backup has obvious performance benefits, but we need to ensure that users understand that there is potential for slower backup if there is no competition for resources.