4 ms·

I don't know anything about databases. How do you roll back a significant schema change?

by crummy 7y ago

abraae 7y ago
For a complex system, with many tables, rolling back a large change is very difficult.
minxomat 7y ago
In your migration file(s) you have both forward and backward steps. If operations are reducers or otherwise destructive, the forward step backs that data up to separate "recovery" tables. The n+1 migration may delete recovery tables. That's how I do it most of the time. Sometimes with full snapshots. Ymmv
twic 7y ago
I believe the strategy described avoids needing to do that. You start by releasing a version of the software which can run with the old or new schema, then you apply the new schema, then you release a version of the software which actually uses the new schema. If you discover a problem at that point, you roll back to the previous version of the software, but leave the schema as it is. You then have time to figure out what to do, which may involve changing the schema again, but that will be as a forward change, rather than as a rollback. Some migration tools do support rollback scripts for schema changes, but unless you're actually testing these before release (deploy the new version in staging, accumulate representative data in the new schema, roll back the schema, deploy an old version of the app, test that it is doing the right thing), then they aren't really something you can rely on in production.
tybit 7y ago
It really depends on the task but the general pattern is to split it up into separate steps, each of which is either low risk and/or easily reversible. Lets say you want to add a new non nullable column with foreign keys, to replace an old non nullable column of foreign keys to a different table that’s obsolete and needs to be deleted. 1) update the code to be ok with a new nullable column. Rollback: deploy previous version of code. 2) create the new column in DB with it’s desired constraint, but make it nullable. Roll back: delete the column. 3) have the code start populating the new column as well as the old. Rollback: deploy previous version of code. 4) start backfilling historical entries with the new column. Rollback: you can’t roll this back! 5) make new column non nullable. Rollback: make it nullable. 6) update code to read from new column, continuing to write to both. Rollback: deploy previous version of code. 7) make old column nullable. Rollback: you can’t roll this back! 8) stop writing to old column. Rollback: deploy previous version of code. 9) once you’re satisfied the old column is no longer used and the version of code from the previous step will never be deployed again, drop the old column. Rollback: you can’t roll this back! 10) rename the obsoleted table and see if anything breaks. Rollback: rename it back to its original name. 11) delete the obsoleted renamed table. Rollback: you can’t roll this back!
evanelias 7y ago
A few strategies used by large companies, e.g. Facebook where the author worked for some time: * Use external online schema change tooling which operates on a shadow table, so the tooling can be interrupted without affecting the original table. (Generally all of the open source MySQL online schema change tools work this way.) * Use declarative schema management (e.g. tool operates on repo of CREATE TABLE statements), so that humans never need to bother writing "down" migrations. Want to roll something back? Use `git revert` to create a new commit restoring the CREATE TABLE to its previous state, and then push that out in your schema management system. (Full disclosure, I spend my time developing an open source system in this area, https://skeema.io https://skeema.io) * Ensure that your ORMs / data access layers don't interact with brand new columns until a code push occurs or a feature flag is flipped.
- aeorgnoieang 7y ago
  Skeema looks cool, and I've long been a fan of a similar tool that's specific to SQL Server – DB Ghost. I even implemented some pretty handy additional automation on top of it at various companies at which I worked. There were tho, inevitably, a small number of schema changes that needed to be encapsulated as 'migrations', and it could be arbitrarily complex and difficult to write, and test, them in a way that supported automatic rollbacks.

I don't know anything about databases. How do you roll back a significant schema change?

by crummy 7y ago

I don't know anything about databases. How do you roll back a significant schema change?

abraae 7y ago

For a complex system, with many tables, rolling back a large change is very difficult.

minxomat 7y ago

In your migration file(s) you have both forward and backward steps. If operations are reducers or otherwise destructive, the forward step backs that data up to separate "recovery" tables. The n+1 migration may delete recovery tables. That's how I do it most of the time. Sometimes with full snapshots. Ymmv

twic 7y ago

I believe the strategy described avoids needing to do that. You start by releasing a version of the software which can run with the old or new schema, then you apply the new schema, then you release a version of the software which actually uses the new schema. If you discover a problem at that point, you roll back to the previous version of the software, but leave the schema as it is. You then have time to figure out what to do, which may involve changing the schema again, but that will be as a forward change, rather than as a rollback. Some migration tools do support rollback scripts for schema changes, but unless you're actually testing these before release (deploy the new version in staging, accumulate representative data in the new schema, roll back the schema, deploy an old version of the app, test that it is doing the right thing), then they aren't really something you can rely on in production.

tybit 7y ago

It really depends on the task but the general pattern is to split it up into separate steps, each of which is either low risk and/or easily reversible. Lets say you want to add a new non nullable column with foreign keys, to replace an old non nullable column of foreign keys to a different table that’s obsolete and needs to be deleted. 1) update the code to be ok with a new nullable column. Rollback: deploy previous version of code. 2) create the new column in DB with it’s desired constraint, but make it nullable. Roll back: delete the column. 3) have the code start populating the new column as well as the old. Rollback: deploy previous version of code. 4) start backfilling historical entries with the new column. Rollback: you can’t roll this back! 5) make new column non nullable. Rollback: make it nullable. 6) update code to read from new column, continuing to write to both. Rollback: deploy previous version of code. 7) make old column nullable. Rollback: you can’t roll this back! 8) stop writing to old column. Rollback: deploy previous version of code. 9) once you’re satisfied the old column is no longer used and the version of code from the previous step will never be deployed again, drop the old column. Rollback: you can’t roll this back! 10) rename the obsoleted table and see if anything breaks. Rollback: rename it back to its original name. 11) delete the obsoleted renamed table. Rollback: you can’t roll this back!

evanelias 7y ago

A few strategies used by large companies, e.g. Facebook where the author worked for some time: * Use external online schema change tooling which operates on a shadow table, so the tooling can be interrupted without affecting the original table. (Generally all of the open source MySQL online schema change tools work this way.) * Use declarative schema management (e.g. tool operates on repo of CREATE TABLE statements), so that humans never need to bother writing "down" migrations. Want to roll something back? Use `git revert` to create a new commit restoring the CREATE TABLE to its previous state, and then push that out in your schema management system. (Full disclosure, I spend my time developing an open source system in this area, https://skeema.io https://skeema.io) * Ensure that your ORMs / data access layers don't interact with brand new columns until a code push occurs or a feature flag is flipped.

aeorgnoieang 7y ago
Skeema looks cool, and I've long been a fan of a similar tool that's specific to SQL Server – DB Ghost. I even implemented some pretty handy additional automation on top of it at various companies at which I worked. There were tho, inevitably, a small number of schema changes that needed to be encapsulated as 'migrations', and it could be arbitrarily complex and difficult to write, and test, them in a way that supported automatic rollbacks.