From: Tom Lane <tgl@sss.pgh.pa.us>
To: Dimitrios Apostolou <jimis@gmx.net>
cc: pgsql-performance@lists.postgresql.org
Subject: Re: parallel pg_restore blocks on heavy random read I/O on all
 children processes
In-reply-to: <6bd16bdb-aa5e-0512-739d-b84100596035@gmx.net>
References: <6bd16bdb-aa5e-0512-739d-b84100596035@gmx.net>
Comments: In-reply-to Dimitrios Apostolou <jimis@gmx.net>
	message dated "Wed, 19 Mar 2025 19:46:58 +0100"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <1095773.1742498237.1@sss.pgh.pa.us>
Content-Transfer-Encoding: quoted-printable
Date: Thu, 20 Mar 2025 15:17:17 -0400
Message-ID: <1095774.1742498237@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/1095774.1742498237%40sss.pgh.pa.us>
Precedence: bulk

Dimitrios Apostolou <jimis@gmx.net> writes:
> I noticed the weird behaviour that doing a pg_restore of a huge database
> dump, leads to constant read I/O (at about 15K IOPS from the NVMe drive
> that has the dump file) for about one hour. I believe it happens with
> any -j value>=3D2.

> In particular, I get output like the following in the pg_restore log, on=
ly
> a few seconds after running it:

> pg_restore: launching item 12110 TABLE DATA yyy
> pg_restore: processing data for table "public.yyy"
> [ long pause ...]
> pg_restore: finished item 12110 TABLE DATA yyy

I am betting that the problem is that the dump's TOC (table of
contents) lacks offsets to the actual data of the database objects,
and thus the readers have to reconstruct that information by scanning
the dump file.  Normally, pg_dump will back-fill offset data in the
TOC at completion of the dump, but if it's told to write to an
un-seekable output file then it cannot do that.

> And here is the pg_dump command which has created the dump file, execute=
d
> on PostgreSQL 16.

> pg_dump -v --format=3Dcustom --compress=3Dzstd --no-toast-compression  $=
DBNAME   |  $send_to_remote_storage

Yup, writing the output to a pipe would cause that ...

> What do you think causes this? Is it something that can be improved?

I don't see an easy way, and certainly no way that wouldn't involve
redefining the archive format.  Can you write the dump to a local
file rather than piping it immediately?

			regards, tom lane