From: Tom Lane <tgl@sss.pgh.pa.us>
To: Chao Li <li.evan.chao@gmail.com>
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: Mon, 13 Oct 2025 22:44:45 -0400
Message-ID: <863353.1760409885@sss.pgh.pa.us> (raw)
In-Reply-To: <2E6A307F-BDB9-498F-AC93-B31E0023AD20@gmail.com>
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>
<2E6A307F-BDB9-498F-AC93-B31E0023AD20@gmail.com>
Chao Li <li.evan.chao@gmail.com> writes:
>> On Oct 14, 2025, at 08:36, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> 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.
> 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.
>
> % time pg_dump -Fc -f db.dump evantest
This won't show the effect, because pg_dump will be able to go back
and insert data offsets into the dump's TOC, so pg_restore can just
seek to where the data is. See upthread discussion about what's
needed to provoke Dimitrios' problem.
I tried this very tiny (relatively speaking) test case:
regression=# create database d1;
CREATE DATABASE
regression=# \c d1
You are now connected to database "d1" as user "postgres".
d1=# create table alpha as select repeat(random()::text, 1000) from generate_series(1,1000000);
SELECT 1000000
d1=# create table omega as select 42 as x;
SELECT 1
d1=# \q
Then
$ pg_dump -Fc d1 | cat >d1.dump
$ time pg_restore -f /dev/null -t omega d1.dump
The point of the pipe-to-cat is to reproduce Dimitrios' problem case
with no data offsets in the TOC. Then the restore is doing about the
simplest thing I can think of to make it skip over most of the archive
file. Also, I'm intentionally using the default choice of gzip
because that already responds to DEFAULT_IO_BUFFER_SIZE properly.
(This test is with current HEAD, no patches except adjusting
DEFAULT_IO_BUFFER_SIZE.)
I got these timings:
DEFAULT_IO_BUFFER_SIZE = 1K
real 0m0.020s
user 0m0.002s
sys 0m0.017s
DEFAULT_IO_BUFFER_SIZE = 4K
real 0m0.014s
user 0m0.003s
sys 0m0.011s
DEFAULT_IO_BUFFER_SIZE = 128K
real 0m0.002s
user 0m0.000s
sys 0m0.002s
This test case has only about 50MB worth of compressed data,
so of course the times are very small; scaling it up to
gigabytes would yield more impressive results. But the
effect is clearly visible.
regards, tom lane
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: tgl@sss.pgh.pa.us, li.evan.chao@gmail.com, 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: <863353.1760409885@sss.pgh.pa.us>
* 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