From: Chao Li <li.evan.chao@gmail.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Dimitrios Apostolou <jimis@gmx.net>
Cc: Nathan Bossart <nathandbossart@gmail.com>
Cc: Thomas Munro <thomas.munro@gmail.com>
Cc: pgsql-hackers@lists.postgresql.org
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
Message-ID: <2E6A307F-BDB9-498F-AC93-B31E0023AD20@gmail.com> (raw)
In-Reply-To: <845486.1760402167@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>
> On Oct 14, 2025, at 08:36, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> 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’t 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.
>
> 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.
>
> 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.
>
>
Not sure if I did something wrong, but I still don’t see much difference between buffer size 4K and 128K with your suggested test.
I created 3 tables, each with 10 millions of rows:
```
evantest=# CREATE TABLE tbl1 AS SELECT generate_series(1,10000000) AS id;
SELECT 10000000
evantest=# CREATE TABLE tbl2 AS SELECT generate_series(1,10000000) AS id;
SELECT 10000000
evantest=# 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 ===
% 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 “directory xxx is not empty”. Why the behaviors are different?
Best regards,
--
Chao Li (Evan)
HighGo Software Co., Ltd.
https://www.highgo.com/
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-hackers@postgresql.org
Cc: li.evan.chao@gmail.com, tgl@sss.pgh.pa.us, jimis@gmx.net, nathandbossart@gmail.com, thomas.munro@gmail.com, pgsql-hackers@lists.postgresql.org
Subject: Re: [PING] [PATCH v2] parallel pg_restore: avoid disk seeks when jumping short distance forward
In-Reply-To: <2E6A307F-BDB9-498F-AC93-B31E0023AD20@gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox