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.

Author here. Thank you for your thoughts! Some comments:

> How does the author suggest non-blocking DDL is actioned?

Online DDL is done in MySQL using one of the 3rd party tools, such as pt-online-schema-change, gh-ost, or now via Vitess. Running online DDL is an industry standard in MySQL since about 2009. We have recently integrated online DDL natively to Vitess, see https://vitess.io/docs/14.0/user-guides/schema-changes/manag... and https://docs.planetscale.com/concepts/nonblocking-schema-cha...

> Try creating a new table (instant) then ETL'ing your data over from the original, dropping and renaming

Alas, in an online service you can't afford such downtime. Online DDL allows you to change the structure of your table even as your app continues to interact with the database.

> It is already possible to revert a migration if you are using Oracle, using Oracle Flashback.

What I call "revert" is something else and more powerful. Once you have migrated your schema, your app keeps writing to the database. Oracle flashback means going back in time, losing that new data. In my definition of Revert, you can restore your original schema while at the same time fully retain your data, including the data accumulated since the migration. Please see https://planetscale.com/blog/its-fine-rewind-revert-a-migrat...

> This would have been a more interesting post if a possible solution to all the points was posited.

That's fair! We plan a followup post. I actually intentionally did not include any solutions in this post as I wanted to first make a statement, an observation of the state of the relational database today. At Vitess and at PlanetScale, we have indeed tackled all those points, to be discussed in the followup post.

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).

It's not like that -- that BTW is super simple to achieve with either of the existing online schema change tools (pt-online-schema-change, gh-ost, facebook's OSC) -- they all end up with your old table renamed away, and which you can instantly reinstate back in place. Very cool and important feature! But then, you lose data; all the data you've accumulated since the migration completed; or some data you've deleted will suddenly reappear.

Rewind does not move you back to an old snapshot, but rather keeps you on your current timeline, with the current data, but with the old schema.

Technically, there are two tables involved, yes! And a synching mechanism that compensates for the structural differences between them. But perhaps I should just point to this technical explanation of how this works internally: https://planetscale.com/blog/behind-the-scenes-how-we-built-...

Engineer at PlanetScale; If you drop columns that are `NOT NULL DEFAULT <something>`, and then you insert some new rows to your newly-versioned table, then you're in a good spot: when you revert, those columns will get the DEFAULT value on those rows.

If those columns were `NOT NULL` and with no DEFAULT, then you are unable to rewind. The rewind process will make an attempt -- after all, maybe you didn't add new rows; maybe you just deleted or updated -- but if you did INSERT new rows, then the Rewind process will fail (and you will be notified that rewind is impossible).

There's a couple more interesting scenarios, see this doc page for more: https://docs.planetscale.com/concepts/deploy-requests

Engineer at PlanetScale -- It is not similar; so temporal tables are about getting a table's dataset at a given point in time in the past, sort of a time machine for your table. Rewind is about undoing a (bad) structural schema change, and _without_ having to go back in time - you continue your current timeline, with your current data, but flipped into the previous schema.

It's also not something you need to activate ahead of time, like you do in MSSQL temporal tables; it is activated on your behalf for any schema change you deploy.

Engineer at PlanetScale; it will let you go back to safety without data loss, and without making your database inconsistent.

If you will indulge a realistic story; I've been through this process multiple times in production.

You change a large table via ALTER TABLE; you possibly change a data type, or drop a column, or modify an index. The change takes 5 hours to complete - and things go bad. Testing in staging was good, but as it turns out the production environment cannot cope with the changes and still needs the previous schema. Some traffic is still able to pass through, but some requests are erroring.

What do you do?

One option is to run another ALTER TABLE that takes you back into the original schema. This will take yet another 5 hours, during which your app may be degraded or altogether down. Plus you'll be unable to recover lost data (such as in a DROP COLUMN scenario). Another option is to do a point in time recovery for your entire database. This will both take time, but more importantly you will lose all the data you've accumulated since the migration completed. Any new user account, any new artifact, any new event - will be lost. Rows that were deleted suddenly reappear. Data that should not be available anymore suddenly is.

Most people will try a third option: do a point in time recovery on an offline server, and extract/copy just the specific table and copy it onto production. Typically this involves a lot of juggling and most environments will not have the infrastructure to automate the entire process. But even once this is done, you're still hit with the unfortunate implication: your data set is now both incomplete as well as inconsistent.

It is incomplete because data is missing from the restored table. Any rows accumulated since the point in time recovery point - are lost. It is inconsistent, because in many cases, due to the natural relational design of your schema, other tables will have rows that relate to the missing restored table's rows. You may try to then manually backfill those missing rows into the restored table (or remove rows previously deleted) , but in reality some processes will already have manipulated the data on the restored table even while you're trying to resolve the situation, leading to more conflicts.

