The problem with MySQL foreign key constraints in Online Schema Changes (2021) (opens in new tab)

You're over-stating the issue. You can always simply try ALTER TABLE ... ALGORITHM=INSTANT first, and this will just return an error immediately if the requested alteration doesn't support instant changes. Not a land mine at all.

The article is also four years old, and INSTANT now covers a few additional cases which it didn't at that time.

If your alter doesn't support INSTANT, at that point you can fall back to an online schema change tool, of which there are several battle-tested options. All of the largest MySQL users (including many of the biggest names in tech) do schema changes any time of day without issue by automating these tools. In comparison the equivalent tools for Postgres are far less mature.

I worked at a MySQL shop in the 5.x days. We'd regularly run overnight "alter table" commands that would take forever... some tables had 100's of millions of rows. We'd have to pause any of the writers (batch jobs, etc.) We had a sharded system, across about a half dozen DB servers, and large migrations would need to be done in phases. Painful.

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.