Performance issues during pg_restore -j with big partitioned table

From: Dimitrios Apostolou <jimis(at)gmx(dot)net>
To: pgsql-general(at)lists(dot)postgresql(dot)org
Subject: Performance issues during pg_restore -j with big partitioned table
Date: 2025-04-02 17:32:31
Message-ID: 84379bfb-bbda-84e6-cacc-e863ba9d6c37@gmx.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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.

+ 1 postgres backend process is doing:

ALTER TABLE the_master_partitioned_table
ADD CONSTRAINT ...
FOREIGN KEY (columnX) REFERENCES another_table(columnX)

According to my logs this started right after COPY DATA for
another_table was finished. And apparently it has a lock on
the_master_partitioned_table that all other TRUNCATE have to wait for.

Is this a bug in the dependency resolution? Wouldn't it make sense for
this to wait until all 1000 partitions have finished their COPY DATA
phase?

+ Investigating why the above ALTER TABLE takes so long, I notice that it
is issuing a lot of writes to the WAL. Digging deeper shows a lot of
time spent in SetHintBits(). Is there a way to avoid that in a clean
pg_restore?

Thanks in advance,
Dimitris

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Adrian Klaver 2025-04-02 17:39:36 Re: Performance issues during pg_restore -j with big partitioned table
Previous Message Danny Im 2025-04-02 17:12:35 Re: Issue installing postgis on RHEL9