It seems like the only safe way is to take everything offline, disable any writes to the broken tables as well as some of, or all tables, associated with it, resolve all conflicts, then restore data onto production and enable writes again. Or, you choose to lose data, track down any known conflicts, reach out to users and inform them of the data loss. Either way this has a significant impact on your service.

And so Rewind offers an instant fall back to your previous schema, while still retaining any data you've accumulated since the time of incident. Rewind resolves the differences between previous and current schema, and adapts the latest data changes onto the old schema. As you rewind the migration your table still has the same amount of rows, and maintains all incoming or outgoing references from and to other tables. It all happens on your production environment and does not require an offline server.

Here's a technical description of how Rewind works: https://planetscale.com/blog/behind-the-scenes-how-we-built-...

Engineer at PlanetScale here: it _does_ work if data has been written to the new structure!

In your scenario, you drop a column, populate some new rows in the new structure. Then, you regret the migration and rewind. You get the column back with all the pre-dropped values, AND you get to keep all the new rows which you've inserted.

The values for the now-restored column for the newly inserted rows is the DEFAULT value per column definition.

Online schema change solutions have been around for over the past decade and are commonly used to ALTER TABLE with no downtime (or with minimal interruption) on the largest deployments of MySQL today.

The two most common solutions are pt-online-schema-change and gh-ost, and if you are running MySQL today and still running direct ALTER TABLE suffering outage, then you're in for a pleasant change.

On top of that, most MySQL ALTER TABLE operations with InnoDB tables support non-blocking, lockless operation as well. My main concern with these is that they're still replicated sequentially leading to replication lags.

MySQL is also slowly adding "Instant DDL", currently still limited to just a few types of changes.

Disclosure: I authored gh-ost (at GitHub), oak-online-alter-table (the original schema change tool) and am a maintainer for Vitess and working on online schema changes in Vitess.

Links:

- https://www.percona.com/doc/percona-toolkit/3.0/pt-online-sc...

- https://github.com/github/gh-ost

- Past HN discussion: https://news.ycombinator.com/item?id=16982986

- https://dev.mysql.com/doc/refman/8.0/en/innodb-online-ddl-op...

- https://vitess.io/docs/user-guides/schema-changes/

Edited for formatting.

Since I feel injustice done here, I'd like to point out that harshit164 is a Vitess maintainer who is my go-to person of reference for these exact topics, and who is authoritative about this database technology.

I really appreciate your feedback. I'll pass on the documentation advice, it's good to have your user perspective.

I hear you on cut-over, and - it's indeed on our radar! I hope to bring good news.

Heh, and in the MySQL space, we use a "trivial" online schema schema migration (that has no actual schema changes) to avoid table bloat :)

Thank you! Please first see my comments to parent, as they describe how online schema change work within the same server; with PlanetScale branching, we do give you a development branch with which you can play as much as you want, without affecting production. Online schema change kicks in when you deploy your changes to production.

> the scope of migrations is limited to those where both old and new DDL are compatible with the currently running application.

You are absolutely correct, and that is the paradigm. Say, for example, you want to add a column, so you first run the migration that adds the column, and only afterwards can you deploy an application change that actually utilizes that column. Likewise if you want to DROP a column, you first deploy an app change that ceases to reference the column, and only then can you actually drop it.

This paradigm worked very well for the companies I worked with, and makes for both loose and tight coupling between code and database. It's loose when you have your test databases where you can deploy schema changes at will. It's loose in the sense you can take small steps at a time, each isolated from the other (e.g. ADD COLUMN does not require you to make any app changes _yet). Then, it's tight where you couple your code changes with the schema in your git repo. It's tight in that the app never gets too far from the database (normally one change away at any given time, per development branch).

> Being the migration asynchronous, I lose control of when to deploy changes to the application

Great point and absolutely on our radar.

> Not knowing exactly then the cut-over process is going to happen is also potentially a problem.

Again great point and on our radar. To be honest I previously moved away from caring about the exact cut-over time. We designed gh-ost to do just that: stall cut-over until the engineer/developer is happy to sit at their desk. OVer time, we found it was unnecessary. But absolutely there's use cases for both approaches.

> why not layer it on top of Git? ... Many orgs and integration tools already have similar workflows

Indeed. See my writeup on skeefree, which we developed at GitHub:

https://github.blog/2020-02-14-automating-mysql-schema-migra...

or watch my FOSDEM presentation:

https://www.youtube.com/watch?v=xyMKhL75Vyg

Yes, there are many tooling, but one of my frustrations is that they're, well, tooling. In Vitess and in PSDB, the database itself gives you the developer flow you expect. You can write a solution that integrates with GitHub, but then someone else uses BitBucket, or Phabricator, or whatever other framework they have.

