PlanetScale offers undo button to reverse schema migration without losing data

Distributed transaction database biz PlanetScale has introduced an "undo" button it says can reverse schema changes, allowing devs to avoid embarrassing disasters by reverting to the original design without losing data within a 30-minute window. Based on YouTube-developed distributed relational database Vitess, PlanetScale is …

[Nate Amsden]

sounds neat

But performance is usually much more important than schema changes, I mean people have been doing schema changes for a long time and generally they get tested quite a bit and it's a known problem to deal with if a schema change goes badly.

I had one situation at a company ~15 years ago with MySQL, we had to do a schema change but the production MySQL server lacked the disk space needed to re-write out the entire table again(at the time maybe it was 100-300GB I don't remember). Fortunately it wasn't our OLTP database(which was Oracle) but rather an data store that didn't have to be updated too often(the data inside was job search results). Ironically enough I was trying to justify the need for a SAN at the time to leverage shared storage and make it available to more hosts. I already had a "demo" SAN installed on site, though I had it installed at our HQ not at our co-location. I did this purposely because the vendor said pretty much every customer that deployed their system ended up buying the demo unit because they had to rush it into production for some critical need, I wanted to avoid that trap.

So here we are, critical need for this SAN. So I came up with a crazy plan. We stopped the app(s) that updated the MySQL database, we may of even put the database in readonly mode I don't recall. The app continued to function fine, users were unaffected other than newer things weren't getting into the DB(the only thing that updated the DB was the app itself, users could never update that DB).

I took a USB drive, drove it to the data center, hooked it up, copied the DB over, drove it back to the office, copied it onto our demo SAN, did the schema migration, then copied the DB back to USB, drove it back to the data center and copied it back into place again. I assume we had to take an outage on that MySQL DB for copying the data back into place, but really wasn't a big deal. Fortunately our colo was only about a 45min drive away. These days my colo is 2,000 miles away.

Manager was convinced at that point we really could benefit from shared storage so we bought that demo unit after all and had the vendor move it to our colo.

[Nate Amsden]

Re: sounds neat

thought about it more and I'm sure I would of pointed the app against the read only standby DB, then I could blow away the primary DB and restore the updated data, then update app to point to the primary DB, restart it, then rebuild the standby. basically just a few seconds of downtime.

[Anonymous Coward]

Sound handy in a pinch, but never tell your boss about it.

This is the sort of thing that could save the day once in a while, but it could also get organizations in trouble as it requires a fairly deep insight into the underlying data and your implementation to ensure it's safe. If management starts to think that they can push poorly planned schema and business rule changes on a regular basis and wave a magic wand to "fix" it they are gonna blow their foot off. A feature that gives a PHB the idea their entitled to make bad/risky decisions is dangerous indeed.

For a 30 min window to work, you probably need to have a tested plan B on deck to handle the exceptions or cleanly shunt operations. Without that a 30min window is just someone whistling the Jeopardy theme in your ear while you try to improvise an unplanned fix as a tractor slowly rolls over you.