Date: Thu, 12 Jun 2025 16:51:00 +0200 (CEST)
From: Dimitrios Apostolou <jimis@gmx.net>
To: pgsql-hackers@lists.postgresql.org
Subject: Re: [PATCH v2] parallel pg_restore: move offset-building phase to
 before forking
In-Reply-To: <9bb6c0f7-4e1a-ad5b-655b-2468e42c620f@gmx.net>
Message-ID: <9c9aaf5b-fad4-9124-dad0-f72a092ec641@gmx.net>
References: <b51f7c7a-f31b-f0e1-fc17-5bb4c3057ef5@gmx.net>
 <9bb6c0f7-4e1a-ad5b-655b-2468e42c620f@gmx.net>
MIME-Version: 1.0
Content-Type: text/plain; charset=US-ASCII; format=flowed
UI-OutboundReport: notjunk:1;M01:P0:VRiwqJe9nL8=;KZn5HUTQq/vZxzsHE5U7FNTd5Y6
 GGGraRdK4vXvg1AS3EhPzjwpdpbOJIe4jf5tqLEJIa6jf6l7Nv6FDNOL+elUFbfmsS/rjxZT+
 tEPMGv4Nog6Ub2kWsvLm/gqIq7RLk0oJsl+fROMT+/fSGYqFQGusaEM2aYxMn50QLHpNrscqZ
 /Tb+MTpYOz85ulOX4j/b/lHv5MO3xKsArYjFNk+Y9Giu/zoSFl/b02A52N4nEyPPQV46Azbcg
 wgMT2J92TKUBv0TN/FgOcfOEd9m9htsSbPau67dpnEHiSAY2Si3Kd/EKauUiFuk6glKaa5myI
 1PXXhbZZpTKD+iNSIYzioX1cF9s+sNWvXrufOeBOL65uBkCYlipog0jnOl1jkyjyPZZLkAx7a
 c1k5OR1nARnOUNf7FpAG4HyqVe8FaIgorwS2sosDhMeeWnbvLE7DPm+eKj2GOkzwXtKXexBWq
 LNUqLUGBbJm5pOX6jqRe+VJZorBtlG7f4DXM1U3+1mQMtwqFloft7G0w/HRVHufwywKao2quO
 BiKLPzMw1IRfRCHVVjfQO3CVbbajJt/jw8LFlvn0os/2BVjJ90RskzsOLKXmTNyNiBmNntfrO
 XMcZriSgJq7CaQ/PuCbJyZe3dTqdF8lcGA2aEMi1/pof9zfmqionV3z36OKK3X7/awrmsjqg5
 ylnj18+BROx8Vka83TJ5+P5OjH7e3HwbhBWdsK13ciSHNspKYc1iYQRsaqkhiGgFvZ61lwuQR
 aQ0FtT7XgLxe9akwIUeV3qhpFmyrHXrmSJCFW92m27OCushep+uGO240aVdGfZ0PflElgellD
 kbRk44aUpZngcKncRNf1ByyoZHW5S+MzZJ5VrU0k2G4tx9bwrkmgH5x0qd7N30A+jZOkuBCPz
 tp4TfajiBfp3wiHA5TqAluiJTvHb1CmeXS0GQ0oBoK4p2oZWdjrnFx0/27vamRwy6jY8Jppd3
 fPfAB3IWKyxEmCFGiqeXAgqA2HTnyzSV8Dcp80qqH9ZTBe2tnvmL9tW/U2JzA+Dk4KvUF5s25
 pE3LpQ/qNrGLYnGzDOEjNz2wrPmHJ8xdFiBBoTgubK20svcyb8/g6CCDL0SOUGX2FdvYMm4FK
 ZaoTeQ7yHhuRA//aXV/6IDtVF5t/YFy+tYCHhhuK1lJSttDcLR1gNqA0/IXg7CNYw7Y+0tndz
 cIVxaJIudizIh5dwYETKB3PtnfR/tMupRm+WzrASfnNUmmEGtiCZY0M0LCDSyXUP0UPmddEi0
 MI6N36Rlyx4z4APNHYyOgWRoxCmXj89DranKW2ShBNy35mwi2b+16d32Bic56hC818ODkI8I4
 w4ptxkzwEygjwoAoIE5EN+FDB3adztt+5ry13rXfbW/nMc6UJbqjJ4HO8uRCPoCRjzU7gBeq6
 1zfLcnZOhq85khj+gl91wy0tgaYEghIDHBQ9aCa2XMmRhxSltcYj1+HvBrJX5hul5kTyU/XnG
 airf4VicfakdRpWuCul+e+coCxDZrQpqZIznHi1jNNnNrQT3L89KKW/AazN4DyL4aGZodjTCc
 potOil4qC0U4XfR0tzjSNPI+uNH7D8pQjCpjHRKgx2UMkZxqY9Fup1lK/b6ZDmyBKui8EFmAa
 CCViirzg28Aj7xtnq05+ddD2NcJmlk3puOAaH/rNEDGtuIeskYi7i+aviQkiv9X45XipS7Nw6
 rxt1vGdOkIPJQtGMe3C5zBkw26AFhI6xtnNF1NS6M1pGAslpUiOm7Iklq43L5JRw8C/ZNWrIY
 BMrSqJR2VQy5fDan+DDb2Uz8AYWoaiaKfvvAo1lNuTOXkdODSL3DY9KXDPnD+yQFJKQ6305sM
 OL/E8JfkDZikSUCHDqFqAZC2PbSe3Rj67g6AnesyJOiAcu+HqNwU7R3E99Ji1LG9ecsILyEES
 ejkdRz5uHYqJ232ajFa+8xaRrDtghfdCXYmFi+SXuSAD8jTkP0EVRgLaf024cWYjZ5lA7KM/V
 HIK5QJ8SgeSnVliD6txk9G4k9Z7hmPIZf9Wa8KWE/LC0G+Zl8bC4mVcj86PD/dKUlRrfeN1nR
 pVio89gH5C9gFQu+K6Y+7ZgLPo74xUGO/oSWYno4WLzpOypMUs/gtc1EoP5Z7OwUKBX31q0Rl
 QP2JNepBBjn3tqyGiTelJzgDfo4f0QUs7LIOHizbq5jQZdjjGVRHJDV++Ga9c6zA5xaER27z3
 Nea7OhYCI2krCfSTjoIqjCYpQBjiemtj8cHnrOCleQB8i3si8K9Ano4AJfiPXXWBVx8R4zkyz
 +tXq/y7daJZcobYI23CHPef9k9Eu19p6ObIhEKRvO1iJZlCfO5YxkdwNBtw/Qws6f0gHxHrkN
 kNGyF+E4ONT+3wB8vC1ZaSWqnkGiUkpnJ1OYNFO9O6TPQnPnKDHvXXay+YLKyAXbRteCq5wQp
 aHRBtYZGzJGaqd8dKrOOk8DMeCvcJSwT0rKAuDIUMkJcfUkXZJO9LyDxr5A2Z9lE5umKzW694
 85n7m4Erf4xrA/R6LLNhgWHvbPdg2Q2VaZnp0IXeZO9tsTFK/Wo5MegN2NriMG2FQTTCuX8uu
 phTvZXK4bX2A3WNiD6VvgJf1ax5CEKbRnGY5cMjKyLX04d2OnUcZUbDNI3xpL4xSqxAzytxlc
 7xQiBgSXIkS3EpLlVltPQeUVzER5/yZKGJVFMeTAz+kRQBeafhCx2J77bBOHlkMqmpxOYmiHn
 NLOJ9ZiGfGH4sXE4ndjJaVvjXwuEWsOi2q9PcNSLTAew/qG/buWT/b6uqSRTudndRMVfBfJ+I
 yiW/nkVPMfJjh8ul8u7dvzdn85/17x9eDY5/1HtGYDc+xZbTIOCujcJ8FZZdQw4eXE5+a7/mA
 SQ2XLZZZQArvAZs0gZTeMAwR5xD0ipFfFg8ZEOZE86rk8znxTxFLbWydWyRRDwtTDULEaT3s8
 QxRXdC2n8eka+M6ndVS7yBtW7IXlfVvE9iI0hzEPBsL2/b5yj2M7rJOXfPYw0vTJMecsy/WnR
 rMZzj0R5/Zve05MU/zH2HXHSiGzQQWql3J6/anRFT60i1HQQSHgh5TvpdYYJVrAX0p0Ir4IVR
 I6yVeZ8oOhfuFwAt/7pDcTPEvDcQLz61VX+wwgX3yp08WOFOBQ2VCGCaTAo2zmObiA2XSzsKD
 KoUNipHfWiLbfybz7dB2fXhu/a8f4pJozjMoF4PRXqM4TXdhLHVyYvGZtU9tDz48gT/lSOPe9
 VPgg=
