Re: ALTER TABLE ADD FOREIGN KEY to partitioned table, is not parallelized

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