MIME-Version: 1.0
References: <84379bfb-bbda-84e6-cacc-e863ba9d6c37@gmx.net>
In-Reply-To: <84379bfb-bbda-84e6-cacc-e863ba9d6c37@gmx.net>
From: Ron Johnson <ronljohnsonjr@gmail.com>
Date: Fri, 4 Apr 2025 12:45:25 -0400
Message-ID: <CANzqJaD0+Mex1tCj6i0SbQK0Z3urqYYN5GUC=7PntGe4DNVgtQ@mail.gmail.com>
Subject: Re: Performance issues during pg_restore -j with big partitioned table
To: pgsql-general <pgsql-general@postgresql.org>
Content-Type: multipart/alternative; boundary="000000000000ac3e6b0631f69de8"
Archived-At: <https://www.postgresql.org/message-id/CANzqJaD0%2BMex1tCj6i0SbQK0Z3urqYYN5GUC%3D7PntGe4DNVgtQ%40mail.gmail.com>
Precedence: bulk

--000000000000ac3e6b0631f69de8
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

On Wed, Apr 2, 2025 at 1:32=E2=80=AFPM Dimitrios Apostolou <jimis@gmx.net> =
wrote:

> Hello list.
>
> My database includes one table with 1000 partitions, all of them rather
> sizeable. I run:
>
>    pg_restore -j12 --no-tablespaces --disable-triggers --exit-on-error
> --no-owner --no-privileges -n public -d newdb custom_format_dump.pgdump
>
> Right now after 24h of restore, I notice weird behaviour, so I have
> several questions about it:
>
> + 11 postgres backend processes are sleeping as "TRUNCATE TABLE waiting".
>    I see that they are waiting to issue a TRUNCATE for one of the
>    partitions and then COPY data to it.  Checking the log I see that
>    several partitions have already been copied finished, but many more
>    are left to start.
>
>    Why is a TRUNCATE needed at the start of a partition's COPY phase? I
>    didn't issue a --clean on the command line (I don't need it as my
>    database is newly created), and I don't see a mention of related
>    TRUNCATE in the pg_restore manual.
>

TRUNCATE statements inside of "toc.dat" files?  I'm skeptical.

Are you maybe doing something else in that database besides pg_restore?

--=20
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> lobster!

--000000000000ac3e6b0631f69de8
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div dir=3D"ltr">On Wed, Apr 2, 2025 at 1:32=E2=80=AFPM Di=
mitrios Apostolou &lt;<a href=3D"mailto:jimis@gmx.net">jimis@gmx.net</a>&gt=
; wrote:</div><div class=3D"gmail_quote gmail_quote_container"><blockquote =
class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px sol=
id rgb(204,204,204);padding-left:1ex">Hello list.<br>
<br>
My database includes one table with 1000 partitions, all of them rather<br>
sizeable. I run:<br>
<br>
=C2=A0 =C2=A0pg_restore -j12 --no-tablespaces --disable-triggers --exit-on-=
error --no-owner --no-privileges -n public -d newdb custom_format_dump.pgdu=
mp<br>
<br>
Right now after 24h of restore, I notice weird behaviour, so I have<br>
several questions about it:<br>
<br>
+ 11 postgres backend processes are sleeping as &quot;TRUNCATE TABLE waitin=
g&quot;.<br>
=C2=A0 =C2=A0I see that they are waiting to issue a TRUNCATE for one of the=
<br>
=C2=A0 =C2=A0partitions and then COPY data to it.=C2=A0 Checking the log I =
see that<br>
=C2=A0 =C2=A0several partitions have already been copied finished, but many=
 more<br>
=C2=A0 =C2=A0are left to start.<br>
<br>
=C2=A0 =C2=A0Why is a TRUNCATE needed at the start of a partition&#39;s COP=
Y phase? I<br>
=C2=A0 =C2=A0didn&#39;t issue a --clean on the command line (I don&#39;t ne=
ed it as my<br>
=C2=A0 =C2=A0database is newly created), and I don&#39;t see a mention of r=
elated<br>
=C2=A0 =C2=A0TRUNCATE in the pg_restore manual.<br></blockquote><div><br></=
div><div></div></div><div>TRUNCATE statements inside of &quot;toc.dat&quot;=
 files?=C2=A0 I&#39;m skeptical.</div><div><br></div><div>Are you maybe doi=
ng something else in that database besides pg_restore?</div><div><br></div>=
<span class=3D"gmail_signature_prefix">-- </span><br><div dir=3D"ltr" class=
=3D"gmail_signature"><div dir=3D"ltr">Death to &lt;Redacted&gt;, and butter=
 sauce.<div>Don&#39;t boil me, I&#39;m still alive.<br><div><div>&lt;Redact=
ed&gt; lobster!</div></div></div></div></div></div>

--000000000000ac3e6b0631f69de8--