From: Chao Li <li.evan.chao@gmail.com>
Message-Id: <2E6A307F-BDB9-498F-AC93-B31E0023AD20@gmail.com>
Content-Type: multipart/alternative;
	boundary="Apple-Mail=_1112C3D0-4CDF-4509-B067-1FA8E74BA452"
Mime-Version: 1.0 (Mac OS X Mail 16.0 \(3826.700.81\))
Subject: Re: [PING] [PATCH v2] parallel pg_restore: avoid disk seeks when
 jumping short distance forward
Date: Tue, 14 Oct 2025 09:37:45 +0800
In-Reply-To: <845486.1760402167@sss.pgh.pa.us>
Cc: Dimitrios Apostolou <jimis@gmx.net>,
 Nathan Bossart <nathandbossart@gmail.com>,
 Thomas Munro <thomas.munro@gmail.com>,
 pgsql-hackers@lists.postgresql.org
To: Tom Lane <tgl@sss.pgh.pa.us>
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>
 <25A25667-AC82-46FD-A5FC-0B2AA0CC7DB9@gmail.com>
 <845486.1760402167@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/2E6A307F-BDB9-498F-AC93-B31E0023AD20%40gmail.com>
Precedence: bulk


--Apple-Mail=_1112C3D0-4CDF-4509-B067-1FA8E74BA452
Content-Transfer-Encoding: quoted-printable
Content-Type: text/plain;
	charset=utf-8


> On Oct 14, 2025, at 08:36, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>=20
> Chao Li <li.evan.chao@gmail.com> writes:
>> I tested DEFAULT_IO_BUFFER_SIZE with 4K, 32K, 64K, 128K and 256K. =
Looks like increasing the buffer size doesn=E2=80=99t improve the =
performance significantly. Actually, with the buffer size 64K, 128K and =
256K, the test results are very close. I tested both with lz4 and none =
compression. I am not suggesting tuning the buffer size. These data are =
only for your reference.
>=20
> Yeah, I would not expect straight pg_dump/pg_restore performance
> to vary very much once the buffer size gets above not-too-many KB.
> The thing we are really interested in here is how fast pg_restore
> can skip over unwanted table data in a large archive file, and that
> I believe should be pretty sensitive to block size.
>=20
> You could measure that without getting into the complexities of
> parallel restore if you make a custom-format dump of a few large
> tables that does not have offset data in it, and then seeing how
> fast is selective restore of just the last table.
>=20
> =09

Not sure if I did something wrong, but I still don=E2=80=99t see much =
difference between buffer size 4K and 128K with your suggested test.

I created 3 tables, each with 10 millions of rows:

```
evantest=3D# CREATE TABLE tbl1 AS SELECT generate_series(1,10000000) AS =
id;
SELECT 10000000
evantest=3D# CREATE TABLE tbl2 AS SELECT generate_series(1,10000000) AS =
id;
SELECT 10000000
evantest=3D# CREATE TABLE tbl3 AS SELECT generate_series(1,10000000) AS =
id;
SELECT 10000000
```

And did a custom-format dump:
```
% time pg_dump -Fc -f db.dump evantest
pg_dump -Fc -f db.dump evantest  51.72s user 1.13s system 98% cpu 53.602 =
total
```

Then pg_restore the last tabl,  compiled with buffer size 4k and 128k: =
(I dropped tbl3 before running pg_restore)
```
# 4K =3D=3D=3D
% time pg_restore -d evantest -t tbl3 db.dump
pg_restore -d evantest -t tbl3 db.dump  0.06s user 0.04s system 6% cpu =
1.528 total

# 128K
% time pg_restore -d evantest -t tbl3 db.dump
pg_restore -d evantest -t tbl3 db.dump  0.05s user 0.04s system 3% cpu =
2.146 total
```

The other thing I noticed is that, when I do custom-format dump, if a =
target file exists, pg_dump will just go ahead overwrite the existing =
file; however, when I do directory dump, if a target dir exists, pg_dump =
will fail with an error =E2=80=9Cdirectory xxx is not empty=E2=80=9D. =
Why the behaviors are different?

Best regards,
--
Chao Li (Evan)
HighGo Software Co., Ltd.
https://www.highgo.com/


--Apple-Mail=_1112C3D0-4CDF-4509-B067-1FA8E74BA452
Content-Transfer-Encoding: quoted-printable
Content-Type: text/html;
	charset=utf-8