I've been in the MySQL space for some 20 years now, 12 of which are active in open source. What frustrates me more than anything is how different companies have to come up with similar solutions to the same problems - but cannot afford to "just use" some existing tooling or framework, because it was build for a specific kind of infrastructure, or assumes this and that setup. Cloud or no cloud? Kubernetes or bare metal? DNS or proxies? Central service discovery or distributed configuration? And so on and on...

So many tooling written to compensate for funtionalities missing in the database. We all wished the database would _just do it_. As an engineer, here is my opportunity to write stuff into the database system, or into a framework that presents itself to the app as the database. To then be used by however users choose to, because they have a functionality they don't need to worry about, and can have less boilerplate code to fit it in their infrastructure.

Again, thank you for the questions.

I am estimating that your database space isn't MySQL, which is just fine of course. Reason I'm asking/guessing, is that in the MySQL space, online schema change toold have been around for over a decade and are the go-to solution for schema changes. A small minority of the industry, based on my understanding as a member of the community, uses other techniques such as rolling migrations on replicas etc., but the vast majority uses one of the common schema change tools:

- pt-online-schema-change - facebook's OSC - gh-ost

I authored the original schema change tool, oak-online-alter-table https://shlomi-noach.github.io/openarkkit/oak-online-alter-t..., which is no longer supported, but thankfully I did invest some time in documenting how it works. Similarly, I co-designed and was the main author for gh-ost, https://github.com/github/gh-ost, as part of the database infrastructure team at GitHub. We developed gh-ost because the existing schema change tools could not cope with our particular workloads. Read this engineering blog: https://github.blog/2016-08-01-gh-ost-github-s-online-migrat... to get better sense of what gh-ost is and how it works. I in particular suggest reading these:

- https://github.com/github/gh-ost/blob/master/doc/cheatsheet....

- https://github.com/github/gh-ost/blob/master/doc/cut-over.md

- https://github.com/github/gh-ost/blob/master/doc/subsecond-l...

- https://github.com/github/gh-ost/blob/master/doc/throttle.md

- https://github.com/github/gh-ost/blob/master/doc/why-trigger...

At PlanetScale I also integrated VReplication into the Online DDL flow. This comment is far too short to explain how VReplication works, but thankfully we again have some docs:

- https://vitess.io/docs/user-guides/schema-changes/ddl-strate... (and really see entire page, there's comparison between the different tools)

- https://vitess.io/docs/design-docs/vreplication/

- or see this self tracking issue: https://github.com/vitessio/vitess/issues/8056#issue-8771509...

Not to leave you with only a bunch of reading material, I'll answer some questions here:

> Can you elaborate? How? Do they run on another servers? Or are they waiting on a queue change waiting to be applied? If they run on different servers, what they run there, since AFAIK the migration is only DDL, there's no data?

The way all schema change tools mentioned above work is by creating a shadow aka ghost table on the same primary server where your original table is located. By carefully both copying data from original table as well as tracking ongoing changes to the table (whether by utilizing triggers or by tailing the binary logs), and using different techniques to mitigate conflicts between the two, the tools populate the shadow table with up-to-date data from your original table.

