Re: Performance issues during pg_restore -j with big partitioned table

On Wed, Apr 2, 2025 at 1:32 PM Dimitrios Apostolou <jimis(at)gmx(dot)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?

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