<html><head><meta http-equiv=3D"content-type" content=3D"text/html; =
charset=3Dutf-8"></head><body style=3D"overflow-wrap: break-word; =
-webkit-nbsp-mode: space; line-break: after-white-space;"><br =
id=3D"lineBreakAtBeginningOfMessage"><div><br><blockquote =
type=3D"cite"><div>On Oct 14, 2025, at 08:36, Tom Lane =
&lt;tgl@sss.pgh.pa.us&gt; wrote:</div><br =
class=3D"Apple-interchange-newline"><div><div>Chao Li =
&lt;li.evan.chao@gmail.com&gt; writes:<br><blockquote type=3D"cite">I =
tested DEFAULT_IO_BUFFER_SIZE with 4K, 32K, 64K, 128K and 256K. Looks =
like increasing the buffer size doesn=E2=80=99t improve the performance =
significantly. Actually, with the buffer size 64K, 128K and 256K, the =
test results are very close. I tested both with lz4 and none =
compression. I am not suggesting tuning the buffer size. These data are =
only for your reference.<br></blockquote><br>Yeah, I would not expect =
straight pg_dump/pg_restore performance<br>to vary very much once the =
buffer size gets above not-too-many KB.<br>The thing we are really =
interested in here is how fast pg_restore<br>can skip over unwanted =
table data in a large archive file, and that<br>I believe should be =
pretty sensitive to block size.<br><br>You could measure that without =
getting into the complexities of<br>parallel restore if you make a =
custom-format dump of a few large<br>tables that does not have offset =
data in it, and then seeing how<br>fast is selective restore of just the =
last table.<br><br><span class=3D"Apple-tab-span" =
style=3D"white-space:pre">	=
</span></div></div></blockquote><br></div><div>Not sure if I did =
something wrong, but I still don=E2=80=99t see much difference between =
buffer size 4K and 128K with your suggested =
test.</div><div><br></div><div>I created 3 tables, each with 10 millions =
of rows:</div><div><br></div><div>```</div><div><div>evantest=3D# CREATE =
TABLE tbl1 AS SELECT generate_series(1,10000000) AS id;</div><div>SELECT =
10000000</div><div>evantest=3D# CREATE TABLE tbl2 AS SELECT =
generate_series(1,10000000) AS id;</div><div>SELECT =
10000000</div><div>evantest=3D# CREATE TABLE tbl3 AS SELECT =
generate_series(1,10000000) AS id;</div><div>SELECT =
10000000</div><div>```</div><div><br></div><div>And did a custom-format =
dump:</div><div>```</div><div><div><div>% time pg_dump -Fc -f db.dump =
evantest</div><div>pg_dump -Fc -f db.dump evantest &nbsp;51.72s user =
1.13s system 98% cpu 53.602 =
total</div></div></div><div>```</div></div><div><br></div><div>Then =
pg_restore the last tabl, &nbsp;compiled with buffer size 4k and 128k: =
(I dropped tbl3 before running pg_restore)</div><div>```</div><div># 4K =
=3D=3D=3D</div><div><div><div>% time pg_restore -d evantest -t tbl3 =
db.dump</div><div>pg_restore -d evantest -t tbl3 db.dump &nbsp;0.06s =
user 0.04s system 6% cpu 1.528 =
total</div></div></div><div><br></div><div># 128K</div><div><div><div>% =
time pg_restore -d evantest -t tbl3 db.dump</div><div>pg_restore -d =
evantest -t tbl3 db.dump &nbsp;0.05s user 0.04s system 3% cpu 2.146 =
total</div></div></div><div>```</div><div><br></div><div>The other thing =
I noticed is that, when I do custom-format dump, if a target file =
exists, pg_dump will just go ahead overwrite the existing file; however, =
when I do directory dump, if a target dir exists, pg_dump will fail with =
an error =E2=80=9Cdirectory xxx is not empty=E2=80=9D. Why the behaviors =
are different?</div><div><br></div><div>Best regards,</div><div>
<div dir=3D"auto" style=3D"caret-color: rgb(0, 0, 0); color: rgb(0, 0, =
0); letter-spacing: normal; text-align: start; text-indent: 0px; =
text-transform: none; white-space: normal; word-spacing: 0px; =
-webkit-text-stroke-width: 0px; text-decoration: none; overflow-wrap: =
break-word; -webkit-nbsp-mode: space; line-break: =
after-white-space;"><div>--</div><div>Chao Li (Evan)<br>HighGo Software =
Co., Ltd.<br>https://www.highgo.com/</div><div><br></div></div><br =
class=3D"Apple-interchange-newline"><br =
class=3D"Apple-interchange-newline">

</div>
<br></body></html>=

--Apple-Mail=_1112C3D0-4CDF-4509-B067-1FA8E74BA452--