Date: Tue, 17 Jun 2025 18:30:51 +0200 (CEST)
From: Dimitrios Apostolou <jimis@gmx.net>
To: pgsql-general@lists.postgresql.org, Tom Lane <tgl@sss.pgh.pa.us>
Subject: Re: Performance issues during pg_restore -j with big partitioned
 table
In-Reply-To: <67469c1c-38bc-7d94-918a-67033f5dd731@gmx.net>
Message-ID: <15c7f5eb-bd50-ee07-24af-c209694ddca7@gmx.net>
References: <84379bfb-bbda-84e6-cacc-e863ba9d6c37@gmx.net> <67469c1c-38bc-7d94-918a-67033f5dd731@gmx.net>
MIME-Version: 1.0
Content-Type: text/plain; charset=US-ASCII; format=flowed
UI-OutboundReport: notjunk:1;M01:P0:0ErSOkByG8s=;UZQMu3qBdFO+/0N1Ybe5DWgVGZf
 XC3NeN9M4wP3jdX+h7iWLWIicvE4Jnxss817A3oDMsRFekVGl5XMO+8iEXd5zducJJ6Bc0Qaa
 +Xd+N1MuElhH5krgVgcw3HVnhf8MrCiQKWO5OyhMzc0hcJvpHK4zztLxFJQ3xiw6lpaBbX6gt
 SI4FeRRBWhUjJcF8gRuy9j8juFw5d8uHmE2UPwpWgT/XDfgPf/OmOBiBqXHgkuV+nvBYKxx/2
 YM763PVkHfiO9YDGdjgmBNfJAJi4ZgW3HI2ksYsdCLTjpbZUDjM5N58GMqbbWXzwoxG2hGqTq
 5jsHMYw9swo9oVV1Jo3XY1rbGU9A19weMsFIIoZUfcBjjlMhbX9Ov1tne0VDIR7L0Vd7Y0RY4
 pBBY1yHxr9HZguYigNXmQ2JjyeYCMRk8A9j2fV9Hzzy5x2NH2/lRRXeJSqF8AJkJmsvcxI3qy
 Mp/XrwCmemGpHrcsSowaoMn7vSVYsOsE9X9adlrSnySoIky7OwlWC3xypQy7DS3WRuO/tW7mL
 7R5pKVxMYOcw4eus9uZlfYL5lVYykqDvmzZMorf2l51rKnm+koQK2NSMG4i2vKdIX9pZzBfSU
 mha2wxcwdD10QV60JRoIoaEYauwhgyTh5e34rLv7l5RWN8E5ut93BOqmhO6uTIboEMBYys2ab
 9Vhs5c8zepmFbOiJodVkfymVN36j75Q+ogmH/wTxq/o4JP4RQbskihIq6D8HbgiLrV3N47uY8
 UuOgIWdvIixDtVNyv26gyuJluX69PPFJsnCkDsTsqQ60txOrf9HgE1uWxK2E8ZAQrGAVhgFw9
 rKKjEULl8zrGvppSI9jNRrUok2P4Rr1yMfnQk0Vm3W77kAJaaRmJOGrDM4hOafEs2eP9fxQUc
 wsTeKVQS0J+AzU8FdQeEM5r6IMcGELhWPi6KxBSgAIT5/IM05p5hTGwy4rQv+/b7XVFtFqFpZ
 qFw0Ao2nZ5+Nk7kNe2XU89tXeNZ60lQBmX6RvlB0axBmrOhjyGDQoyWXXWLjOneC6t0QQ1afo
 lUN0aoRfHWmWq5suAT55jtEL31JDUh31vMMTgyvXdBasKJgxBF2A7NsIAJRgEqgZMs94+SrIl
 XjfupGXRA1BJD8TegZTbjatEzHH1i5ipeDjo7JugbmeNM0jkk8E51eAzNvL430NDEjoe8+3Yp
 twEI4xc0ypNbuyeZ6uhjnoWu4PDdHzEhTwz8fsCHn+o1FYR59a2UDclqCkHFBO+VTLs46+66l
 qAZAMi8DEBr9GXEaqN+XctHzJjMzFfJZ3etSna81lYom3Sc5hCXqL3r3YYYCL4tqSJt+FgvqE
 HjyME1PST/oOAtV7o1QM59h5WyUvK8ei0uobTZKv/bwWJcELM3/FqvvLIEcNqbtCwty/sDFtY
 mVFHLI9QZAKYqpcOUwGLlQNsTOHV2T4VOvRazU3KjK/C8a2h0jgcuOu8jNKQxjakkPvxlFpQS
 N2yGWLEsjkISTOzrwHJna4eTiKdT92qxxeI5HkzmstbdJwIe0QNIOIsZilKFN3HLKT36Vyswg
 YrRX9cy+Ihza6JnlJ0jO5iaeWKi1HDyhVJs1dVjbwimReretdC6gNYBZMSNTjxEYHTznK6aST
 60Ol3ONlWkiMSdoiMBNDYsCAjdG/MXiSaD/o5Ozoi+ypkgDhdhgyDuoBjyHfECNNzt8gOErpl
 hE84SOeu0fST+Eiz49T/yeAh4CD0XvTKUr8zNCTprk0umgqbFrksyMKxJHS2W6Mc51wAp9K5U
 dtsBXW/YcT4w6sDT99NSuMwFayJHu0UgpYG2UbjtkWat8HZ+tepFwSetUbv9F8hMx9f/rb83r
 7qTJZ9ZNGJj77gqnL1Q8YC7DSxSqQCje633y6lk0kPnqhUwm2TH6+qYmmPwaEtyoYtnUy/FkC
 STF1xbUVM2qVnKLDkqO/SkKDZu2KnhZkXXELcGKzcELKNcZh/si5xmkHQjaDbkcerO4POfnfM
 dmrt3jdv8Ly4mI1akVUxRLhudWVHqMPFD5HOamZMPDeCQFAREB3Z27OVE6tjXT8dn9FokV5B3
 5HUqnyyqpB/yIKryPeiCQ5e8WRmBYmS5qMN2AzO03Gpc0R2KTaG+kO8KoVZxNGlfGqXMCyV0s
 +jbiljhAvLWdWx0btf9TRxjqFWTUHbf3PFm0oRM+lPcye3eNeh3+6aEdvsVzx7uEwjn6eylXa
 goY2zYod31/kTIch1wUX3y57e7UzE4ZMbquAeABRa+lcpZebiEtlbMSdeX/o7xa6+JcwZJiez
 8L+OQKJJCZn1rJTKVbPUZNJaxqzbmO7Af55wBWR33KFMZQK6hBEcrjJo1ul3UO2DDxN1G6NBc
 jNeW8I2B9tQdW0uA58IvG9EqT9lPOE56JfEljh6HnkKsrEt6q8oAmrLNurC7I0dywW2TbvC0X
 XD2GH25mqqAEuoTEw4kIlnHX7hcpGaNdbkOc/quAtLCcJ5n+L7S0B38zCfubzKqf6sVw/aVV9
 smlS6bdfbPoKDMVTkF7NCVO/QLcDG7dSZVTm+984Pgp0FJd/sE8MHlfUZQF4Ipd3vLzaS3dvY
 cZ7GpAHOQEFWc8t/aOIvbN3TYwY8/wI/Pwl/c3L48gjqJZPMgblVFTKu+v1jNvaxYxY2v9rWy
 lfl1Ke0fPgWu63KSlAVXlBqgwoOIGdYwMV7jA2lNsozssErZRPFlLsdT0ABqwumWsWYuYvAnC
 /frYvOf6adxyLgLf03PcHeKAUbiZdYkgVFnynqc7nG1aesh9OD658N7Mh+azfz2jP3BuQNwiK
 6GMSiw3nTnDliat8Jgxwz0By4DaqEXxkKyVmoKbiXbvNhWQeXUUBs7w4tOB3g4vDxrCFtfhV2
 cuT47fEj3XLw8w2ok8HZVasmJ1gQnYzerrpes8OexW9fHPJzJkklBLgqhfP7CvYD7otbPruuY
 22KK+zBdyTQenCQZcjw1j77UX/yYvMeAeC4enJJEegzvVW7dPB0gFXSjMOoxdef4yHyf6mEnI
 JQOarjPjChfhgM9R/4jlg+XXtKgrqGdvwu1bLYaN9kh5bBRnGLkxp0oKTH0Xe9Z5UILoKfrxD
 MTDXGPsdzZe5WBhhcpzKqNY2fVt5zhSF9wsH6xSNK+D668UJRoOPuzRQFkzGY7LcPF0wMqeBo
 vtjHhDXmJYABbV4JrRtNFUtxFyOX2QxqRLwVI7vkEsDdTAatpoiLl/yv2F0sNq1OznjEBGBqk
 UCZnJPodG6P9c5QTDNP414kipz31aS7cOcGBHEDt3ZOvjvQ==
