Date: Thu, 20 Mar 2025 20:57:45 +0100 (CET)
From: Dimitrios Apostolou <jimis@gmx.net>
To: Tom Lane <tgl@sss.pgh.pa.us>
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: <1095774.1742498237@sss.pgh.pa.us>
Message-ID: <e11dd37a-3409-175e-040d-c773838b7934@gmx.net>
References: <6bd16bdb-aa5e-0512-739d-b84100596035@gmx.net>
 <1095774.1742498237@sss.pgh.pa.us>
MIME-Version: 1.0
Content-Type: text/plain; charset=US-ASCII; format=flowed
UI-OutboundReport: notjunk:1;M01:P0:SVFiuM1F1hc=;gbCgS712wTalyw3jsTAW75gcADM
 2/qBaFPHHHTnjk4LrDuGB+6xr1yrEYftVE16Dbf8LZz6g+qUtXXfbZ+anxURRgoUbDzzZZsdk
 IMHJ5ZzUNaMzEep0BEhkC8+JjzX0Z4/e6qTvJFK9j/Hq1WPOgnqMlYyFoOxA4G5z5XPcjr2EI
 V/z3IWlFT9HAcfx1GKguKjfPP8IKOgMeX4+ETzH9iHNC/NEdW+n8ITPN2W0JWFNGQNODrhF71
 7/bs/nEIdK5YQPra7q/qXyX02QM6ro7rxRslYaMgDjHeeumeSQPjvf2E7KR/NsYCFCsYPuudp
 njpBBOPTc5/Dga77tMYJh1x4g1M82ErtnrxOAgDPwxKr5b7YPXSFsq4/gAy53eFPeXdx6XeN/
 5e/NV9pO6LxC1RP7jGYrjRug9l/eQqW/F4Ce8ploU9dWVItRE37jPIz5EgLA/iYj5az9VbQqT
 ICRE3kMNfipZYfsr2V4kUC3l9zsuqsWwr2ptNdl04fwoE5h5Amab9YLidsCMPUQkIHDA7bstC
 NGvMsyJX9Fd4iWZxgX1C2QZlZ2NOiCBkJoV/c5nkWZFmG1IfVWJMZ+4LFWVE8LyEiGyg+zJXA
 Qtpd8K3jr+mYlYtMAHbe3B0TgwgFEtjSEOXu3iRCrJuH4HCVh+KhSsAevYah3K6hRiWEdc3Gr
 +9kWfhYGyQUtgzRRoqR1XD+cD1ce+Vrbau3RGHbbnO4DyKDkZazJy7zWFi/JEV2GUdZ3STCEK
 z5AFxGNd5US7JvtfE3x7GUbExtqCCcXPNxXoytISQz0CaHz8NxPA2giez1zTWTQQYBiJCPcUb
 chZcbK2s1hbdbag6P2jk/0q0qQnygePg0cp4zyKdOYWFw2LlmnBkPdIeT+145pXjwqKg/nQiu
 MzZijgXQOaJO5p2T5hKAUl1n0lgTjQBmf2q9HA92G3B33xMUYiT/gsV8CqrMlE4tEkdtOmlgc
 PzeNmOOe4jeQpEAjgkIeB78CDMJvVkF3PwncavwYhyPXGWXDH4suMjOK+MJ+aE0URIRFY6of7
 DGaXPbrw447P25McMBW3kPElrhdGp4dljBV7Od1YCw1fNZl2izfrePZZDchyTWEFwgrRN/beV
 ctPQCFFDnjAhdJqW9vU3JGXVAww7oH67blVrFI9fAVJw8kERKULAem6FCfXkN45Xwq4WI63/m
 UIKaDHZ1KyUlFX1i6OXX30NXlPtDOkHk7V632X5grPl60Tjq6FMGJ3drmSOTQm5jGMO33WXYn
 LglxZDpR76GTFa8YEhJVK9xtLl2yrZ2HcDKw9ey3xznad+Z2qNSVJj05T93gDQYcg4auPlY5d
 /vomWyELb13zHEcFkS9HCG2GloL5otXSgmi93oMt98WSP3wXHQb1I4sVSH16vFgFYrRNBvUNT
 bujd/teFBaULxMbHHtsNJS1GuBk3Pu4epQ8ZVIAqq+wkDl9x5uVRpNLkrwtZhkTqdR+25JYSZ
 JvhZsjFQf8IW+094PdHxdG4cvrGo=
Content-Transfer-Encoding: quoted-printable
Archived-At: 
 <https://www.postgresql.org/message-id/e11dd37a-3409-175e-040d-c773838b7934%40gmx.net>
Precedence: bulk

On Thu, 20 Mar 2025, Tom Lane wrote:

> 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.

Thanks Tom, this makes sense! As you noticed, I'm piping the output, and
this was a conscious choice.

> 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?

Unfortunately I don't have enough space for that. I'm still testing, but
the way this is designed to work is to take an uncompressed pg_dump
(unlike the above which was compressed for testing purposes) and send it
to a backup server having its own deduplication and compression.

Further questions:

* Does the same happen in an uncompressed dump? Or maybe the offsets are
   pre-filled because they are predictable without compression?

* Should pg_dump print some warning for generating a lower quality format?

* The seeking pattern in pg_restore seems non-sensical to me: reading 4K,
   jumping 8-12K, repeat for the whole file? Consuming 15K IOPS for an
   hour. /Maybe/ something to improve there... Where can I read more about
   the format?

* Why doesn't it happen in single-process pg_restore?


Thank you!
Dimitris