This can take a long time, and requires an extra amount of space to accommodate the shadow table (both time and space are also required by "natural" ALTER TABLE implementations in DBs I'm aware of).

With non-trigger solutions, such as gh-ost and VReplication, the tooling have almost ocmplete control over the pace. Given load on the primary server or given increasing replication lag, they can choose to throttle or completely halt execution, to resume later on when load has subsided. We have used this technique specifically at GitHub to run the largest migrations on our busiest tables at any time of the week, including at peak traffic, and this has show to pose little to no impact to production. Again, these techniques are universally used today by almost all large scale MySQL players, including Facebook, Shopify, Slack, etc.

> who will throttle, the migration? But what is the migration? Let's use my example: a column type change requires a table rewrite. So the table rewrite will throttle, i.e. slow down? But where is this table rewrite running, on the main server (apparently not) or on a shadow server (apparently either since migrations have no data)? Actually you mention "when your production traffic gets too high". What is "high", can you quantify?

The tool (or Vitess if you will, or PlanetScale in our discussion) will throttle based on continuously collecting metrics. The single most important metric is replication lag, and we found that it predicts load more than any other matric, by far. We throttle at 1sec replication lag. A secondary metric is the number of concurrent executing threads on the primary; this is mroe improtant for pt-online-schema-change, but for gh-ost and VReplication, given their nature of single-thread writes, we found that the metric is not very important to throttle on. It is also trickier since the threshold to throttle at depends on your time of day, particular expected workload etc.

> We run customers that do dozens to thousands of transactions per second. Is this high enough?

The tooling are known to work well with these transaction rates. VReplication and gh-ost will add one more transaction at a time (well, two really, but 2nd one is book-keeping and so low volume that we can neglect it); the transactions are intentionally kept small so as to not overload the transaction log or the MVCC mechanism; rule of thumb is to only copy 100 rows at a time, so exepect possibly millions of sequential such small transaction on a billion row table.

> Will their migrations ever run, or will wait for very long periods of time, maybe forever?

Some times, if the load is so very high, migrations will throttle more. At other times, they will push as fast as they can while still keeping to low replication lag threshold. In my experience a gh-ost or vreplication migration is normally good to run even on the busiest times. If a database system is such that it _always_ has substantial replication lag, such that a migration cannot complete in a timely manner, then I'd say the database system is beyond its own capacity anyway, and should be optimized/sharded/whatever.

> How is this possible? Where the migration is running, then? A shadow table, shadow server... none?

So I already mentioned the ghost table. And then, SELECTs are non blocking on the original table.

> What's cut-over?

Cut-over is what we call the final step of the migration: flipping the tables. Specifically, moving away your original table, and renaming the ghost table in its place. This requires a metadata lock, and is the single most critical part of the schema migration, for any tooling involved. This is where something as to give. Tooling such as gh-ost and pt-online-schema-change acquire a metadata lock such that queries are blocked momentarily, until cut-over is complete. With very high load the app will feel it. With extremely high load the database may not be able to (or may not be configured to) accommodate so many blocked queries, and app will see rejections. For low volume load apps may not even notice.

I hope this helps. Obviously this comment cannot accommodate so much more, but hopefully the documentation links I provided are of help.

PlanetScale engineer and Vitess contributor here. As a brief history, Vitess was originally developed as an open source project in YouTube, and then handed over to CNCF.

PlanetScale was created by authors of Vitess, and employs a team of Vitess developers and contributors. There are Vitess contributors outside PlanetScale, from companies such as Slack, Square, Nozzle, Pinterest and others (apologies to people/companies not mentioned).

Just to explain how we work, if you ever come by to KubeCon, you're likely to find our Vitess booth. It is normally staffed by PlanetScale engineers. But in that booth we wear the Vitess hat, not the PlanetScale hat. We want to respect Vitess for being its own open source project, which we share with our community friends from different companies.

Then again, as we employ many Vitess contributors, including the Vitess project leader Deepthi Sigireddi, we do pride in driving this OSS project from within PlanetScale. As we wear our PlanetScale hat, we give credit to the Vitess technology our product is based on.

Hi, engineer at PlanetScale and maintainer for Vitess here. Appreciate your thoughtful comment, a couple answers:

> is something that sounds like I could do ... from Git platforms themselves

Git is very bad at analyzing SQL diffs. It can show you the textual diff between two CREATE TABLE statements, but it will not know what it takes to get you from _here_ to _there_. It has many parsing issues, like capturing irrelevant columns due to trailing commas. Or, for example, if you change a column's data type as per your suggestion, Git cannot differentiate that from a complete drop and recreation of the column. It just doesn't have insight into how SQL works/parses. And most importantly, it is unable to provide the actual operational diff you're seeking: the ALTER TABLE statement to take you from state A to state B. At this point I just want to give a shout out to skeema [0] and its underlying library tengo [1], which tackled this issue in a git-like manner for MySQL dialects/flavors.

> Many DDL changes will take different amount of locks on rows or tables, which may cause some queuing and even lock storms in the presence of incoming traffic.

This is indeed one of our main premises. Online schema changes in PlanetScale (and based on Vitess) will:

- Run concurrently to your production traffic

- Will automatically throttle when your production traffic gets too high, and in particular taking care not to affect replication lag

- Will run completely lockless throughout the migration, up to the cut-over point, where locking is required

- At cut-over point, will only cut-over when it predicts smooth operation (i.e. when satisfied that its own backlog for cutting over is short enough that lock time is minimal)

- To top it all, PS also manages the lifecycle of the Online DDL, such as service discovery, scheduling, error handling, throttling (mentioned), cleanup and garbage collection and more.

I just want to clarify the above is based on proven technologies, widely used in the MySQL community, which I was fortunate enough to be involved in for the past years. These run at scale for the largest deployment in the world today. We keep evolving Online DDL with more to come.

Please also see these docs on the Vitess website: [2]

[0]: https://www.skeema.io/

[1]: https://github.com/skeema/tengo

[2]: https://vitess.io/docs/user-guides/schema-changes/#the-schem...

(Comment edited for formatting and grammar)

Good take. "Fortunately" if your database becomes a monster you'll need to run some schema changes to clean it up and optimize :)

In seriousness, if we learn anything from code deployments, it's that we should aim for easy and quick deployments, and allow the developers to rollback (undeploy their code).

Today, there is a "natural" mechanism today to push back on database development velocity, which is _time_. It can take hours or more to alter a large table. Can we at least make it possible to quickly revert a change? Here's some work we're pushing in OSS Vitess: https://vitess.io/docs/user-guides/schema-changes/revertible...

(Engineer at PlanetScale)