Date: Wed, 4 Jun 2025 16:12:54 +0200 (CEST)
From: Dimitrios Apostolou <jimis@gmx.net>
To: =?UTF-8?Q?Fr=C3=A9d=C3=A9ric_Yhuel?= <frederic.yhuel@dalibo.com>
cc: pgsql-performance@lists.postgresql.org
Subject: Re: ALTER TABLE ADD FOREIGN KEY to partitioned table, is not
 parallelized
In-Reply-To: <e89cfa42-edeb-431e-b33a-881556583666@dalibo.com>
Message-ID: <ec31717f-511b-fbf2-f6b5-ade5b3c98899@gmx.net>
References: <5f002654-92e9-8032-3e1a-134461b4e819@gmx.net>
 <e89cfa42-edeb-431e-b33a-881556583666@dalibo.com>
MIME-Version: 1.0
Content-Type: multipart/mixed;
 boundary="-1463770111-1200378283-1749046375=:940075"
UI-OutboundReport: notjunk:1;M01:P0:+IM03PZAzbc=;NopUje9RwKFLlfdTczKzLfmvLCw
 XLhdZiFtcZQ+DAQJsuO5sEJGPcZPJic5rGKbbSttiVQFRMCHNCqplj5U2w8fbBexvT7ekodCg
 fxmty0RDDEQTXe+OEiVKZDOEjynS/EwEvB78CQ077VqSZexIHhHYJZbwAyxsZw/PWYIS9bXHB
 3AJitKdwPdBMDAGy7EBSDO2cJbsHRgEY++ieeqAT+Y5XFyqDt/8b3qSMityNJl+a950Un94SS
 GrK6vPbhxNGF/KdG/K1DohDpRCnYj3/BYuwYkfhqstP5qxKgryqhjaiW5gGangXwBCZG3y9hn
 PhFmqBu0XP6RGQGAnL3xsyHGK63u7xbn8vzDaoQEj/WUc0UL4yuLogx6f+IdlqoofmkAb02km
 onXbsy8n+vLg4IKI3QJCMZEgrp0V9JouHY9alvbPRhyFOf+WnzU786XQI+PfOgqTPtfhOgP9b
 SZhPNDkkJYxHBoEnh2uFzbeNvNx4kc2E+qyKIw0viSoRvugygFZPr0J+b3l6ZeK5G86DPY/8Z
 T8NO+ZGpKx9F80d2lE0zVUzIj6y1vqpUNcmgyVuVB/JytLLxq8Xi6pX+LPbmyHAmnlnS4nKji
 G3EbA5uDI2sPVUEY5A7cakecVt8KJmwslmB+lbEWZg2P5P5XCNwYEfGvEXU3g4mjehh5oNCmG
 L9V3ufgvw4bo3ch+gGp6TQS8reQniaheHBtVydga6wgHZFS7P8Ebdf6BVqY/9XgFmnWJ+XWeL
 USPZthlAlMmSPShpDIllHX1VgeLpnd+t1nzxKuGQMokub7NY9TEoJ3aHCjnn0CjcxjaHy3sEM
 G0nqwOz/KlFKuOWGiH9XaQXlI+wreuvITb5poPRMrtUDPlrmIRxY8sQ34NSYNMrltOEOo7Fp1
 VaNVmI1CAuvctrYzG719btCIkAHKbbsdUHkA8lgS76x5S0pXgKZwrV3K+aYlcdjcOKD5KQ0Ct
 t68CuITs5FxKLJZ47U5AUS3Ga2arJTPUDCl0ig1bnlOuIC4Ji9/3v5QaOztcRtobSgyU1FB7Y
 4vnOsiZ53eUH0IaeV9RqD3mEZ/Rp1kYKA3o/AvAtRwpl96y7YbtH5feTpnwlkdnvjCIvpTxiC
 Tg+G6FIJeovctDMJHMUr9M7E4jZUpvls8YOU96CmNDxZ8ld1O0vXkuw7df+z4INsfOjHsWBIO
 peWar9pqxkWJPx1TF44r+Qf7TZVuOLqXvSlEyX+qu+7lgcIuVDutNBTuzMh9X4WuR5vlV5P2y
 H8yTm6MKZvvHe60u9Yw7SZym1JKjwcYgcQNXQiWkdhGlJuQNPl8k5pmy9v8pBkrlBv3sq1PUd
 XujYs5g4j829RZdWlPIL9oIf9PiTmieuMwc+v2SZXX0Cctiqo7fBhxSrH5TR8tlg6Ysj/aT9+
 Q2M2li8Gi6yA+ai9wswDJXth/7dyENoL0FYHYN5kjgWd/A6Alo09Zn+ObpfjbFgkxBfIYW6yT
 L2Nrf11KB+ukCsLzz72Goika6BDDxE23v11FLoYf5yENg3G9735x33RltvYoPmSUDwhX9gekU
 urpwpnzoC48mVfYQ8gQq4HEnpW69yZilaeqi3DKLmZX9Rtp5uTTY+wNl8HEce+RltMRwMSb9D
 c9ji4tlf2cH1NpWmjVJndJy2gDMfDla4Eh5cGieDsCJP+HoM51VzOXO8fEVae/7PQtuRIB2hN
 0jQ/oK8+iHjk24qS2bT8CTfoB00eMclXKHcRg37/R3mshWd2sFByKelU1FnphUlACCo05jfLL
 5C8orLqKae+m6QFDPofZNgq9bXg9U3srLMLsG8cY9VkiaaVu8u2kZVNKiUjR9tvdjeJh5Xzuu
 jJpE7CB3kkzvPu19Ftioa/PIBai4avN2cfEXTmSiaNrNGuqHJF+QAVg7MeuQKnXlOHgh78Wp4
 7UIrgW0JW4/XodObtYnX0DK3sUjbAGzHhBUb+hmmV0CYgvYmmEDTb4R/Ey/vwXaL1NQ8dAIYf
 RqoZTjd3pi9Ybk3ndnXKSAfKcVEIWS5zl+7xwnpQ2t2QMiMLunHpjoEUgiNIDqYkNrXSkst0J
 3GjOC8fP1dWA7AmUgGJENZAd0NPiSnbD592KFqCLDmu4df22oCbC6YMtcCtF/Ky72OYNBkaqP
 4L+d4605hHmsgxdjNOZOvGuli3kzEItscRx4DZ++T70eGg9LNSZf2SOKhdXQdiXu6/SkUCfnO
 5NGLWIQtt8oyIEhpq9PXbIf893zVoewoWfhp1yaYNVFJr0c/vaOHr7XuSicH4ygqsQr0qxn2g
 sAPUi5vmTBW1sKVdjvGRgqDbnaWvEWuHkAI8wNnjv0dlmuwFvniOqNMVJaRmeCx/cBGrKW1iy
 hBca2Mo2QynbCBcyw3Xjs14qZGZ5sbxiXTNxnAIdfXPrBqN+wnYO8sxURfIAcwTILwiNMb/Kj
 eG4ellFwf9By3Z23yb3jAnMbCgoRd4fM0BLBRzkteB+4+N70NLP1cOv+umOtMhP04pelfneVL
 WQgxIPNaesT+uySY9z5oXpWuRVJdsE0sVNaDgKCgk2znmdI75/tjmJ41qsAd+dh4S4AkClsYT
 S165izIRqVz8efIyqBmn9T8zf7P3hOzrvZ4fe1In9XZNcnAk6CmDg3UBVHdOmtCT9il+Buz4M
 QT6H7M8cLkyUAuPigYOgk3Qrsi3vCUDBeHT1W8VPibeSn2Fd0kcoUp6DPqA2Px1xQOhC5IzxO
 RDM6bJcsIRrnf/4OYlJY7h41HywiyfiOSVDHSW9eYt7foRnbm3oom22CJPQ0X8b1zZuhl5aft
 SefiPHfp5Wez0pdAuJhFRgeuty5pX2uQuEg5mcAGyjLQmwYuNDyWdzgQvgPVlEQe/VMVk9JlA
 mKlb4tCFPav5uRn7mMC9Hnhl/yhk4EkEvzxgPUcSCPWohc+rZj3ndr3Bns74UFi0RLpxNtS+O
 COGZZ56CGtmLphu3qg34ezEa1fv0LbfYIeZmpRxdF74DHkT8TcXAT5x/lAKzRxkWysdPSqWmO
 zhhAtUGdTynSY9coV/bGUly5rRso4oBMXrA+Wk6suXOCqyYEig6y2TIvwb2tt4uLiPru+fogm
 xVcXY4FpypVWlHSTGnaiXu36M8yg/dkM/6mXbgk5LH6zHxKYJUBOBfUOoDcPlnjoJVOMGM9RG
 dDhrzyZ69o0KFgK9oWQ3EHcEnHRNcYX+eb9DgAKXsRECqxOsCw4hSRk3vjCcTtllWPMTE3f1h
 hShID3+LB9281cHwX5URTCjfIMf2j8ib92KFh+Rh9u4bzcJDSJGBYtmeklliDJr1l7aEVFv9p
 k1pyssgHpBKLj/AM7deMRplP+gOCZrIsV5/EYyoIpKE22by1rnrTsfhMFgc=
