3 ms·

Too much here to deconstruct in a comment, but some choice responses: * How does the author suggest non-blocking DDL is actioned? You'd have to snapshot your

Too much here to deconstruct in a comment, but some choice responses: * How does the author suggest non-blocking DDL is actioned? You'd have to snapshot your entire table using e.g. a similar method to snapshot isolation at the row level to allow concurrent access. Accessing the table, while in transition, will lead to inconsistent output data sets. * If your migration is 'days-long', then you are doing something wrong. Try creating a new table (instant) then ETL'ing your data over from the original, dropping and renaming. It will be much faster AND it addresses accessibility on the table (no table lock). * You can schedule migrations if using a tool like Flyway by doing this in code. If you insist on using a database product, you could use (for MSSQL) SQL Agent, or cron for anything on Linux (run a SP), etc. * It is already possible to revert a migration if you are using Oracle, using Oracle Flashback. For other DBs, you can still revert a migration if you are using your deployment tool properly i.e. redeploy an earlier version of your schema and drop all other objects. You'll have to deal with your data, of course. This would have been a more interesting post if a possible solution to all the points was posited.

shlomi-noach 4y ago
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/managed-online-schema-changes/ https://vitess.io/docs/14.0/user-guides/schema-changes/manag... and https://docs.planetscale.com/concepts/nonblocking-schema-changes 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-migration-without-losing-data 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.

shlomi-noach 4y ago

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/managed-online-schema-changes/ https://vitess.io/docs/14.0/user-guides/schema-changes/manag... and https://docs.planetscale.com/concepts/nonblocking-schema-changes 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-migration-without-losing-data 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.