Content-Transfer-Encoding: quoted-printable
Archived-At: <https://www.postgresql.org/message-id/15c7f5eb-bd50-ee07-24af-c209694ddca7%40gmx.net>
Precedence: bulk

For the record, I haven't seen this deadlock again. I guess it was a bug=
=20
on the master branch that got fixed, because I've been testing later=20
versions a few times.

Dimitris


On Fri, 4 Apr 2025, Dimitrios Apostolou wrote:

> On Wed, 2 Apr 2025, Dimitrios Apostolou wrote:
>
>>  Hello list.
>>
>>  My database includes one table with 1000 partitions, all of them rathe=
r
>
> I was not clear here: my database dump has all that, and the database is=
=20
> 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.pgdum=
p
>>
>>  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 waitin=
g".
>>    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 fo=
r.
>>
>>    Is this a bug in the dependency resolution? Wouldn't it make sense f=
or
>>    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=20
> 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=20
> database 44090; blocked by process 465408.
> Process 465408 waits for AccessShareLock on relation 44383 of database 4=
4090;=20
> blocked by process 465409.
> HINT:  See server log for query details.
>
> From the logs I see that:
>
> + Process 465409 waits for AccessExclusiveLock on relation 44437 of data=
base=20
> 44090; blocked by process 465408.
>  -->  44437 is test_runs_raw__part_max10120k (a single partition)
> + Process 465408 waits for AccessShareLock     on relation 44383 of data=
base=20
> 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=20
> dump I'm trying to restore is from postgres 17.4.
>
>
> Thanks
> Dimitris
>
>