[PATCH v4] parallel pg_restore: avoid disk seeks when jumping short distance forward

On Thursday 2025-10-16 19:01, Tom Lane wrote:

>> I think this is more or less committable, and then we could get
>> back to the original question of whether it's worth tweaking
>> pg_restore's seek-vs-scan behavior.
>
> And done. Dimitrios, could you re-do your testing against current
> HEAD, and see if there's still a benefit to tweaking pg_restore's
> seek-vs-read decisions, and if so what's the best number?

Sorry for the delay, I hadn't realized a needed to generate a new
database dump using the current HEAD. So I did that, using
--compress=none and storing it on compressed btrfs filesystem, since
that's my primary use case.

I notice that things have improved immensely!
Using the test you suggested (see NOTE1):

pg_restore -t last_table -f /dev/null huge.pg_dump

1. The strace output is much more reasonable now; basically it's
repeating the pattern

read(4k)
lseek(~128k forward)

As a reminder, with old archives it was repeating the pattern:

read(4k)
lseek(4k forward)
lseek(same offset as above) x ~80 times

2. The IO speed is better than before:

On my 20TB HDD I get 30-50 MB/s read rate.

With old archives I get 10-20 MB/s read rate.

3. Time to complete: ~25 min

4. CPU usage is low. With old archives the pg_restore process shows
high *system* CPU (because of the amount of syscalls).

I can't really compare the actual runtime between old and new dump,
because the two dumps are very different. But I have no doubt the new
dump is several times faster to seek through.

NOTE1: My original testcase was

pg_restore -t last_table -j $NCPU -d testdb

This testcase does not show as big improvement,
because every single of the parallel workers is
concurrently seeking through the dump file.

*** All above was measured from master branch HEAD **
277dec6514728e2d0d87c1279dd5e0afbf897428
Don't rely on zlib's gzgetc() macro.

*** Below I have applied attached patch ***

Regarding the attached patch (rebased and edited commit message), it
basically replaces seek(up to 1MB forward) with read(). The 1MB number
comes a bit out of the top of my head. But tweaking it between 128KB and
1MB wouldn't really change anything, given that the block size is now
128KB: The read() will always be chosen against the seek(). Do you know
of a real-world case with block sizes >128KB?

Anyway I tried it with the new archive from above.

1. strace output is a loop of the following:

read(4k)
read(~128k)

2. Read rate is between 150-250MB/s basically max that the HDD can give.

3. Time to complete: ~5 min

4. CPU usage: HIGH (63%), most likely because of the sheer amount
of data it's parsing.

Regards,
Dimitris