shlomi-noach

Vitess maintainer here. While Vitess can still use gh-ost, the recommended way is to use Vitess' internal VReplication mechanism. With this, your can throttle, pause, resume, cancel, retry, revert a migration, as well as auto-resume from failover (migration continues from point of interruption once a new primary is instated).

Vitess throttling is by default based on replication lag, but you can use different metrics, such as load average, or indeed multiple metrics combined, to define what constitutes a load.

We've all been there and have been hit hard by rolling back data as well as by not rolling back data. What we do with PlanetScale Reverts, though, is to preserve your data through the rollback. There aren't three classes of data, just one (or, it's nuanced, let's call it 1.5).

As you complete the migration, PlanetScale keeps your old table, and continues to sync any further incoming changes to the table, back to the old table. They're kept in sync, apart of course from what incompatible schema changes they may have. As you revert, we flip the two, placing your old table back, but now not only with the old data, but also with all the newly accumulated data.

I completely appreciate the roll-forward approach. That's something we do for code deployments. Except when we don't, when there's that particular change where the best approach is to revert a commit, revert a PR. I think of schema deployments in the same way. Reverts will be rare, hopefully, but they can save the day.

Valid points! In my experience, when someone has foreign key constraints in their database, they tend to develop their apps in "trusting" way. Meaning, the app trusts the DB to maintain referential integrity. When you do the three step breakdown, you remove that integrity, and the app doesn't know any better: it keeps feeding the database with data, the database says "fine", and the app assumes referential integrity is preserved.

This is why in our design PlanetScale will not take upon itself to do this three step change. The user is more than welcome to break this into three different (likely they'll be able to make it in just two) schema changes. But then the user takes ownership of handling unchecked references.

> making data changes that might look invalid until all data changes are done

In effect, the data _will be_ invalid, and potentially for many hours.

Now, it's true that if the user messed up the data in between, then adding the foreign key constraint will fail, in both PostgreSQL and in MySQL. To me, this signals more bad news, because now the user has to scramble to clean up whatever incorrect data they have, before they're able to complete their schema change and unblock anyone else who might be interested in modifying the table.

Personally, my take is to not use foreign key constraints on large scale databases. It's nice to have, but comes at a great cost. IMHO referential data integrity should be handled, gracefully, by the app. Moreover, referential integrity is but one aspect of data integrity/consistency. There are many other forms of data integrity, which are commonly managed by the app, due to specific business logic. I think the app should own the data as much as it can. My 2c.

Thank you!

Yes, you got that right! If you drop a foreign key constraint from a child table, and then follow up to INSERT/DELETE rows on parent and child in such way that is incompatible with foreign key constraints, and then revert, then the child, now again with the foreign key constraint, can have orphaned rows. It's as if you did `SET FOREIGN_KEY_CHECKS=0` and manipulated the data unobstructed. The schema itself remains valid, and some rows do not comply.

It's worth noting that MySQL has no problem with this kind of situation. It never cares about the existence of orphaned rows. It only cares about not letting you creating them in the first place, and it cares about cleaning up. But it doesn't blow up if orphaned rows do exist. They will just become ghosts.

Post author here, happy to answer technical questions.

Vitess maintainer here. I feel like the discussion was about 100% feature (queries/protocol/...) compatibility and that somehow it shifted to 100% performance compatibility? 100% performance compatibility is trivially not something to claim.

We are not the same. My mistake for writing "OP".

Whoops. I wrote "OP" when I really meant "Post author". I'm a bit rusty with HN notations.

Like the issues I mention in my post: modifying the data type of a column that is used by a foreign key; otherwise the fact you can't run Online DDL on a table that participates in foreign key relationship ; that INSTANT does not support (yet?) adding/removing foreign key constraints ; that cascaded writes are not written the the binary logs.

These are all things that the casual developer doesn't deal with when designing a schema and writes an app that INSERTs/DELETEs/UPDATEs to tables with foreign keys. But once there's a need for a change; once you wire 3rd party tools onto your database, that's where the operations hit a wall.

OP. I agree! RESTRICT is by far the best rule to use, and makes the most sense. Perhaps to balance my post a bit, and for what it's worth, I don't advocate for "don't ever use foreign keys" as a blanket statement. My experience was one where using foreign keys did not make sense. I do wish they were more operationally friendly.

Thank you, good idea!

So what you are describing here is Rewind-the-Rewind. We have built the support for this in OSS Vitess, and the answer would be "yes", you would roll forward and regain the new column with data again. But this is not (yet?) supported in PlanetScale.

Right. So if you're both adding one column and removing another, then the revert will lose your new column and will regain your old column. Normally, you deploy DB and app in steps. E.g. if your migration adds a new column, your app is not yet aware of the column (or else your app would break). The moment the migration completes, your app is still on the not-knowing state. It takes an app deployment to actually start utilizing the new column. If you do that, and then want to revert -- you will lose any new data you've added to the new column.

In my experience, when a schema migration goes wrong, it goes wrong with a bang. It takes seconds to maybe one minute until pagers are alarming. So I'd say in a common scenario you will not get to deploy your app with the new column awareness, because you'll have realized the migration was bad right away.

> Alternatively, can we separate those actions out? Drop columns in one migration.

If you choose to do that, then you're on safer grounds; it costs you some wall clock time, because migrations do take a while to complete on medium to large tables.

Do note that Rewind only lets you rewind your most recent deployment (PlanetScale's app will not let you run the next migration before you've committed to, or have rewinded, the previous one).