To make pg_dump and pg_restore parallel in processing limited number of LOs

Hi team,

Hope you are all doing well.

Recently I have encountered a scenario that a user need to dump/restore a database with 1k ~ 2k large objects. In both dump and restore process, parallel mode was used with 10 processes. However, in both dump and restore processes, it seems only 1 process was used for LO.

Checking further on this issue, it seems that from PostgreSQL 17, there was a change on function getLOs. In function getLOs, blob information would be ordered by owner/ACL. For each same owner/ACL appear pair, we will group them in a ArchiveEntry for every MAX_BLOBS_PER_ARCHIVE_ENTRY entry. This brings parallelism in dump/restore if a user has millions of LOs.

However, if a user only has a limited number of LOs, like 1k, which seems sensible as LOs should be large. In this scenario, there would be only 1 process work. Therefore, I'm proposing a change. Instead of using a fixed number to group LOs with same owner/ACL pair, we can use a SQL query to distribute each pair into a fixed number of batches. For each batch, it would be assigned an ArchiveEntry. So, the workload for each pair could be distributed into processes even if there are only few numbers of LO.

For the fixed batch number, 50 seems to be a sensible value, as normal user would not use more threads than 50 in a dump/restore process.

I have tested in a cloud VM. For a database with 600 LOs (24GB), before this patch, it would take
kaifan(at)kaifanvm:~/test$ time pg_dump -Fd -d test -f dump2 -j 10
real 23m38.274s
user 12m24.477s
sys 0m50.456s

After the patch, it would take
kaifan(at)kaifanvm:~/test$ time pg_dump -Fd -d test -f dump3 -j 10
real 7m50.295s
user 16m55.940s
sys 1m12.640s

As multiple processes are used. May I know if you have any thought on this?

Kai Fan