Date: Fri, 4 Apr 2025 15:13:09 +0200 (CEST)
From: Dimitrios Apostolou <jimis@gmx.net>
To: pgsql-general@lists.postgresql.org
Subject: Re: Performance issues during pg_restore -j with big partitioned
 table
In-Reply-To: <84379bfb-bbda-84e6-cacc-e863ba9d6c37@gmx.net>
Message-ID: <67469c1c-38bc-7d94-918a-67033f5dd731@gmx.net>
References: <84379bfb-bbda-84e6-cacc-e863ba9d6c37@gmx.net>
MIME-Version: 1.0
Content-Type: text/plain; charset=US-ASCII; format=flowed
UI-OutboundReport: notjunk:1;M01:P0:1AVlbgujL9w=;cvN71ZExidixF5HFN4+WPd5GV+4
 1Qy0nVusBxDRX/uBJCCh9d145O+0uV5pAddSPntPne8VXggk+/+cvtrQONSllxZOU7qZIK7mQ
 ++zVdIh0kDbvk4pXgOcgQafF83LHWzFOsvDS7t0HF/1AYkyvZrbn7Z+laWuHXNwkdfO/O0lg9
 abC8zLWREiKPzDLbZjeExYWv/enwH2leBD3v5ZA+ef7Ba8sFnmdh+X5Zi4o7TgR+eA0GDSzd7
 Ul3xlj+1U0pC2VMXXfFWkfkKNKBxODkcCIrZvN1eA8B2+n1iAzCShPhZ+T0+QrYvxwM+msJ36
 KydXezdOVZjP5ILALmPoE+7yid+GCGjUdDyvHiEZx+41/GDiIeE+ntLm9U94a8jyTYKkzvL//
 aZCut5q1EBt+H7kboMh3QwqXvmnhwDyQf25Ydh9MyOE/zv3hz2eXxlIO7BDTjj7cLKYN5r58K
 Q3N3BzQdwCdNP3w2324eu1oNoM87vD7JK9oYoFEAhlwyvNIKk5OdcOxc2N6pFFx2OS0zWYEuw
 3HhLl/06vFmrvSEUNOaCfODlBJdYOPnCSUWXiGEwczPfLJb9F0fkecFBhKEi6+O6Tp8bGCcOW
 0rr4Rx7BJ36wuE+KckRUv8gmON5cskVxtNPTW+sOjZnLS2h6aAMsMDWV4EUCWtCEGe9s9Rrhh
 JhNDAu9cGU6mKgNToHYfQN+uvRn3Ba9q5SO0zMsUNqXW7x5VUENxYeHl6z90OXBc/Lqv8JW+W
 7b34qK3jKYu4GwVufOb0hP3ZWp4dypvsF1RLJSJ/nSZJkkjt5J4mJpmV3cfWvNncZzEkaNEGW
 2eJsiQxnvc6P1eY1fsmJf5iFhm01VVVZrgVN6qCUvdJUZVPbT4bRX822G1rHW1tKZQWgbgt3W
 GSSCABKgYzatJemdTBR8NQz7wmBVO2tAXkAt1VoLegvqBT8uLPUT3lvXua8LMtiDFEFBpeca4
 yMdlqDj9SmlXX2rXjOcx+vB83vvregFWBeV/UxeiZfRMP1zZGiqNZumfqcGhKuGpBN1v+ZMyf
 YCvhBMim1iSRoix4U2QJmGbEC9NRrglCWhtzvwi+owdwgXyNU832RZEpsq6sUHYO0fh9F/0Gg
 FgDK3Tjm0fnJh7zA8t3m1pfOx21rgFdxBvwz4oLDkZVescHZZTUfHu5LHzY7Aaj0lJklHtty2
 bfiKLfPfe/kzQNRsowXFOT63uidRtpe/Ae0QQp5r4aRdpkSVQnZL52UNlFFLZbPujwX3RnYTd
 L2Y0PiJggOuEysxJIIC5hFDxyBOo/fDgBsMQbqd1Tk0JaGcdxvNAbzUiDiOkHnziGqfB4hvVq
 YeU80RoOAQJISSreiN0rccvmhvjg2UknlfuSYeXrelyEXg3Tu+hqsJdumDrWCjNDrpRLJ3QCl
 Hj75LiwtIEr5Ji3S6jUu9nBa0uR+oevMfIBloMxGtZDM46DHBfOuzr+mZ8vOv1wqrW0Yduj3/
 hNr1LUw==
Content-Transfer-Encoding: quoted-printable
Archived-At: <https://www.postgresql.org/message-id/67469c1c-38bc-7d94-918a-67033f5dd731%40gmx.net>
Precedence: bulk

On Wed, 2 Apr 2025, Dimitrios Apostolou wrote:

> Hello list.
>
> My database includes one table with 1000 partitions, all of them rather

I was not clear here: my database dump has all that, and the database is
brand new and empty.

> 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 seve=
ral
> 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?

Trying again, pg_restore exited with error after almost 24h:

pg_restore: while PROCESSING TOC:
pg_restore: from TOC entry 8904; 2606 16529 CONSTRAINT test_runs_raw test_=
runs_raw_partitioned_pkey
pg_restore: error: could not execute query: ERROR:  deadlock detected
DETAIL:  Process 465409 waits for AccessExclusiveLock on relation 44437 of=
 database 44090; blocked by process 465408.
Process 465408 waits for AccessShareLock on relation 44383 of database 440=
90; blocked by process 465409.
HINT:  See server log for query details.

=46rom the logs I see that:

+ Process 465409 waits for AccessExclusiveLock on relation 44437 of databa=
se 44090; blocked by process 465408.
   --> 44437 is test_runs_raw__part_max10120k (a single partition)
+ Process 465408 waits for AccessShareLock     on relation 44383 of databa=
se 44090; blocked by process 465409.
   --> 44383 is test_runs_raw  (the master partitioned table)

Process 465409:
   ALTER TABLE ONLY public.test_runs_raw
     ADD CONSTRAINT test_runs_raw_partitioned_pkey PRIMARY KEY (workitem_n=
, run_n);

Process 465408:
   COPY public.test_runs_raw__part_max10120k(...) FROM stdin;


Bug? This happened on a postgres compiled from last week's master branch.
The dump I'm trying to restore is from postgres 17.4.


Thanks
Dimitris