[aeyes]

You can create Foreign Keys as NOT VALID and then VALIDATE the constraint later. This only takes a RowShareLock.

Dropping the constraints takes an AccessExclusiveLock for a very short time in both tables so yes, that would interrupt traffic but the online migration tools also do this to switch the tables so I give it a pass.

[javier2]

wouldnt that take an awful long time when validating the foreign keys again? You cant flip the tables again until all foreign keys are valid.

I am asking out of curiosity, as I have used pt online schema change extensively before, but am right now working mostly with postgres

[cwillu]

“After that, a VALIDATE CONSTRAINT command can be issued to verify that existing rows satisfy the constraint. The validation step does not need to lock out concurrent updates, since it knows that other transactions will be enforcing the constraint for rows that they insert or update; only pre-existing rows need to be checked. Hence, validation acquires only a SHARE UPDATE EXCLUSIVE lock on the table being altered. (If the constraint is a foreign key then a ROW SHARE lock is also required on the table referenced by the constraint.) ”

--https://www.postgresql.org/docs/current/sql-altertable.html