From: Tom Lane <tgl@sss.pgh.pa.us>
To: Dimitrios Apostolou <jimis@gmx.net>
cc: Chao Li <li.evan.chao@gmail.com>,
        Nathan Bossart <nathandbossart@gmail.com>,
        Thomas Munro <thomas.munro@gmail.com>,
        pgsql-hackers@lists.postgresql.org
Subject: Re: [PATCH v4] parallel pg_restore: avoid disk seeks when jumping
 short distance forward
In-reply-to: <9o35ps41-040p-n471-qoos-6q8648594768@tzk.arg>
References: <2edb7a57-b225-3b23-a680-62ba90658fec@gmx.net>
 <a7344d8f-927c-1f97-6cc0-ac630deb98c4@gmx.net>
 <c0e531f9-accc-3a93-e9e6-2cec1471f9e9@gmx.net>
 <1282067.1744651649@sss.pgh.pa.us>
 <c6bb93c1-4042-743c-096c-27c5e7c73347@gmx.net>
 <f7a23708-c336-c189-f155-4b3197bf6ea0@gmx.net> <aEioDuePEBRnfJYk@nathan>
 <5ed51587-5a24-4d79-e36e-927620d0e3a2@gmx.net> <aEygf8AchBk04gOA@nathan>
 <f99e9774-da49-60f0-20e0-b97d9f12a04e@gmx.net>
 <72d2c81b-a0fe-3d99-9dd4-0d771e6e673e@gmx.net>
 <1po8os49-r63o-2923-p37n-12698o1qn7p0@tzk.arg>
 <3515357.1760128017@sss.pgh.pa.us> <3698234.1760149336@sss.pgh.pa.us>
 <3686.1760232320@sss.pgh.pa.us>
 <CBF70398-279C-4BA7-9FA1-2879A49F4EC9@gmail.com>
 <717335.1760389672@sss.pgh.pa.us> <1306492.1760556110@sss.pgh.pa.us>
 <1518975.1760634070@sss.pgh.pa.us>
 <9opr64ps-625r-667n-q19o-op35rs414n59@tzk.arg>
 <375562.1760995276@sss.pgh.pa.us> <381756.1760998513@sss.pgh.pa.us>
 <9o35ps41-040p-n471-qoos-6q8648594768@tzk.arg>
Comments: In-reply-to Dimitrios Apostolou <jimis@gmx.net>
	message dated "Tue, 21 Oct 2025 16:16:26 +0200"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <526971.1761068213.1@sss.pgh.pa.us>
Date: Tue, 21 Oct 2025 13:36:53 -0400
Message-ID: <526972.1761068213@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/526972.1761068213%40sss.pgh.pa.us>
Precedence: bulk

Dimitrios Apostolou <jimis@gmx.net> writes:
> On Tuesday 2025-10-21 00:15, Tom Lane wrote:
>> Not sure where we go from here, but clearly a bunch of research
>> is going to be needed to decide whether this is committable.

> pg_dump files from before your latest fix still exist, and they possibly 
> contain block header every 30 bytes (or however wide is the table rows). 
> A patch in pg_restore would vastly improve this use case.

Yes, that's a fair case to worry about.

> May I suggest the attached patch, which replaces fseeko() with fread() 
> if the distance is 32KB or less? Sounds rather improbable that this 
> would make things worse, but maybe it's possible to generate a dump file 
> with 32KB wide rows, and try restoring on various hardware?
> If this too is controversial, then we can reduce the number to 4KB. This 
> is the buffering that glibc does internally. By using the same in the 
> given patch, we avoid all the lseek(same-offset) repetitions between the 
> 4K reads. This should be a strict gain, with no downsides.

I spent some time strace'ing pg_restore on older dump files with
relatively small block sizes.  You are right that glibc seems quite
stupid about this: it appears to issue a kernel lseek() call for every
fseeko(), even though it keeps using the same 4K worth of data it had
previously read in.  I thought maybe that was an artifact of the
relatively old glibc in RHEL8, but glibc 2.40 from Fedora 41 is no
better.  I also checked current macOS, and it's marginally smarter:
it issues just one seek call per read call.  But that seek is still
useless, since the read is still 4K adjacent to the previous 4K.

(I wonder if maybe there is some POSIX standards compliance issue
involved here?  We don't care about the possibility that the disk
file is changing under us, but other applications do, and maybe
the kernel-visible seek calls are required for some reason.)

Putting in a minimum-block-size-to-seek check gets rid of the seek
calls entirely, producing a straight stream of reads, on all three
platforms.  I agree that doing this with a threshold of 4K seems
like a no-brainer, since that seems to be the common default stdio
buffer size.  Using a larger threshold, or setting up a larger buffer,
seems to risk platform-dependent results, so it would require more
performance testing than I care to do now.  Let's do 4K and call it
a day.

			regards, tom lane