The problem with MySQL foreign key constraints in Online Schema Changes (2021) | Hacker News Reader

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

code.openark.org

> Isn’t there some Instant DDL?

Run into this way too many times at my last few jobs. MySQL has some serious land mines built in, which result in many companies just adopting the policy of never upgrading the schema during business hours.

You are basically required to implement pt-online-schema-change in order to reliabily do basic changes.

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.

Doesnt Postgres have the same issue?

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.

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

“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

Reply on news.ycombinator.com