[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