Archived-At: 
 <https://www.postgresql.org/message-id/ec31717f-511b-fbf2-f6b5-ade5b3c98899%40gmx.net>
Precedence: bulk

  This message is in MIME format.  The first part should be readable text,
  while the remaining parts are likely unreadable without MIME-aware tools.

---1463770111-1200378283-1749046375=:940075
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: quoted-printable

On Wed, 4 Jun 2025, Fr=C3=A9d=C3=A9ric Yhuel wrote:

> On 6/3/25 17:34, Dimitrios Apostolou wrote:
>>  The backend process for each of the above ALTER TABLE commands, does n=
ot
>>   =C2=A0 parallelize the foreign key checks for the different partition=
s. I
>>   =C2=A0 know, because in the logs I see gigabytes of temporary files b=
eing
>>   =C2=A0 written, with the CONTEXT showing queries issued incrementally=
 on
>>   =C2=A0 all the different partitions:
>>
>>  =C2=A0 :LOG:=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 temporary file: path "pg_tb=
lspc/16390/PG_17_202406281/
>>  pgsql_tmp/pgsql_tmp3363462.579", size 1073741824
>>  =C2=A0 :CONTEXT:=C2=A0 SQL statement "SELECT fk."columnX" FROM ONLY
>>  "public"."table_partition_214" fk
>>   =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=
=A0 LEFT OUTER JOIN ONLY "public"."another_table" pk
>>   =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=
=A0=C2=A0=C2=A0=C2=A0=C2=A0 ON ( pk."columnX" OPERATOR(pg_catalog.=3D) fk.=
"columnX")
>>   =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=
=A0 WHERE pk."columnX" IS NULL AND (fk."columnX" IS NOT NULL)"
>>
>>   =C2=A0 Why can't the backend issue these queries in parallel workers?
>
> This has been discussed here:=20
> https://www.postgresql.org/message-id/flat/0d21e3b4-dcde-290c-875e-6ed50=
13e8e52%40dalibo.com
>
> Perhaps we should exhume this patch, but I believe the optimal strategy =
is to=20
> perform a VACUUM between the data and post-data to build the visibility =
map.=20
> The anti-join can then use an efficient index-only scan.

Thanks for pointing to this patch.
Since I run each of the pg_restore sections separately, I will try to=20
manually do a VACUUM after the "data" and before the "post-data" section.

In general I have noticed most operations are slower after a succesful=20
pg_restore until VACUUM is complete, which is unfortunate as the database=
=20
is huge and it takes days to run. Something I have on my list to try, is=
=20
whether a COPY FREEZE would alleviate all this trouble, since all tuples=
=20
are immediately visible then. Maybe a patch for a new pg_restore=20
option --freeze is a better solution. Are my assumptions right?

Thanks,
Dimitris

---1463770111-1200378283-1749046375=:940075--