Content-Transfer-Encoding: quoted-printable
Archived-At: 
 <https://www.postgresql.org/message-id/9c9aaf5b-fad4-9124-dad0-f72a092ec641%40gmx.net>
Precedence: bulk

On Tue, 10 Jun 2025, Dimitrios Apostolou wrote:

> Rebased and attached new patch. Should I add it to July's commitfest?

Added to commitfest:
https://commitfest.postgresql.org/patch/5817/


> On Fri, 4 Apr 2025, Dimitrios Apostolou wrote:
>
>>  Hello list,
>>
>>  based on the delays I experienced in pg_restore, as described at:
>>
>>  https://www.postgresql.org/message-id/flat/6bd16bdb-aa5e-0512-739d-b84=
100596035@gmx.net
>>
>>  I noticed that the seeking-reading behaviour was manifested by every o=
ne
>>  of the pg_restore worker processes, in parallel, making the situation =
even
>>  worse. With this patch I moved this phase to the parent process before
>>  fork(), so that the children have the necessary information from birth=
.
>>
>>  Copying the commit message:
>>
>>  A pg_dump custom format archive without offsets in the table of
>>  contents, is usually generated when pg_dump writes to stdout instead o=
f
>>  a file. When doing parallel pg_restore (-j) from such a file, every
>>  worker process was scanning the full archive sequentially, in order to
>>  build the offset table and find the parts assigned to restore. This le=
d
>>  to the worker processes competing for I/O.
>>
>>  This patch moves this offset-table building phase to the parent proces=
s,
>>  before forking the worker processes.
>>
>>  The upside is that we now have only one extra scan of the file.
>>  And this scan happens without other competing I/O, so it completes
>>  faster.
>>
>>  The downside is that there is a delay before spawning the children and
>>  starting assigning jobs to them.
>>=20
>>
>>  What do you think?
>>
>>  Thanks,
>>  Dimitris
>>=20
>