pg.ddx.io  pgsql-hackers@postgresql.org mailing list archive  

help / color / mirror / Atom feed
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 20:36:07 -0400
Message-ID: <845486.1760402167@sss.pgh.pa.us> (raw)
In-Reply-To: <25A25667-AC82-46FD-A5FC-0B2AA0CC7DB9@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>

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.

			regards, tom lane





view thread (47+ messages)  latest in thread

Message-ID: <845486.1760402167@sss.pgh.pa.us>
Permalink:  ../845486.1760402167@sss.pgh.pa.us/
Also on:    postgresql.org/message-id/845486.1760402167@sss.pgh.pa.us

 · 

reply
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: <845486.1760402167@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