[PATCH] ALTER TABLE ADD FOREIGN KEY to partitioned table, is not parallelized

On Thu, 5 Jun 2025, Frédéric Yhuel wrote:
>
> On 6/4/25 16:12, Dimitrios Apostolou wrote:
>>  In general I have noticed most operations are slower after a succesful
>>  pg_restore until VACUUM is complete, which is unfortunate as the database
>>  is huge and it takes days to run. Something I have on my list to try, is
>>  whether a COPY FREEZE would alleviate all this trouble, since all tuples
>>  are immediately visible then. Maybe a patch for a new pg_restore option
>>  --freeze is a better solution. Are my assumptions right?
>
> It seems that the idea has already been discussed: 
> https://www.postgresql.org/message-id/flat/CA%2BU5nM%2BXvkUu9ran%2B5cY%3DTWQquLTpvzte4KVMK%3DaDfbr-xfNXA%40mail.gmail.com#b61a7fee06e10e61afa68712bc0b3c5b
>
> I've CCed Bruce Mojman, in the hope that he can tell us more about it.

Thanks for all the pointers, it shows that changes in postgres are harder 
than they appear.

FWIW I implemented a pg_restore --freeze patch, see attached. It needs 
another patch of mine from [1] that implements pg_restore --data-only 
--clean, which for parallel restores encases each COPY in its own 
transaction and prepends it with a TRUNCATE. All feedback is welcome.

[1] https://www.postgresql.org/message-id/c61263f2-7472-5dd8-703d-01e683421f61%40gmx.net

It works really fast for the data, and I see that some, but not all items 
from section=post-data, start parallel plans. For example I see CREATE 
INDEX spawns parallel workers.

But unfortunately the item in question (ADD FOREIGN KEY) is not parallel 
(probably because the discussion [2] you posted in your previous email 
never concluded). I /think/ though it's reading all the data faster than 
before, but still has to go through terabytes of data and this takes a 
long time, for each of the foreign keys it adds.

[2] https://www.postgresql.org/message-id/flat/0d21e3b4-dcde-290c-875e-6ed5013e8e52@dalibo.com

Still I wonder why pg_restore can't issue many ADD FOREIGN 
KEY for the same table in parallel.


Regards,
Dimitris