Message-ID: <e89cfa42-edeb-431e-b33a-881556583666@dalibo.com>
Date: Wed, 4 Jun 2025 09:00:00 +0200
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: ALTER TABLE ADD FOREIGN KEY to partitioned table, is not
 parallelized
To: Dimitrios Apostolou <jimis@gmx.net>,
 pgsql-performance@lists.postgresql.org
References: <5f002654-92e9-8032-3e1a-134461b4e819@gmx.net>
From: =?UTF-8?Q?Fr=C3=A9d=C3=A9ric_Yhuel?= <frederic.yhuel@dalibo.com>
Content-Language: fr, en-US
Autocrypt: addr=frederic.yhuel@dalibo.com; keydata=
 xjMEXn3bgxYJKwYBBAHaRw8BAQdA6tX5FT/n5ztMWIoBdl6k5avvu65fv6ryfVzIx/aH3V3N
 JEYuIFlodWVsIDxmcmVkZXJpYy55aHVlbEBkYWxpYm8uY29tPsKWBBMWCAA+FiEEi7OTyf6H
 ePS+C6n11XGWhN8zGNUFAmZDVJ8CGwMFCQtfi90FCwkIBwIGFQoJCAsCBBYCAwECHgECF4AA
 CgkQ1XGWhN8zGNV65gEA6o+UrebFjn2CIAOYwP3CiiJugiM5TMH9yJyehQua5ZoA/2ChxiVn
 8OUaAw/ErxJmQi1mSN0tNZBL/KhkUPBDUDQNzjgEXn3bgxIKKwYBBAGXVQEFAQEHQCuh5cLQ
 AtEBXHa2Fmtp0kduBu7msM7qO/gaEcdjFUdiAwEIB8J+BBgWCAAmAhsMFiEEi7OTyf6HePS+
 C6n11XGWhN8zGNUFAmZDVJ8FCQtfi90ACgkQ1XGWhN8zGNWOOAEArSqePBqfd4Kx4ulACaWO
 6fM+XSDfUlBAQXx9rU6DR4MBALwozl9g91tRysim6lKxggvBfp/PvbpTZNrxYLWB2fYP
In-Reply-To: <5f002654-92e9-8032-3e1a-134461b4e819@gmx.net>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit
Archived-At: 
 <https://www.postgresql.org/message-id/e89cfa42-edeb-431e-b33a-881556583666%40dalibo.com>
Precedence: bulk


On 6/3/25 17:34, Dimitrios Apostolou wrote:
> The backend process for each of the above ALTER TABLE commands, does not
>    parallelize the foreign key checks for the different partitions. I
>    know, because in the logs I see gigabytes of temporary files being
>    written, with the CONTEXT showing queries issued incrementally on
>    all the different partitions:
> 
>    :LOG:      temporary file: path "pg_tblspc/16390/PG_17_202406281/ 
> pgsql_tmp/pgsql_tmp3363462.579", size 1073741824
>    :CONTEXT:  SQL statement "SELECT fk."columnX" FROM ONLY 
> "public"."table_partition_214" fk
>               LEFT OUTER JOIN ONLY "public"."another_table" pk
>                   ON ( pk."columnX" OPERATOR(pg_catalog.=) fk."columnX")
>               WHERE pk."columnX" IS NULL AND (fk."columnX" IS NOT NULL)"
> 
>    Why can't the backend issue these queries in parallel workers?

This has been discussed here: 
https://www.postgresql.org/message-id/flat/0d21e3b4-dcde-290c-875e-6ed5013e8e52%40dalibo.com

Perhaps we should exhume this patch, but I believe the optimal strategy 
is to perform a VACUUM between the data and post-data to build the 
visibility map. The anti-join can then use an efficient index-only scan.

Best regards,
Frédéric